Data Warehouse

Dashboard Violência Contra Mulheres – Parte 2 – Populando Dimensões do DW com Pentaho

Olá pessoal, nesta segunda parte do nosso artigo, iremos criar e popular um Data Warehouse para o nosso dashboard. Criaremos uma base de dados dimensional extraindo os dados do modelo anterior com o Pentaho Data Integration. Na Parte 1 demonstrei como extrair os dados a partir de uma fonte de dados utilizando web scraping, com ajuda do Python e preenchendo uma base de dados relacional com o PDI. 

Relacional x Dimensional

Uma base de dados relacional utiliza a modelagem que estamos acostumados, dentro das formas de normalização e suas regras. Já uma base dimensional tem uma abordagem diferente, os dados ficam separados em dimensões e temos umas tabela fato, este modelo é conhecido como esquema estrela. Veja na imagem abaixo  um simples diagrama (que não está usando nada de UML) de como ficará a nossa base de dados, o nome “estrela” vem do formato do esquema onde todas as dimensões estão ligadas a fato.

Modelagem dimensional, esquema estrela
Modelagem dimensional, esquema estrela

A tabela fato é populada com informações que descrevem o fato principal dos dados, em nosso caso cada ocorrência é um fato. Já as dimensões são criadas para descrever os fatos, em nosso exemplo teremos uma dimensão que descreve o tipo da ocorrência, outra que descreve a região e uma para as datas. Nosso esquema ficou bem simples, porém se tivéssemos mais detalhes nos dados, como dia exato de cada ocorrência, ou cidade onde ocorreram poderíamos incrementar mais nossas dimensões.

Na tabela fato (FATO_OCORRENCIA) temos as chaves que à liga as outras dimensões, em um Data Warehouse chamamos estas chaves de Surrogate Keys, ou chaves substitutas. Como não temos maiores informações as dimensões de Tipo (DIM_TIPO) e de Região (DIM_REGIAO) estão bem simples, se tivéssemos maiores informações sobre os locais da ocorrências poderíamos ter outros atributos relevantes como cidade, bairro, região, etc.

Uma dimensão que sempre deve existir em um DW é a de Data, a mesma é populada apenas uma vez e por um longo período de tempo, por exemplo 50 anos. Mas o que devemos colocar nessa dimensão? Tudo relacionado a Data como: dia, mês, ano, hora do dia, se foi feriado, se é final de semana, se é véspera de feriado, etc… Cada dia é um registro, então para 50 anos teríamos em torno de 18.250 registros (365 x 50), cada um com seu ID e que será ligado a um fato, assim podemos obter informações importantes a respeito do fato, como: Com que frequência ocorre no final de semana? E em qual feriado o fato ocorre mais? Em nosso caso terão menos registros pois não temos os dias das ocorrências, apenas os meses.

Extração de Dados

Vamos a parte da extração dos dados, nosso Job principal para esta tarefa chama outras transformações, cada uma para a sua dimensão específica. Para as dimensões de Tipo de Ocorrência e Região consultaremos a nossa base atual, relacional. Já para a dimensão de data será criada uma transformação no PDI  com informações relevantes para nós.

Job para popular DW de Violência Contra as Mulheres
Job para popular DW de Violência Contra as Mulheres

Dimensão Tipo de Ocorrência

A transformação que irá popular a nossa dimensão tipo é extremamente simples, como temos uma tabela bem parecida vamos praticamente copiar os dados de uma para outra. Nesse caso vou utilizar um step para extrair dados de uma tabela através de uma consulta SQL, depois outro para ordenar os dados (o que poderia ser feito no SQL) e um que ficará responsável por persistir os dados na nossa base. Veja abaixo como fica a transformação:

Transformação responsável por popular a dimensão Tipo de Ocorrência
Transformação responsável por popular a dimensão Tipo de Ocorrência

A busca SQL é bem simples, apenas lembre-se de alterar a conexão para que a consulta seja feita na base correta.

Busca de Tipos de Ocorrência

A ordenaçao dos dados poderia ter sido feita no step anterior, um simples “ORDER BY” já resolveria nosso problema, porém coloquei este step a mais para demonstrar este recurso do PDI.

Ordenação dos resultados
Ordenação dos resultados

