29 Outubro 2018

Princípios básicos do desenvolvimento de um processo ETL em Qlikview

A Qlik é uma empresa de software que fornece produtos como o QlikView e QlikSense, tendo sido considerada “líder” no quadrante mágico da Gartner para plataformas analíticas e de Business Intelligence em 2018 pelo oitavo ano consecutivo.

qlik gartner

O QlikView é uma ferramenta de Business Intelligence que permite aos utilizadores a análise de dados, proporcionando uma resposta rápida à mudança de requisitos de negócio.

Esta ferramenta permite a leitura de diversas fontes de dados, como bases de dados (Oracle, SQL Server…), ficheiros Excel, XML ou de texto, entre outros.

Ao nível da arquitetura da solução, é criado e executado um script no qual se especificam as tabelas a carregar. É possível criar várias tabs num script, de forma a estruturar o mesmo. Num novo script são gerados automaticamente uma série de comandos SET (usado para definir as variáveis do script), nos quais são definidas algumas configurações (sendo utilizada por defeito a configuração regional do computador no qual o script é gerado) mas é ainda possível incluir configurações (comandos SET) adicionais.

É também possível trabalhar com ficheiros QVD (.qvd) que são ficheiros que contém tabelas exportadas do QlikView, e podem ser novamente lidos pelo QlikView. Este é um formato otimizado e compacto, nativo do Qlik, tornando a leitura de dados extremamente rápida.

Desenvolvimento de um processo de ETL

Ao trabalhar com o QlikView é boa prática criar várias camadas de dados. De um modo geral, podemos construir um processo de ETL, criando um script (ficheiro. qvw) por cada uma das camadas E, T e L, com um ficheiro final em que se procede à criação do dashboard. Para facilitar o processo, deverão ser utilizados ficheiros QVD para armazenar os dados, removendo os mesmos de memória.

Exemplificação da arquitetura de referência da Qlik

Fonte: http://livingqlikview.com/create-layered-qlik-architecture/

O primeiro passo será a extração dos dados dos sistemas fonte, utilizando os comandos SELECT ou LOAD, consoante se tratem de dados provenientes de bases de dados ou outro tipo de dados (inclusive o resultado de um SELECT subsequente) respetivamente. Em seguida devem ser gerados os ficheiros QVD correspondentes às tabelas extraídas.

Abaixo um exemplo de carregamento de dados de um ficheiro (podendo ser utilizando um assistente para auxiliar a criação do comando) e geração de um ficheiro QVD (comando STORE):

É boa prática a definição e uso de variáveis para, por exemplo, definir caminhos, pois estas tornam o script mais dinâmico, evitando a repetição de dados “hardcoded”.

Exemplo: SET vPath = './QVDs/';

Esta variável vPath foi usada no exemplo acima, ao definir o caminho onde foi feito o STORE do QVD.

Quando se trata do carregamento de dados de uma base de dados, é possível criar uma ligação à mesma e gerar automaticamente a connection string através de um assistente, clicando no botão “connect” da tab “Data” do editor do script, escolhendo em seguida o provider correto e seguindo os passos de configuração da conexão.

Para gerar um SELECT a uma tabela da base de dados através do assistente do QlikView, clicar no botão “Select” que se encontra por baixo do botão “Connect”.

É ainda possível fazer o carregamento de uma tabela Inline, ou seja, contendo dados gerados no momento, conforme o exemplo abaixo, em que é gerado um calendário contendo todas as datas entre 2000 e 2020:

De forma a tornar o ficheiro (.qvw) mais leve, é importante fazer o drop das tabelas no modelo após gerar os ficheiros QVD, através do comando “DROP table ”. Estes dados não são perdidos pois já ficaram armazenados nos ficheiros QVD.

O passo seguinte será o passo de transformação dos dados, em que habitualmente se realizam ações como o cálculo de novos valores, junção de tabelas, renomear campos, agregação de valores e validação de dados, entre outras.

