Facts about Facts: Organizing Fact Tables in Data Warehouse Systems

O processo de definição do seu sistema de armazenamento de dados (DWH) foi iniciado. Você delineou as tabelas de dimensões relevantes, que se relacionam com as necessidades do negócio. Essas tabelas definem o que nós pesamos, observamos e escalamos. Agora precisamos definir como medimos.

As tabelas de fatos são onde armazenamos essas medidas. Elas contêm dados empresariais que podem ser agregados através de combinações de dimensões. Mas o fato é que as tabelas de fatos não são tão facilmente descritas – elas têm seus próprios sabores. Neste artigo, vamos responder algumas perguntas básicas sobre tabelas de fatos e examinar os prós e contras de cada tipo.

O que são tabelas de fatos?

No sentido mais geral, tabelas de fatos são as medidas de um processo de negócio. Elas contêm principalmente dados numéricos e correspondem a um evento e não a um relatório em particular.

A característica mais importante de uma tabela de fatos, além das medidas, é o grão. O grão define que nível de detalhe é observado para um determinado evento. (Compare isto com a frase ‘controles de grão fino’, o que significa que os usuários podem controlar detalhes muito pequenos para sua conta).

Quanto mais detalhado for o grão da tabela de fatos, melhor ela lidará com requisitos de negócios imprevisíveis. O lado negativo de grãos maiores é que é necessário mais espaço físico para o armazenamento de dados. Também pode causar um desempenho mais lento.

Como são estruturadas as tabelas de fatos?

Uma tabela de fatos gerais consiste em dois grupos de atributos principais:

• Classificar as tabelas dimensionais
• Medidas

Classificar as tabelas dimensionais são auto-explicativas; as dimensões degeneradas também pertencem a este grupo. Uma dimensão degenerada é uma chave de dimensão sem tabela de dimensões parentais. Elas ocorrem quando todas as informações importantes sobre a dimensão já se encontram na tabela de fatos. Exemplos incluem vários números de cabeçalho de controle, números de tickets, números de pedidos, etc.

Medidas (ou seja métricas ou fatos comerciais) em uma tabela de fatos pode ser:

• Aditivo: somável através de qualquer dimensão
• Semi-aditivo: somável através de algumas dimensões
• Não aditivo: não somável (por exemplo, várias proporções)

Medidas laterais e chaves estrangeiras, pode haver muitas colunas técnicas. As colunas técnicas são úteis para a auditoria e manutenção de baixo nível do modelo. Os timestamps, que são usados para marcar quando as inserções ou atualizações ocorrem na tabela de fatos, são um exemplo comum de uma coluna técnica.

Para ilustrar uma tabela de fatos genérica, vamos olhar para um esquema de estrelas muito simples:

Neste modelo, temos uma única tabela de fatos rodeada por tabelas tridimensionais. Nossas chaves estrangeiras para a tabela de fatos incluem:

• time_id – refere-se à tabela de dimensões de tempo (dim_time)
• product_id – refere-se à tabela de dimensões de produto (dim_product)
• store_id – refere-se à tabela de dimensões de loja (dim_store)
• pos_transaction – uma dimensão degenerada. Esta é toda a informação sobre POS que precisamos, por isso podemos armazená-la aqui. Não há razão para construir outra tabela de dimensões.

O nosso grupo chave de medida consiste em:

• sales_quantity – As vendas de um produto de cada vez numa loja. Esta medida é aditiva.
• sales_price – O preço de um item como vendido na loja. Esta é uma medida não aditiva.

Existe uma coluna técnica, time_inserted, que armazena o tempo em que uma linha é inserida.

Agora temos uma idéia geral das tabelas de fatos, vamos cavar em seus diferentes sabores. Existem quatro tipos de tabelas de fatos: transações, instantâneos periódicos, instantâneos acumulados e tabelas de fatos sem fatos.

Todos os sabores servem para representar o negócio subjacente que o sistema de data warehousing suporta. Entretanto, antes de nos aprofundarmos no que essas diferentes tabelas de fatos fazem, vamos falar sobre um importante fator comum: a sparsity, ou a quantidade proporcional de dados armazenados em uma tabela de fatos. Sparsity está relacionado a grãos, e tem um efeito no desempenho da consulta.

Qual é a Sparsity da tabela de fatos?

Ao projetar uma tabela de fatos, considere a sua sparsity – ou seja, qual o número de linhas da tabela são preenchidas versus quantas estão vazias. Se nós preenchermos as tabelas de fatos de muitas tabelas subjacentes, é sábio estimar a sparsity. Nós fazemos a estimativa com base no nível de grão na tabela de fatos. Se a sua tabela de fatos corresponder à tabela de origem subjacente, você pode usar a cardinalidade da tabela subjacente para calcular a sparsity.

