Modelagem dimensional no Microsoft Fabric Warehouse: tabelas de carga

Aplica-se a: ponto de extremidade de análise SQL e Warehouse no Microsoft Fabric

Nota

Este artigo faz parte da série de artigos Modelagem dimensional. Esta série se concentra na orientação e nas práticas recomendadas de design relacionadas à modelagem dimensional no Microsoft Fabric Warehouse.

Este artigo fornece orientação e práticas recomendadas para carregar tabelas de dimensões e fatos em um modelo dimensional. Ele fornece orientação prática para o Warehouse no Microsoft Fabric, que é uma experiência que oferece suporte a muitos recursos do T-SQL, como a criação de tabelas e o gerenciamento de dados em tabelas. Assim, você tem o controle total de criar suas tabelas de modelo dimensional e carregá-las com dados.

Nota

Neste artigo, o termo data warehouse refere-se a um data warehouse corporativo, que oferece integração abrangente de dados críticos em toda a organização. Em contraste, o termo autônomo de armazém refere-se a um Fabric Warehouse, que é uma oferta de banco de dados relacional de software como serviço (SaaS) que você pode usar para implementar um data warehouse. Para maior clareza, neste artigo este último é mencionado como Armazém de Tecidos.

Gorjeta

Se você não tem experiência com modelagem dimensional, considere esta série de artigos seu primeiro passo. Não se destina a fornecer uma discussão completa sobre o design de modelagem dimensional. Para obter mais informações, consulte diretamente o conteúdo publicado amplamente adotado, como The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3ª edição, 2013), de Ralph Kimball, e outros.

Carregar um modelo dimensional

O carregamento de um modelo dimensional envolve a execução periódica de um processo ETL (Extract, Transform and Load). Um processo ETL orquestra a execução de outros processos, que geralmente estão preocupados com o preparo de dados de origem, sincronização de dados de dimensão, inserção de linhas em tabelas de fatos e registro de dados e erros de auditoria.

Para uma solução de Fabric Warehouse, você pode usar o Data Factory para desenvolver e executar seu processo de ETL. O processo pode preparar, transformar e carregar dados de origem em suas tabelas de modelo dimensional.

Mais concretamente, pode:

• Use pipelines de dados para criar fluxos de trabalho para orquestrar o processo ETL. Os pipelines de dados podem executar scripts SQL, procedimentos armazenados e muito mais.
• Use fluxos de dados para desenvolver lógica low-code para ingerir dados de centenas de fontes de dados. Os fluxos de dados suportam a combinação de dados de várias fontes, a transformação de dados e, em seguida, o carregamento para um destino, como uma tabela de modelo dimensional. Os fluxos de dados são criados usando a experiência familiar do Power Query que está disponível atualmente em muitos produtos da Microsoft, incluindo o Microsoft Excel e o Power BI Desktop.

Nota

O desenvolvimento de ETL pode ser complexo, e o desenvolvimento pode ser desafiador. Estima-se que 60-80% de um esforço de desenvolvimento de data warehouse seja dedicado ao processo de ETL.

Orquestração

O fluxo de trabalho geral de um processo ETL é:

1. Opcionalmente, carregue tabelas de preparo.
2. Tabelas de dimensões de processo.
3. Tabelas de fatos de processo.
4. Opcionalmente, execute tarefas de pós-processamento, como acionar a atualização do conteúdo dependente da malha (como um modelo semântico).

As tabelas de dimensão devem ser processadas primeiro para garantir que armazenem todos os membros da dimensão, incluindo aqueles adicionados aos sistemas de origem desde o último processo de ETL. Quando há dependências entre dimensões, como é o caso das dimensões outrigger, as tabelas de dimensões devem ser processadas em ordem de dependência. Por exemplo, uma dimensão geográfica usada por uma dimensão de cliente e uma dimensão de fornecedor devem ser processadas antes das outras duas dimensões.

As tabelas de fatos podem ser processadas quando todas as tabelas de dimensão são processadas.

