SCD – Slowly Changing Dimensions

Hoje irei demonstrar como criar dimensões históricas – Slowly Changing Dimensions, ou simplesmente SCD – através do Talend. Este tipo de dimensão é interessante em muitos processos onde seja importante manter histórico dos dados.

Primeiramente, crie um arquivo de texto com o seguinte conteúdo:

ID;Funcionario;EstCivil;Cargo;SalBase;Comissao
33;Pedro Siqueira;Solteiro;Vendedor;2100.00;3

Em seguida, crie um novo metadado no repositório de seu projeto apontando para este arquivo.

O schema deste metadado deverá ficar conforme a imagem abaixo:

Feito isso, crie uma conexão com o banco de dados que você irá utilizar, neste tutorial estou utilizando o MySQL.

Crie um novo job e então selecione as duas conexões até este momento criadas e arraste para o mesmo, uma nova janela irá surgir para que você escolha em qual componente deseja utilizar cada conexão, selecione tMysqlSCD para a conexão com o banco de dados e tFileInputDelimited para o arquivo de texto.

Arraste o fluxo Main a partir do componente tFileInputDelimited até o componente tMysqlSCD (clique com o direito sobre o primeiro, vá ao submenu Row, clique em Main e então leve a seta até o componente de saída).

Selecione o componente tMysqlSCD e vá até a view Component, altere a opção “Action o table” para “Create table if not exists” e então vá até a opção “SCD Editor”.

Uma nova janela irá se abrir onde customizaremos o funcionamento de nossa SCD, mas primeiramente é necessário compreender o que são os Types SCD:

Os Types dentro do SCD determinam qual atitude desejamos tomar caso um determinado campo em um registro sofra alterações, os types utilizados no Talend são os seguintes:

Type 0 – Sem ação
Type 1 – Sem histórico
Type 2 – Histórico através de registros (linhas)
Type 3 – Histórico através de campos (colunas)

Além dos types, temos também a Surrogate key, que é a nossa chave substituta, afinal é uma boa prática ter uma chave diferente daquela do sistema de origem.

Vamos arrastar o campo ID de Unused para Source keys
Funcionario para Type 0
EstCivil para Type 1 pois desejamos atualizar este campo, mas não manter histórico
Cargo e SalBase para Type 2 pois desejamos manter histórico através de novos registros
Comissao para Type 3, pois desejamos manter a comissão anterior como histórico em outro campo no mesmo registro, altere também o nome do campo em previous value de previous_Comissao para Comissao_Anterior.

Vamos também marcar as opções version e active, para que sejam criados dois campos onde teremos o indicador de ativo e a versão do registro.

Por fim, vamos dar um nome à nossa Surrogate key, coloque SK e na opção creation deixe “Table max + 1”.

Execute o job.

Após a execução, abra o SQL Builder, selecionando o componente tMysqlSCD, indo até a view Component e clicando no botão com um par de óculos ao lado do campo “Table”.

Execute a query “select * from dm_funcionario”, a dimensão que acabamos de criar através deste componente. Observe o resultado, como as colunas estão desordenadas:

Uma possível solução a esse problema seria editar o schema do componente, no entanto parece que o editor SCD e o editor de schema não conversam muito bem e provavelmente você irá se deparar com o seguinte erro:

Isso acontece pois o previous value do type 3 é renomeado erroneamente para o mesmo nome do current value, e se você tentar renomear, o schema voltará à desordem anterior.

Existem alguns workarounds para este problema, vulgo gambiarras, e nós iremos evitá-las em pról da boa prática de criar nossos modelos de dados e tabelas previamente ao invés de encarregar as ferramentas de ETL.

Delete a tabela dm_funcionario criada pelo componente de SCD e crie novamente executando a seguinte query:

CREATE TABLE `demoproject`.`dm_funcionario` (
 `SK` int(11) NOT NULL,
 `ID` int(11) NOT NULL,
 `Funcionario` varchar(30) DEFAULT NULL,
 `EstCivil` varchar(10) DEFAULT NULL,
 `Cargo` varchar(15) DEFAULT NULL,
 `SalBase` float(10,3) DEFAULT NULL,
 `Comissao` float(3,2) DEFAULT NULL,
 `Comissao_Anterior` float(3,2) DEFAULT NULL,
 `scd_active` bit(1) NOT NULL,
 `scd_version` int(11) NOT NULL,
 `scd_start` datetime NOT NULL,
 `scd_end` datetime DEFAULT NULL,
 PRIMARY KEY (`SK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Execute novamente o job e depois vá novamente ao SQL Builder e execute “select * from dm_funcionario”, veja que agora tomamos o controle da estrutura de nossa tabela por mais que o schema esteja desordenado.

Agora vamos realizar alguns experimentos alterando o nosso arquivo de entrada e observando os resultados.

Type 0

Primeiramente, experimente alterar o nome do funcionário: observe que mesmo que nenhuma alteração acontece em nossa tabela.

E se agora você alterar o ID do funcionário? Um novo registro será inserido em nossa tabela com o nome anteriormente alterado, pois o ID que é nossa chave na origem foi alterado, porém o registro anterior permanecerá ativo.

Type 1

Agora vamos alterar o campo EstCivil deste funcionário: o campo é atualizado, mas nenhum histórico é mantido. Experimente alterar o estado civil e o nome do funcionário ao mesmo tempo e observe que mesmo assim o nome do mesmo não é atualizado.

Type 2

Agora deixando o nome ainda alterado, experimente alterar os campos Cargo e/ou o SalBase do funcionário: um novo registro será inserido com estes campos atualizados, o registro anterior de mesmo ID será marcado como inativo (scd_active = 0) e terá atualizado o campo indicando o período de tempo pelo qual ele ficou ativo (scd_end). O novo registro será marcado como ativo (scd_active = 1) e terá a versão incrementada (scd_version = 2).

Além disso, uma observação importante e que você deverá levar em conta para tomá-la a seu favor: observe que o nome do funcionário foi atualizado! A explicação para isso é os dados a serem inseridos no novo registro são obtidos a partir do arquivo de entrada, e neste caso mesmo os campos marcados como type 0 irão se comportar como type 2. Use isso a seu favor: há situações nas quais determinados campos somente devem ser atualizados caso outros campos sejam alterados, nestes casos, utilize type 0 e type 2 em conjunto.

Type 3

Por fim, experimente alterar o campo Comissao no arquivo de entrada: observe que o valor anterior irá para o campo Comissao_Anterior e o novo valor será atualizado no campo Comissao.

É isso, qualquer dúvida, talendbrasil.com.br 🙂

Anúncios