Projetar um esquema de data warehouse
Como todos os bancos de dados relacionais, um data warehouse contém tabelas nas quais os dados que você deseja analisar são armazenados. Mais comumente, essas tabelas são organizadas em um esquema otimizado para modelagem multidimensional, no qual medidas numéricas associadas a eventos conhecidos como fatos podem ser agregadas pelos atributos de entidades associadas em várias dimensões. Por exemplo, as medidas associadas a uma ordem de venda (como o valor pago ou a quantidade de itens encomendados) podem ser agregadas por 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 armazéns de dados relacionais é definir um esquema que inclua dois tipos de tabela: tabelas de dimensão e tabelas de fatos .
Tabelas de dimensões
As tabelas de dimensões descrevem entidades comerciais, como produtos, pessoas, locais e datas. As tabelas de dimensão contêm colunas para atributos de uma entidade. Por exemplo, uma entidade cliente pode ter um nome, um sobrenome, um endereço de e-mail e um endereço postal (que pode consistir em um endereço, uma cidade, um código postal e um país ou região). Além das colunas de atributo, uma tabela de dimensões contém uma coluna de chave exclusiva que identifica exclusivamente cada linha da tabela. Na verdade, é comum que uma tabela de dimensões inclua duas colunas principais:
- Uma chave substituta que é específica para o data warehouse e identifica exclusivamente cada linha na tabela de dimensões no data warehouse - geralmente um número inteiro incrementante.
- Uma chave alternativa , geralmente uma chave natural ou comercial que é usada para identificar uma instância específica de uma entidade no sistema de origem transacional do qual o registro de entidade se originou - como um código de produto ou um ID de cliente.
Nota
Porquê ter duas chaves? Existem algumas boas razões:
- O armazém de dados pode ser preenchido com dados de vários sistemas de origem, o que pode levar ao risco de chaves de negócio duplicadas ou incompatíveis.
- Chaves numéricas simples geralmente têm melhor desempenho em consultas que unem muitas tabelas - um padrão comum em armazéns de dados.
- Os atributos das entidades podem mudar ao longo do tempo - por exemplo, um cliente pode alterar o seu endereço. Como o data warehouse é usado para dar suporte a relatórios históricos, convém manter um registro para cada instância de uma entidade em vários pontos no tempo; de modo que, por exemplo, as ordens de venda de um cliente específico são contadas para a cidade onde ele morava no momento em que o pedido foi feito. Nesse caso, vários registros de clientes teriam a mesma chave comercial associada ao cliente, mas chaves substitutas diferentes para cada endereço discreto onde o cliente morava em vários momentos.
Um exemplo de uma tabela de dimensões para o cliente pode conter os seguintes dados:
| CustomerKey | ClienteAltKey | Nome | Rua | City | PostalCode | CountryRegion | |
|---|---|---|---|---|---|---|---|
| 123 | I-543º | Navin Jones | navin1@contoso.com | 1 Rua Principal | Porto | 90000 | Estados Unidos da América |
| 124 | R-589 | Maria Soares | mary2@contoso.com | 234 190 Ave | Buffalo | 50001 | Estados Unidos da América |
| 125 | I-321.º | Antoine Dubois | antoine1@contoso.com | 2 Rue Jolie | Paris | 20098 | França |
| 126 | I-543º | Navin Jones | navin1@contoso.com | 24 125ª Av. | Nova Iorque | 50000 | Estados Unidos da América |
| ... | ... | ... | ... | ... | ... | ... | ... |
Nota
Observe que a tabela contém dois registros para Navin Jones. Ambos os registros usam a mesma chave alternativa para identificar essa pessoa (I-543), mas cada registro tem uma chave substituta diferente. A partir disso, você pode supor que o cliente se mudou de Seattle para Nova York. As vendas feitas ao cliente enquanto morava em Seattle estão associadas à chave 123, enquanto as compras feitas depois de se mudar para Nova York são registradas contra o recorde de 126.
Além das tabelas de dimensão que representam entidades comerciais, é comum que um armazém de dados inclua uma tabela de dimensões que represente o tempo. Esta tabela permite que os analistas de dados agreguem dados em intervalos temporais. Dependendo do tipo de dados que você precisa analisar, a menor granularidade (referida como o grão) de uma dimensão de tempo pode representar tempos (para a hora, segundo, milissegundo, nanossegundo ou até inferior) ou datas.
Um exemplo de uma tabela de dimensão de tempo com um grão no nível de data pode conter os seguintes dados:
| DateKey | DataAltKey | DayOfWeek | DayOfMonth | Weekday | Month | NomeDoMês | Trimestre | Anual |
|---|---|---|---|---|---|---|---|---|
| 19990101 | 01-01-1999 | 6 | 1 | Sexta-feira | 1 | Janeiro | 1 | 1999 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20220101 | 01-01-2022 | 7 | 1 | Sábado | 1 | Janeiro | 1 | 2022 |
| 20220102 | 02-01-2022 | 1 | 2 | Domingo | 1 | Janeiro | 1 | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20301231 | 31-12-2030 | 3 | 31 | Tuesday | 12 | Dezembro | 4 | 2030 |
O período abrangido pelos registos no quadro deve incluir os primeiros e os últimos pontos no tempo para quaisquer eventos associados registados num quadro de factos conexo. Normalmente, há um registro para cada intervalo no grão apropriado no meio.
Tabelas de factos
As tabelas de fatos armazenam detalhes de observações ou eventos; por exemplo, ordens de venda, saldos de estoque, taxas de câmbio ou temperaturas registradas. 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 contendo detalhes de ordens de venda pode conter os seguintes dados:
| OrderDateKey | CustomerKey | StoreKey | ProductKey | N.º de encomenda | 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 seu grão. Por exemplo, a tabela de fatos de ordens de venda inclui chaves para datas, clientes, lojas e produtos. Um pedido pode incluir vários produtos, de modo que o grão representa itens de linha para produtos individuais vendidos em lojas para clientes em dias específicos.
Projetos de esquema de armazém de dados
Na maioria dos bancos de dados transacionais usados em aplicativos de negócios, os dados são normalizados para reduzir a duplicação. Em um data warehouse, no entanto, os dados de dimensão geralmente são desnormalizados para reduzir o número de junções necessárias para consultar os dados.
Muitas vezes, um data warehouse é organizado como um esquema em estrela , no qual uma tabela de fatos está diretamente relacionada às tabelas de dimensão, como mostrado neste exemplo:
Os atributos de uma entidade podem ser usados para agregar tabelas de medidas em vários níveis hierárquicos - por exemplo, para encontrar a receita total de vendas por país ou região, cidade, código postal ou cliente individual. Os atributos para 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 atributos hierárquicos, 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 de floco de neve, conforme mostrado no exemplo a seguir:
Neste caso, a tabela DimProduct foi normalizada para criar tabelas de dimensão 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-chave para as linhas correspondentes nas tabelas DimCategory e DimSupplier e cada linha nas tabelas DimCustomer e DimStore contém um valor-chave para a linha correspondente na tabela DimGeography .