Quando todas as tabelas de modelos dimensionais são processadas, você pode acionar a atualização de modelos semânticos dependentes. Também é uma boa ideia enviar uma notificação ao pessoal relevante para informá-lo do resultado do processo de ETL.

Dados do estágio

O preparo de dados de origem pode ajudar a dar suporte aos requisitos de carregamento e transformação de dados. Ele envolve extrair dados do sistema de origem e carregá-los em tabelas de preparo, que você cria para dar suporte ao processo ETL. Recomendamos que você prepare os dados de origem porque eles podem:

• Minimizar o impacto nos sistemas operacionais.
• Ser usado para auxiliar e otimizar o processamento de ETL.
• Fornecer a capacidade de reiniciar o processo ETL, sem a necessidade de recarregar dados de sistemas de origem.

Os dados em tabelas de preparo nunca devem ser disponibilizados para usuários corporativos. É apenas relevante para o processo de ETL.

Nota

Quando seus dados são armazenados em um Fabric Lakehouse, pode não ser necessário preparar seus dados no data warehouse. Se ele implementar uma arquitetura medalhão, você pode obter seus dados da camada de bronze, prata ou ouro.

Recomendamos que você crie um esquema no depósito, possivelmente chamado staging. As tabelas de preparo devem se assemelhar o mais possível às tabelas de origem em termos de nomes de colunas e tipos de dados. O conteúdo de cada tabela deve ser removido no início do processo ETL. No entanto, observe que as tabelas do Fabric Warehouse não podem ser truncadas. Em vez disso, você pode soltar e recriar cada tabela de preparo antes de carregá-la com dados.

Você também pode considerar alternativas de virtualização de dados como parte de sua estratégia de preparação. Pode utilizar:

• Espelhamento, que é uma solução turnkey de baixo custo e baixa latência que permite criar uma réplica de seus dados no OneLake. Para obter mais informações, consulte Por que usar o espelhamento na malha?.
• Atalhos do OneLake, que apontam para outros locais de armazenamento que podem conter seus dados de origem. Os atalhos podem ser usados como tabelas em consultas T-SQL.
• PolyBase no SQL Server, que é um recurso de virtualização de dados para o SQL Server. O PolyBase permite que consultas T-SQL juntem dados de fontes externas a tabelas relacionais em uma instância do SQL Server.
• Virtualização de dados com a Instância Gerenciada SQL do Azure, que permite executar consultas T-SQL em arquivos que armazenam dados em formatos de dados comuns no Azure Data Lake Storage (ADLS) Gen2 ou no Armazenamento de Blobs do Azure e combiná-los com dados relacionais armazenados localmente usando junções.

Transformar dados

A estrutura dos dados de origem pode não se assemelhar às estruturas de destino das tabelas de modelos dimensionais. Portanto, seu processo de ETL precisa remodelar os dados de origem para se alinhar com a estrutura das tabelas de modelo dimensional.

Além disso, o data warehouse deve fornecer dados limpos e conformes, portanto, os dados de origem podem precisar ser transformados para garantir qualidade e consistência.

Nota

O conceito de entrada de lixo, saída de lixo certamente se aplica ao armazenamento de dados — portanto, evite carregar dados de lixo (baixa qualidade) em suas tabelas de modelo dimensional.

Aqui estão algumas transformações que seu processo ETL pode executar.

• Combinar dados: os dados de diferentes fontes podem ser integrados (mesclados) com base em chaves correspondentes. Por exemplo, os dados do produto são armazenados em diferentes sistemas (como fabricação e marketing), mas todos eles usam uma unidade comum de manutenção de estoque (SKU). Os dados também podem ser anexados quando partilham uma estrutura comum. Por exemplo, os dados de vendas são armazenados em vários sistemas. Uma união das vendas de cada sistema pode produzir um superconjunto de todos os dados de vendas.
• Converter tipos de dados: os tipos de dados podem ser convertidos para aqueles definidos nas tabelas de modelos dimensionais.
• Cálculos: Os cálculos podem ser feitos para produzir valores para as tabelas de modelos dimensionais. Por exemplo, para uma tabela de dimensões de funcionário, você pode concatenar nomes e sobrenomes para produzir o nome completo. Como outro exemplo, para sua tabela de fatos de vendas, você pode calcular a receita bruta de vendas, que é o produto de preço unitário e quantidade.
• Detetar e gerenciar alterações históricas: as alterações podem ser detetadas e armazenadas adequadamente em tabelas de dimensão. Para obter mais informações, consulte Gerenciar alterações históricas mais adiante neste artigo.
• Dados agregados: A agregação pode ser usada para reduzir a dimensionalidade da tabela de fatos e/ou aumentar a granularidade dos fatos. Por exemplo, a tabela de fatos de vendas não precisa armazenar números de ordem de venda. Portanto, um resultado agregado que agrupa por todas as chaves de dimensão pode ser usado para armazenar os dados da tabela de fatos.

