Compartilhar via

Modelagem dimensional no Warehouse do Microsoft Fabric: carregar tabelas

  • Artigo

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

Observação

Este artigo faz parte da série sobre Modelagem Dimensional. Esta série se concentra nas diretrizes e melhores práticas de design relacionadas à modelagem dimensional no Warehouse do Microsoft Fabric.

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

Observação

Neste artigo, o termo data warehouse refere-se a um data warehouse empresarial, que oferece integração abrangente de dados críticos em toda a organização. Por outro lado, o termo autônomo warehouse refere-se a um Warehouse do Fabric, uma oferta de banco de dados relacional de software como serviço (SaaS) que pode ser usada para implementar um data warehouse. Para maior clareza, neste artigo, este último termo é mencionado como Fabric Warehouse.

Dica

Caso não tenha experiência com modelagem dimensional, esta série de artigos é o primeiro passo. Ela não se destina a fornecer uma discussão completa sobre o projeto 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

Carregar um modelo dimensional envolve a execução periódica de um processo de extração, transformação e carregamento (ETL). Um processo de ETL orquestra a execução de outros processos, que geralmente se preocupam com o preparo de dados de origem, a sincronização de dados de dimensão, a inserção de linhas em tabelas de fatos e o registro de dados e erros de auditoria.

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

Especificamente, você pode:

  • Use pipelines de dados para criar fluxos de trabalho e orquestrar o processo de ETL. Os pipelines de dados podem executar scripts SQL, procedimentos armazenados e muito mais.
  • Use fluxos de dados para desenvolver lógica low-code e ingerir dados de centenas de fontes de dados. Os fluxos de dados oferecem suporte à combinação de dados de várias fontes, à transformação de dados e ao carregamento deles em 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.

Observação

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

Orquestração

O fluxo de trabalho geral de um processo de ETL serve para:

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

O diagrama mostra as quatro etapas do processo de ETL, conforme descrito no parágrafo anterior.

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 de tabela de dimensões, 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 deve ser processada antes das outras duas dimensões.

As tabelas de fatos podem ser processadas depois que todas as tabelas de dimensões são processadas.

Quando todas as tabelas de modelo dimensional são processadas, você pode acionar a renovação de modelos semânticos dependentes. Também é uma boa ideia enviar uma notificação à equipe relevante para informá-la sobre o resultado final do processo de ETL.

Preparar dados

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

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

Os dados em tabelas de preparo nunca devem ser disponibilizados para usuários de negócios. São relevantes apenas para o processo de ETL.

Observação

Quando os dados são armazenados em um Lakehouse do Fabric, talvez não seja necessário prepará-los no data warehouse. Se uma arquitetura medallion é implementada, você pode obter seus dados na camada de bronze, prata ou ouro.

Recomendamos criar um esquema no warehouse, possivelmente chamado staging. As tabelas de preparo devem se assemelhar o máximo 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 de ETL. No entanto, observe que as tabelas do Warehouse do Fabric não podem ser truncadas. Em vez disso, você pode descartar 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 da sua estratégia de preparo. Você pode usar:

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 do modelo dimensional.

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

Observação

O conceito de garbage in, garbage out certamente se aplica ao data warehousing; portanto, evite carregar dados inúteis (baixa qualidade) nas tabelas de modelos dimensionais.

Estas são algumas transformações que o processo de ETL pode executar.

  • Combinar dados: 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 de manutenção de estoque (SKU) comum. Os dados também podem ser acrescentados quando compartilham 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 do modelo dimensional.
  • Cálculos: os cálculos podem ser feitos a fim de produzir valores para as tabelas do modelo dimensional. Por exemplo, em uma tabela de dimensão de funcionário, você pode concatenar nomes e sobrenomes para produzir o nome completo. Como outro exemplo, na tabela de fatos de vendas, você pode calcular a receita bruta de vendas, que é o produto do preço unitário pela quantidade.
  • Detectar e gerenciar alterações históricas: as alterações podem ser detectadas e armazenadas adequadamente em tabelas de dimensões. Para obter mais informações, confira 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 Warehouse do Fabric usando as opções de ingestão de dados a seguir.

  • COPIAR PARA (T-SQL): essa opção é útil quando os dados de origem compreendem arquivos Parquet ou CSV armazenados em uma conta de armazenamento externa do Azure, como o ADLS Gen2 ou o Armazenamento de Blobs do Azure.
  • Pipelines de dados: além de orquestrar o processo de ETL, os pipelines de dados 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 de dados, os fluxos de dados fornecem uma uma experiência sem código para transformar e limpar dados.
  • Ingestão entre warehouses: quando os dados são armazenados no mesmo espaço de trabalho, a ingestão entre warehouses permite ingressar diferentes tabelas de warehouse ou lakehouse. Ela oferece suporte a comandos T-SQL, como INSERT…SELECT, SELECT INTO e CREATE TABLE AS SELECT (CTAS). Esses comandos são especialmente úteis quando você deseja transformar e carregar dados de tabelas de preparo no mesmo espaço de trabalho. Eles também são operações baseadas em conjuntos, sendo provavelmente a maneira mais eficiente e rápida de carregar tabelas de modelos dimensionais.

