Exportar (0) Imprimir
Expandir Tudo
Expandir Minimizar

Project REAL: Práticas de design do ETL de inteligência comercial

Publicado em: 1 de setembro de 2005
Por Erik Veerman

Artigo técnico sobre o SQL Server
Revisores técnicos: Donald Farmer, Grant Dickinson
Parceiro: Intellinet
Aplica-se a: SQL Server 2005

Resumo: leia sobre o SSIS (SQL Server 2005 Integration Services) em ação. Usado na implementação de referência de inteligência comercial chamada de Project REAL, o SSIS demonstra um processo de ETL (extração, transformação e carregamento) de alto volume baseado em dados reais. Essa solução de ETL oferece suporte a um data warehouse de vários terabytes e contém mecanismos representativos de processamento, configuração e administração de dados de uma grande iniciativa de warehouse.

Nesta página

Introdução
Perfil dos dados
Ambiente de desenvolvimento do SSIS
Conclusão

Introdução

Aplicativos de BI (inteligência comercial) bem-sucedidos precisam ser executados em ferramentas sólidas. O processo de criação desses aplicativos também é facilitado quando os desenvolvedores e administradores possuem uma base de conhecimento associada sobre como executar uma implementação com êxito. Em resumo, informações sobre práticas recomendadas. Por meio do Project REAL, a Microsoft e vários de seus parceiros estão descobrindo práticas recomendadas para aplicativos de BI que são baseados no Microsoft® SQL Server™ 2005. No Project REAL, estamos fazendo isso ao criar implementações de referência baseadas em cenários reais de clientes. Isso significa que dados de clientes são trazidos internamente e usados para superar os mesmos problemas que esses clientes enfrentam durante a implantação. Esses problemas incluem:

  • Design de esquemas, tanto de esquemas relacionais quanto os usados no Analysis Services.

  • Implementação de processos de ETL (extração, transformação e carregamento) de dados.

  • Design e implantação de sistemas de front-end de clientes, para geração de relatórios e análise interativa.

  • Dimensionamento de sistemas para produção.

  • Gerenciamento e manutenção dos sistemas em uma base contínua, incluindo atualizações incrementais dos dados.

Ao trabalhar com cenários reais de implantação, obtemos um entendimento completo de como trabalhar com as ferramentas. Nosso objetivo é abordar toda a gama de problemas que uma grande empresa enfrenta durante uma implantação de verdade.

Este artigo se concentra no design do ETL do SSIS (SQL Server Integration Services) para o Project REAL. Esse design é baseado na arquitetura de ETL da Barnes & Noble, criada a partir do zero para usar o SSIS e a primeira implementação de ETL do SSIS em ambiente de produção. Como a solução não é um design atualizado baseado no DTS (Data Transformation Services) ou em outra ferramenta de ETL, você verá que muitas das abordagens usadas são diferentes da arquitetura típica de ETL vista no DTS. O objetivo dessa solução era pensar criativamente e criar um processo de ETL que pudesse modelar uma prática recomendada para o design geral de ETL, usando a nova arquitetura de aplicativo do SSIS. No decorrer deste white paper, explicaremos as decisões de design que foram feitas para cada cenário e os detalhes da implementação do esforço do SSIS para o Project REAL.

Para obter uma visão geral do Project REAL, consulte o white paper chamado Project REAL: Technical Overview (em inglês). O Project REAL produzirá vários documentos, ferramentas e exemplos durante sua vida útil. Para localizar as informações mais recentes, visite o site a seguir:

http://www.microsoft.com/sql/bi/ProjectREAL (em inglês)

O Project REAL é um esforço cooperativo entre a Microsoft e vários de seus parceiros na área de BI. Esses parceiros incluem: Apollo Data Technologies, EMC, Intellinet, Panorama, Proclarity, Scalability Experts e Unisys. O cenário comercial do Project REAL e o conjunto de dados de origem foram generosamente fornecidos pela Barnes & Noble.

Observação: este white paper é um esboço. Ele contém as práticas recomendadas baseadas em nossas experiências de trabalho com as compilações CTP (Community Technology Preview) anteriores do SQL Server 2005. Este white paper apresenta informações precisas na data de sua publicação. A funcionalidade do produto descrita neste documento poderá ser alterada ou informações sobre práticas recomendadas melhores poderão ser desenvolvidas posteriormente.

Objetivos de ETL do Project REAL

Em qualquer sistema de BI (inteligência comercial), o processamento de ETL existe para oferecer suporte a requisitos analíticos e de geração de relatórios. O ETL deve ser implementado com essa função de suporte em mente. Isso não reduz a importante função que ele desempenha, pois os dados a serem relatados serão tratados diretamente pelo processamento de ETL. Alguns aspectos do ETL incluem prazo, desempenho e precisão do processamento. Igualmente importantes são o suporte, o gerenciamento, a escalabilidade e a extensibilidade do design do ETL. Os sistemas reais sempre têm aspectos desconhecidos e anomalias que afetam o ETL. Esses problemas requerem que o processamento do ETL possa tratar de alterações facilmente e oferecer suporte ao objetivo final de um sistema estável.

Para o Project REAL, essas áreas-chave levaram a várias metas essenciais para o design do ETL, da seguinte maneira:

  • Administração do ETL. Para fornecer suporte administrativo, foi implementado um design que permite o controle e a geração de relatórios de metadados do ETL. Esse elemento permite uma imagem clara do estado do processamento para fins informativos e de solução de problemas, ajudando a isolar e a solucionar problemas.

  • Configuração dinâmica. Esse objetivo foi desenvolvido para oferecer suporte a um sistema empresarial na publicação e distribuição de componentes principais. Ele também envolvia a adaptabilidade do design para alterações de requisitos comerciais e técnicos e um ambiente adequado para uma grande equipe de suporte e desenvolvimento.

  • Integração de plataformas. Isso envolvia a criação de uma solução que interagisse com as várias camadas de uma solução de BI, incluindo segurança, infra-estrutura, estruturas relacionais e de OLAP e ferramentas de relatório e de análise que coletam os dados.

  • Desempenho. A atenção nos problemas de desempenho era essencial para a solução Project REAL, devido ao volume de dados processados e gerenciados no warehouse. No total, os dados atingiam vários terabytes.

Resumo

Este artigo destaca vários princípios específicos de design, algumas das lições aprendidas através do processo de design e uma visão geral da arquitetura da solução. O documento fornece uma referência mais ampla e inclui alguns pontos de destaque do design da solução. À medida que novas soluções forem amadurecidas e desenvolvidas na plataforma do SQL Server 2005, mais material detalhado e abrangente será publicado. Os documentos futuros se aprofundarão nos conceitos, no design de desempenho de ajuste fino e provavelmente demonstrarão alguns exemplos de designs melhores. Este documento fornece uma referência sólida para o ETL de BI baseado no SSIS. Ele pode ser uma ferramenta usada por arquitetos de BI durante o planejamento e o desenvolvimento de redesigns, atualizações e novas implementações do ETL.

A funcionalidade do SSIS transcende o manuseio do ETL. Ela fornece mais capacidades para integração de sistemas, gerenciamento de informações e transformação de dados. Este artigo abrange apenas alguns dos aspectos do produto, cobrindo partes principais do SSIS relacionadas ao processamento do ETL.

Estrutura de tópicos

Os exemplos deste documento estão diretamente relacionados à implementação do Project REAL. Cada exemplo foi selecionado porque destaca aspectos particulares do SSIS à medida que se aplicam ao processamento do ETL. Esses exemplos demonstram alguns dos objetivos descritos acima e cenários comuns do ETL, incluindo:

  • Ambiente de desenvolvimento do SSIS

  • Auditoria e log do ETL

  • Design de configuração dinâmica para a administração de propriedades e de fontes de dados

  • Processamento de dimensão para cenários padrão e exclusivos

  • Processamento da tabela de fatos de associações de dimensões e atualizações da tabela de fatos

  • Design da arquitetura de processamento de dados

  • Técnicas de otimização do processamento de dados

Todos esses exemplos são implementados na solução do SSIS do Project REAL que foi executada com êxito usando 90 dias de dados do volume da produção diária e semanal. Os dados processados incluem uma parte da produção relacionada aos feriados de fim de ano e se estendem por dois anos para comprovar a estabilidade e a confiabilidade desse exemplo como real. Conforme mencionado anteriormente, a solução "real" do SSIS na qual o ETL do Project REAL é baseado está em produção desde novembro de 2004 na Barnes & Noble. Muitos desses exemplos e pacotes estão ou estarão disponíveis para uma revisão detalhada das particularidades de implementação. Essas informações serão publicadas no site do Project REAL no futuro. Além disso, os pacotes serão apresentados em conferências da indústria para demonstrar o design do SSIS do Project REAL.

Perfil dos dados

O ETL do Project REAL representa os requisitos de extração para muitos cenários comerciais, embora esse projeto de referência esteja focalizado em um sistema de varejo. Como é comum no processamento de ETL, existe um conjunto de requisitos de processamento diários, em que alterações e adições aos dados de origem são extraídas e processadas pelo sistema diariamente. Além disso, um conjunto de processos semanais é executado para gerenciar o detalhamento do instantâneo do inventário semanal para o modelo dimensional. O perfil de dados inclui extrações diárias de transações, atualizações de dimensão e gerenciamento semanal das tabelas de fatos de inventário.