Carregar dados

Você pode carregar tabelas em um Armazém de Malha usando as seguintes opções de ingestão de dados.

• COPY INTO (T-SQL): essa opção é útil quando os dados de origem incluem arquivos Parquet ou CSV armazenados em uma conta de armazenamento externa do Azure, como ADLS Gen2 ou Armazenamento de Blob do Azure.
• Pipelines de dados: além de orquestrar o processo ETL, os pipelines de dados podem incluir atividades que executam instruções T-SQL, executam pesquisas ou copiam dados de uma fonte de dados para um destino.
• Fluxos de dados: Como uma alternativa aos pipelines de dados, os fluxos de dados fornecem uma experiência livre de código para transformar e limpar dados.
• Ingestão entre armazéns: Quando os dados são armazenados no mesmo espaço de trabalho, a ingestão entre armazéns permite unir diferentes mesas de armazém ou lakehouse. Ele suporta comandos T-SQL como INSERT…SELECT, SELECT INTOe CREATE TABLE AS SELECT (CTAS). Esses comandos são especialmente úteis quando você deseja transformar e carregar dados de tabelas de preparo dentro do mesmo espaço de trabalho. Eles também são operações baseadas em conjuntos, o que provavelmente será a maneira mais eficiente e rápida de carregar tabelas de modelos dimensionais.

Gorjeta

Para obter uma explicação completa dessas opções de ingestão de dados, incluindo as práticas recomendadas, consulte Ingerir dados no Warehouse.

Registo

Os processos de ETL geralmente requerem monitoramento e manutenção dedicados. Por esses motivos, recomendamos que você registre os resultados do processo ETL em tabelas de modelo não dimensionais em seu depósito. Você deve gerar um ID exclusivo para cada processo ETL e usá-lo para registrar detalhes sobre cada operação.

Considere o registro:

• O processo ETL:
  • Um ID exclusivo para cada execução de ETL
  • Hora de início e hora de fim
  • Status (sucesso ou fracasso)
  • Quaisquer erros encontrados
• Cada tabela de modelos dimensionais e de preparação:
  • Hora de início e hora de fim
  • Status (sucesso ou fracasso)
  • Linhas inseridas, atualizadas e excluídas
  • Contagem de linhas da tabela final
  • Quaisquer erros encontrados
• Outras operações:
  • Hora de início e hora de término das operações de atualização do modelo semântico

Gorjeta

Você pode criar um modelo semântico dedicado a monitorar e analisar seus processos de ETL. A duração do processo pode ajudá-lo a identificar gargalos que podem se beneficiar da revisão e otimização. As contagens de linhas podem permitir que você compreenda o tamanho da carga incremental cada vez que o ETL é executado e também ajudar a prever o tamanho futuro do data warehouse (e quando aumentar a capacidade da malha, se apropriado).

Tabelas de dimensões do processo

O processamento de uma tabela de dimensões envolve a sincronização dos dados do armazém de dados com os sistemas de origem. Os dados de origem são primeiro transformados e preparados para serem carregados em sua tabela de dimensões. Esses dados são então combinados com os dados da tabela de dimensão existente juntando-se nas chaves de negócios. Em seguida, é possível determinar se os dados de origem representam dados novos ou modificados. Quando a tabela de dimensões se aplica a dimensão de mudança lenta (SCD) tipo 1, as alterações são feitas atualizando as linhas da tabela de dimensões existentes. Quando a tabela aplica alterações do tipo 2 do SCD, a versão existente expira e uma nova versão é inserida.