Dica

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

Logging

Os processos de ETL costumam exigir monitoramento e manutenção dedicados. Por esses motivos, recomendamos que você faça o registro em log dos resultados do processo de ETL em tabelas de modelos não dimensionais no warehouse. Você deve gerar uma ID exclusiva para cada processo de ETL e usá-la para registrar detalhes em log sobre cada operação.

Considere registrar em log:

  • O processo de ETL:
    • Uma ID exclusiva para cada execução de ETL
    • Hora de início e Hora de término
    • Status (êxito ou falha)
    • Quaisquer erros encontrados
  • Cada tabela do modelo dimensional e de preparo:
    • Hora de início e Hora de término
    • Status (êxito ou falha)
    • Linhas inseridas, atualizadas e excluídas
    • Contagem final de linhas da tabela
    • Quaisquer erros encontrados
  • Outras operações:
    • Hora de início e de término das operações de renovação do modelo semântico

Dica

Você pode criar um modelo semântico dedicado a monitorar e analisar os processos de ETL. As durações do processo ajudam a identificar gargalos que podem se beneficiar da revisão e otimização. As contagens de linhas permitem compreender o tamanho da carga incremental cada vez que o ETL é executado, além de ajudar a prever o tamanho futuro do data warehouse (e quando escalar verticalmente a capacidade do Fabric, se apropriado).

Processar tabelas de dimensão

O processamento de uma tabela de dimensões envolve a sincronização dos dados do data warehouse com os sistemas de origem. Os dados de origem são primeiro transformados e preparados para carregamento na tabela de dimensões. Esses dados são então combinados com os dados da tabela de dimensões existente por meio do ingresso das 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 aplica a dimensão variável lenta (SCD) do tipo 1, as alterações são feitas atualizando as linhas existentes da tabela de dimensões. Quando a tabela aplica alterações de SCD do tipo 2, a versão existente expira e uma nova é inserida.

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

O diagrama mostra um fluxo que descreve como as linhas de origem novas e alteradas são carregadas em uma tabela de dimensões, conforme descrito no parágrafo a seguir.

Considere o processo da tabela de dimensões Product.

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

Chaves alternativas

Recomendamos que cada tabela de dimensões tenha uma chave alternativa, que deve usar o menor tipo de dados Integer possível. Em ambientes baseados no SQL Server, isso costuma ser feito criando uma coluna de identidade. No entanto, esse recurso não é compatível com o Warehouse do Fabric. 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 alternativas geradas automaticamente, você nunca deve executar um truncamento e uma recarga completa dela. O motivo é que isso invalidaria os dados carregados em tabelas de fatos que usam a dimensão. Além disso, se a tabela de dimensões oferecer suporte a alterações de SCD do tipo 2, talvez não seja possível regenerar as versões históricas.

Gerenciar alterações históricas

Quando uma tabela de dimensões precisar armazenar alterações históricas, será necessário implementar uma dimensão variável lenta (SCD).

Observação

Se a linha da tabela de dimensões for um membro inferido (inserido por um processo de carregamento de fatos), você deve tratar quaisquer alterações como detalhes de dimensão que chegam atrasados 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 suporte alterações de SCD do tipo 1 e/ou do tipo 2.

SCD do tipo 1

Quando forem detectadas alterações de SCD do tipo 1, use a lógica a seguir.

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

SCD do tipo 2

Quando forem detectadas alterações de SCD do tipo 2, use a lógica a seguir.

  1. Expire a versão atual definindo a coluna de validade da data de término para a data de processamento de 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 feita 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 de término (usado para atualizar a versão anterior) e que tenham o sinalizador da versão atual definido como TRUE.
  4. Se a tabela incluir as colunas data de criação e criado por, defina a data atual e o processo que fez as inserções.

SCD do tipo 3

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

Exclusões de membros da dimensão

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 é registrá-las como uma exclusão temporária. Uma exclusão temporária marca um membro da dimensão como não estando mais ativo ou válido. Para dar suporte a esse caso, sua tabela de dimensões deve incluir um atributo booliano com o tipo de dados de bit, como IsDeleted. Atualize esta coluna de qualquer membro da dimensão excluído para TRUE (1). A versão atual e mais recente de um membro da dimensão também pode ser marcada com um valor booliano (bit) nas colunas IsCurrent ou IsActive. Todas as consultas de relatório e modelos semânticos do Power BI devem filtrar registros que são exclusões temporárias.