Para calcular a sparsity, primeiro encontramos a razão de preenchimento – ou seja, o número de células não vazias para o total de células. Para calcular a razão de preenchimento, divida o número de linhas na tabela de origem pelo número de linhas distintas em cada tabela de dimensões. A Sparsity é então calculada como 1-(f), onde f = taxa de preenchimento.

Exemplo:

Ponha que preenchamos a tabela fact_retail_sale de duas fontes: uma tabela de banco de dados com 100.000 linhas e uma planilha com 20.000 linhas. Estas linhas são preenchidas anualmente. As tabelas de dimensões são: datas (365 linhas), produtos (100 linhas) e lojas (1.000 linhas).

Calculamos sparsity como:

1-(100.000 + 20.000)/(365*100*1.000) = 0,99671

Esta tabela de fatos é esparsa, porque menos de 1% de suas linhas têm valores não zero.

Uma boa regra de um polegar é que consideramos qualquer coisa abaixo de 2% muito esparsa; qualquer coisa acima que não seja tão esparsa.

Tabelas de fatos de transação

Esta é a tabela de fatos mais simples e mais comum. O grão deste tipo é uma linha por transação, ou uma linha por linha em uma transação. O grão de uma tabela de fatos sobre transações é um ponto no espaço e no tempo. Eles contêm os menores detalhes do negócio.

Quando uma transação acontece, um amplo contexto sobre ela é capturado. Este contexto cria muitos detalhes nas tabelas de dimensão, então esperamos muitos deles.

Após inserirmos uma linha em uma tabela de transações ela raramente é revisitada, se é que alguma vez o é. Isto leva-nos a considerar técnicas específicas para povoar estes tipos de tabelas. Sem atualizações significa um ETL muito mais simples (extrair, transformar e carregar o processo).

Pros: Maior granularidade permite o monitoramento de atividades comerciais detalhadas.

Cons: Problemas de desempenho na consulta. Dificuldade em interpretar comportamentos de tendência em pontos fixos no tempo.

Um exemplo de uma tabela de fatos de transação

Vejamos um típico esquema de estrelas financeiras:

Registramos cada ação de conta na tabela de fatos. Os dados de exemplo desta tabela incluem:

Nota: time_date é representado como é mostrado na dimensão dim_time, e a coluna técnica é ignorada.

Como o dono da conta completa transações, como depositar e retirar dinheiro, nós registramos cada ação.

Com esta estrutura podemos responder perguntas como: Quantas transações por dia o nosso processo de negócios? Qual é o valor médio retirado em um dia? etc.

Tabelas de Instantâneos Periódicos

As tabelas de Instantâneos Periódicos registram o desempenho cumulativo do negócio em períodos de tempo pré-definidos. Um intervalo pré-determinado para tirar instantâneos é a chave: diário, semanal, mensal, etc. Os resultados são gravados na tabela de fatos periódicos.

Este tipo de tabela de fatos nos dá muita margem de manobra: podemos incorporar qualquer informação que descreva a atividade durante um período de tempo.

Mover de uma transação para uma tabela periódica é possível. (Há alguns algoritmos limpos em SQL que lidam com isso, como veremos nos próximos artigos). É apenas uma questão de somar as transações de um snapshot para outro. Assim como as tabelas de fatos de transações têm muitas dimensões, o snapshot geralmente tem menos dimensões.

Pros: Ganho de performance, visão longitudinal do negócio, compactação física.

Cons: Lower grain.

An Example of a Periodic Snapshot Fact Table

Let’s consideremos outro esquema de estrelas financeiras:

Let’s assumamos que construímos esta tabela de fatos e seu mart de dados a partir da mesma fonte do exemplo anterior. Poderíamos até ter construído esta tabela de fatos a partir dos dados da tabela de fatos anterior.

Precisamos mostrar o balance_amount em cada data de snapshot; neste caso, a granularidade do snapshot é de um dia. Nós agregamos todas as transações de saída diariamente, e o tipo de depósito é adicionar e retirar é subtrair. O resultado é:

Nota: Apresentamos time_date como é mostrado na dimensão dim_time e a coluna técnica é ignorada.

Agora podemos responder a algumas perguntas comerciais comuns, como por exemplo: Quantos dias um cliente tem uma conta com saldo positivo em um mês? Qual é o saldo médio de um tipo específico de cliente a cada mês? etc.

Reorganizar uma Tabela de Fatos Instantâneos Periódicos