O diagrama a seguir descreve a lógica usada para processar uma tabela de dimensão.

Considere o Product processo da tabela de dimensões.

• Quando novos produtos são adicionados ao sistema de origem, as linhas são inseridas na tabela de Product dimensões.
• Quando os produtos são modificados, as linhas existentes na tabela de dimensões são atualizadas ou inseridas.
  • Quando o tipo 1 do SCD se aplica, são feitas atualizações nas linhas existentes.
  • Quando o SCD tipo 2 se aplica, as atualizações são feitas para expirar as versões de linha atuais e novas linhas que representam a versão atual são inseridas.
  • Quando o SCD tipo 3 se aplica, ocorre um processo semelhante ao SCD tipo 1, atualizando as linhas existentes sem inserir novas linhas.

Chaves substitutas

Recomendamos que cada tabela de dimensão tenha uma chave substituta, que deve usar o menor tipo de dados inteiro possível. Em ambientes baseados no SQL Server, isso normalmente é feito criando uma coluna de identidade, no entanto, esse recurso não é suportado no Fabric Warehouse. Em vez disso, você precisará usar uma técnica de solução alternativa que gere identificadores exclusivos.

Importante

Quando uma tabela de dimensões inclui chaves substitutas geradas automaticamente, você nunca deve executar um truncado e recarga completa dela. Isso porque invalidaria os dados carregados em tabelas de fatos que usam a dimensão. Além disso, se a tabela de dimensões suportar alterações do tipo 2 do SCD, talvez não seja possível regenerar as versões históricas.

Gerenciar mudanças históricas

Quando uma tabela de dimensões precisa armazenar alterações históricas, você precisará implementar uma dimensão de mudança lenta (SCD).

Nota

Se a linha da tabela de dimensões for um membro inferido (inserido por um processo de carregamento de fatos), você deverá tratar quaisquer alterações como detalhes de dimensão que chegam tardiamente em vez de uma alteração de SCD. Nesse caso, todos os atributos alterados devem ser atualizados e a coluna do sinalizador de membro inferido definida como FALSE.

É possível que uma dimensão possa suportar alterações de SCD tipo 1 e/ou SCD tipo 2.

SCD tipo 1

Quando forem detetadas alterações do tipo 1 do SCD, use a seguinte lógica.

1. Atualize todos os atributos alterados.
2. Se a tabela incluir a data da última modificação e a última modificação por colunas, defina a data atual e o processo que fez as modificações.

SCD tipo 2

Quando as alterações do tipo 2 do SCD forem detetadas, use a seguinte lógica.

1. Expire a versão atual definindo a coluna de validade da data final como a data de processamento ETL (ou um carimbo de data/hora adequado no sistema de origem) e o sinalizador atual como FALSE.
2. Se a tabela incluir a data da última modificação e a última modificação por colunas, defina a data atual e o processo que fez as modificações.
3. Insira novos membros que tenham a coluna de validade da data de início definida como o valor da coluna de validade da data final (usada para atualizar a versão anterior) e tenha o sinalizador da versão atual definido como TRUE.
4. Se a tabela incluir a data de criação e a criação por colunas, defina a data atual e o processo que fez as inserções.

SCD tipo 3

Quando forem detetadas alterações no SCD tipo 3 , atualize os atributos usando uma lógica semelhante ao processamento do SCD tipo 1.

Exclusões de membros do Dimension

Tenha cuidado se os dados de origem indicarem que os membros da dimensão foram excluídos (porque não foram recuperados do sistema de origem ou porque foram sinalizados como excluídos). Você não deve sincronizar exclusões com a tabela de dimensões, a menos que os membros da dimensão tenham sido criados por erro e não haja registros de fatos relacionados a eles.

