Olá pessoal, tudo bem!??
Hoje quero mostrar como desenvolvi minha dimensão produto para o portfólio.
Neste post, além de desenvolver a dimensão, mostro uma melhoria que fiz no processo de controle de mudanças, o Slowly changing dimension. Nesse processo, aprimorei o formato do tipo II que o PDI fornece.
Como eu estava percebendo algumas inconsistências dimension/lookup decidi criar uma transformação que classificasse os dados após uma comparação e com isso, modificar os registros ou criar o histórico desejado.
Para esta dimensão, trabalharemos criando um registro histórico.
TRANSFORMAÇÃO BASE
Abaixo a transformação base, com os jobs de transformação que carregam tanto o stage quanto o warehouse em si.
Como já existe um post sobre a stage da dimensão produto, vamos direto para a transformação da dimensão para o warehouse.
Como o ETL ficou grande para caber na imagem, irei dividir em partes e explicar a linha de raciocínio tomada a partir dos dados consumidos do banco.
Como na área de stage existiam muitos produtos com null e nem todos os produtos possuíam vendas registradas no período, separei entre ativos e inativos e então desenvolvi o tratamento dos dados ativos.
Na imagem, temos a estrutura base com a primeira parte do processo separando os produtos. Reparem que destacado em vermelho, utilizei o step Stream Lookup que utiliza duas fontes de dados para comparação.
Nesse processo, utilizei o arquivo txt extraído da tabela que registra todas as vendas no banco transacional chamada SalesOrderHeader, arquivo esse que foi utilizado como base.
Na imagem abaixo, a configuração da stream lookup.
Repare que o campo New name, será o campo que terá a classificação dos registros quando não houver match entre as comparações.
Após esta estrutura, utilizei o step filter rows para separar justamente por essa coluna, e enviar para uma outra tabela no warehouse os produtos “inativos”.
Com os registros classificados, adiciono o table output para gravar no banco esses produtos sem perdê-los no processo.
Com os produtos devidamente separados, posso tratar apenas os produtos “ativos” com maior agilidade.
Por ter uma quantidade menor de linhas, o processo além de ficar mais rápido fica assertivo e mais fácil de conferir.
Nesta segunda etapa do tratamento, normalizo todas as colunas que estavam inconsistentes e com campos vazios, começando pelas colunas ProductStyle, Class, Line e Name.
Para esta tarefa em si, utilizei a transformação Replace in string. Como haviam muitas abreviações nas colunas, esse step resolvia muito bem esse problema.
Cuidado quando utilizarem o use RegEx ele pode causar conflito e resultar em situações inesperadas. Por esse motivo, pouco utilizei nesta transformação em questão.
O uso do Whole Word e isUnicode é válido para garantir que o processamento irá ocorrer devidamente como desejamos e o unicode garante que mesmo se o campo for alterado implicitamente para NVARCHAR a transformação conseguirá processar corretamente.
NOTA: marque sempre a opção isUnicode, principalmente quando houver símbolos entre caracteres.
Ajustando estas três colunas, foi necessário padronizar os tamanhos dos produtos, ainda que muitos tenham sido padronizados de forma imprecisa. Os produtos que possuíam letras comuns utilizadas em tamanhos foram mais fáceis; quando me deparei com a classificação numérica, precisei criar um range para cada.
Veja na imagem abaixo como ficou estruturado.
Não foi possível criar um range de linha única dentro da transformação, mesmo após diversas tentativas, o step não se comportava muito bem, sendo necessário criar uma linha para cada número.
A normalização do nome dos produtos foi um processo mais complicado por ter redundância de informação.
Em alguns produtos era possível encontrar o tamanho, cor e até mesmo os dois juntos. Veja abaixo uma amostra do problema encontrado.
Utilizando a transformação split field destaquei os valores que vinham após a vírgula.
Após, precisei separar o campo nome em várias colunas para agrupar apenas as que atendiam de fato ao nome do produto. Nesse ponto, para não ficar confuso, adicionei outro split field, pois agora queria separar pelo espaço em branco.
Aqui temos a demonstração da coluna ProductName separada com as suas devidas criações.
Como a coluna PrNwName1 é uma das colunas que possui os dados desejados, mas, em algumas linhas havia nome de cor em seus registros, precisei substituir por null em alguns casos para ter um resultado melhor.
Para tal, utilizei a transformação NullIf com as cores encontradas nesta coluna.
A seguir limpei o nome dos produtos removendo os números que haviam, como por exemplo, os produtos da imagem acima.
Para tal, utilizei duas novas colunas onde uma ficou apenas com as letras e outra somente com os números. O step utilizado foi o operation string.
Daí, basta utilizar o concat fields, para unir as colunas que interessam e recriar o nome do produto devidamente normalizado, como no exemplo abaixo.
Por ter criado muitas colunas de apoio para limpar a tabela, utilizei a transformação select values removendo todas as colunas que não seriam mais úteis.
UTILIZANDO SQL UPDATE STEP
Em algumas colunas da tabela eu encontrei alguns problemas para normalizar os dados e por a dimensão em um estado conforme, como foi o caso das colunas ProductLine e Size.
Eu queria atualizar essas colunas com base em outras, mas por ser mais de uma, o valuemapper não ajudaria. Deste modo precisei recorrer a transformação execute sqlscript.
Esta transformação é bem simples também, utilizei de forma básica e não explorei muito o seu potencial. Assim, desenvolvi apenas o comando de update no banco de dados para testar o comportamento e passei para a transformação.
Esse foi o trecho final, onde utilizo as duas transformações, uma para cada coluna.
A seguir coloco os dois comandos para atualizar as duas colunas da dimensão. É importante deixar claro que o update deve ocorrer na stage e não no warehouse em si. Isso porque os dados estão “viajando” para seu destino final, então, eles precisam sair da fonte atualizados.
Uma outra solução aqui seria criar uma tarefa após a carga na dimensão, atualizando os dados já no seu destino.
O único problema deste step é que em algumas situações ele exibe um comportamento estranho e fora do usual, é sempre bom conferir e ficar de olho antes de executar a transformação com um todo.
No mais, ele atende bem às necessidades; como se estivéssemos executando no próprio banco.
Aqui temos as colunas corrigidas após as duas transformações de update serem executadas.
CARGA FINAL E ARQUIVO TXT
Finalizando esta parte da transformação, é hora da carga final no banco de dados.
Para esta carga utilizo o step Dimension lookup/update que já utilizamos em outros posts e mostrei como configurar sua ação. (neste post, temos um exemplo básico do uso.).
Dito isso, basta ligar as transformações e executar a carga, como mostra a imagem abaixo com os dados devidamente inseridos no banco.
Já o arquivo txt criado aqui, ele tem duas funções básicas:
- Garantir uma forma de carga rápida com os dados devidamente tratados, caso ocorra algum problema
- Comparar os dados que vem do stage com os dados que já estão no warehouse.
Essa comparação irei mostrar no próximo post e será o modelo de comparação para o processo de controle de mudanças.
CONCLUSÃO
Como o post ficou muito grande devido a quantidade de alterações necessárias para a tabela de produtos, achei melhor dividi-lo em dois e mostrar como elaborei o slowly changing dimension no próximo post; dedicado somente a isso.
A transformação em si não foi difícil embora tenha sido trabalhosa do ponto de vista da criação dos steps e da quantidade de vezes que precisei rever o processo para entender como estava interagindo com a fonte e o destino.
Tenham atenção e cuidado com o execute sql script. Em algumas situações seu comportamento é estranho.
Como esta foi a primeira carga, utilizei o dimension lookup/update para obter as colunas de controle que serão necessárias para o próximo passo deste processo.
Espero que gostem do post, deus os abençoe!
Saúde!
ManageBI 