Tabelas de fatos

O modelo relacional é um design de esquema em estrela padrão com dois tipos principais de tabelas de fatos: vendas e inventário. As transações de vendas são coletadas diariamente e representam compras detalhadas de produtos nas lojas de varejo, incluindo pedidos pela Web. Vários milhões de transações são processadas e precisam ser adicionadas à estrutura de fatos de vendas, com uma maioria dos registros de vendas sendo provenientes das vendas do dia anterior. Além disso, existe uma pequena quantidade de vendas que são novas para o sistema, mas representam vendas históricas que chegam atrasadas. Todas as vendas são controladas em um detalhamento diário, e o processo de ETL é criado para permitir o processamento múltiplo dos dados durante todo o dia. As métricas principais controladas giram em torno da quantidade e do valor da venda de cada transação em nível de item.

A estrutura do inventário é criada como uma tabela de fatos padrão do instantâneo no detalhamento semanal, onde as posições do inventário são diariamente atualizadas, mas gerenciadas historicamente em incrementos semanais. O inventário é controlado no detalhamento da loja e do centro de distribuição até o nível de item, o que produz dezenas de milhões de linhas semanalmente e requer milhões de alterações em uma base diária. O objetivo principal do relatório é ver as tendências do inventário e de vendas e evitar cenários de falta de estoque. Portanto, além das quantidades padrão "em mãos", um fato de "dias em estoque" é controlado e identifica o número de dias durante o qual um item existiu no estoque para o detalhamento semanal na loja ou nos locais do centro de distribuição. Quando uma semana é encerrada, os níveis do inventário são duplicados e inicializados para o início de uma nova semana, um processo intensivo e requerido pelo ETL.

Tabelas de dimensão

As dimensões que oferecem suporte às tabelas de fatos têm alguns aspectos exclusivos que tornam o design interessante e destacam vários aspectos do SSIS. A dimensão do produto tem vários milhões de membros e contém atributos padrão de alteração e de histórico, mas também requer que o atributo controlado historicamente e as alterações na hierarquia iniciem apenas após a ocorrência de uma venda. A maneira como isso impacta o ETL é discutida neste documento. Além da dimensão do produto, várias outras dimensões típicas do varejo estão envolvidas. Devido à maneira como os dados de origem são gerenciados, todas as dimensões requerem que, se um membro da dimensão estiver faltando durante o processamento do fato, um registro de espaço reservado com a chave comercial relevante seja adicionado à dimensão até que a origem total da dimensão esteja disponível para uma atualização completa. Isso é chamado de membro inferido e tem impacto no processamento do ETL. Algumas das dimensões também são originadas diretamente da transação principal ou de tabelas de origem do inventário e requerem tratamento especial para suas adições às tabelas de fatos.

Dada a complexa capacidade de dimensionamento, o processo global do SSIS demonstra a flexibilidade e a escalabilidade da ferramenta e tem como objetivo fornecer um ponto de referência forte para muitos designs de ETL criados na plataforma do SQL 2005.

Ambiente de desenvolvimento do SSIS

O Business Intelligence (BI) Development Studio é criado na plataforma do Microsoft Visual Studio® 2005 (VS). Embora isso seja uma alteração substancial da interface do usuário baseada no Enterprise Manager do SQL Server 2000, os recursos do BI Development Studio são implementados de uma maneira que permitirá uma transição mais fácil para os DBAs (administradores de banco de dados), arquitetos de dados e desenvolvedores. O fator intimidante próprio do VS foi abrandado por um perfil simplificado da interface do usuário que focalizava as necessidades do desenvolvedor de BI.

No Project REAL, uma única solução de BI hospeda o projeto de ETL recorrente. Esse projeto tem todos os pacotes do SSIS sendo executados em uma base incremental. A Figura 1 mostra o Solution Explorer com fontes de dados e pacotes do SSIS compartilhados. Os pacotes são nomeados para indicar sua função. Existem vários tipos de pacotes. O primeiro conjunto e o mais simples é o pacote de dimensão. Cada dimensão (originada de sua própria entidade de origem) possui seu próprio pacote. Os pacotes de tabelas de fatos têm um design semelhante, exceto que eles são nomeados de acordo com sua respectiva programação recorrente (diária ou semanal). Um pacote diferente existe para as programações diária e semanal, desde que a lógica comercial seja distinta entre esses grupos. Por exemplo, o pacote Fact_Daily_Store_Inventory executa tarefas de processamento diferentes do pacote semanal equivalente, o pacote Fact_Weekly_Store_Inventory, embora os dois afetem a mesma tabela de fatos.

Figura 1

Figura 1

Para a coordenação do processamento, um terceiro conjunto de pacotes, chamados de pacotes de grupo de carregamento, é exibido. Esses pacotes não contêm nenhuma lógica comercial de processamento e são usados para tratar da coordenação do fluxo de trabalho do pacote de dimensão e de fatos descrito anteriormente, bem como alguma auditoria e capacidade de inicialização do aplicativo. A Figura 2 mostra um exemplo de um pacote de grupo de carregamento. A tarefa Execute Package é usada para executar pacotes filho de dimensão e de fatos e o fluxo de controle é usado para tratar do fluxo de trabalho e da paralelização de algumas tarefas de processamento. Outras tarefas estão incluídas que ajudam na auditoria e na capacidade de reinicialização do processo. Elas são descritas em mais detalhes mais adiante neste documento.

Figura 2

Figura 2

Integração do controle de origem

Uma vantagem oferecida pelo BI Development Studio baseado no VS é a integração com o controle de origem. Embora não limitado ao Microsoft Visual Source Safe® (VSS), o ambiente de desenvolvimento compartilhado para o REAL usou o VSS para ajudar a evitar “deadlocks” de desenvolvimento quando dois ou mais desenvolvedores tentavam trabalhar nos mesmos processos ao mesmo tempo. A funcionalidade padrão do VSS inclui histórico e backup, funcionalidade de bloqueio para pacotes, fontes de dados ou arquivos dos quais foi feito check-in ou chek-out e comparação de versões para destacar diferenças. A maioria dos recursos de controle de origem estão localizados no menu File | Source Control, conforme mostrado na Figura 3.

Figura 3

Figura 3

Após a implementação do controle de origem com o Visual SourceSafe ou com algum outro aplicativo de controle de origem, muitos dos recursos relacionados a objetos no BI Development Studio podem ser acessados com um simples clique com o botão direito do mouse no pacote ou na fonte de dados, conforme mostrado na Figura 4.

Figura 4

Figura 4

Práticas de nomenclatura e de layout

A convenção usada para layout de pacotes, nomeação de tarefas e anotações é digna de nota. Para manter a consistência, os nomes de todas as tarefas e transformações começam com uma abreviatura de três ou quatro letras do tipo de tarefa ou transformação, seguida por uma descrição de três ou quatro palavras da função do objeto. Isso é de grande ajuda para as funções de auditoria e log, pois os detalhes do log são controlados pelo nome do objeto. O layout do pacote geralmente flui para baixo primeiro e, em seguida, para a direita à medida que as tarefas ou transformações se separam dos fluxos de controle e de dados principais. As anotações ajudam a documentar os pacotes, descrevendo cada tarefa em mais detalhes.

Figura 5

Figura 5

Auditoria e log do ETL

Integradas com o design da estrutura do pacote por meio dos pacotes pai de fluxo de trabalho e dos pacotes filho estão etapas de auditoria personalizadas que controlam detalhes de execução de alto nível. Isso inclui horários de início, fim e de falha de pacotes, bem como contagens de linhas que ajudam na validação de volumes de dados e no processamento. O SSIS nativamente fornece log de execução detalhado de pacotes para uma variedade de tipos de provedor de logs. Essas entradas detalhadas do log são acionadas por eventos e desnormalizadas no destino do provedor selecionado. Por exemplo, se um banco de dados for escolhido como o provedor de logs, todas as entradas do log serão inseridas em uma única tabela. Um evento é o ponto de referência do mecanismo durante a execução: em aviso, em validação, em execução, em pré-execução, em pós-execução, etc. A Figura 6 destaca o seletor de detalhes de eventos do log.

Figura 6

Figura 6

Cada registro em um evento de log está associado à identificação da execução do pacote associado no qual a tarefa ou transformação foi executada. Essa identificação da execução é uma GUID gerada exclusivamente a cada vez que um pacote é executado. Como você pode imaginar, quando o log do SSIS está ativado no detalhamento mais baixo, ele pode produzir centenas, se não milhares, de entradas para cada pacote. O log do SSIS fornece bons detalhes para fins de solução de problemas. No entanto, por si só essas informações são difíceis de coletar e seguir sem um conhecimento íntimo de eventos do SSIS e uma maneira limpa de mapear a identificação da execução do pacote para um pacote específico executado pelo mecanismo.

No Project REAL, o objetivo era aperfeiçoar o provedor de logs interno com alguma auditoria de execução específica relacionada ao BI, mas também usar os recursos de log para relatórios detalhados. A estrutura da auditoria tinha como alvo os seguintes recursos:

  • Associação e identificação de pacotes e de grupos de carregamento.

  • Adições de colunas de linhagem às estruturas do warehouse.

  • Auditoria de validação da contagem de linhas.

  • Relatório do processamento de ETL com a habilidade de detalhamento.