A maneira apropriada de lidar com exclusões de origem é gravá-las como uma exclusão suave. Uma exclusão suave marca um membro da dimensão como não mais ativo ou válido. Para dar suporte a esse caso, sua tabela de dimensões deve incluir um atributo booleano com o tipo de dados bit , como IsDeleted. Atualize esta coluna para todos os membros de dimensão excluídos para TRUE (1). A versão atual e mais recente de um membro da dimensão pode ser marcada de forma semelhante com um valor booleano (bit) nas IsCurrent colunas ou IsActive . Todas as consultas de relatório e modelos semânticos do Power BI devem filtrar registros que são exclusões suaves.

Dimensão de data

As dimensões de calendário e tempo são casos especiais porque geralmente não têm dados de origem. Em vez disso, eles são gerados usando lógica fixa.

Você deve carregar a tabela de dimensões de data no início de cada novo ano para estender suas linhas para um número específico de anos à frente. Pode haver outros dados da empresa, por exemplo, dados do ano fiscal, feriados, números da semana para atualizar regularmente.

Quando a tabela de dimensões de data inclui atributos de deslocamento relativo, o processo de ETL deve ser executado diariamente para atualizar os valores dos atributos de deslocamento com base na data atual (hoje).

Recomendamos que a lógica para estender ou atualizar a tabela de dimensão de data seja escrita em T-SQL e encapsulada em um procedimento armazenado.

Tabelas de fatos do processo

O processamento de uma tabela de fatos envolve a sincronização dos dados do data warehouse com os fatos do sistema de origem. Os dados de origem são primeiro transformados e preparados para serem carregados em sua tabela de fatos. Em seguida, para cada chave de dimensão, uma pesquisa determina o valor da chave substituta a ser armazenada na linha de fatos. Quando uma dimensão suporta SCD tipo 2, a chave substituta para a versão atual do membro da dimensão deve ser recuperada.

Nota

Normalmente, a chave substituta pode ser calculada para as dimensões de data e hora, porque elas devem usar YYYYMMDD ou HHMM formatar. Para obter mais informações, consulte Calendário e hora.

Se uma pesquisa de chave de dimensão falhar, isso pode indicar um problema de integridade com o sistema de origem. Neste caso, a linha de fatos ainda deve ser inserida na tabela de fatos. Uma chave de dimensão válida ainda deve ser armazenada. Uma abordagem é armazenar um membro de dimensão especial (como Desconhecido). Essa abordagem requer uma atualização posterior para atribuir corretamente o valor da chave de dimensão verdadeira, quando conhecido.

Importante

Como o Fabric Warehouse não impõe chaves estrangeiras, é fundamental que o processo ETL verifique a integridade quando carrega dados em tabelas de fatos.

Outra abordagem, relevante quando há confiança de que a chave natural é válida, é inserir um novo membro da dimensão e, em seguida, armazenar seu valor de chave substituta. Para obter mais informações, consulte Membros de dimensão inferidos mais adiante nesta seção.

O diagrama a seguir mostra a lógica usada para processar uma tabela de fatos.

Sempre que possível, uma tabela de fatos deve ser carregada gradualmente, o que significa que novos fatos são detetados e inseridos. Uma estratégia de carga incremental é mais escalável e reduz a carga de trabalho para os sistemas de origem e de destino.

Importante

Especialmente para uma grande tabela de fatos, deve ser um último recurso para truncar e recarregar uma tabela de fatos. Essa abordagem é cara em termos de tempo de processo, recursos de computação e possível interrupção nos sistemas de origem. Também envolve complexidade quando as dimensões da tabela de fatos aplicam SCD tipo 2. Isso porque as pesquisas de chave de dimensão precisarão ser feitas dentro do período de validade das versões de membros de dimensão.

