Modelagem dimensional no Microsoft Fabric Warehouse: tabelas de carga

Aplica-se a:✅ Endpoint de Análise SQL e Warehouse no Microsoft Fabric

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.

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 Fabric Warehouse, você pode usar o Data Factory no Microsoft Fabric para desenvolver e executar seu processo ETL. O processo pode encenar, transformar e carregar os dados de origem nas suas tabelas de modelo dimensional.

Mais concretamente, pode:

• Use pipelines para criar fluxos de trabalho para orquestrar o processo de ETL. Os pipelines podem executar scripts SQL, procedimentos armazenados e muito mais.
• Use fluxos de dados para desenvolver lógica de low-code e integrar 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.

Orquestração

O fluxo de trabalho geral de um processo ETL é:

1. Opcionalmente, carregue tabelas de preparação.
2. Tabelas de dimensões de processo.
3. Tabelas de fatos de processos.
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 transição nunca devem ser disponibilizados para utilizadores empresariais. É apenas relevante para o processo de ETL.

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. TRUNCATE TABLE é apoiado para este fim.

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.

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 todas as chaves de dimensão pode ser usado para armazenar os dados da tabela de factos.

Carregar dados

Você pode carregar tabelas em um Armazém Fabric 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: Além de orquestrar o processo ETL, os pipelines podem incluir atividades que executam instruções T-SQL, realizam pesquisas ou copiam dados de uma fonte de dados para um destino.
• Fluxos de dados: Como alternativa aos pipelines, 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 relacionar diferentes tabelas 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ê/tu deseja transformar e carregar dados de tabelas de estágio 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.

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
  • Número total de linhas da tabela
  • 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

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 processo da tabela de dimensão Product.

• 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 se aplica o SCD tipo 2, realizam-se atualizações para expirar as versões atuais das linhas, e são inseridas novas linhas que representam a versão atual.
  • 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 em SQL Server, isso é normalmente feito criando uma IDENTITY coluna, e no Fabric Data Warehouse, IDENTITY as colunas estão disponíveis com algumas limitações. Para mais informações, consulte as colunas IDENTITY e Use as colunas IDENTITY para criar chaves substitutas no Fabric Data Warehouse.

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).

É 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 colunas de data da última modificação e última modificação por, 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. Expirar a versão atual configurando a coluna de validade da data final para a data de processamento do ETL (ou um carimbo de data/hora adequado no sistema de origem) e definindo o sinalizador atual para FALSE.
2. Se a tabela incluir colunas de data da última modificação e última modificação por, 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 tenham o sinalizador da versão atual definido como TRUE.
4. Se a tabela incluir colunas de data de criação e criado por, defina a data atual e o processo que realizou 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 fonte é registá-las como uma remoçã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.

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 especial de dimensão (como Desconhecido). Essa abordagem requer uma atualização posterior para atribuir corretamente o valor da chave de dimensão verdadeira, quando conhecido.

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.

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 regista de forma fiável as ordens de venda que estão em sequência, pode armazenar o número da ordem de venda mais recente recuperado (conhecido como ponto máximo). 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 de venda e o número da linha da ordem de venda. 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 do hotel faz o check-in, é convidado a aderir à rede de hotéis como membro de fidelidade. Um número de sócio é emitido imediatamente, mas os detalhes do convidado podem não ser disponibilizados enquanto a documentação não for enviada pelo convidado, se é que alguma vez o forem.

O que se sabe sobre o membro da dimensão é apenas a sua chave natural. O processo de carregamento de fatos precisa criar um novo membro de dimensão usando valores de atributo desconhecidos . Importante, deve definir o IsInferredMemberatributo de auditoria para TRUE. Dessa forma, quando os detalhes que chegam atrasados são recebidos, o processo de carregamento da dimensão pode fazer as atualizações necessárias na linha da 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 alterar a quantidade, seria necessário atualizar a medida da quantidade na linha de factos. Essa estratégia de usar deleçõ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:

• Carregar dados no Armazém de Dados
• Ingerir dados em seu armazém usando pipelines
• Ingerir dados no seu Armazém de Dados usando o comando COPY
• Ingerir dados em seu Warehouse usando Transact-SQL
• Tutorial: Configurar o dbt para o Fabric Warehouse