Você pode ter notado alguma redundância nos dados exibidos no exemplo anterior. Às vezes até mesmo a tabela de fatos instantâneos é muito grande e o desempenho toma um tombo. O que fazer então? Podemos reorganizar nossa tabela de fatos instantâneos em algo mais compacto.

Adicionamos duas novas colunas, date_from e date_to. Elas representam o período de tempo em que o balance_amount foi válido.

Os dados nesta tabela ficariam assim:

A cada vez que o balance_amount muda, fechamos o período de tempo e abrimos um novo.

Notem que a coluna date_to contém um delimitador de tempo não inclusivo. Temos de ter isto em conta quando consultamos os dados. Também encontramos uma data interessante – 31/12/999999. Esta data demonstra que a última linha é o último saldo conhecido. Nós a atualizaremos quando o balanço mudar.

Below, podemos ver o SQL que usamos para consultar um instantâneo em uma data em particular (:date).

SELECT ft.*FROMfact_account_snapshot ftWHERE date_from 
Accumulating Snapshot Fact Tables
The accumulating snapshot fact table is the least common type, but it's also by far the most interesting and challenging. We use this table type when we represent processes that have a definite beginning and end.They are most appropriate when users must perform pipeline and workflow analysis.
Accumulating snapshot tables usually have multiple date foreign keys, each corresponding to a major milestone in the workflow. And while the processes captured in this type of table have beginnings and ends, intermediate steps are recorded as well.
Transaction fact tables may have several dates associated with each row; the accumulating fact table allows for many of the date values to be unknown when the row is first loaded. This is unique among fact tables, and it can lead to anomalies – incomplete dimensions – in dimensional modeling.
We solve this by adding a default value to the missing fields in the fact table and the appropriate dimension table. As we revisit the row containing specific workflows, we must implement a different type of ETL for working with these tables.
Pros: Enables complex analysis of tables and workflow analysis.
Cons: High ETL complexity.
An Example of an Accumulating Snapshot Fact Table
In this example, imagine that we have to analyze the effectiveness of our loan approval process, from initial application to final funding. Obviously, we first need to prepare our data!

  

Our loan approval process has four steps:
 
Loan application from a bank customer
 
Submission of complete documentation needed for loan approval (appraisal, credit status etc.)
 
Approval of the loan application from a higher-ranking bank employee
 
Funding the loan
 
One row in our fact table represents one instance of this process.
As we see in the data table, not all our workflows are complete; future actions will make us revisit the fact table and update our data. This is the main difference between this and other types of fact table. 
Some questions that we can answer with this type of fact table are: Where is the bottleneck in the loan approval process? What is causing it? What is the average time needed for end-to-end approval? etc.
 Let's look at the data from the fact table. We'll split the display into two tables:


Looking at the data, we see a strange date: 01.01.1900. This date represents a missing or "not yet filled" date. It is more practical to have a default value for missing key data so we don't have abnormalities when we do a roll-up. 
We can also draw some conclusions from this data. Bigger loans take more time to process. This is normal because they need more documentation and safety checks. Smaller loans complete the process much faster.
Factless Fact Tables
This inconspicuous fact table can also be found in the data warehouse modeling world. The factless fact table does not have any measurements; it only holds foreign keys to dimensional tables. This information is enough to answer relevant business questions.
Pros: Simplicity
Cons: Limited usefulness
An Example of a Factless Fact Table
In the following example, we see a star schema featuring a factless fact table. The schema is used for tracking student attendance, with four dimensional tables representing courses, facilities, dates, and departments.


With this schema, we can find answers to questions like: How many students attended a class last month? What course has the lowest attendance this year? What facility is not being used? etc.
A simple query can answer the last question. The code would be:
SELECT course_id, count(*) number of attendanceFROMfact_student_attendance ftJOIN dim_course dc ON (dc.course_id = ft.course_id)JOIN dim_time dt ON (dt.time_id = ft.time_id)WHERE dt.time_year = 2016GROUP BY course_idORDER BY count(*) desc
Nossa resposta é retornada na primeira linha da consulta.
As tabelas de fatos são a carne do mundo de armazenamento de dados. O seu tamanho pode ser igual a muitos terabytes, e eles ocupam o maior espaço num data warehouse. Um erro no início do processo de design da tabela de fatos gera muitos problemas, que não ficam mais fáceis de resolver à medida que o armazém evolui! 
 É possível reorganizar os dados de um tipo de tabela de fatos para outro. Vamos explorar técnicas para fazer isso em um próximo post. E sobre suas experiências com diferentes tipos de tabelas de fatos? Você conseguiu misturá-las de alguma forma nova e emocionante? Se conseguiu, avise-me.