20 Julho 2018

Como utilizar Python para comparar automaticamente os dados entre um sistema de DWH e de Reporting?

Importância de garantir a qualidade dos dados

Num sistema de Business Intelligence (BI), a camada de apresentação de um sistema de Data Warehouse (DWH) é aquela onde os dados se encontram organizados, armazenados e disponíveis para consulta. Por definição, todas as aplicações de BI acedem aos dados disponíveis nessa camada e as ferramentas de Reporting não são exceção. Os utilizadores de negócio fazem uso de tais ferramentas para analisar os dados disponíveis na camada de apresentação e, consequentemente, extrair informação que possa melhorar a tomada de decisão do seu negócio. Em ferramentas de Reporting como o Qlik Sense (Qlik), os dados ficam disponíveis após um processo de refrescamento, a partir do qual é feita uma leitura direta dos dados armazenados no Data Warehouse. Todavia, o refrescamento dos dados nem sempre ocorre com sucesso. Quando tal acontece, existe uma incoerência de dados entre o Qlik e a base de dados, afetando assim a análise que um utilizador de negócio pretenda fazer aos mesmos. Como forma de garantir a qualidade dos dados que chega ao utilizador final, é importante testar as visualizações criadas no Qlik, comparando os dados armazenados na Base de Dados/DWH com aqueles apresentados nessa ferramenta de Reporting.

Como comparar os dados entre o Qlik e o DWH?

Uma das ferramentas de Reporting que pode ser usada como parte integrante de uma solução de um sistema de Business Intelligence é o Qlik Sense (Qlik). Atualmente, não existe nenhuma funcionalidade out-of-the-box que permita de modo, imediato, comparar os dados gerados pelos relatórios do Qlik com os do sistema DWH de onde são lidos. Esse tipo de análise pode ser feita, manualmente, da seguinte forma:

1) Extrair os dados do relatório do Qlik. O Qlik disponibiliza uma funcionalidade que permite ao utilizador exportar os dados de um relatório/visualização nele disponível. Ao selecionar essa funcionalidade, é possível gerar um ficheiro Excel com todos os dados que alimentam essa visualização.

2) Extrair os dados do DWH: A extração de dados do sistema de DWH é feita mediante a leitura de queries criadas especificamente para consultar os dados nele armazenados. A ideia consiste em criar uma query que alimenta a visualização disponibilizada no Qlik e exportar os dados obtidos como resultado para um ficheiro de consulta (Excel).

3) Comparar os dados extraídos no Excel: A comparação dos dados extraídos nos pontos 1) e 2) pode ser feita através do Excel. Os dados são copiados e ordenados para 2 folhas de cálculo distintas: “DWH” e “Qlik”. A folha “DWH” apenas contém os dados extraídos do Data Warehouse, enquanto que a folha “Qlik” apenas contém os dados extraídos da visualização/relatório do Qlik.

Estas 2 folhas de cálculo podem ser lidas por outras 2 (“Comparison” e “Comparison Details”) que, mediante a aplicação de um conjunto de fórmulas, faz uma comparação direta dos valores de cada uma das células da folha de cálculo “DWH” com a respetiva célula da folha de cálculo “Qlik”. As fórmulas usadas nas folhas de cálculo “Comparison” e “Comparison Details” são, respetivamente:

=Qlik!A2=DWH!A2 e =IF(Comparison!C2=TRUE;0;CONCATENATE(Qlik!C2;" - ";DWH!C2)). As imagens seguintes ilustram a utilização do Excel na comparação dos dados entre o Qlik e o DWH.

python

Figura 1 – Comparação entre os dados de Qlik e de DWH – Folha de Cálculo DWH.

python

Figura 2 – Comparação entre os dados de Qlik e de DWH – Folha de Cálculo Qlik.

python

Figura 3 – Comparação entre os dados de Qlik e de DWH – Folha de Cálculo Comparison.

python

Figura 4 – Comparação entre os dados de Qlik e de DWH – Folha de Cálculo Comparison Details.

Desta forma, consegue-se analisar rapidamente os dados e perceber se existe alguma diferença entre os dados do Qlik e do DWH e, se tal acontecer, qual é a diferença e em que casos ela ocorre.