Controle de pacotes e de grupos de carregamento

Entender a coordenação dos pacotes usados para um processo de ETL é crítico para o suporte e a administração de uma solução de BI. Portanto, uma tabela de controle de nível superior foi estabelecida para habilitar as associações de pacotes relacionados executados em conjunto. Em segundo lugar, uma tabela também foi criada no detalhamento de execução de um único pacote. Embora isso possa parecer levemente redundante com os recursos de log do SSIS, permite um mapeamento direto entre a identificação da execução do provedor de logs interno com o nome do pacote e a identificação do processo do grupo do fluxo de trabalho de execução de nível superior. Como a tabela de auditoria de pacote contém apenas um único registro para cada execução de um pacote, ela também simplifica a geração de relatórios e permite recursos de relatórios detalhados.

A auditoria de aplicativo do Project REAL é implementada principalmente com a tarefa Execute SQL. Para os pacotes do fluxo de trabalho, a primeira e a última etapas do fluxo de controle gerenciam a auditoria do grupo de carregamento. Neste pacote de grupo de carregamento de exemplo, você observará que a primeira e a última etapas são destacadas no fluxo de controle que trata desse processo.

Figura 7

Figura 7

Em um nível inferior ao de auditoria do fluxo de trabalho do pacote está o controle de nível do pacote, que também contém um design semelhante usando a primeira e a última etapas do fluxo de controle. Como o pacote do grupo de carregamento também é um pacote, você pode ver, na Figura 7, que a segunda e a penúltima etapas também estão auditando tarefas Execute SQL. Em um pacote de tabela de dimensão ou de fatos, essas são a primeira e a última etapas.

Outro aspecto principal da auditoria é a identificação rápida de erros. Um recurso bom do SSIS é o conceito de Fluxos de controle do manipulador de eventos. Eles estão disponíveis na terceira guia da interface do usuário do pacote. O manipulador de eventos OnError é usado para esse fim e definido no nível do pacote, que interceptará qualquer erro de pacote que apareça.

Figura 8

Figura 8

Além disso, um conjunto de variáveis de pacote é usado para controlar metadados entre o banco de dados e os pacotes por meio dessas tarefas Execute SQL. As principais variáveis usadas são as variáveis do sistema system::PackageExecutionID e system::PackageName e a variável do usuário user::ETL_Load_ID, que é o identificador criado no banco de dados e passado de volta para os pacotes. Essas variáveis também são passadas do pacote pai para os pacotes filho usando o recurso de configuração de variável pai do SSIS.

Adições de linhagem ao warehouse

O identificador de lotes, ETL_Load_ID, une não apenas os metadados de auditoria para geração de relatórios e isolamento, mas também é usado no warehouse para identificar a origem do registro. Cada registro de dimensão e de fatos originado de um carregamento de dados específico e essa coluna identificam esse carregamento. Isso é útil para fins de linhagem e de validação, bem como para correções manuais necessárias em caso de corrupção de dados.

O identificador de lotes é adicionado ao fluxo de dados imediatamente após os dados de origem serem extraídos, usando a transformação Derived Column. Portanto, quaisquer transformações downstream podem usar essa coluna para atualizações, inserções e controle, e é necessário pouca sobrecarga para incluir esses metadados nos registros.

Figura 9

Figura 9

Controle da contagem de linhas

A validação de dados representa uma grande ajuda para os DBAs: integral para a solução de BI para administração e solução de problemas, mas também um construtor de confiança para a comunidade de usuários. Se os usuários não confiarem nos dados, a solução estará arriscada a falhar. O Project REAL usa a contagem de linhas como uma demonstração de um aspecto da validação de dados. Embora esse nível de validação represente apenas um subconjunto do que deve ser feito, ele é uma primeira camada sólida de validação.

A auditoria de contagem de linhas é implementada no fluxo de dados usando a transformação Row Count, que simplesmente conta o número de linhas que passam por ela e armazena o valor em uma variável predefinida pelo usuário. Uma ótima característica da transformação Row Count é a quantidade mínima de sobrecarga e recursos que ela requer. Portanto, dentro do fluxo de dados principal de todos os pacotes de dimensão e de fatos, uma transformação Row Count foi inserida após cada origem, antes de cada destino ou OLE DB Command e em qualquer ponto de alto valor no meio do fluxo de dados. As contagens são colocadas em variáveis separadas e persistidas no banco de dados com uma tarefa Execute SQL imediatamente após a transformação Data Flow. A Figura 10 mostra a implementação dessas transformações Row Count no fluxo de dados.

Figura 10

Figura 10

Relatórios de ETL

Para unir todos os pontos e apresentar informações bem-organizadas para o administrador e uma ferramenta de solução de problemas para o desenvolvedor, uma série de relatórios do Reporting Services foi criada, combinando auditoria, validação e logs. Retornando, se você tiver acompanhado a discussão sobre auditoria, provavelmente já terá uma idéia do esquema de suporte que permite que os metadados sejam associados. Existem quatro tabelas principais usadas no esquema: três tabelas definidas pelo usuário integradas com a tabela de log interna do SSIS.

Figura 11

Figura 11

Os relatórios de ETL do Project REAL são baseados nessas estruturas e fornecem um resumo de execução de grupo de carregamento de alto nível com a capacidade de exibir detalhes, usando o agrupamento de tabelas do Reporting Services e subrelatórios vinculados. Os seguintes níveis de relatórios estão incluídos:

  • Resumo de execução do grupo de carregamento – Horários de início e de conclusão, resumo da duração e status da execução.

  • Resumo de execução do pacote – Associação do grupo de carregamento, horários de início e de conclusão, resumo da duração e status da execução.

  • Detalhes da contagem de linhas – Descrição e tipo da etapa, contagem de linhas.

  • Resumo da tarefa de fluxo de controle do pacote – Agregação do resumo da tarefa derivada da tabela de log base com duração e status da tarefa.

  • Linhagem detalhada do log de eventos – Entradas detalhadas do log ordenadas e filtradas para o pacote ou a tarefa selecionada.

Uma rápida visualização de um relatório do ETL demonstra sua utilidade para gerenciar soluções do SSIS e solucionar problemas.

Figura 12

Figura 12

Design da configuração dinâmica

A chave para a capacidade de gerenciamento, distribuição e implantação de pacotes do SSIS está na configuração. O SSIS contém várias maneiras de configurar propriedades de pacotes em tempo de execução, o que permite a atualização de informações de conexão, variáveis e quaisquer outras tarefas ou propriedades de transformação que requerem uma configuração dinâmica durante a execução. Vários métodos internos de configuração estão disponíveis cobrindo uma ampla gama de requisitos do ambiente dos quais diferentes soluções podem tirar proveito, incluindo:

  • Arquivos de configuração

  • Variáveis de ambiente

  • Tabelas de configuração do SQL

  • Variáveis do pacote pai

Uma área em que essa funcionalidade é realmente útil é ao migrar um pacote de ETL do desenvolvimento para o ambiente de armazenamento temporário e para a produção. Na maioria dos casos, isso pode ser conseguido simplesmente alterando algumas entradas no sistema de configuração.

Configurações do SQL

O objetivo do Project REAL era centralizar configurações e permitir que o design do ETL fosse implantado em dois ambientes exclusivos, o modelo de expansão e o modelo distribuído. De maneira semelhante, várias versões da solução REAL (uma versão de volume completo, uma versão de exemplo e uma versão de demonstração) também existem para fins de referência e de demonstração. Portanto, as configurações foram centralizadas nas diferentes versões do banco de dados da solução usando as Configurações internas do SQL, que permitem que as propriedades de configuração e os mapeamentos sejam colocados em uma tabela relacional e compartilhados entre os pacotes. Para abrir a ferramenta de gerenciamento centralizado, selecione SSIS e, em seguida, Configurations. A Figura 13 mostra as opções de configuração do SQL.

Figura 13

Figura 13

Existem várias entradas para diferentes propriedades na tabela de configuração. O primeiro conjunto de entradas é para conexões. É importante indicar como as entradas de configuração para conexões se aplicam e como as fontes de dados da solução funcionam para um pacote. Quando uma conexão é criada a partir de um objeto Fonte de Dados, a conexão é uma cópia de tempo de execução da fonte de dados e não é atualizada a partir da fonte de dados pai quando um pacote é executado. Fontes de dados são construções em tempo de design; portanto, quando um pacote for aberto na interface do usuário, as conexões serão atualizadas se tiverem sido construídas a partir de uma fonte de dados dentro da solução do SSIS. Por causa disso, as conexões são boas candidatas para configurações. Tipicamente, elas precisam ter base dinâmica no ambiente (desenvolvimento, teste e produção). Outras entradas na tabela de configuração do Project REAL são mapeamentos de variáveis, que permitem a atualização de valores de variáveis utilizados no ETL para a lógica de processamento e o gerenciamento.

Configurações do arquivo XML

