Usar partições de tabelas quentes e frias para otimizar modelos de dados muito grandes do Power BI

Artigo
04/10/2024

Este artigo descreve como usar partições de tabelas quentes e frias para otimizar modelos de dados muito grandes. As partições fornecem uma maneira de dividir os dados de uma tabela em subconjuntos discretos. As partições não são expostas diretamente nas ferramentas padrão de modelagem de dados do Power BI, mas você pode aproveitar os métodos avançados de particionamento configurando uma política de atualização incremental em Power BI Desktop. A atualização incremental depende de partições, conforme explicado em Atualização incremental e dados em tempo real para conjuntos de dados. No entanto, a configuração de partições de tabelas quentes e frias vai além do que uma política de atualização incremental pode realizar e pressupõe familiaridade com esquemas típicos de particionamento de tabela e ferramentas baseadas em XMLA.

Pré-requisitos

Devido à complexidade relativa dessa técnica de particionamento, ela é mais adequada para usuários avançados com experiência nas seguintes áreas:

Noções básicas sobre conceitos de particionamento de tabela, como as partições de modo de importação, o modo DirectQuery e o modo Dual funcionam.
Conhecimento de como criar tabelas híbridas usando ferramentas baseadas em XMLA. As tabelas híbridas usam uma ou mais partições de modo de importação e uma partição DirectQuery .
Conhecimento dos requisitos das funções DAX que você pode usar para especificar um DataCoverageDefinition. Esta é uma nova propriedade para partições DirectQuery para descrever quais dados a partição DirectQuery de uma tabela híbrida contém para que o mecanismo do Power BI possa excluir essa partição do processamento de consultas quando apropriado. Excluir a partição DirectQuery pode ajudar a evitar consultas desnecessárias de fonte de dados e melhorar o desempenho do processamento de consultas DAX.
Noções básicas sobre a diferença entre relações de tabela regulares e limitadas. Por exemplo, a função RELATED será útil se você quiser definir a cobertura de dados de uma partição de tabela de fatos com base nos valores de uma tabela de dimensões de data relacionada. Tenha em mente que a partição da tabela de fatos é uma partição DirectQuery com a chance de uma relação limitada com a tabela de datas sobre a qual a função RELATED não pode buscar valores. Nesse cenário, RELATED só funcionará se a tabela de dimensões de data for uma tabela dupla. A tabela de datas deve estar no modo DirectQuery ou Dual . Não pode ser importação pura.

Lembre-se de que um definido DataCoverageDefinition incorretamente pode levar a resultados errados porque o Power BI pode excluir incorretamente a partição DirectQuery do processamento de consulta. Portanto, certifique-se de comparar os resultados com e sem o DataCoverageDefinition para garantir que eles sejam somá-los.

Quando usar partições de tabelas quentes e frias

Aqui está um exemplo em que partições quentes e frias podem ajudar a ajustar uma tabela híbrida para análise histórica. Suponha que você tenha uma fonte de dados muito grande, acumulada ao longo de muitos anos. O principal uso é analisar os dados mais recentes dos últimos dois anos. Ocasionalmente, você também deseja analisar dados mais antigos. Talvez você tenha notado um recente aumento acentuado de vendas ano após ano. Isso já aconteceu antes? É o maior pico de vendas desde o início do rastreamento de vendas?

Sem suporte para partições quentes e frias, esse tipo de análise histórica exigiria que você importasse todos os dados históricos junto com os dados mais recentes para a tabela de fatos. Na melhor das hipóteses, esse é um uso ineficiente de recursos, pois a análise primária nem sequer usa nenhum dos dados históricos mais antigos. Na pior das hipóteses, o volume de dados é tão grande que nem sequer pode ser importado na íntegra. Você precisa mudar o modelo de dados para o modo DirectQuery e aceitar uma penalidade de desempenho em comparação com o modo de importação ou criar modelos separados e forçar os usuários a alternar entre relatórios. Uma tabela híbrida com partições quentes e frias oferece uma opção melhor.

Como usar partições de tabelas quentes e frias