Esperamos que você possa detetar novos fatos de forma eficiente confiando em identificadores do sistema de origem ou carimbos de data/hora. Por exemplo, quando um sistema de origem registra de forma confiável as ordens de venda que estão em sequência, você pode armazenar o número de ordem de venda mais recente recuperado (conhecido como marca d'água alta). O próximo processo pode usar esse número de ordem de venda para recuperar ordens de venda recém-criadas e, novamente, armazenar o número de ordem de venda mais recente recuperado para uso pelo próximo processo. Também pode ser possível que uma coluna de data de criação possa ser usada para detetar de forma confiável novos pedidos.

Se você não puder confiar nos dados do sistema de origem para detetar novos fatos com eficiência, poderá confiar em um recurso do sistema de origem para executar uma carga incremental. Por exemplo, o SQL Server e a Instância Gerenciada SQL do Azure têm um recurso chamado captura de dados de alteração (CDC), que pode controlar alterações em cada linha de uma tabela. Além disso, o SQL Server, a Instância Gerenciada SQL do Azure e o Banco de Dados SQL do Azure têm um recurso chamado controle de alterações, que pode identificar linhas que foram alteradas. Quando ativado, ele pode ajudá-lo a detetar com eficiência dados novos ou alterados em qualquer tabela de banco de dados. Você também pode adicionar gatilhos a tabelas relacionais que armazenam chaves de registros de tabela inseridos, atualizados ou excluídos.

Por fim, você poderá correlacionar dados de origem à tabela de fatos usando atributos. Por exemplo, o número da ordem do cliente e o número da linha da ordem do cliente. No entanto, para grandes tabelas de fatos, pode ser uma operação muito cara para detetar fatos novos, alterados ou excluídos. Também pode ser problemático quando o sistema de origem arquiva dados operacionais.

Membros da dimensão inferida

Quando um processo de carregamento de fatos insere um novo membro de dimensão, ele é conhecido como um membro inferido. Por exemplo, quando um hóspede de um hotel faz check-in, ele é solicitado a se juntar à rede de hotéis como membro fidelidade. Um número de sócio é emitido imediatamente, mas os detalhes do hóspede podem não ser seguidos até que a documentação seja enviada pelo hóspede (se for o caso).

Tudo o que se sabe sobre o membro da dimensão é a sua chave natural. O processo de carregamento de fatos precisa criar um novo membro de dimensão usando valores de atributo desconhecidos . É importante ressaltar que ele deve definir o IsInferredMember atributo audit como TRUE. Dessa forma, quando os detalhes que chegam atrasados são originados, o processo de carregamento de dimensão pode fazer as atualizações necessárias na linha de dimensão. Para obter mais informações, consulte Gerenciar alterações históricas neste artigo.

Atualizações ou exclusões de fatos

Poderá ser-lhe pedido que atualize ou elimine dados de factos. Por exemplo, quando uma ordem de venda é cancelada ou uma quantidade de ordem é alterada. Conforme descrito anteriormente para carregar tabelas de fatos, você precisa detetar alterações de forma eficiente e executar modificações apropriadas nos dados de fatos. Neste exemplo para a ordem cancelada, o status da ordem de venda provavelmente mudaria de Aberto para Cancelado. Essa alteração exigiria uma atualização dos dados dos factos, e não a eliminação de uma linha. Para a alteração da quantidade, seria necessária uma atualização da medida da quantidade da linha de fatos. Essa estratégia de usar exclusões suaves preserva o histórico. Uma exclusão suave marca uma linha como não mais ativa ou válida, e todas as consultas de relatório e modelos semânticos do Power BI devem filtrar registros que são exclusões suaves.

Ao antecipar atualizações ou exclusões de fatos, você deve incluir atributos (como um número de ordem de venda e seu número de linha de ordem de venda) na tabela de fatos para ajudar a identificar as linhas de fatos a serem modificadas. Certifique-se de indexar essas colunas para oferecer suporte a operações de modificação eficientes.

Por fim, se os dados de fatos foram inseridos usando um membro de dimensão especial (como Desconhecido), você precisará executar um processo periódico que recupere os dados de origem atuais para essas linhas de fatos e atualize as chaves de dimensão para valores válidos.

Conteúdos relacionados

Para obter mais informações sobre como carregar dados em um Fabric Warehouse, consulte:

• Ingerir dados no Armazém
• Ingerir dados em seu Armazém usando pipelines de dados
• Ingerir dados no seu Armazém usando a instrução COPY
• Ingerir dados em seu Warehouse usando Transact-SQL
• Tutorial: Configurar o dbt para o Fabric Warehouse