Na Figura 13, o local da tabela apropriada de configurações do SQL é baseado em uma conexão de pacote. No entanto, se todas as informações de conexão estiverem localizadas na tabela de configuração, isso produzirá uma referência circular que poderá resultar no uso do valor da conexão do pacote embutido no código, o que não é desejado. Para evitar isso, um segundo tipo de configuração foi usado: as configurações do arquivo XML. Novamente, com o objetivo de centralizar as configurações em uma tabela do banco de dados, apenas uma entrada da configuração é necessária no arquivo XML: a seqüência de caracteres de conexão que contém a tabela de configurações do SQL. Como você pode ver, existe realmente apenas uma propriedade para a configuração de um arquivo XML — o local e o nome do arquivo XML.

Figura 14

Figura 14

Um recurso valioso da configuração de arquivo é a habilidade de usar uma variável de ambiente do servidor que define o local do arquivo de configuração. Como todos os pacotes fazem referência a esse arquivo, o uso de uma variável de ambiente permite um único local para uma alteração do arquivo. Isso também é valioso para a implantação, quando outros servidores que executam esses pacotes podem usar diferentes locais e nomes de arquivo. Esse uso de variáveis de ambiente é diferente da configuração da variável de ambiente do SSIS, na qual um servidor pode conter várias variáveis de ambiente que substituem qualquer propriedade do pacote.

Configurações de variável pai

Todo uso da configuração do Project REAL descrito até agora estava relacionado a propriedades que são globais em escopo, isto é, conexões e variáveis que são usadas para cada execução de um pacote ou grupo de pacotes em um ambiente designado. Algumas configurações precisam ser limitadas à execução específica do pacote e do grupo de fluxo de trabalho do qual o pacote participa. Por exemplo, o identificador do lotes do grupo do fluxo de trabalho, ETL_Load_ID, é criado na etapa inicial do pacote do grupo de carregamento e usado em todos os pacotes filho. Cada execução dos pacotes é executada sob o contexto de um lote diferente e, portanto, a configuração dessa variável precisa ser dinâmica de acordo com o pacote em execução.

O recurso de configuração de variável pai do SSIS permite que as variáveis de um pacote pai sejam herdadas por um pacote filho. Isso é diferente do predecessor do SSIS, o DTS, com o qual variáveis eram enviadas do pacote pai para o pacote filho. No SSIS, o pacote filho solicita a variável pelo nome ao pacote pai, permitindo que a variável seja herdada de qualquer pacote pai de chamada que use a tarefa Execute Package para chamar o pacote filho.

O requisito do Project REAL de ter uma configuração que seja local para a instância de execução do pacote ou do conjunto de pacotes é bem atendido pelo recurso de configuração de variável pai. Como foi mencionado, o ETL_Load_ID é herdado por todos os pacotes de dimensão e de fatos, bem como pela identificação de execução do pacote pai para permitir ainda mais a correlação de dados entre os pacotes. A Figura 15 mostra a configuração da variável para o identificador vnETL_Load_ID.

Figura 15

Figura 15

Arquitetura do processamento de dados

Até agora, discutimos a estrutura de suporte do design do SSIS sem entrar na lógica principal de processamento do ETL. Essa visão geral ajudou a preparar o campo para uma consideração mais profunda da lógica de processamento, dados os conceitos abordados nas seções anteriores deste documento. No entanto, antes de descrever as particularidades da implementação do processamento dos dados do Project REAL, é importante retornar e considerar alguns dos novos recursos do SSIS no contexto de princípios importantes do ETL.

Fluxo de controle e fluxo de dados

Os principais recursos do SSIS usados para implementar a lógica comercial principal estão contidos nos componentes de fluxo de controle e fluxo de dados. Esses componentes já foram mencionados algumas vezes neste artigo ao fazer referência às estruturas de ambiente e de auditoria do Project REAL.

Em um nível superficial, o fluxo de controle é o mecanismo de fluxo de trabalho da tarefa que coordena a lógica do fluxo do processo comercial de um pacote. Cada pacote tem exatamente um fluxo de controle principal (manipuladores de eventos também são um tipo de fluxo de controle), tenha ele uma etapa simples ou dúzias de tarefas interconectadas. As tarefas dentro do fluxo de controle são vinculadas por restrições — expressões de restrição de êxito, falha, conclusão e personalizadas, e a lógica booleana.

O fluxo de dados é o mecanismo de processamento de dados que trata da movimentação dos dados, da lógica de transformação, da organização dos dados e da extração e confirmação dos dados para origens e destinos e vice-versa. Ao contrário do fluxo de controle, pode haver vários fluxos de dados definidos em pacotes que são organizados pelo fluxo de controle. Embora o fluxo de dados tenha conectores verde e vermelho que são muito semelhantes aos conectores do fluxo de trabalho do fluxo de controle, sua função é completamente diferente. Considere o conector do fluxo de dados como um pipeline de dados que está fluindo de uma transformação para outra em pequenos lotes de dados, chamados de buffers. Embora essa seja a maneira mais fácil de visualizar como o fluxo de dados funciona, na realidade as transformações definidas é que fazem a maior parte da movimentação, através dos buffers de dados para obter um desempenho ideal.

Vantagens da arquitetura do SSIS

Além das vantagens de produto que o SSIS tem sobre o DTS nas áreas de interoperabilidade, configuração, capacidade de reinicialização e logs, ele introduz um mecanismo de transformação que permite uma economia de escala e abre a arquitetura do ETL para designs mais estáveis, flexíveis e baseados no desempenho. No Project REAL, essas vantagens foram consideradas no desenvolvimento central do ETL e, portanto, determinadas decisões de design partiram da arquitetura baseada no status quo do DTS.

Armazenamento temporário limitado

Para começar, o SSIS permite a redução de um ambiente de armazenamento temporário, permitindo a execução de transformações, limpeza e pesquisas de dados complexos diretamente no fluxo de dados, com pouca dependência do mecanismo e do armazenamento do RDBMS. Comparações de dados entre as tabelas de origem e do warehouse podem ser tratadas por meio de pesquisas e transformações de mesclagem com divisões condicionais para direcionar os resultados para a lógica de carregamento apropriada. Com essa consideração, o único requisito para o mecanismo do banco de dados é dar saída aos dados para o fluxo de dados do SSIS e não para o banco de dados que está executando as pesquisas, uniões ou comparações de linhas.

Vantagens do pipeline

A maioria dos componentes do fluxo de dados permite um paralelismo de pipeline real (com algumas exceções notáveis, como as transformações Sort e Aggregate), o que significa que o espectro de processamento para objetos do warehouse está ocorrendo simultaneamente em pequenos buffers de dados sem a necessidade de aguardar pela conclusão de todo o processo de upstream antes de passar para o próximo. Isso ajuda a atenuar o impacto da extração no sistema de origem e, em muitos casos vistos durante o desenvolvimento do Project REAL, quando um pacote do SSIS é otimizado, o tempo necessário para extrair os dados não processados da origem e colocá-los imediatamente em um banco de dados é aproximadamente equivalente ao tempo necessário para extrair os dados e passá-los por uma série de transformações em memória designadas no componente de fluxo de dados.

Limpeza e transformação de dados

As transformações prontas do fluxo de dados do SSIS incluem uma série de ferramentas de limpeza de dados, como pesquisas difusas e uniões, mapas de caracteres, conversões de tipos de dados, colunas derivadas e um conjunto de funções baseadas em booleanos para comparações e substituição de dados.

Origens e destinos de vários para vários

Como um único fluxo de dados pode conter várias origens e destinos heterogêneos, isso libera dados originados de uma única fonte para serem divididos em vários destinos. O mesmo se aplica ao cenário inverso. Vários objetos de origem podem ser combinados em um único destino. Normalmente, em um sistema de BI, uma dimensão pode ser originada de diferentes tabelas dentro do mesmo sistema ou de sistemas diferentes. De maneira semelhante, fatos podem ser originados de uma ou mais tabelas ou uma origem transacional pode ser quebrada em vários destinos da tabela de fatos.

Alterações no detalhamento e no tipo de dimensões e de fatos

Na maior parte do tempo, objetos do warehouse são carregados no mesmo detalhamento que seu objeto OLTP de origem. No entanto, pode haver situações em que uma dimensão é agregada a um detalhamento mais alto ou uma origem com pai e filho unidos é desnormalizada para uma hierarquia padrão. Registros de origem podem requerer dinamização, por exemplo, quando as linhas de origem forem consolidadas de um quarto design normal para um conjunto de registros consolidado de atributos relacionados. Tabelas de fatos também passam por transformações semelhantes quando são agregadas ou agrupadas para atender a requisitos de relatórios. Esses cenários menos comuns podem normalmente ser tratados dentro do fluxo de dados, usando outras transformações prontas: aggregate, pivot, un-pivot, sort, merge join, etc.

Processamento de dimensões

A manipulação do histórico de uma dimensão é um dos aspectos mais complexos de uma solução de ETL. No Project REAL, os cenários de carregamento de dimensões envolvem mais do que apenas o processamento de atributos de histórico e de alteração, mas também tipos de alteração de dimensão e associações não sincronizadas de fatos para dimensões. Além de considerar a funcionalidade interna do assistente do SCD (Slowly Changing Dimension), nós também abordaremos dois requisitos distintos incluídos no projeto:

  • Membros da dimensão inferidos, em que um fato é recebido sem uma dimensão correspondente porque o registro completo da dimensão ainda não estava disponível para carregamento. São algumas vezes chamados de fatos órfãos.

  • Tipos de SCD em constante mudança, quando os membros dentro de uma única dimensão têm requisitos de alteração de histórico diferentes que podem sofrer alterações ao longo do tempo.

