Criar um esquema de data warehouse
Assim como todos os bancos de dados relacionais, um data warehouse contém tabelas nas quais os dados que você deseja analisar são armazenados. O mais comum é que essas tabelas sejam organizadas em um esquema otimizado para a modelagem multidimensional, em que medidas numéricas associadas a eventos conhecidos como fatos podem ser agregadas segundo os atributos de entidades associadas entre várias dimensões. Por exemplo, as medidas associadas a um pedido de venda (como o valor pago ou a quantidade de itens pedidos) podem ser agregadas segundo atributos da data em que a venda ocorreu, o cliente, a loja e assim por diante.
Tabelas em um data warehouse
Um padrão comum para data warehouses relacionais é definir um esquema que inclui dois tipos de tabela: tabelas de dimensões e tabelas de fatos.
Tabelas de dimensões
As tabelas de dimensões descrevem entidades de negócios, como produtos, pessoas, locais e datas. As tabelas de dimensões contêm colunas para os atributos de uma entidade. Por exemplo, uma entidade de cliente pode ter um nome, um sobrenome, um endereço de email e um endereço postal (que pode ser composto por um endereço, uma cidade, um código postal e um país ou uma região). Além das colunas de atributos, uma tabela de dimensões contém uma coluna de chave exclusiva que identifica exclusivamente cada linha na tabela. Na verdade, é comum que uma tabela de dimensões inclua duas colunas de chave:
- uma chave alternativa específica do data warehouse que identifica exclusivamente cada linha da tabela de dimensões no data warehouse – geralmente, um número inteiro incremental.
- Uma chave alternada, geralmente uma chave natural ou de negócios que é usada para identificar uma instância específica de uma entidade no sistema de origem transacional do qual o registro de entidade se origina – como um código de produto ou uma ID do cliente.
Observação
Por que ter duas chaves? Há alguns bons motivos:
- O data warehouse pode ser preenchido com dados de vários sistemas de origem, o que pode levar ao risco de ter chaves de negócios duplicadas ou incompatíveis.
- Chaves numéricas simples geralmente têm melhor desempenho em consultas que unem muitas tabelas – um padrão comum em data warehouses.
- Os atributos de entidades podem mudar ao longo do tempo – por exemplo, um cliente pode mudar de endereço. Como o data warehouse é usado para dar suporte a relatórios históricos, convém manter um registro de cada instância de uma entidade em vários pontos no tempo, para que, por exemplo, os pedidos de vendas de um cliente específico sejam contabilizados para a cidade onde ele morava no momento em que o pedido foi feito. Nesse caso, vários registros de cliente teriam a mesma chave de negócios associada ao cliente, mas chaves alternativas diferentes para cada endereço separado onde o cliente morou em diferentes momentos.
Um exemplo de uma tabela de dimensões para o cliente pode conter os seguintes dados:
| CustomerKey | CustomerAltKey | Nome | Street | City | PostalCode | CountryRegion | |
|---|---|---|---|---|---|---|---|
| 123 | I-543 | Navin Jones | navin1@contoso.com | 1 Main St. | Seattle | 90000 | Estados Unidos |
| 124 | R-589 | Mary Smith | mary2@contoso.com | 234 190th Ave | Buffalo | 50001 | Estados Unidos |
| 125 | I-321 | Antoine Dubois | antoine1@contoso.com | 2 Rue Jolie | Paris | 20098 | França |
| 126 | I-543 | Navin Jones | navin1@contoso.com | 24 125th Ave. | Nova Iorque | 50000 | Estados Unidos |
| ... | ... | ... | ... | ... | ... | ... | ... |
Observação
Observe que a tabela contém dois registros de Navin Jones. Ambos os registros usam a mesma chave alternada para identificar essa pessoa (I-543), mas cada registro tem uma chave alternativa diferente. Partindo disso, você pode supor que o cliente se mudou de Seattle para Nova York. Vendas feitas ao cliente enquanto ele morava em Seattle estão associadas à chave 123, enquanto compras feitas após a mudança para Nova York recebem o registro 126.
Além das tabelas de dimensões que representam entidades de negócios, é comum que um data warehouse inclua uma tabela de dimensões que representa o tempo. Essa tabela permite que analistas de dados agreguem dados em intervalos temporais. Dependendo do tipo de dados que você precisa analisar, a granularidade mais baixa de uma dimensão temporal pode representar horas (em unidades de hora, segundo, milissegundo, nanossegundo ou até menores) ou datas.
Um exemplo de tabela de dimensões temporais com granularidade no nível da data pode conter os seguintes dados:
| DateKey | DateAltKey | DayOfWeek | DayOfMonth | Weekday | Month | MonthName | Trimestre | Year |
|---|---|---|---|---|---|---|---|---|
| 19990101 | 01-01-1999 | 6 | 1 | Friday | 1 | Janeiro | 1 | 1999 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20220101 | 01-01-2022 | 7 | 1 | Sábado | 1 | Janeiro | 1 | 2022 |
| 20220102 | 02-01-2022 | 1 | 2 | Sunday | 1 | Janeiro | 1 | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20301231 | 31-12-2030 | 3 | 31 | Terça-feira | 12 | Dezembro | 4 | 2030 |
O período coberto pelos registros na tabela deve incluir os pontos mais antigos e mais recentes no tempo referentes aos eventos associados registrados em uma tabela de fatos relacionada. Normalmente, há um registro para cada intervalo com a granularidade apropriada no meio.
Tabela de fatos
Tabelas de fatos armazenam detalhes de observações ou eventos; por exemplo, ordens de vendas, saldos de ações, taxas de câmbio ou leituras de temperatura. Uma tabela de fatos contém colunas para valores numéricos que podem ser agregados por dimensões. Além das colunas numéricas, uma tabela de fatos contém colunas de chave que fazem referência a chaves exclusivas em tabelas de dimensões relacionadas.
Por exemplo, uma tabela de fatos que contém detalhes de ordens de vendas pode conter os seguintes dados:
| OrderDateKey | CustomerKey | StoreKey | ProductKey | OrderNo | LineItemNo | Quantidade | UnitPrice | Imposto | ItemTotal |
|---|---|---|---|---|---|---|---|---|---|
| 20220101 | 123 | 5 | 701 | 1001 | 1 | 2 | 2.50 | 0,50 | 5.50 |
| 20220101 | 123 | 5 | 765 | 1001 | 2 | 1 | 2,00 | 0,20 | 2,20 |
| 20220102 | 125 | 2 | 723 | 1002 | 1 | 1 | 4,99 | 0,49 | 5.48 |
| 20220103 | 126 | 1 | 823 | 1003 | 1 | 1 | 7,99 | 0.80 | 8.79 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
As colunas de chave de dimensão de uma tabela de fatos determinam sua granularidade. Por exemplo, a tabela de fatos de ordens de vendas inclui chaves para datas, clientes, lojas e produtos. Uma ordem pode incluir vários produtos, portanto, a granularidade representa itens de linha para produtos individuais vendidos nas lojas para clientes em dias específicos.
Designs de esquema de data warehouse
Na maioria dos bancos de dados transacionais usados em aplicativos empresariais, os dados são normalizados para reduzir a duplicação. No entanto, em um data warehouse, os dados de dimensão geralmente são desnormalizados para reduzir o número de junções necessárias para consultá-los.
Muitas vezes, um data warehouse é organizado como um esquema em estrela, em que uma tabela de fatos está diretamente relacionada às tabelas de dimensões, conforme mostrado neste exemplo:
Os atributos de uma entidade podem ser usados para agregar medidas em tabelas de fatos em vários níveis hierárquicos – por exemplo, para localizar a receita total de vendas por país ou região, cidade, código postal ou cliente individual. Os atributos de cada nível podem ser armazenados na mesma tabela de dimensões. No entanto, quando uma entidade tem um grande número de níveis de atributo hierárquico ou quando alguns atributos podem ser compartilhados por várias dimensões (por exemplo, clientes e lojas têm um endereço geográfico), pode fazer sentido aplicar alguma normalização às tabelas de dimensões e criar um esquema floco de neve, conforme mostrado no seguinte exemplo:
Nesse caso, a tabela DimProduct foi normalizada para criar tabelas de dimensões separadas para categorias de produtos e fornecedores, e uma tabela DimGeography foi adicionada para representar atributos geográficos para clientes e lojas. Cada linha na tabela DimProduct contém valores de chave para as linhas correspondentes nas tabelas DimCategory e DimSupplier; e cada linha nas tabelas DimCustomer e DimStore contém um valor de chave para a linha correspondente na tabela DimGeography.