Quais os problemas resultantes de uma análise manual dos dados?

A comparação manual dos dados entre o Qlik e o DWH, nem sempre resulta numa análise rápida e eficaz. Se a informação extraída de uma visualização do Qlik resultar numa enorme quantidade de dados, analisá-los poderá consumir algum tempo, como tal, aplicar um conjunto de fórmulas, manualmente, a todas as linhas e colunas de um ficheiro Excel para comparar os dados não é prático e requer tempo por parte de quem está a analisar os mesmos.

Suponha-se que se pretende fazer uma análise das vendas faturadas para 2018, nos países da EMEA. Esta análise irá resultar em 50 linhas aproximadamente. Neste caso, a análise dos dados entre o Qlik e o DWH é relativamente rápida. No entanto, se o objetivo for fazer uma análise das vendas faturadas para 2018, nos países da EMEA, por cliente e produto, o número de linhas extraídas do Qlik e do DWH é bastante superior a 50, podendo rondar os milhares. Neste caso, a análise dos dados entre o Qlik e o DWH consome bastante tempo, não só devido à tarefa repetitiva de aplicar, manualmente, as fórmulas a todas as linhas do ficheiro do Excel, como também a copiar e a ordenar da mesma maneira os dados provenientes do Qlik e do DWH.

Garantir a mesma ordenação dos dados do Qlik e do DWH também é um aspeto importante a ter em conta na comparação dos dados, pois, desta forma, consegue-se evitar diferenças causadas por uma incorreta ordenação quando os dados são copiados para as folhas de cálculo do ficheiro Excel. Em suma, quanto maior for a volumetria de dados, maior será o tempo necessário a despender na sua análise.

Como automatizar a análise dos dados entre o Qlik e o DWH?

A comparação dos dados entre o Qlik e o DWH pode ser feita, automaticamente, mediante a utilização do Python e do Selenium. A ideia é simples e consiste em criar um script que receba, como input, os dados do Qlik e do DWH, compare-os entre si e devolva um ficheiro Excel como output, semelhante ao apresentado nas figuras 1-4. O ficheiro gerado pode ser usado para analisar os dados, aquando a comparação dos dados entre o Qlik e o DWH. Desta forma, qualquer pessoa é capaz de consultar o ficheiro e perceber como os dados foram comparados entre si e que fórmulas foram aplicadas na comparação dos mesmos.

Tal como referido anteriormente, a ideia consiste em passar dados de entrada, compara-los entre si e devolver o resultado da sua comparação. Tipicamente, os dados do Qlik são obtidos através de ficheiros excel. Já os dados do DWH podem ser obtidos através de uma query feita ao sistema sem que seja necessário exportá-los, previamente, para um ficheiro Excel para que possam ser consultados. Em Python, é possível definir modos distintos como os dados do Qlik e do DWH são passados como input. Dependendo da forma como os dados são obtidos, é possível criar um script que possibilite 3 tipos de dados de entrada e, consequentemente, 3 modos distintos de comparação de dados:

1) Excel vs Excel: dois ficheiros em formato Excel, um com os dados exportados do Qlik e outro com os dados exportados do DWH.
2) DWH vs Excel: um ficheiro Excel com os dados exportados do Qlik e um ficheiro de texto (.txt) com a query para ler os dados armazenados no DWH.
3) DWH vs Selenium: um ficheiro de texto (.txt) com a query para ler os dados armazenados no DWH e um link, passado num ficheiro de configuração, para o relatório/visualização do Qlik.

Excel vs Excel:
Se for escolhida uma comparação do tipo Excel vs Excel, a leitura dos dados via Python, passa por criar uma função capaz de ler os dados do Qlik e do DWH provenientes do ficheiro Excel e guardá-los em variáveis do tipo dataframe (qlik_df e dwh_df). Estas variáveis serão, posteriormente, usadas para a escrita do ficheiro de output.

