11 Janeiro 2019

Técnicas de Validação de Lógica de Data Warehousing

Introdução

O presente artigo demonstra três validações lógicas de data warehousing que devem ser cumpridas e que permitem assegurar a qualidade de dados ao nível de verificação de tabelas sem duplicados, da inexistência de nulls e strings vazias e também garantir a integridade referencial entre as tabelas.

O teste dos duplicados consiste em detetar se existe mais do que um registo para a mesma chave de uma tabela.

O objetivo da validação da inexistência de nulls e string vazias é detetar a existência das mesmas, uma vez que não devem nunca os campos assumir estes valores.

Relativamente à integridade referencial, o objetivo deste teste passa por garantir que os valores das surrogate keys (SK’s) são corretamente referenciados entre as dimensões e tabelas de facto no data warehouse.

Abaixo encontra-se esquematizado um modelo exemplificativo que será usado ao longo deste artigo, a fim de podermos demonstrar problemas de dados que podem ser identificados com estes três testes.

Técnicas de Validação de Lógica de Data Warehousing

 

Verificar Duplicados

O teste dos duplicados consiste em detetar se existe mais do que um registo para a mesma chave. Esta verificação é bastante importante no contexto de um data wharehouse. Tomemos como exemplo o modelo de vendas apresentado. No caso de existir um registo duplicado para uma venda, o valor total de vendas de um determinado produto está a ser inflacionado incorretamente.

Dimensão

Para fazer estas validações é possível ter um conjunto de queries que validem todas as dimensões existentes no data warehouse. Estas validações são feitas de acordo com a respectiva chave da tabela.

     1. Chave de apenas uma Coluna

Na tabela abaixo é possível verificar um exemplo de uma dimensão com uma chave constituída por apenas uma coluna, e onde exemplifica um caso de duplicado. Neste caso a coluna que define a chave da dimensão é o Product_Code, pois só pode existir um produto para cada linha da dimensão D_Product.

A fim de se verificar a duplicação acima apresentada, é possível utilizar a seguinte query:

A query deve retornar 0 resultados. Se retornar alguma linha, esta representa um duplicado que pode consistir num ou mais múltiplos da mesma, dependendo da quantidade verificada. A quantidade de duplicados dessa linha pode ser verificada na coluna number_of_duplicates.

Neste caso, a query irá retornar um resultado, que representa o duplicado do product_code PRODUCT1.

     2. Chave com Múltiplas Colunas

Para verificar se uma tabela tem duplicados, onde a chave é definida por múltiplas colunas, tomemos como exemplo a tabela SnowFlake sales_category do nosso modelo. A query SQL deve ter em consideração o conjunto de colunas que compoem a granularidade mais alta da tabela, que neste caso é a combinação de produto e cliente, pois só pode existir uma categoria para cada combinação dos dois.

Assim, a query para a sales_category é criada em função dessas duas colunas:

A fim de se verificar a duplicação acima apresentada, utilizamos a seguinte query:

A query deve retornar 0 resultados. Da mesma forma, se retornar alguma linha, esta representa uma duplicação que pode consistir numa ou mais múltiplos da mesma, dependendo da quantidade verificada. A quantidade de duplicados dessa linha pode ser verificada na coluna number_of_duplicates.

Ao executar a query acima, iriamos obter uma linha com um duplicado.

Factual

   1. Chave por Degenerate Dimension

Tomando por base o nosso exemplo de modelo multidimensional, vamos supor que a factual f_sales tem uma coluna chamada Transaction_ID, que é constituída por uma referência única para cada venda presente na factual. O nível de granularidade da factual está, assim, reduzido apenas a essa coluna, que representa neste caso uma dimensão degenerada.

A query utilizada para encontrar duplicados é a seguinte:

A query deverá retornar 0 resultados. Da mesma forma, se resultar alguma linha, essa linha representa um duplicado.  A quantidade de duplicados dessa linha pode ser verificada na coluna number_of_duplicates.

     2. Chave com múltiplas colunas

O nível de granularidade de uma factual normalmente está definido ao nível da combinação dos identificadores únicos das dimensões, isto é, foreign keys ou surrogate keys, de atributos e de métricas não aditivas, como por exemplo um preço unitário.

Exemplo de registos da tabela F_Sales:

Seguindo mais uma vez o modelo multidimensional exemplificativo, a query para procurar por duplicados na factual é a seguinte:

A query deverá retornar 0 resultados. Da mesma forma, se resultar alguma linha, essa linha representa um duplicado. A quantidade de duplicados dessa linha pode ser verificada na coluna number_of_duplicates.

Ao executar a query acima, iriamos obter uma linha com um duplicado. Esta linha poderá ser uma duplicação ou simplesmente uma falha na agregação que faz com que sejam apresentadas duas linhas em vez de uma. Em todo o caso, existe um problema e foi detetado com esta validação.

Verificar a Integridade Referencial

As tabelas que compõem um data warehouse num modelo multidimensional devem obedecer à regra da integridade referencial, isto é, todas as chaves estrangeiras devem obrigatoriamente ter a correspondente chave primária na tabela fonte. O mesmo aplica-se se as tabelas estiverem a utilizar chaves artificiais como as Surrogate Keys.

Torna-se importante fazer esta validação, pois, por exemplo, se uma tabela de factos tiver chaves de produtos que não existem na dimensão, significa que existem produtos que estão a ser contabilizados de forma errada.

Tomando como exemplo o modelo multidimensional deste artigo, seguem-se alguns casos de análise:

     1. De Factual para Dimensão

O objetivo é garantir que todas as surrogate keys presentes na factual F_Sales existem nas dimensões correspondentes. Isto é, todos os valores da coluna sk_date presentes na F_Sales têm obrigatoriamente que existir na coluna sk_date da dimensão D_Date e a mesma lógica aplica-se aos outros SKs.

A query SQL para validar se existem sk_customer na f_Sales que não existem na D_Customer:

O resultado esperado são 0 linhas. Se a query devolver resultados, esses resultados representam os sk_customer que existem na F_Sales e que não têm referência na D_Customer.

     2. De Snowflake para Dimensão

Da mesma forma temos de garantir que uma tabela snowflake tem as Surrogate Keys todas na respectiva dimensão.

Voltando ao exemplo, a seguinte query valida se os sk_customer que existem na Sales_category também existem na sua tabela de origem, D_Customer:

Verificar Nulls e Strings Vazias

O objetivo desta verificação é detetar a existência de nulls e strings vazias, uma vez que não devem nunca os campos assumir estes valores. A verificação de nulls e string vazias é aplicada a campos do tipo VARCHAR e NVARCHAR, já nos restantes tipos (ex.: integer, double, …) a verificação aplica-se apenas ao tipo null.

Analisando o exemplo abaixo, para a segunda venda podemos verificar que está em falta o valor da venda (está a null). Estes casos não se podem verificar num data warehouse, pois não se pode apresentar um registo de uma venda sem saber o seu valor. Um outro exemplo é uma string vazia num atributo, como se pode verificar na terceira venda na tabela abaixo. Neste caso, sem o campo preenchido não se sabe a origem da venda, o que pode gerar conflitos se na camada de apresentação se tiver em conta as fontes de venda nos cálculos apresentados.

Para fazer estas verificações é possível ter um conjunto de queries que validem cada campo de cada tabela existente. Para analisar os casos acima apresentados podemos utilizar as seguintes queries:

  • Validar o campo Source_System (data type varchar)

  • Validar o campo Invoiced_Sales (data type decimal)

  Ana Russo            
 Consultant     

 

 
Tiago Marques       
   Consultant     

 



					
Blog