Dimensão de data

As dimensões de calendário e temporal são casos especiais porque não costumam conter dados de origem. Em vez disso, elas são geradas usando lógica fixa.

Você deve carregar a tabela de dimensões de data no início de cada novo ano a fim de estender as linhas para um número específico de anos à frente. Pode haver outros dados de negócios, 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 de atributo de deslocamento com base na data atual (hoje).

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

Processar tabelas de fatos

Processar 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 carregamento na tabela de fatos. Em seguida, para cada chave de dimensão, uma pesquisa determina o valor da chave alternativa a ser armazenada na linha de fatos. Quando uma dimensão oferece suporte à SCD do tipo 2, a chave alternativa para a versão atual do membro da dimensão deve ser recuperada.

Observação

Normalmente, a chave alternativa pode ser calculada para as dimensões de data e temporal, porque elas devem usar o formato YYYYMMDD ou HHMM. Para obter mais informações, confira Calendário e tempo.

Se uma pesquisa de chave de dimensão falhar, isso pode indicar um problema de integridade com o sistema de origem. Nesse 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 da dimensão especial (como Desconhecido). Essa abordagem requer uma atualização posterior para atribuir corretamente o verdadeiro valor da chave de dimensão, quando conhecido.

Importante

Como o Warehouse do Fabric não impõe chaves estrangeiras, é fundamental que o processo de ETL verifique a integridade ao carregar 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 alternativa. Para obter mais informações, confira Membros da dimensão inferidos mais adiante nesta seção.

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

O diagrama mostra um fluxo que descreve como as novas linhas de origem são carregadas em uma tabela de fatos, conforme descrito nos parágrafos anteriores.

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

Importante

Principalmente em uma grande tabela de fatos, o último recurso deve ser 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 dos sistemas de origem. Ela também envolve complexidade quando as dimensões da tabela de fatos se aplicam à SCD do tipo 2. Isso ocorre porque as pesquisas da chave de dimensão precisarão ser feitas dentro do período de validade das versões do membro da dimensão.

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

Caso não possa confiar nos dados do sistema de origem para detectar novos fatos com eficiência, você pode confiar em um recurso do sistema de origem para executar uma carga incremental. Por exemplo, o SQL Server e a Instância Gerenciada de SQL do Azure têm um recurso chamado captura de dados de alterações (CDA), capaz de controlar as alterações em cada linha de uma tabela. Além disso, o SQL Server, a Instância Gerenciada de SQL do Azure e o Banco de Dados SQL do Azure têm um recurso chamado controle de alterações, que pode identificar linhas alteradas. Quando habilitado, ele ajuda a detectar dados novos ou alterados com eficiência 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ê pode correlacionar os dados de origem à tabela de fatos usando atributos. Por exemplo, o número da ordem de venda e da linha da ordem de venda. No entanto, em grandes tabelas de fatos, pode ser uma operação muito cara detectar fatos novos, alterados ou excluídos. Também pode ser problemático quando o sistema de origem arquiva dados operacionais.

Membros da dimensão inferidos

Quando um processo de carregamento de fatos insere um novo membro da dimensão, ele é conhecido como um membro inferido. Por exemplo, quando um hóspede de um hotel faz check-in, ele é convidado a ingressar no programa de fidelidade da rede de hotelaria. Um número de subscrição é emitido imediatamente, mas os detalhes do hóspede podem não ser divulgados até que a papelada seja enviada por ele (se for o caso).

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

Atualizações ou exclusões de fatos

Talvez seja necessário atualizar ou excluir dados de fatos. Por exemplo, quando uma ordem de venda é cancelada ou uma quantidade da ordem é alterada. Conforme descrito anteriormente para carregar tabelas de fatos, você precisa detectar alterações e executar modificações apropriadas nos dados de fatos com eficiência. Neste exemplo, na ordem cancelada, o status da ordem de venda provavelmente mudaria de Aberto para Cancelado. Essa alteração exigiria uma atualização dos dados de fatos e não a exclusão de uma linha. Na alteração da quantidade, seria necessária uma atualização da medida de quantidade da linha de fatos. Essa estratégia de usar exclusões temporárias preserva o histórico. Uma exclusão temporária 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 temporárias.

Ao antecipar atualizações ou exclusões de fatos, você deve incluir atributos (como um número de ordem de venda e o respectivo número da linha da 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 da 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údo relacionado

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