Por fim a persistência dos dados, utilize o step “Insert/Update” que está na aba “Output”. Nele colocamos a comparação do campo chave da nossa tabela de destino com a coluna do stream, no caso a chave da tabela é a coluna “tipo” e no stream nossos dados estão no campo “descricao”. Como só temos um campo vamos criar a mesma correspondência, também, na parte de baixo.

Persistindo dados na dimensão
Persistindo dados na dimensão

Dimensão Região

A nossa dimensão de Região é ainda mais simples de ser feita, como só temos dados fixos utilize um “Data Grid” que nada mais é do que uma tabela manual, a partir dele podemos inserir os dados em nossa dimensão. Serão apenas dois steps:

Transformação da Dimensão Região
Transformação da Dimensão Região

O step “Data Grid” está na aba “Input”, com ele podemos gerar uma estrutura de tabela e popular essa estrutura, é muito útil quando queremos gerar pequenas listas para comparações ou, como é nosso caso, para popular pequenas tabelas. Neste step temos duas abas: Meta e Data, na primeira criamos a estrutura para os nossos dados, podemos definir o nome da coluna, o tipo, formato, etc. Já na segunda imputamos os dados, veja abaixo como ficou em nosso exemplo:

Data Grid: Colunas
Data Grid: Colunas
Data Grid: Dados
Data Grid: Dados

Dimensão de Data

Vamos a dimensão de Data, está dimensão fundamental em um DW pois nos permite fazer análises com base em eventos e acontecimentos. Ela deve ter um grande range de datas onde a chave é o próprio dia, e os demais atributos podemos colocar colunas que nos respondam várias perguntas sobre o evento, por exemplo: qual o dia da semana? qual o bimestre? qual o trimestre? é feriado? qual? dia de jogo da seleção? período de olimpíadas? Com esses dados conseguimos analisar a influência dessas datas sobre o nosso evento, o que é muito importante para planejamentos estratégicos.

Se os dados que você utilizar possuírem horário (imagine informações de venda em um mercado) você pode criar uma dimensão Tempo, com informações sobre o horário do evento, muito útil para extrair informações como: qual horário determinado fato mais acontece? Em dia de futebol qual horário o evento mais ocorre? Em nosso exemplo, infelizmente, não temos o dia e o horário da ocorrência, somente o mês está disponível, então vários atributos dependentes do dia e da hora não conseguiremos obter.

Caso você utilize um dataset que disponha dessa informação pode utilizar o PDI para extrair essas informações, como esse é o formato mais comum vou disponibilizar uma transformação simples que montei. Basicamente o fluxo será criar um intervalo no período desejado, depois iterar sobre estes valores, com um contador, então incrementar uma data inicial e a cada loop extrair os dados necessários dessa data. Utilizando “lookups” conseguimos extrair outros dados a partir das datas geradas. Veja como fica a transformação:

Dimensão Data Completa
Dimensão Data Completa

Como não é o foco do nosso artigo não vou explicar passo-a-passo mas ela é bem intuitiva e você consegue “desvendar” o que foi feito. Um desafio bem grande é acrescentar a essa transformações datas variáveis, como por exemplo o dia das mães que é no segundo domingo de Maio, isso daria um bom artigo, quem sabe mais para frente.

Vamos para a transformação da dimensão data do nosso exemplo, assim como na transformação anterior deve-se definir um intervalo, criar um contador e incrementar a data inicial em “n meses”, onde o “n” é o valor do contador. Com a data atualizada a cada linha devemos extrair informações dela e por fim atualizar esses dados no banco. Como é uma transformação fixa não precisar ser sempre executada, veja como fica:

Dimensão Data para Meses
Dimensão Data para Meses

Primeiro temos de definir o range que será utilizando, vou criar um intervalo de 50 anos começando em Janeiro de 1980, como só temos os meses para saber o total de linhas que teremos multiplique os meses pelo total de anos, assim teremos 600 linhas. Com o step “Generate Rows” que fica na pasta “Input”, criaremos um campo do tipo Data, então aplicar um formato “yyyyMM” com o valor “198001”, com isso já temos nossa data inicial pronto em todas as linhas.

Generate Rows: Criação das linhas para o intervalo
Generate Rows: Criação das linhas para o intervalo

