SSIS – Criando seu próprio SCD 4

Neste post da série você irá aprender a usar alguns componentes da ferramenta de ETL e integração de dados da Microsoft, o Integration Services, para criar um fluxo de carga de dimensões personalizado.


Para conseguir manter a abstração de SGBD, não utilizando queries complexas para efetuar as cargas, e ainda assim otimizar o processo de ETL da dimensão de Clientes da Light LTDA, você precisará criar o seu próprio fluxo de SCD, utilizando outros componentes disponíveis no Integration Services.

Para iniciar a criação do novo fluxo de ETL, primeiramente crie uma nova dimensão chamada DM_CLIENTES_v2 para efetuar a nova carga. utilize o código abaixo.

USE OLAP
GO 

CREATE TABLE DM_CLIENTES_v2 ( 
    SK INT NOT NULL IDENTITY (1,1),
    ID INT,
    NOME VARCHAR(25),
    CPF VARCHAR(14),
    EMAIL VARCHAR(25),
    DDD CHAR(2),
    TELEFONE CHAR(8),
    DATA_INICIO DATE,
    DATA_FIM DATE)
GO

Em solution explorer clique com o botão direito em SSIS Packages e crie um novo pacote. Arraste um novo Data Flow Task da barra de ferramentas, para o Control Flow. Dê um duplo clique e, novamente da barra de ferramentas, arraste para dentro do campo em branco do Data Flow os seguintes componentes:

  • Dois componentes OLE DB SOURCE – Serão responsáveis pelas conexões com a tabela CLIENTES do banco OLTP e com a Dimensão DM_CLIENTES_v2 do banco OLAP;
  • Dois componentes SORT – O componente SORT é responsável, como o próprio nome já diz, por ordenar os registros de uma origem ligada a ele de acordo com a(s) chave(s) selecionada(s);
  • Um componente MERGE JOIN – Esse componente será responsável por concatenar as colunas da tabela de origem (CLIENTES) com as colunas da tabela de destino (DM_CLIENTES_v2) de acordo com o(s) campos(s) ordenado(s) pelos sorts;
  • Um componente CONDITIONAL SPLIT – Esse componente é responsável por filtrar os dados conforme os parâmetros passados. Nesse fluxo, ele separará os novos registros (as linhas que existem na tabela de Clientes mas não estão na dimensão), daqueles a serem atualizados dentro da dimensão DM_CLIENTES_v2 (seja com relação ao update ou a inserção de um novo registro histórico);
  • Um componente DERIVED COLUMN – Esse componente será responsável por criar a data de inicio e de fim que fará o versionamento dos dados históricos da dimensão DM_CLIENTES_v2;
  • Um componente OLE DB DESTINATION – Esse componente servirá para que possamos fazer a inserção dos novos registros na dimensão DM_CLIENTES_v2;
  • Três componentes OLE DB COMMAND  Esses componentes serão responsáveis por fazer o update nos campos que precisam ser atualizados e por inserir os novos registros históricos, atualizando esses registros com as respectivas datas de inicio e fim.

Captura de Tela 2015-10-13 às 17.00.37


Comece definindo cada um dos OLE DB Source. O primeiro deve estar configurado na base de dados OLTP, na tabela Clientes, chame-o de Origem. O segundo deve estar configurado na base de dados OLAP, na dimensão DM_CLIENTES_v2, chame-o, por sua vez, de Destino. Ligue cada um dos OLE DB Source em um operador Sort.

Captura de Tela 2015-10-13 às 17.08.59

Agora configure cada um dos Sorts para ordenar os registros pelo campo ID.

Captura de Tela 2015-10-13 às 17.09.59

Renomeie cada um deles (Segundo o OLE DB Source em que estiver ligado) de Ordena_Origem e Ordena_Destino.  Ligue o Sort Ordena_Origem ao componente Merge Join. Na janela que se abrirá, escolha a opção Merge join left output.

Captura de Tela 2015-10-13 às 17.11.37