O SSIS forneceu os recursos para lidar com casos padrão e exclusivos da solução Project REAL, como será mostrado.

Assistente do Slowly Changing Dimension

Na lista de desejos de qualquer designer de ETL está uma ferramenta que possa manipular, de forma mágica, as dimensões que sofrem alterações lentamente. O SSIS chega perto disso. Dentro do SSIS há um assistente que orienta o desenvolvedor por uma série de etapas baseadas nos esquemas de dimensão de origem e de destino, para determinar as características a serem alteradas. Em seguida, o assistente cria as transformações necessárias para processar aquela dimensão. Mesmo quando os requisitos são alterados, invocar o assistente novamente propicia a monitoração de estado, permitindo que as seleções originais sejam modificadas para tratar dos novos processos.

No Project REAL, a ferramenta SCD (Slowly Changing Dimension) era bastante proveitosa. Todas exceto uma das tabelas de dimensão do esquema em estrela usam a transformação do SCD. Ela reduz drasticamente o tempo de desenvolvimento para processamento de dimensões. Para mostrar como o assistente do SCD funciona, a dimensão Store fornece o uso mais abrangente do assistente. Os requisitos da dimensão Store envolvem:

  • Novos membros da dimensão – a adição de um novo membro da dimensão que é adicionado à origem

  • Atributos da dimensão em constante mudança – a coluna tradicional de tipo 1 é alterada quando o histórico é substituído toda vez que o valor da coluna de origem é alterado.

  • Atributos da dimensão histórica – a coluna tradicional de tipo 2 onde o histórico é mantido pela adição de um novo registro de dimensão que está associado a todos os novos registros de fatos até a próxima alteração.

  • Membros inferidos – a situação em que o membro de uma dimensão não foi carregado na tabela de dimensão antes da execução do processo do fato e, portanto, um registro de espaço reservado é adicionado, que é atualizado subseqüentemente (tanto colunas de tipo 1 quanto de tipo 2) quando a dimensão completa da origem torna-se disponível.

Percorrendo o assistente para obter a dimensão Store, a primeira tela mostra uma lista das colunas com uma seleção disponível para suas chaves comerciais, conforme mostrado na Figura 16.

Figura 16

Figura 16

Em seguida, o assistente requer uma distinção das colunas que participam dos tipos de alteração. A opções são os atributos Changing, Historical e Fixed, que identificam as colunas que não devem ser alteradas. A Figura 17 mostra esses atributos.

Figura 17

Figura 17

As dimensões que contêm as colunas históricas ou de tipo 2 requerem que alguns metadados gerenciem a natureza atual e histórica de cada alteração. A próxima tela (Figura 18) ajuda o processo a conhecer como a dimensão Store controla o histórico. Nessa situação, uma coluna Current_Row controla qual registro da dimensão é o atual para a linha da dimensão que é alterada.

Figura 18

Figura 18

Em seguida, se membros inferidos forem usados, a tela mostrada na Figura 19 identificará como o assistente do SCD sabe quando um registro de dimensão é um membro inferido, de forma que todas as colunas, além da chave comercial, sejam atualizadas durante o processamento. Existem duas opções. A primeira opção indica que todas as colunas não chave são valores NULL para identificar um membro inferido. A segunda opção será controlada por uma coluna de sinalizador que indica se o membro é inferido. Uma vez que as colunas NULL não são exibidas corretamente no Analysis Services, decidimos usar uma coluna chamada Inferred_Member. Em seguida, pudemos substituir os atributos usados nas hierarquias do Analysis Services por valores denominados Unknown.

Figura 19

Figura 19

Após a última tela, o assistente gera uma série de transformações personalizadas com os detalhes que foram inseridos durante o processo do assistente. A transformação principal é chamada de Slowly Changing Dimension. Como entrada, ela usa os registros de origem da dimensão, sejam eles uma cópia completa da origem da dimensão ou apenas um subconjunto dos registros da origem, aqueles adicionados ou alterados na origem. Considere a tarefa SCD como uma combinação de uma transformação Lookup não armazenada em cache e uma transformação Conditional Split, em que os registros de origem da dimensão são avaliados em relação à dimensão do warehouse e, em seguida, distribuídos para as diferentes saídas do SCD. A Figura 20 mostra a imagem final da interface do usuário da transformação Store SCD com suas saídas associadas.

Figura 20

Figura 20

Cenário de dimensão exclusiva

O único pacote de processamento de dimensão do Project REAL que não usa a transformação SCD é a dimensão Item. Seus requisitos são exclusivos e seu tamanho (aproximadamente 6 milhões de membros) requer tratamento especial para fins de escalabilidade.

Uma característica que diferencia a dimensão Item das outras dimensões é a espécie de tipos de alterações de SCD que ocorrem. Além de precisar do membro inferido, dos atributos Changing e Historical, os requisitos especificam que, para um determinado membro, seus tipos de alterações de atributos podem variar de Changing para Historical e de tipo 1 para tipo 2. Essa situação ocorre quando um item é vendido pela primeira vez. Antes da primeira venda, todos os atributos agem como atributos de tipo 1 de alteração, mas assim que ocorre uma venda, um subconjunto de atributos se tornam alterações de tipo 2 de histórico. Esse cenário foi cunhado como uma alteração de tipo 1,5 e é controlado pelo desejo comercial de limitar o número de adições de tipo 2 à dimensão. Isso é porque, quanto um item é inserido no sistema transacional pela primeira vez, o processo para estabelecer suas características faz com que várias alterações aconteçam nos primeiros vários dias. À medida que esses detalhes iniciais do atributo são processados, o membro da dimensão está em um estado em que uma alteração em qualquer atributo provoca uma atualização naquele atributo na dimensão e não um novo registro histórico de tipo 2. O valor que essa abordagem fornece está na limitação do crescimento da tabela de dimensão a alterações históricas valiosas, que ocorrem quando um item está estável e vendendo. Embora controlado por um requisito comercial diferente, esse cenário é semelhante à maneira como um membro inferido funciona. No entanto, nesse caso o registro de origem da dimensão está disponível e o requisito de atualizar todos os atributos permanece até que o requisito de venda seja atingido.

Um fator decisivo quanto ao uso ou não do assistente do SCD foi o volume de registros processados para a dimensão. A dimensão Item de 6 milhões de membros pode passar por dezenas de milhares de alterações por dia através de seus 100 atributos. O processo de pesquisa de componente do SCD estava gerando um número de chamadas equivalente no banco de dados, consultando uma tabela bastante grande e retornando dúzias de colunas na linha de resultados. Esse processo não era suficiente para o intervalo de tempo desejado. Portanto, uma abordagem alternativa foi adotada.

Uma opção era usar uma transformação Lookup e trazer toda a dimensão para o cache para que todas as colunas estivessem disponíveis para as comparações de tipo de alteração. No entanto, o armazenamento em cache de todas as colunas de uma grande tabela necessitaria de vários GB de memória e demoraria uma quantidade de tempo significativa para carregamento na memória. Portanto, em vez disso, uma transformação Merge Join foi usada, na qual os registros de origem à esquerda foram combinados com os membros da dimensão atual à direita através da chave comercial, como mostra a Figura 21. O efeito dessa união era transmitir apenas os registros Item que eram realmente usados nos fatos relevantes. As colunas necessárias da dimensão para a análise de tipos de alterações foram incluídas no fluxo de dados dos registros correspondentes. Uma mesclagem à esquerda foi usada para que os novos registros da origem (esquerda) continuassem fluindo para onde eles seriam adicionados à dimensão como novos membros.

Figura 21

Figura 21

A mesclagem neste cenário é executada muito bem porque as colunas de entrada já estão classificadas — à medida que ocorrem correspondências, os registros são liberados para as transformações downstream para processamento.

A transformação Conditional Split (localizada imediatamente abaixo da transformação Merge Join) avalia determinadas condições e, em seguida, direciona as linhas para várias saídas de transformação. As condições são avaliadas na ordem. A primeira condição atendida para uma linha designa sua saída; portanto, essa linha não é enviada para várias saídas.

Figura 22

Figura 22

A transformação Conditional Split na Figura 22 primeiro avalia se o lado direito da união teve uma correspondência, usando uma função ISNULL. A saída das linhas de origem que correspondem à verificação de nulos é feita para uma transformação que adiciona a linha como um novo membro da dimensão. Como todos os membros restantes tinham correspondências na tabela de dimensão do warehouse, os critérios do tipo de alteração são avaliados. Para registros correspondentes, os primeiros critérios avaliados são as condições Inferred Member e Sales Flag. Como as duas requerem uma atualização completa nos atributos da dimensão, elas são combinadas e tratadas ao mesmo tempo. Em seguida, os atributos de alteração do histórico são avaliados. Se houver uma alteração em um ou mais dos atributos controlados historicamente, um registro de alteração de tipo 2 será gerado. Finalmente, qualquer alteração nas colunas de tipo de alteração restantes fazem com que uma instrução de atualização da dimensão substitua o valor anterior daquele atributo (de uma maneira tipo 1).