Primeiro, configure a tabela de vendas com uma partição de modo de importação frequente para os dados mais recentes e mantenha os dados mais antigos em uma partição directQueryfria, como ilustra o diagrama a seguir para a tabela FactInternetSales de um modelo de dados de exemplo adventureWorks. Todas as linhas com um OrderDateKey maior ou igual a 20200101 são importadas para o modelo de dados por meio da partição de modo de importação frequente. Linhas com um OrderDateKey menor que 20200101 são cobertas pela partição fria do DirectQuery . Agora, o Power BI pode fornecer os principais casos de uso rapidamente com o modo de importação e você não precisa importar grandes volumes de dados históricos que você só analisa ocasionalmente porque a partição DirectQuery tem isso abordado.

Captura de tela da tabela Fact Internet Sales de um modelo de dados de exemplo da Adventure Works. A tabela de vendas pela Internet de fato é aberta com as linhas filtradas sendo exibidas.

Se você tiver um data warehouse de exemplo do AdventureWorks e quiser acompanhar, aqui estão as etapas gerais:

Crie o conjunto de dados. Use Power BI Desktop para criar um conjunto de dados e um relatório do AdventureWorks. Inclua todas as tabelas no modo DirectQuery puro. Em seguida, converta todas as tabelas, exceto a FactInternetSales tabela, no modo Dual . Deixe a FactInternetSales tabela no modo DirectQuery .
Carregue o conjunto de dados. Use um workspace hospedado em Power BI Premium com o ponto de extremidade XMLA habilitado para operações de gravação.
Atualize o nível de compatibilidade. Abra o workspace com seu conjunto de dados AdventureWorks no SQL Server Management Studio (SSMS). Clique com o botão direito do mouse no Banco de Dados script descript> do conjunto > de dados AdventureWorkscomoCriar ou Substituir para e selecione Nova janela do editor de consultas. Defina a propriedade compatibilityLevel como 1603 (ou superior). Selecione Executar ou pressione F5. Verifique se a operação foi concluída com êxito.

Configure as partições de tabela FactInternetSales. Clique com o botão direito do mouse no Banco de Dados script descript> do conjunto > de dados AdventureWorkscomoCriar ou Substituir para e selecione Nova janela do editor de consultas. Substitua toda a seção de partições pela seção a seguir. Atualize as linhas sql.database para apontar para o banco de dados AdventureWorksDW em seu ambiente. Selecione Executar ou pressione F5. Verifique se a operação foi concluída com êxito.

   "partitions": [ 
    { 
      "name": "FactInternetSales-DQ-Partition", 
      "mode": "directQuery", 
      "dataView": "full", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    }, 
    { 
      "name": "FactInternetSales-Import-Partition", 
      "mode": "import", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    } 
  ],

Processe o modelo de dados. No portal do Power BI, abra o workspace com seu conjunto de dados AdventureWorks e execute uma atualização sob demanda do conjunto de dados para carregar a partição de importação com dados.
Verifique se os relatórios mostram dados recentes e históricos. Abra seu AdventureWorks e verifique se o relatório é capaz de mostrar resultados para transações de vendas antes e depois de 1º de janeiro de 2020, como na captura de tela a seguir.

Captura de tela de dois relatórios diferentes. Um mostra dados de 2020 e outro mostra dados de 2019.

Definir a cobertura de dados da partição DirectQuery

A solução funciona perfeitamente em dados recentes e históricos. No entanto, por padrão, o Power BI consulta todas as partições de tabela, pois não sabe quais dados cada partição abrange. Portanto, o Power BI ainda consulta a partição DirectQuery mesmo durante esses anos que a partição DirectQuery não abrange. Os dados de vendas estão prontamente disponíveis na partição de importação e a partição DirectQuery não contribui com nenhuma linha, mas essa consulta de origem supérflua ainda pode causar carga perceptível na fonte de dados e contribuir com atrasos no processamento de consultas DAX. Para evitar essa consulta de origem supérflua, use o DataCoverageDefinition.

Como mostra a captura de tela a seguir, o relatório do Power BI ainda envia várias consultas SQL desnecessárias para 2020 para a fonte de dados, pois a consulta DAX de cada visual faz com que o Power BI consulte a partição DirectQuery .

Captura de tela das consultas DAX.