Ligue também o Sort Ordena_Destino ao Merge Join. feito isso, abra o componente Merge Join. Ele servirá para concatenar as colunas da tabela Clientes com as colunas da dimensão DM_Clientes, ordenando-as através da chave definida nos Sorts. É necessário que você altere a opção join type de Inner join, para Left outer join. Isso se faz necessário para trazermos os registros que estão na Origem, mas ainda não foram inseridos no Destino.

Uma boa prática, já que as colunas da Origem e as colunas do Destino têm o mesmo nome, é concatenar no Output das colunas derivadas do Sort Ordena_Destino, o sufixo _Dest para que não venhamos a fazer confusão nas configurações seguintes. Veja na figura abaixo.

Captura de Tela 2015-10-13 às 17.16.09

Seguindo com a construção do fluxo de ETL, ligaremos o componente Merge Join ao componente Derived Column. Renomeie esse componente para Cria_Datas. Abra-o e configure  duas novas colunas para o fluxo. Uma chamada Data_Inicio e a outra Data_Fim. Use o código abaixo em ambas as colunas na aba Expression.

(DT_DBTIMESTAMP)(@[System::StartTime])

Agora, ligue o Cria_Datas ao componente Conditional Split. Esse permitirá que os dados sejam divididos para que se possa trabalhar separadamente com cada situação. Configure as três linhas no Conditional Split seguindo a figura abaixo.

Captura de Tela 2015-10-13 às 17.39.27

Após ter configurado cada uma das três linhas de código do Conditional Split, você o ligará ao componente OLE DB Destination. Será apresentada uma janela com a opção de você fazer esse link pela condição Insert, Update ou Histórico. Escolha a opção Insert. Configure este componente para receber os novos registros, ignorando a coluna SK (ela é IDENTITY) e a coluna DT_FIM, logo esses registros não possuem esse dado pois são novos.

Captura de Tela 2015-10-13 às 17.47.37

Agora faça outra ligação do Conditional Split para o primeiro OLE DB Command. Selecione a opção UPDATE na janela de configurações.

Esse componente merece uma atenção especial pois, além de sua configuração ser um pouco mais complexa que a dos demais componentes, ele é o principal componente do fluxo por ser o responsável, tanto pelos updates, quanto pela atualização do versionamento dos registros através das colunas DATA_INICIO e DATA_FIM, criando assim os registros históricos. Ele funciona através de parâmetros e queries ANSI.

Ao abrir a tela do componente OLE DB Command, ele estará na aba de conexões. Você precisará indicar o banco o qual deseja fazer mudanças. Ness caso, faça conexão com o database OLAP.

Na aba de propriedades do componente, você se preocupará somente com a última definição: SqlCommand. Nesse campo você escreverá a query que será responsável pela mudança que você desejar executar nos seus através do ETL.

Nesse caso você usará o script abaixo.

UPDATE DM_CLIENTES2 SET DATA_INICIO = ?, NOME = ? WHERE ID = ? AND DATA_FIM IS NULL; 

Essa query é um simples de update, construída no padrão ANSI que fará a atualização dos campos DATA_INICIO, NOME com base no ID da dimensão DM_CLIENTES_v2.

Na próxima aba, você irá mapear que campos os parâmetros receberam para que possam atualizar o campo DATA_INICIO e NOME da Dimensão DM_CLIENTES_v2.

Os parâmetros são definidos na sequência exata de como são configurados na query. Ligue o Param_0 ao campo DT_INICIO (campo que foi definido no Derived Column antes de começarmos a configurar o OLE DB Command), o Param_1 à coluna NOME e o Param_2 à coluna ID (note que estamos usando os campos que vieram da origem e foram concatenados aos campos do destino com o auxilio do componente Merge Join).

Feito isso, você já tem configurado no fluxo os novos registros que serão adicionados diariamente pelo banco OLTP da Light LTDA e também as possíveis mudanças nos nomes dos Clientes, que possam vir a ocorrer devido algum tipo de erro de cadastro.