Observe que a condição final não é especificada, mas a saída padrão para Conditional Split descreve esse cenário. Como os registros de origem são apenas linhas novas e alteradas, sabemos que, se todos os outros requisitos forem atendidos, os últimos critérios deverão se aplicar aos registros restantes. Isso enfatiza o fato de que a ordem dos critérios é crucial para o processamento correto da dimensão Item dessa maneira.

As transformações downstream da divisão condicional são muito semelhantes à saída da transformação de SCD no exemplo de Store. Isso é porque a saída é modelada de acordo com o processamento do SCD de novas adições, alterações e membros inferidos (chamado de atualizações completas por causa das alterações de tipo 1,5).

Figura 23

Figura 23

Processamento da tabela de fatos

A manipulação do processamento da tabela de fatos é, em sua maior parte, muito diferente do processamento de dimensões. Além disso, o processo de uma tabela de fatos pode ser muito diferente do próximo. No entanto, a maioria dos processos da tabela de fatos contém comparações de linhas de fatos e pesquisas de chave de dimensão. Para fins de ilustração, dois pacotes de tabelas de fatos do Project Real são destacados nesta seção. Esses pacotes modelam cenários comuns.

Extrações incrementais e completas da origem

O ETL do Project REAL possui dois tipos de extrações de tabela de fatos: extrações completas da origem, em que registros alterados ou novos não são identificáveis, e extrações incrementais, em que registros novos e alterados são apenas extraídos.

Extrações completas da origem

As posições do inventário do DC (centro do distrito) são controladas semanalmente por cerca de 8 milhões de diferentes combinações de DC e itens. No sistema de origem, esses registros estão contidos em uma tabela que não identifica um registro novo ou modificado; portanto, o processo de ETL deve comparar os registros entre a origem do inventário e a tabela de fatos para identificar quando uma alteração ocorreu. Em seguida, ele pode manipular corretamente a inserção ou a atualização.

A abordagem utilizada foi usar uma união mesclada completa com o conjunto de dados completo para as tabelas de origem e de destino. Uma união completa ajuda a identificar quando um registro do inventário foi adicionado à origem ou removido completamente. Para esta solução, os requisitos especificados que excluíram registros da origem precisavam ser controlados como uma posição zero do inventário na tabela de fatos. Usar uma transformação Merge Join configurada como uma união completa atende a esse requisito. A mesclagem é mostrada na Figura 24.

Figura 24

Figura 24

A próxima transformação downstream, uma transformação Conditional Split, trata da identificação das alterações do registro, avaliando os resultados da união e a comparação de valores das colunas.

Figura 25

Figura 25

As condições aplicadas na transformação Conditional Split são ordenadas por desempenho, a partir do caso em que a união produziu uma correspondência e os atributos e as medidas não foram alterados. Essa saída é listada primeiro no designer, já que a maioria dos registros atende a esse critério, mas o fluxo de saída não é usado. Como os registros inalterados não exigem processamento, isso tem o efeito de excluir essas linhas da filtragem. O segundo critério é identificar novos registros do inventário, aqueles em que a linha de origem não tinha uma correspondência com um registro existente da tabela de fatos. Em comparação, a próxima avaliação é o cenário da união completa, no qual a posição no inventário de fatos precisa ser definida como zero porque a linha de origem foi removida. Finalmente, todas as outras linhas são interceptadas com a saída padrão. Essas tiveram alterações na posição do inventário e precisam ser atualizadas no banco de dados.

Extrações incrementais da origem

O desempenho do processo de ETL pode melhorar muito quando um processo de extração pode isolar o conjunto de atualizações e inserções em um sistema de origem. Felizmente, muitas das grandes fontes de dados no Project REAL podem tirar proveito dessas extrações direcionadas. Um exemplo disso é a extração de Store Inventory. Se toda a origem de inventário da loja, cerca de 200 milhões de registros, fosse necessária para processar as alterações diárias, não haveria horas suficientes em um dia para processar o carregamento. Mas, como o inventário diário pode ser extraído de forma incremental, a janela de processamento é reduzida a uma janela bastante gerenciável.

Para o inventário, o processo de extração incremental usa uma pesquisa em cache para ajudar a determinar se um registro incremental é uma inserção ou uma atualização. Um fator de grande ajuda é um processo de armazenamento temporário, que é usado para filtrar os registros na pesquisa, otimizar o processamento e ajudar na implementação global. Para obter mais informações, consulte as técnicas de otimização do processamento de dados, mais adiante neste documento.

A Figura 26 mostra o fluxo de dados do processamento de fatos do inventário. A pesquisa que compara registros de origem alterados com registros de fatos atuais é destacada.

Figura 26

Figura 26

Pesquisas de dimensão

Todo processo de tabela de fatos requer uma maneira de associar os fatos à tabela de dimensão. Isso foi resolvido usando a transformação Lookup, que pode armazenar a dimensão em cache. Conforme as linhas de origem fluem, ela retorna rapidamente chaves substitutas necessárias para a tabela de fatos baseada na associação de chave comercial. Esse processo é direto e particularmente efetivo para dimensões com números menores de linhas. Sempre que uma dimensão tem alterações históricas de tipo 2 e, portanto, um identificador de linha atual, o cache é filtrado para usar apenas as linhas atuais, para que a versão mais recente do membro da dimensão seja associada ao fato. A Figura 27 mostra a guia Reference Table da transformação Lookup. Neste exemplo de dimensão Store, uma consulta é usada para filtrar Current_Row.

Figura 27

Figura 27

A guia Columns da transformação Lookup é onde as colunas do fluxo de dados são mapeadas para as colunas da tabela de referência. Como o objetivo da pesquisa de dimensão é obter a chave substituta da dimensão, a chave comercial é usada como um mapa (Store_Num), e a chave substituta é retornada (SK_Store_ID) juntamente com uma coluna secundária que é usada downstream no fluxo de dados.

Figura 28

Figura 28

Do ponto de vista do fluxo de dados, as linhas de origem simplesmente vão de pesquisa em pesquisa, associando as linhas com as chaves substitutas de dimensão mais recentes. O fluxo de dados na Figura 29 mostra as transformações Lookup descritas acima. Várias outras transformações são exibidas, incluindo uma transformação Script e transformações Union All usadas para os membros inferidos, que são descritas em detalhes na próxima seção.

Figura 29

Figura 29

Para a dimensão Item, que contém milhões de linhas, foi utilizada uma técnica de otimização para limitar a pesquisa apenas às linhas necessárias para qualquer processo que a tabela de fatos esteja executando. Para obter mais informações, consulte as técnicas de otimização de processamento de dados, mais adiante neste documento.

Manipulando adições de membros inferidos

Como as dimensões do Project REAL exigem que membros inferidos sejam criados quando um registro de dimensão não existe, temos mais trabalho a fazer. Como um lembrete, um membro inferido é um registro de dimensão que age como um espaço reservado apenas com o valor da chave comercial, de forma que quando o registro completo da dimensão está disponível, todas as colunas de dimensão são atualizadas com os novos valores disponíveis. O processo de atualização ocorre durante o processamento da dimensão, mas a adição do membro inferido ocorre durante o processo do fato, quando a transformação Lookup não produz uma correspondência. Quando a transformação Lookup que trata da pesquisa de dimensões armazenadas em cache para a chave não consegue localizar uma correspondência, a linha realmente falha. Para configurar a saída para que a linha possa ser redirecionada por meio do pipe com falha, clique no botão Configure Error Output na interface do usuário da pesquisa e configure a linha a ser "redirecionada". Consulte a Figura 30:

Figura 30

Figura 30

Ao configurar a transformação Lookup principal dessa forma, as linhas não correspondentes são redirecionadas e os membros inferidos podem ser adicionados à dimensão por meios alternativos. Todas as chaves de dimensão substitutas no Project REAL são colunas de identidade, que tornam esse processo mais difícil do que se as chaves fossem identificadores exclusivos que pudessem ser gerados pelo SSIS. Várias abordagens foram consideradas para tratar das adições de membros inferidos. Devido ao volume de dados, os requisitos precisavam de uma maneira de manter o processo em execução de maneira ideal, mantendo as adições de membros inferidos no fluxo de dados principal. Para manter o processo descomplicado e evitar vários fluxos de dados, as chaves recém-geradas dos membros inferidos precisavam ser trazidas de volta para o fluxo de dados antes da próxima pesquisa de dimensão. Outra consideração era que, como o cache da transformação Lookup é carregado antes da execução do fluxo de dados, quando um membro inferido era adicionado ao banco de dados pela primeira vez, ele não era adicionado ao cache com o restante dos registros de dimensão. Portanto, se houver milhares de registros de fatos chegando pela mesma chave comercial não correspondente, nenhum dos registros será localizado no cache e, assim, serão enviados para a saída com erro do processo de adição de membros inferidos.