DWH vs Excel:
Se a opção passar por uma comparação do tipo DWH vs Excel, significa que uma query num ficheiro de texto é passada como input como forma de obter os dados do DWH. Em Python, é necessário criar uma função, não só capaz de ler os dados do Qlik num ficheiro Excel, como também capaz de ler a query de entrada. Essa query terá de ser passada como argumento de uma outra função capaz de estabelecer uma conexão ao sistema de DWH e de executar a query para que os dados do DWH possam ser extraídos. Tal como no método anterior, os dados serão guardados em variáveis do tipo dataframe (qlik_df e dwh_df) para que possam ser usados, posteriormente, na escrita do ficheiro de output.

DWH vs Selenium:
Para o caso em que a opção passa por uma comparação do tipo DWH vs Selenium, a solução, tal como no modo anterior, passa por usar o Python para estabelecer a conexão ao DWH, correr a query de input e extrair os dados devolvidos como resultado de execução da query. A diferença está na forma como os dados do Qlik são obtidos. Uma alternativa consiste em utilizar o Selenium para aceder à visualização/relatório do Qlik, aplicar os filtros necessários, se tal se justificar, e exportar os dados para o formato excel sem que esta exportação tenha que ser feita manualmente. O Selenium é uma framework com bindings, disponível para Python, que providencia uma API para a escrita de testes funcionais do sistema. Desta forma, é possível escrever código que simule o comportamento de um utilizador de negócio quando este pretende aceder ou visualizar um relatório no Qlik e exportar os seus dados para análise. As funções disponíveis na API do Selenium replicam o comportamento do utilizador, recebendo como argumentos, elementos de código HTML ou XPath e sendo executadas sobre um webdriver. Novamente, os dados podem ser guardados em variáveis do tipo dataframe (qlik_df e dwh_df) para que possam ser usados na escrita do ficheiro de output.

Conversão do tipo de dados:

Um dos problemas subjacentes à extração dos dados do Qlik e do DWH está relacionado com o tipo de dados associado aos atributos das dimensões. Por vezes, um dado atributo de uma dimensão, no Qlik, apresenta um tipo de dados diferente do tipo de dados com o qual esse mesmo atributo foi criado no DWH. A existência de diferentes tipos de dados para um mesmo atributo faz com que os dados do Qlik e do DWH sejam ordenados de maneira diferente. A sua incorreta ordenação leva a que sejam detetadas diferenças quando estas, na verdade, não existem. Tome-se como exemplo a dimensão Cliente e o código do cliente como um dos atributos dessa dimensão.

Suponha-se também que existem 3 clientes, cujos códigos são 2000, 3000 e 10000. Se o código do cliente, no Qlik, for representado como uma string, ordenar esses 3 clientes, por ordem crescente, gera a sequência: 10000, 2000 e 3000. Se esse mesmo atributo for criado no DWH como um inteiro, a ordenação desses 3 clientes, por ordem crescente, gera a sequência: 2000, 3000 e 10000. Uma solução alternativa para lidar com as diferenças de tipos de dados entre o Qlik e o DWH consiste em converter todos os atributos das dimensões em string e as métricas como float, durante a leitura dos dados, no Python. Desta forma, garante-se que os dados do Qlik e do DWH serão ordenados sempre da mesma maneira.

Como distinguir as dimensões das métricas?
Um modo simples de distinguir as dimensões das métricas consiste em indicar o número de métricas envolvidas na análise, num ficheiro de configuração, e analisar os dados, criando relatórios/queries que devolvam tabelas compostas por dimensões do lado esquerdo e métricas do lado direito. Em Python, ao ler os dados do Qlik e do DWH conseguimos saber qual o número total de colunas e qual o número de colunas que representam as métricas (indicado no ficheiro de configuração). O número de colunas que representam as dimensões (M) é obtido, subtraindo o número total de colunas pelo número de métricas (N). Desta forma, conseguimos fazer 2 iterações distintas. As primeiras M colunas são dimensões e, como tal, os seus dados são convertidos em string, enquanto as últimas N colunas são métricas e, por isso, os seus dados são convertidos em floats.

Arredondamento das métricas:

É comum, ao comparar os dados entre o Qlik e o DWH, serem detetadas diferenças nos valores das métricas. Essas diferenças muitas vezes surgem devido à precisão com que os valores são apresentados, levando a que os dígitos das últimas casas decimais das métricas do Qlik e do DWH não sejam os mesmos. Não sendo uma diferença significativa, faz sentido arredondar os valores das métricas em 2 ou 3 casas decimais quando é feita a comparação dos dados. Em Python, apenas é preciso invocar a função responsável por arredondar os valores, em 2 ou 3 casas decimais, de todas as colunas que representam métricas. A solução descrita, anteriormente, para distinguir as dimensões das métricas pode ser também aqui aplicada, apenas para identificar as colunas que representam as métricas e iterar sobre elas para arredondar os valores nelas apresentados.

É importante notar que, independentemente do modo como é feita a comparação de dados (Excel vs Excel; DWH vs Excel ou DWH vs Selenium), a conversão do tipo de dados, assim como o arredondamento das métricas é aplicado, no Python, após a leitura dos dados do Qlik e do DWH e antes de serem guardados nos dataframes qlik_df e dwh_df. No caso do arredondamento das métricas, estas variáveis passam a ter mais uma coluna com os dados arredondados a 2 ou 3 casas decimais.

Escrita do ficheiro de output – Excel de comparação dos dados:

Tendo os dados do Qlik e do DWH disponíveis no Python, antes de proceder à escrita do ficheiro Excel com a sua comparação, é importante validar primeiro o número de linhas e de colunas dos dados do Qlik e do DWH guardados nas variáveis qlik_df e dwh_df. Se o número de linhas e/ou colunas for diferente em ambos, é devolvido um ficheiro de log com essa informação, em vez do ficheiro de Excel. Este ficheiro Excel para comparação dos dados apenas é criado se o número de linhas e colunas for igual em ambos os dataframes.

Uma possível solução para a escrita do ficheiro de Excel gerado como output, pode ser feita através da criação de uma função que recebe como argumentos as variáveis qlik_df e dwh_df, assim como a localização onde o ficheiro, devolvido como resultado, será gerado. Resumidamente, esta função recebe os dados do Qlik e do DWH e escreve, não só estes dados para um ficheiro de Excel, como também as fórmulas para a sua comparação. Estas fórmulas devolvem TRUE ou FALSE caso os dados sejam iguais ou não, respetivamente, assim como o detalhe da comparação (valor no Qlik versus valor no DWH). Em seguida, são apresentados os passos da função, assim como a ordem pela qual eles são executados.

1) Ordernar os dados: Os dados do Qlik e do DWH são ordenados do mesmo modo, ascendente ou descendentemente.

2) Escrita dos dados provenientes do DWH para a folha de excel “DWH”: Os dados da variável dwh_df são copiados para a folha de cálculo DWH do ficheiro de excel de comparação.

3) Escrita dos dados provenientes do Qlik para a folha de excel “Qlik”: Os dados da variável qlik_df são copiados para a folha de cálculo Qlik do ficheiro de Excel de comparação.

4) Escrita das fórmulas de comparação genérica: Um dataframe (comparison_df) é criado com um conjunto de fórmulas de comparação a aplicar a cada uma das células do Excel preenchidas com os valores do Qlik e do DWH provenientes das variáveis qlik_df e dwh_df, respetivamente. A fórmula a aplicar poderá ser definida num ficheiro de configuração como ‘=Qlik!??=DWH!??’ e construída, dinamicamente, de acordo com o número de linhas e de colunas provenientes das variáveis qlik_df e dwh_df, onde o símbolo ‘??’ é substituído pelo valor de cada célula do Excel à qual serão aplicadas (por exemplo, =Qlik!A2=DWH!A2). A Figura 5 mostra um exemplo do dataframe comparison_df que é gerado caso as variáveis qlik_df e dwh_df tenham 5 linhas e 4 colunas.

python

Figura 5 – Conjunto de fórmulas geradas e armazenadas na variável comparison_df para 5 linhas e 4 colunas.

Por fim, os dados da variável comparison_df são copiados para a folha de cálculo Comparison do ficheiro de Excel de comparação.