Agora você irá configurar a etapa final do fluxo que é a inserção dos registros históricos. Ligue o componente Conditional Split ao próximo OLE DB Command e na janela de configurações selecione a ultima opção disponível, HISTORICO. Conecte no banco de dados OLAP.

Usaremos o código abaixo para configurar na aba de propriedades do componente a opção SqlCommand.

UPDATE DM_CLIENTES_v2 SET DATA_FIM = ? WHERE ID = ? AND DATA_FIM IS NULL;

Essa query também realizará um update, e também é construída em padrão ANSI. Por sua vez, será responsável pela atualização do campo DATA_FIM dos registros que formarão o conteúdo histórico da dimensão DM_CLIENTES_v2.

Como já fora dito, basta seguir uma sequência lógica para configurar os parâmetros aos campos devidos. O primeiro parâmetro fora definido como sendo o novo valor da coluna DATA_FIM, logo o ligaremos aos campo DATA_FIM. O segundo ligaremos à coluna ID.

O próximo passo é a configuração do OLE DB Command que fará a inserção do novo registro histórico. Você usará a query abaixo para configurar a opção SqlCommand.

INSERT INTO DM_CLIENTES_v2 (
    ID,NOME,CPF,EMAIL,DDD,TELEFONE,DATA_INICIO )
VALUES (?,?,?,?,?,?,?);

Esta é a última query (também em ANSI) de configuração do nosso fluxo de SCD que está sendo criado. Essa query de INSERT irá depositar na dimensão DM_CLIENTES_v2 todos os registros que ainda não foram adicionados no Data Warehouse da Light LTDA.

Agora apenas configure os parâmetros de forma sequencial à forma que a query foi construída ligando cada um deles ao campo responsável por popular cada coluna da DM_CLIENTES_v2. O fluxo ficará conforme a figura abaixo.

Captura de Tela 2015-10-13 às 18.17.03

Dessa forma, resumidamente, o Fluxo de SCD criado funciona assim:

  1. O fluxo lê os dados do OLTP e da Dimensão que está no OLAP;
  2. Em seguida ordena esses dados pela chave primária da dimensão (nesse caso o campo ID);
  3. Acontece no componente Merge Join nada mais que uma união entre os campos da origem e do destino. É formada uma grande tabela. Isso serve para que o proximo componente possa, com base na comparação entre um campo da origem e o mesmo campo no destino, decidir se aquela linha de registro irá ser inserida, atualizada, transformada em histórico ou se nem mesmo participará do resto do fluxo;
  4. O Conditional Split funciona como um Case da liguagem SQL. Ele irá decidir como cada linha de registro vai ser tratada com base na comparação entre o registro na origem e aquele mesmo registro do destino. Caso naquela linha de registro o campo de ID da origem estiver preenchido e o ID do destino não, ele direciona o dado para ser inserido na Dimensão. Funcionará no mesmo raciocínio com o fluxo que direciona para o Update e para o Histórico.

Se executada, a carga de ETL que foi criada será capaz de inserir todos os 500 mil registros da tabela Clientes na dimensão DM_CLIENTES_v2, em apenas aproximadamente 7 segundos.


Como foi demonstrado, o componente Slowly Changing Dimension do Integration Services, apesar de cumprir com o prometido de manter o versionamento dos dados históricos, deixa muito a desejar com relação a performance.

Não fora demonstradas atualizações e inserções de registros, mas, caso seja de seu interesse, altere dados ou insira novas linhas no banco de dados OLTP da Light LTDA e observe como o SSIS irá trabalhar em ambas as situações. A performance é incomparável.

Sendo assim, seguindo os conceitos de SCDs e o passo a passo de criação do fluxo de ETL de Slowly Changing Dimension, será possível ajustá-lo às realidades de qualquer situação existente no mercado.

Anúncios

3 comentários sobre “SSIS – Criando seu próprio SCD 4

  1. Pingback: SSIS – Criando seu próprio SCD 1 | Alex Souza

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s