Devido às exigências acima, uma transformação Script foi selecionada para tratar de adições de membros inferidos. Uma transformação Script, ao contrário de uma tarefa Script, é usada no fluxo de dados. Ela pode executar manipulações baseadas em script em linhas e colunas recebidas pelo pipeline. Nas situações em que um cenário exclusivo requer tratamento especial, a transformação Script é de grande valor devido a sua flexibilidade e capacidade de personalização. O objetivo específico aqui era utilizar a saída da pesquisa não correspondente, adicionar o membro inferido à tabela de dimensão no banco de dados e obter a chave substituta recém-gerada, tudo sem fazer várias chamadas ao banco de dados para a mesma chave comercial não correspondente. Antes de desenvolver o script do Visual Basic® .NET, uma estrutura de código foi reunida para fornecer uma visão geral do processo de transformação Script:

  1. As etapas iniciais executadas antes do processamento do primeiro registro são:

    1. Declarar variáveis.

    2. Criar uma tabela de hash a ser usada para chaves comerciais e pesquisa de chave substituta de saída para otimização.

  2. Para cada linha recebida pelo pipeline de transformação Script, verifique se a tabela de hash contém a chave comercial atual. Em seguida:

    1. Se a chave comercial existir, retornar a chave substituta para a saída do pipeline.

    2. Se a chave comercial não existir:

      1. Executar iConnect com o banco de dados.

      2. Executar o procedimento armazenado que cria o membro inferido e retorna a chave substituta.

      3. Adicionar a chave comercial e a nova chave substituta à tabela de hash.

      4. Retornar a chave substituta à saída do pipeline.

  3. As etapas de limpeza e desalocação seguem a última linha da entrada do pipeline.

A seguinte transformação Script é um processo de exemplo de membro inferido para Store Lookup, usada quando Store_Num não tem um registro correspondente na pesquisa.


' Microsoft Data Transformation Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for DTS Script Components

Imports System
Imports System.Data
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
___________________________________

Public Class ScriptMain
  Inherits UserComponent
  Private htBusinessID As New Hashtable
  Private objConnection As New SqlClient.SqlConnection
  Private objCommand As New SqlClient.SqlCommand
  Private boolInit As Boolean = False
  Private strProcedureName As String = "config.up_ETL_DimStore_CreateInferredMember"
  Private strBusinessID As String = "@pnStore_Num"
  Private strSurrogateID As String = "@pnSK_Store_ID"
  Private strETLLoadID As String = "@pnETL_Load_ID"
  Private strReturnValue As String = "@RETURN_VALUE"
___________________________________

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    If (boolInit = False) Then Me.Connect()
    If Not (htBusinessID.Contains(Row.InBusinessID)) Then
      Dim strSurrogateKey As String = Me.Execute(Row.INBusinessID, Row.ETLLoadID)
      htBusinessID.Add(Row.InBusinessID, strSurrogateKey)
    End If
    Me.ProcessRow(Row)
  End Sub
  Public Sub ProcessRow(ByVal Row As Input0Buffer)
    Row.OutSurrogateID = 
     System.Convert.ToInt16(htBusinessID.Item(Row.INBusinessID).ToString())
  End Sub
  Private Function Execute(ByVal BusinessId As Decimal, ByVal ETLLoadID As Integer, ByVal) 
  As String
    Me.objCommand.Parameters(strBusinessID).Value = System.Convert.ToString(BusinessId)
    Me.objCommand.Parameters(strETLLoadID).Value = System.Convert.ToString(ETLLoadID)
    Me.objCommand.ExecuteNonQuery()

    Execute = 
     System.Convert.ToDecimal(Me.objCommand.Parameters(strSurrogateID).Value).ToString()
  End Function
___________________________________

  Private Sub Connect()
    If boolInit = False Then
      Dim strConnection As String = Connections.SQLRealWarehouse.ConnectionString
      Dim x As Integer = strConnection.ToUpper().IndexOf("PROVIDER")
      If x >= 0 Then
        Dim y As Integer = strConnection.IndexOf(";", x + 1)
        If (y >= 1) Then strConnection = strConnection.Remove(x, y - x + 1)
      End If

      Me.objConnection.ConnectionString = strConnection
      Me.objConnection.Open()
      Me.objCommand.Connection = Me.objConnection
      Me.objCommand.CommandType = CommandType.StoredProcedure
      Me.objCommand.CommandText = Me.strProcedureName

      Dim Parm As New SqlClient.SqlParameter(strBusinessID, SqlDbType.Decimal)
      Parm.Direction = ParameterDirection.Input
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strETLLoadID, SqlDbType.Int)
      Parm.Direction = ParameterDirection.Input
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strSurrogateID, SqlDbType.Int)
      Parm.Direction = ParameterDirection.InputOutput
      Parm.Value = 0
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strReturnValue, SqlDbType.Int)
      Parm.Direction = ParameterDirection.ReturnValue
      objCommand.Parameters.Add(Parm)

      Me.boolInit = True
    End If
  End Sub
___________________________________

  Private Sub Close()
    If boolInit = True Then Exit Sub
    Me.objCommand.Dispose()
    Me.objConnection.Close()
    Me.objConnection.Dispose()
    Me.htBusinessID = Nothing
    MyBase.Finalize()
  End Sub
End Class

O procedimento executado pela transformação Script verifica se a chave comercial existe na dimensão e insere um novo registro (o membro inferido) caso o registro não exista. Em seguida, ele retorna a coluna Identity recém-adicionada ao script para que ela possa ser usada dowsntream no fluxo de dados.

Após a transformação do script do membro inferido, os registros são mesclados novamente no pipeline principal usando a transformação Union All. Em seguida, eles estão disponíveis para a próxima pesquisa de dimensão. A Figura 31 mostra uma série de pesquisas, suas pesquisas associadas de membros inferidos e as uniões necessárias para combinar os resultados novamente.

Figura 31

Figura 31

Técnicas de otimização do processamento de dados

Durante o processo de desenvolvimento do Project REAL, algumas técnicas de otimização foram identificadas para ajudar a descomplicar o ETL. Essas técnicas envolvem princípios baseados nas vantagens da arquitetura do produto, configurações do SSIS e ajustes do fluxo de dados para tratar de grandes volumes. Algumas das otimizações incluem:

  • O uso do armazenamento temporário de dados específicos de alto valor para filtrar pesquisas e fontes de dados mescladas.

  • A limitação da transformação Data Flow do conjunto de linhas completo (bloqueio) como agregações e classificação.

  • O tratamento de cenários de processamento de dados comuns antes da exceção.

  • A consideração de atualizações em lotes de tabelas de dimensão ou fatos de grande volume.

Armazenamento temporário direcionado de alto valor

A transformação Lookup totalmente armazenada em cache correlaciona os dados entre fontes, como a associação de chaves de dimensão com registros da tabela de fatos. No entanto, para as grandes dimensões, o carregamento de toda a dimensão no cache de memória da pesquisa levará um longo tempo e usará RAM que poderia estar disponível para outros processos. No Project REAL, criamos uma tabela de armazenamento temporário de destino para as chaves comerciais da dimensão de itens grandes, que contém de 6 a 7 milhões de membros. Essa pequena tabela de armazenamento temporário é preenchida nos pacotes de tabelas de fatos usando um fluxo de dados que contém uma única origem e um único destino, e extrai apenas a chave comercial do produto da origem transacional e a coloca em uma tabela de armazenamento temporário. A Figura 32 mostra a conclusão bem-sucedia do armazenamento temporário de 4,4 milhões de chaves comerciais em 55 segundos. Como a extração é tão específica, esse fluxo de dados pode ser concluído em uma questão de segundos para vários milhões de linhas.

Figura 32

Figura 32

Em seguida, as chaves temporárias são usadas para filtrar a consulta que a pesquisa usa para carregar o cache. Como as chaves comerciais em uma dimensão já estão indexadas, a união para limitar os registros da dimensão é bem-sucedida. Para este exemplo, essa técnica filtra o cache de pesquisa da dimensão até quase 1/10 do tamanho total da dimensão, que ainda contêm todos os membros da dimensão necessários para a pesquisa durante o processamento dos fatos, uma vez que o mesmo conjunto de chaves comerciais é usado para a pesquisa. O seguinte código SQL é usado para preencher o cache da pesquisa e envolve a filtragem da dimensão (Tbl_Dim_Item) com as chaves comerciais contidas na tabela de armazenamento temporário (tbl_DWS_ETL_Store_Inventory_Log).


SELECT distinct
 ITEM.SK_Item_ID
, ITEM.SysID
, ITEM.SK_Parent_Item_ID
, ITEM.Retail_Amt
FROM dbo.Tbl_Dim_Item as ITEM
INNER JOIN config.tbl_DWS_ETL_Store_Inventory_Log_Keys as INV_LOG
ON ITEM.SysID = INV_LOG.SysID
AND ITEM.Current_Row = 1

Essa abordagem também pode ser utilizada para limitar os registros de origem usados em uma transformação Merge Join. Uniões mescladas são usadas em vários cenários de uma maneira semelhante à da pesquisa para associar dados de origem e de destino. Quando os requisitos exigem a comparação de várias dúzias de colunas entre a origem e o warehouse, uma transformação Lookup pode não conseguir manipular o tamanho do cache da pesquisa, pois cada coluna de cada linha precisa ser armazenada na memória. Uma abordagem alternativa é usar uma transformação Merge Join para reunir os dados da origem e do warehouse. A transformação Merge Join não tem a sobrecarga de memória, mas também pode tirar proveito de uma origem de warehouse filtrada quando as chaves comerciais são armazenadas temporariamente, conforme descrito anteriormente.