5) Escrita das fórmulas de comparação detalhada: um dataframe (comparison_details_df) é criado com um conjunto de fórmulas de comparação a aplicar a cada uma das células do Excel preenchidas com os valores do Qlik e do DWH provenientes das variáveis qlik_df e dwh_df, respetivamente. A fórmula a aplicar poderá ser definida num ficheiro de configuração como ‘=IF(Comparison!??=TRUE,0,CONCATENATE(Qlik!??,\" - \",DWH!??))’ e construída, dinamicamente, de acordo com o número de linhas e de colunas provenientes das variáveis qlik_df e dwh_df, onde o símbolo ‘??’ é substituído pelo valor da célula do excel à qual serão aplicadas (por exemplo, =IF(Comparison!A2=TRUE,0,CONCATENATE(Qlik!A2,\" - \",DWH!A2))). A Figura 6 mostra um exemplo do dataframe comparison_ details_df que é gerado caso as variáveis qlik_df e dwh_df tenham 5 linhas e 4 colunas.

python

Figura 6 – Conjunto de fórmulas geradas e armazenadas na variável comparison_details_df para 5 linhas e 4 colunas

Por fim, os dados da variável comparison_detail_df são copiados para a folha de cálculo Comparison Details do ficheiro de Excel de comparação.

6) Aplicar a formatação condicional às folhas de cálculo Comparison e Comparison Details: para estas folhas de cálculo, as células são preenchidas a verde ou rosa consoante o resultado das fórmulas referidas nos pontos 4 e 5. Se os valores apresentados nas células das folhas de cálculo do “DWH” e “Qlik” forem iguais entre si, as respetivas células nas folhas de cálculo “Comparison” e “Comparison Details” são preenchidas a verde. Caso contrário, são preenchidas a rosa.

python

Figura 7 – Formatação condicional aplicada às folhas de cálculo Comparison e Comparison Details.

7) Aplicar filtros e fixar o cabeçalho a todas as folhas de cálculo do ficheiro de output: em todas as folhas de cálculo (DWH, Qlik, Comparison e Comparison_Details) existe um cabeçalho com o nome das dimensões e métricas extraídas para análise. Os cabeçalhos são fixados e a eles são aplicados filtros, automaticamente.

python

Figura 8 – Filtros e freeze do cabeçalho aplicado às folhas de cálculo.

Após a execução destes passos, é criado o ficheiro de Excel de comparação, semelhante ao apresentado nas figuras 1-4, para a análise dos dados.

Ficheiro de configuração:

Nos parágrafos anteriores, foi mencionado o ficheiro de configuração. Neste ficheiro, podem ser definidas constantes, cujos valores possam ser parametrizáveis, tornando mais fácil a sua alteração. Eis alguns exemplos de parâmetros configuráveis:

• Fórmulas de comparação a aplicar no ficheiro de Excel;
• Localização dos ficheiros de entrada e de saída;
• Dados para a conexão ao DWH (Hostname; Port; User e Password);
• Link de acesso ao relatório/visualização do Qlik;
• Filtros a aplicar na visualização do Qlik;
• Número de métricas a analisar;
• Modo de comparação escolhido:
1 – Excel vs Excel
2 – DWH vs Excel
3 – DWH vs Selenium

Tornar o resultado de comparação entre o Qlik e DWH persistente:

Segundo esta solução, o resultado proveniente da comparação entre os dados do Qlik e do DWH apenas fica disponível no ficheiro de Excel. Para o caso de se querer persistir tal resultado numa base de dados como o Redshift, rapidamente e sem ter problemas de performance com a volumetria dos dados gerados, uma solução simples passaria por:

• converter os dados do ficheiro Excel para o formato .CSV;
• adicionar este novo ficheiro no S3;
• estabelecer uma conexão ao Redshift e executar o comando COPY..FROM para criar uma nova tabela com os dados do ficheiro de Excel.

Estando os dados disponíveis no Redshift, seria possível consultá-los diretamente ou até mesmo, criar um relatório no Qlik para que os mesmos possam ser analisados através de uma ferramenta de Reporting.