Tendo gerado ficheiros QVD no passo anterior, correspondentes às tabelas fonte, pode agora criar-se uma nova camada de dados, ou seja, um novo documento QlikView com um novo script, em que é feito o carregamento desses QVDs.

É conveniente gerar um modelo em estrela, com uma tabela de factos ligada a várias tabelas de dimensões, efetuando para isso as transformações necessárias aos dados originais.

Exemplo de modelo em estrela com informação de Vendas

É necessário ter em atenção que o QlikView faz automaticamente os joins entre tabelas quando dois campos em tabelas diferentes têm o mesmo nome. Desta forma, convém evitar nomenclaturas homogéneas entre campos que não sejam chave, e assegurar que os campos que são chaves das tabelas têm exatamente o mesmo nome. Além disso, se o nome do campo que faz a ligação entre tabelas não for exatamente o mesmo, ou se esse campo não for incluído no SELECT/LOAD o Qlikview irá proceder ao produto cartesiano das duas tabelas, o que, além de gerar informação incorreta, torna a tabela gerada extremamente pesada, tendo impactos na performance. Um outro problema quando se utilizam campos com nomenclatura homogénea em várias tabelas, sem que estes sejam campos chave, é a ocorrência de chaves sintéticas e referências circulares, que também impactam negativamente a performance (ver boas práticas/considerações). Para resolver estes problemas basta renomear os campos em conflito, utilizando um alias (keyword “as”).

Após a realização de todas as transformações e criação de um modelo em estrela, deverão ser gerados novos ficheiros QVD, desta feita contendo as tabelas de dimensões e tabela de factos obtidas e fazer o DROP das tabelas.

Na fase de carregamento (Load) é criado um QlikMart (datamart), em que são carregados os ficheiros QVD gerados na fase anterior. Este QlikMart corresponde ao modelo de dados em formato QVW que será posteriormente carregado num novo documento de QlikView por meio de binary load na presentation layer – ou seja, o carregamento dos dados é feito através de outro documento de QlikView.

Nas dimensões, se desejado, poderão ser considerados apenas os valores existentes na respetiva factual, utilizando o código “Where Exists(column_name);”. Isto permite que, posteriormente, ao desenvolver um dashboard, apenas apareçam disponíveis os valores dos campos utilizados, em vez de todos os valores que existem em cada uma das dimensões (útil quando existem muitas dimensões sem factos). Por exemplo, ao fazer o carregamento inline da dimensão data, criaram-se todas as datas entre o ano 2000 e o ano 2020, no entanto nem todas são utilizadas/existem na factual – ou seja, apesar de constarem na dimensão, não existem mais dados associados a esses valores, pelo que poderá não fazer sentido que os mesmos sejam apresentados num dashboard.

Por último, na camada final de dashboard é gerado um ficheiro QVW em que se efetua o carregamento do ficheiro QVW (QlikMart) gerado na fase de carregamento, através do comando “Binary file_name.qvw;”, que deverá ser sempre o primeiro do script. Este ficheiro representa a camada de apresentação de dados, não havendo qualquer transformação.

Depois de concluídas as várias etapas da construção de um modelo de dados, estaremos prontos para começar a construir um dashboard, que pode conter vários tipos de estruturas como listas, tabelas, gráficos e botões, entre muitos outros.

Alguns conceitos e boas práticas/considerações

• Chave sintética - é a forma de o QlikView lidar automaticamente com vários campos com nomes iguais em tabelas diferentes, gerando uma chave composta. Pode ser resolvido renomeando os campos, utilizando a keyword “as”.

• Referência circular - ocorre sempre que três ou mais tabelas se encontram ligadas criando um “loop”. Pode ser resolvida renomeando campos de forma a evitar ligações desnecessárias, utilizando a keyword “as”.

• Tipos de JOIN - Por defeito, o QlikView procede a um outer join. No entanto, é também possível consolidar duas tabelas numa só, definindo o tipo de join a efetuar, usando as keywords INNER JOIN, LEFT JOIN, RIGHT JOIN ou OUTER JOIN.

 
Adriana Policarpo         
     Consultant