Limitação das transformações Sort e Aggregate do fluxo de dados

Embora a limitação das transformações Sort e Aggregation beneficiem o desempenho (uma vez que elas mantêm todas as linhas no pipeline e consomem tempo e recursos), às vezes elas são necessárias ou obrigatórias. Por exemplo, a transformação Merge Join, que é usada em vários dos pacotes do Project REAL, requer que as origens sejam classificadas pelas colunas que definem a união. O uso de uma transformação Sort para as duas origens requer que todas as linhas dos dois lados da mesclagem sejam mantidas na transformação Sort antes de serem liberadas para Merge Join. Pequenos conjuntos de dados podem não ser afetados por isso, mas volumes de tamanhos maiores provocam dois efeitos. Em primeiro lugar, os registros que estão sendo carregados nas classificações (ou nas agregações, em outros exemplos) são armazenados na memória. À medida que os limites são atingidos, partes do cache podem ser temporariamente carregadas para o disco pela transformação Sort ou por meio do gerenciador de memória virtual, criando, dessa forma, ineficiências de E/S e usando recursos de memória que outras transformações downstream podem precisar para processamento. Quando as transformações usadas no fluxo de dados fazem com que os dados sejam retidos, o efeito coloca pressão de volta no upstream dos dados no pipeline, que, se filtrados de volta para as conexões de origem, reduzem o tempo de extração e, portanto, o tempo de processamento global.

Isso não quer dizer que evitamos completamente o uso das transformações Sort ou Aggregation. Em geral, elas operam consideravelmente rápido e são úteis para muitas situações. Essa é mais uma precaução para cenários de alto volume ou de disponibilidade limitada de recursos de memória.

Para este exemplo de Merge Join, a transformação Sort poderá ser eliminada se for possível pré-classificar os dados de origem. Em todos os cenários do Project REAL, isso funciona muito bem. Como um processo de ETL normalmente usaria uma transformação Merge Join para a associação de dados entre chaves comerciais para dimensões ou chaves substitutas para fatos, a classificação pode ser enviada de volta para a consulta da conexão de origem. A classificação em um mecanismo relacional pode exigir muita sobrecarga, a menos que o índice ou a combinação de índices correta exista. Como as chaves comerciais e substitutas são fortes candidatas a índices, a adição de uma cláusula ORDER BY à consulta SQL pode ser valiosa e eficiente. Para isso, o fluxo de dados precisa estar ciente de que a origem está classificada e precisa conhecer as colunas e a direção às quais a classificação se aplica. Isso é feito no Advanced Editor da conexão de origem. Na guia Input and Output Properties, examine as propriedades de nível superior de OLE DB Source Output. Existe uma propriedade chamada IsSorted que precisa ser configurada como True. Em segundo lugar, as colunas que estão classificadas precisam ser especificadas por meio da propriedade SortKeyPosition no recipiente Output Columns, conforme mostrado na Figura 33. Em seguida, o fluxo de dados reconhecerá a classificação.

Figura 33

Figura 33

Em outras situações onde os requisitos de classificação ou de agregação envolvem apenas um subconjunto dos dados do fluxo de dados, outra sugestão de otimização é ramificar os dados (usando uma transformação Multicast) e filtrar as linhas, se necessário (usando uma transformação Conditional Split) e, em seguida, especificar o subconjunto de colunas de saída necessárias para o processamento dos dados (dentro da classificação ou da agregação).

Tratando de cenários comuns antes da exceção

Com recursos de ramificação, mesclagem, filtragem e união no pipeline, existem maneiras de otimizar o processamento lidando com cenários exclusivos separadamente. Isso pode ser percebido quando uma operação pode ser realizada mais de noventa por cento do tempo usando um processo descomplicado, como a pesquisa em cache ou o destino em massa, mas a exceção dos dez por cento restantes requer limitar o processo com um método menos eficiente.

A transformação OLE DB Command versus atualizações em lotes

Atualizações de grandes volumes podem ser o calcanhar de Aquiles de um processo de ETL. Alguns sistemas evitam totalmente as tabelas de fatos para evitar o custo adicional do processo. A criação de registros de alteração da tabela de fatos para compensar diferenças de medidas introduz seu próprio conjunto de desafios na geração de relatórios e no processamento. O processamento diário do inventário do Project REAL modela essa situação. A tabela de fatos do instantâneo do inventário tem um detalhamento semanal com quase 200 milhões de registros armazenados por semana. Além disso, podem existir até 10 milhões de alterações nos dados do inventário atual em uma base diária. A soma de tudo resulta em um afunilamento.

Os dois métodos principais para tratar de um processo de atualização desse tamanho são:

  1. Usar a transformação OLE DB Command com uma consulta parametrizada.

  2. Colocar os registros de alteração em uma tabela de armazenamento temporário e executar uma atualização de RDBMS baseada em conjunto.

Para o primeiro método, o SSIS contém uma transformação que pode interagir com o banco de dados diretamente a fim de manipular várias operações, a mais comum sendo uma instrução de atualização. A transformação OLE DB Command usa uma instrução SQL que é parametrizada com mapeamentos para colunas do fluxo de dados. Conforme as linhas são passadas para a transformação, a operação é executada com os dados fornecidos nas linhas. Como a operação é executada uma linha de cada vez, ela tem várias limitações quando usada para processar uma grande quantidade de atualizações. Quando milhões de linhas são enviadas por meio dessa transformação para executar atualizações, isso resulta em sérios inconvenientes, como o impacto no banco de dados relacional, a pressão colocada no fluxo de dados e o tempo necessário para concluir o processamento.

A segunda abordagem envolve um armazenamento temporário dos dados e o uso do mecanismo relacional para manipular a atualização, unindo a tabela de armazenamento temporário com a tabela de destino em uma instrução de atualização. Isso envolverá o uso intensivo de um ambiente de armazenamento temporário, mas, considerando o custo, pode haver ganhos gerais ao adotar essa abordagem como uma alternativa ao primeiro método. As desvantagens dessa abordagem são os custos com os recursos de um ambiente de armazenamento temporário e o impacto no banco de dados do warehouse durante o processo de atualização, o que pode envolver uma sobrecarga dos recursos do sistema e o bloqueio de tabelas, páginas e linhas.

As vantagens da última abordagem são evidentes apenas quando comparadas com a alternativa. Com o armazenamento temporário dos dados necessários para a atualização, o pipeline trabalhará de forma muito melhor, pois a transformação de destino poderá ser otimizada. Além disso, a duração do impacto global na tabela de destino deve ser reduzida, porque uma operação baseada em conjunto poderá ser tratada de maneira mais eficiente pelo SQL do que a alternativa de atualizações linha a linha. A atualização também se beneficiará da indexação otimizada e do tratamento potencial das atualizações em uma série de processos menores em lotes.

Conclusão

A lógica comercial encapsulada nos componentes centrais de controle e fluxo de dados do SSIS representa as operações fundamentais de ETL que dão suporte aos principais objetivos do Project REAL: a disponibilização, organização e precisão das informações para análise. Além dos processos de dados comerciais, uma solução de ETL requer mecanismos que ofereçam suporte aos métodos de administração e desenvolvimento, que também foram incorporados na solução Project REAL. No todo, o SSIS forneceu os recursos para atender a esses pré-requisitos — de dados e operacionais — com o desempenho, a facilidade de desenvolvimento e a flexibilidade que são essenciais para as necessidades complexas atuais.

Reconhecimento e agradecimentos são devidos às equipes da Barnes & Noble, Intellinet e Microsoft Consulting por orientar a solução “real” para o sucesso e também à equipe de desenvolvimento do Microsoft SSIS por sua valiosa ajuda no fornecimento de consultoria e suporte ao design durante o ciclo de vida beta do SQL Server 2005. As lições contidas neste white paper foram obtidas não apenas devido ao tempo e esforço despendidos durante o Project REAL, mas também por causa das horas gastas com a execução de testes, revisões do design, tentativas e erros e da coleta e do planejamento de requisitos do projeto que foram feitos pela Barnes & Noble. Essa base permitiu que o esforço do Project REAL fosse focalizado na otimização, na adaptação e no aperfeiçoamento do ETL principal para que ele se tornasse um modelo forte de ETL para a plataforma do SQL Server 2005.

Para obter mais informações:

http://www.microsoft.com/sql/

Este documento foi útil para você? Envie-nos seus comentários. Em uma escala de 1 (fraco) a 5 (excelente), como você classificaria este documento?

Download

Dd569875.icon_Word(pt-br,TechNet.10).gifProject REAL ETL Design Practices.doc
656 KB
Arquivo do Microsoft Word


Isso foi útil para você?
(1500 caracteres restantes)
Agradecemos os seus comentários
A Microsoft está realizando uma pesquisa online para saber sua opinião sobre o site do MSDN. Se você optar por participar, a pesquisa online lhe será apresentada quando você sair do site do MSDN.

Deseja participar?
Mostrar:
© 2014 Microsoft