Ao definir a dataCoverageDefinition propriedade na partição DirectQuery como no snippet TMSL a seguir, essas consultas SQL são evitadas. Tenha em mente, no entanto, que você deve atualizar o conjunto de dados depois de aplicar ou alterar uma definição de cobertura de dados. Um recalc de processo é suficiente para avaliar a definição de cobertura de dados. Se você esquecer essa etapa, as consultas que tocam na partição falharão com uma mensagem de erro informando "DataCoverageDefinition da partição DQ na tabela '[Nome da Tabela]" ainda não será calculada após uma alteração recente. Ele precisa ser reprocessado".

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        }

Conforme mencionado anteriormente, a dataCoverageDefinition propriedade ajuda a eliminar a carga desnecessária da fonte de dados. Ele também melhora o desempenho da análise de dados recentes porque agora o Power BI pode excluir a partição DirectQuery do processamento de consulta DAX, quando apropriado. Você pode definir expressões de cobertura de dados simples para valores únicos, bem como intervalos com operadores SIMPLES AND, OR e NOT. Você também pode usar a função RELATED para definir a cobertura de dados com base em uma coluna de uma tabela de dimensões que tem uma relação regular com a tabela de fatos. Se uma expressão de cobertura de dados usar colunas de uma tabela de dimensões, verifique se a tabela de dimensões está no modo duplo . Você também pode definir a cobertura de dados com base em colunas da própria tabela de fatos. Consulte a tabela a seguir para operações com suporte, categorizadas em três grupos. 

Digite	Comentários	Exemplos
Predicado único (baseado em valor)	Operadores de igualdade, desigualdade e IN Suporte a tabelas de dimensão e de fatos	RELATED('Date'[Year]) = 2020 NOT RELATED('Date'[Year]) = 2020 RELATED('Date'[Year]) IN {2020, 2021, 2022} InternetSales'[SalesAmt] = CURRENCY(100.0) NOT InternetSales'[SalesAmt] = CURRENCY(100.0) InternetSales'[SalesAmt] IN {CURRENCY(100.0), CURRENCY(200.0)}
Predicado único (baseado em intervalo)	Pode ser operadores de comparação como >, <, >=, <= Exigir que a tabela de dimensões esteja no modo Duplo	RELATED('Date'[Year]) > 2020 RELATED('Date'[Year]) <= 2020
Vários predicados	Igualdade, desigualdade e comparação Não dá suporte ao operador IN Limitado a uma única tabela de dimensões no modo duplo	RELATED('Date'[Year]) > 2010 && RELATED('Date'[Year]) > 2020 RELATED('Date'[Year]) = 2020 && RELATED('Date'[Calendar Quarter]) = 1 RELATED('Date'[Year]) > 2020 && NOT RELATED('Date'[Calendar Quarter]) = 1 RELATED('Date'[Year]) > 2020 && RELATED('Date'[Calendar Quarter]) < 3 RELATED('Date'[Year]) > 2020 && (RELATED('Date'[Calendar Quarter]) = 1 \|\| RELATED('Date'[Calendar Quarter]) = 2)

A DataCoverageDefinition propriedade em partições DirectQuery permite otimizar até mesmo os maiores modelos de dados do Power BI com base em partições ativas no modo de importação e partições a frio no modo DirectQuery , evitando consultas desnecessárias da fonte de dados. Essa redução de consulta de origem ajuda a aumentar o desempenho do relatório ao analisar dados frequentes. Ele também ajuda a diminuir a carga na fonte de dados e, dessa forma, ajuda a maximizar a escala da fonte de dados. No entanto, tenha em mente que otimizar um modelo de dados usando a dataCoverageDefinition propriedade ainda é um cenário avançado. Verifique os resultados com cuidado.

Considerações e limitações

Atualmente, a DataCoverageDefinition propriedade em partições DirectQuery requer valores estáticos, como RELATED('Date'[Year]) = 2020 ou RELATED('Date'[Year]) IN {2020, 2021, 2022}. Não há suporte para atribuições dinâmicas, como RELATED('Date'[DateKey]) = TODAY().
A atualização incremental com dados em tempo real não aproveita a DataCoverageDefinition propriedade. Se você aplicar uma definição de cobertura de dados a uma partição DirectQuery (em tempo real), a Atualização Incremental removerá a definição de cobertura de dados ao recriar a partição.

Compartilhar via