Fala pessoal, beleza?!
Este post será dividido em duas partes: a primeira parte trata sobre como extrair dados armazenados no formato XML como coluna do banco de dados em um processo de transformação no ETL.
E a segunda, da criação da dimensão cliente, que contém esse tipo de estrutura em seu formato e possui informações que podem ser importantes para análise de perfil dos clientes.
EXTRAINDO DADOS XML
Antes de começar o post em si deixo claro que não irei entrar nos detalhes do formato XML. Por desconhecer e necessitar de um estudo mais dedicado sobre o assunto, iremos ver apenas como extrair os dados que interessam.
Se quiserem entender mais sobre XML, basta acessar este o site da w3school.
Nosso primeiro passo neste processo será consultar a fonte para extrair as colunas que nos interessam para análise.
Destaquei o inner join para mostrar que estou retornando na consulta somente os registros cadastrados como cliente na tabela person.
Já com a fonte configurada, vamos para o próximo passo que se trata da extração das colunas que estão armazenadas em XML no SQL Server.
Adicione o step get data from XML ligado ao step table input.
Abaixo a primeira configuração indicando para o step qual coluna vamos utilizar como fonte:
Repare que sublinhado em vermelho, indico que a fonte de dados provém de um campo da tabela e sublinhado em azul, indicando qual coluna contém a fonte.
Definido esta primeira parte da configuração, vá no SSMS na coluna demographics (baixe a query aqui) e quando o resultado for exibido, abra o link que a coluna retorna para acessar o formato.
Procure um dos registros que possuam estas informações:
Quando encontrar um registro que possua todas estas informações, selecione tudo e copie.
Mude para a aba content no step get data from XML. Aqui vamos configurar quais tags ou nodes vamos buscar dentro desse formato XML.
Na aba content clique em get xpath nodes para e cole os dados que foram copiados do link XML no banco de dados ali dentro, veja abaixo:
NOTA: não remova o link que está sublinhado em vermelho, caso o faça, irá causar erro na extração dos valores entre as tags.
Ao clicar em ok, uma nova janela se abrirá pedindo para selecionar o path para utilizar na transformação.
Selecione o primeiro e clique em ok novamente.
Selecionado o path vá para aba fields e clique em get fields.
Repare que o nome que a transformação extrai para as colunas é exatamente o mesmo que encontramos no arquivo XML..
Se executarmos a transformação, ainda não veremos as colunas separadas como queremos, mas podemos ter uma noção se a configuração do get data from XML funcionou corretamente.
Para selecionar as colunas que queremos extrair de fato do step, adicione o select values e clique em get fields to select
Estas são as colunas que vieram do step do XML, como podemos verificar, estão de acordo.
Ao final, adicione o step table output para destinarmos no banco de stage para o pré armazenamento.
NOTA: não altere os data types agora, foque apenas em separar as colunas da fonte xml e armazenar no stage, quando formos passar para o data warehouse na limpeza, vamos corrigir todos os erros.
Algumas colunas que vieram do XML eu achei melhor remover por não ver adição de valor em nenhuma situação de análise. Mas se quiserem, fiquem livres para deixar no seu projeto.
O processo de carga é um pouco lento por se tratar de um arquivo diferente e ter uma extração um pouco mais complexa, se atentem a isso.
Não se esqueçam é claro, do tratamento de erros. É sempre bom manter o hábito de criar essa configuração como prevenção.
Conferido os resultados.
CONCLUSÃO
Este foi um post mais tutorial explicando de maneira primária uma das formas de se interagir com o formato XML dentro do Pentaho.
Existem outros métodos que trarei mais a frente como forma de tutorial e até mais ‘rápido’ que o utilizado nesse processo.
Claro, também aproveitei para criar a dimensão cliente aliando ao tutorial!
Espero que gostem do post, saúde!
Baixe aqui o arquivo.
ManageBI 