Agora é preciso definir um contador, utilize o step “Add sequence” dentro da pasta “Transform”, nele defina o nome do campo, no caso “contador_meses” e as propriedades do contador, então altere somente o valor inicial para 0, assim não  perdemos a primeira data do nosso intervalo.

Contador para intervalo de LinhasContador para intervalo de Linhas

Com este contador podemos incrementar nossa data em um mês a cada linha, para isso vamos utilizar o step “Calculator” que fica dentro da pasta “Transform”.

Criando cálculos a partir da data inicialCriando cálculos a partir da data inicial

Neste step crie o campo “dataCompleta”, na coluna “Calculo” escolha a opção “Date A + B Months” ou seja, some um valor em meses a uma data, daí na coluna “Campo A” escolha a nossa data inicial e na coluna “Campo B” o contador, dessa forma a cada linha a data será incrementada em n meses. Para extrair o ano dessa data e o número do mês, na coluna Calculo, utilize as opções “Ano da data A” e “Mês da data A”, para ambos o valor da coluna “Campo A” será a data que criamos na primeira linha. Podemos extrair várias informações sobre uma data, veja abaixo todos os cálculos possíveis:

Opções de cálculo de data
Opções de cálculo de data

Para não “passarmos” lixo para frente devemos selecionar somente os campos que serão utilizados, podemos fazer isso com o step “Select Values”, que está dentro da pasta “Transform”, nele selecione apenas os campos irá utilizar, veja abaixo:

Selecionando somente os campos que serão utilizados
Selecionando somente os campos que serão utilizados

Temos somente o número dos meses, no campo “mesNumero”, porém precisamos da sua descrição, para obter essa informação precisamos ter esses dados em algum lugar para que seja possível fazer um “join” com o número do mês, que já temos, e trazer os outros campos. Para esse processo utilize dois steps: “Data Grid” que fica na pasta “Input” e o “Stream Lookup” que está na pasta “Lookup”. No “Data Grid” crie uma tabela com os dados que queremos utilizar no “join”, ele deve conter um campo com o número do mês e as descrições, veja abaixo:

Grid com Dados dos Meses: Colunas
Grid com Dados dos Meses: Colunas

Depois de montar a estrutura do Grid popule-o com os dados dos meses, também criei uma coluna com a abreviação do mês. Muito importante que os valores da coluna “mesNumero” do Grid sejam os mesmo da coluna “mesNumero” do stream para realizar o “join” corretamente.

Grid com Dados dos Meses: Dados
Grid com Dados dos Meses: Dados

Agora adicione o step “Stream Lookup” para puxar os dados do Grid com base no valor da coluna “mesNumero” do stream. Na janela do step deixe o nome do step do Grid no campo “Lookup step”, este campo indica qual step iremos “olhar”. Abaixo selecione o campo que será a junção do stream com o grid, no caso os dois tem o mesmo nome “mesNumero”. Por fim temos de selecionar os campos do grid que serão “trazidos” para o strem caso o relacionamento das chaves seja concretizado, selecione os dois campos de descrição que criamos. Veja como ficou:

Stream Lookup: recuperando descrições dos meses a partir do número
Stream Lookup: recuperando descrições dos meses a partir do número

Para finalizar a transformação é necessário persistir os dados em nossa dimensão, utilize o step “Table Output” na pasta “Output”. Nele selecione a conexão e a tabela corretas, veja abaixo como ficou o relacionamento entre os campos do stream e os campos da tabela:

Persistir dados na Dimensão de Data
Persistir dados na Dimensão de Data

Arquivos utilizados

Conclusão

Neste artigo realizados a carga das dimensões do nosso DW com o Pentaho, dentre as dimensões, em um outros modelos podemos contar com diversas dimensões e cada uma precisa do seu processo de atualização, alguma com uma periodicidade maior do que outras. A dimensão de Data não tem a necessidade de ser carregada sempre, pois seus dados não mudam, porém outras podem sofrer alterações e precisariam de uma rotina constante de atualização. Você deve fazer esta análise e planejar corretamente estas periodicidades para que não sejam realizadas tarefas desnecessárias. No próximo artigo veremos como criar uma transformação para popular a tabela fato e o Job principal do nosso processo de ETL.

Até a próxima parte pessoal!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *