Compartilhar via

Diretrizes sobre modelos de DirectQuery no Power BI Desktop

Este artigo destina-se a modeladores de dados que desenvolvem modelos de DirectQuery do Power BI usando o Power BI Desktop ou o serviço do Power BI. Ele descreve os casos de uso, as limitações e diretrizes do DirectQuery. Especificamente, as diretrizes têm a finalidade de ajudar você a determinar se o DirectQuery é o modo apropriado para seu modelo, bem como a melhorar o desempenho de seus relatórios com base em modelos de DirectQuery. Este artigo se aplica aos modelos de DirectQuery hospedados no serviço do Power BI ou no Servidor de Relatórios do Power BI.

Ele não tem a finalidade de apresentar uma discussão completa sobre o design de modelos de DirectQuery. Para obter uma introdução, confira o artigo Modelos de DirectQuery no Power BI Desktop. Para ver uma discussão mais aprofundada, consulte diretamente o white paper DirectQuery no SQL Server 2016 Analysis Services. Tenha em mente que o white paper descreve o uso do DirectQuery no SQL Server Analysis Services. No entanto, grande parte do conteúdo ainda se aplica aos modelos de DirectQuery no Power BI.

Observação

Para obter considerações ao usar o modo de armazenamento do DirectQuery para o Dataverse, confira Diretrizes de modelagem do Power BI para o Power Platform.

Este artigo não aborda diretamente os modelos compostos. Um Modelo composto tem, pelo menos, uma fonte de DirectQuery e, possivelmente, mais. As diretrizes descritas neste artigo ainda são relevantes – pelo menos em parte – para o design de Modelos compostos. No entanto, as implicações de combinar Tabelas de importação e Tabelas de DirectQuery não estão no escopo deste artigo. Para obter mais informações, confira Usar modelos compostos no Power BI Desktop.

É importante entender que os modelos de DirectQuery impõem uma carga de trabalho diferente ao ambiente do Power BI (serviço do Power BI ou Servidor de Relatórios do Power BI), bem como às fontes de dados subjacentes. Se você determinar que o DirectQuery é a abordagem de design apropriada, recomendamos que envolva as pessoas certas no projeto. Costumamos observar que implantações bem-sucedidas de modelos de DirectQuery são resultado de equipes de profissionais de TI que trabalham em conjunto. Normalmente, a equipe é composta por desenvolvedores de modelo e administradores de banco de dados de origem. Ela também pode envolver arquitetos de dados, bem como desenvolvedores de ETL e data warehouse. Muitas vezes, otimizações precisam ser aplicadas diretamente à fonte de dados para obter bons resultados de desempenho.

Otimizar o desempenho da fonte de dados

A fonte do banco de dados relacional pode ser otimizada de várias maneiras, conforme descrito na lista com marcadores a seguir.

Observação

Entendemos que nem todos os modeladores têm as permissões ou as habilidades necessárias para otimizar um banco de dados relacional. Embora essa seja a camada preferencial para preparar os dados para um modelo de DirectQuery, algumas otimizações também podem ser feitas no design do modelo, sem modificar o banco de dados de origem. No entanto, frequentemente os melhores resultados de otimização são obtidos aplicando otimizações ao banco de dados de origem.

  • Verifique se a integridade dos dados está concluída: é especialmente importante que as tabelas de dimensão contenham uma coluna de valores exclusivos (chave de dimensão) que mapeia para as tabelas de fatos. Também é importante que as colunas de dimensão da tabela de fatos contenham valores de chave de dimensão válidos. Elas permitem configurar relações de modelo mais eficientes que esperam valores correspondentes nos dois lados das relações. Quando os dados de origem não têm integridade, é recomendável que um registro de dimensão "desconhecido" seja adicionado para reparar os dados de maneira eficaz. Por exemplo, você pode adicionar uma linha à Product tabela para representar um produto desconhecido e atribuí-la a uma chave fora do intervalo, como -1. Se as linhas na Sales tabela contiverem um valor de chave do produto ausente, substitua-as por -1. Ele garante que cada Sales valor de chave do produto de tabela tenha uma linha correspondente na Product tabela.

  • Adicionar índices: defina índices apropriados, em tabelas ou exibições, para dar suporte à recuperação eficiente de dados para a filtragem e agrupamento visual de relatório esperados. Para fontes do SQL Server, do Banco de Dados SQL do Azure ou do Azure Synapse Analytics (antigo SQL Data Warehouse), confira Guia de Arquitetura e Design de Índice do SQL Server para obter informações úteis sobre as diretrizes de design de índices. Para fontes voláteis do SQL Server ou do Banco de Dados SQL do Azure, confira Introdução a Columnstore para análise operacional em tempo real.

  • Projetar tabelas distribuídas: Para fontes do Azure Synapse Analytics (antigo SQL Data Warehouse), que usam a arquitetura MPP (Processamento Paralelo Massivo), considere configurar tabelas de fatos grandes como distribuídas por hash e tabelas de dimensão para replicar em todos os nós de computação. Para obter mais informações, confira Diretrizes para criar tabelas distribuídas no Azure Synapse Analytics (antigo SQL Data Warehouse).

  • Verifique se as transformações de dados necessárias são materializadas: para fontes de banco de dados relacionais do SQL Server (e outras fontes de banco de dados relacionais), as colunas computadas podem ser adicionadas às tabelas. Essas colunas se baseiam em uma expressão, como Quantity multiplicada por UnitPrice. Colunas computadas podem ser persistidas (materializadas) e, assim como colunas regulares, às vezes podem ser indexadas. Para obter mais informações, consulte Índices em Colunas Calculadas.

    Considere também exibições indexadas que podem pré-agregar os dados da tabela de fatos com uma granulação mais alta. Por exemplo, se a Sales tabela armazenar dados no nível da linha de pedido, você poderá criar uma exibição para resumir esses dados. A exibição pode ser baseada em uma SELECT instrução que agrupa os dados da Sales tabela por data (no nível do mês), cliente, produto e resume valores de medida como vendas, quantidade etc. Em seguida, a exibição pode ser indexada. Para fontes do SQL Server ou do Banco de Dados SQL do Azure, confira Criar Exibições Indexadas.

  • Materializar uma tabela de data: um requisito de modelagem comum envolve a adição de uma tabela de datas para dar suporte à filtragem baseada em tempo. Para dar suporte aos filtros baseados em tempo conhecidos em sua organização, crie uma tabela no banco de dados de origem e verifique se ela foi carregada com um intervalo de datas que abrange as datas da tabela de fatos. Além disso, verifique se ela inclui colunas para períodos úteis, como ano, trimestre, mês, semana etc.

Otimizar o design de modelos

Um modelo de DirectQuery pode ser otimizado de várias maneiras, conforme descrito na lista com marcadores a seguir.

  • Evite consultas complexas do Power Query: um design de modelo eficiente pode ser obtido removendo a necessidade de que as consultas do Power Query apliquem quaisquer transformações. Isso significa que cada consulta é mapeada para uma única tabela ou exibição de fonte de banco de dados relacional. Você pode visualizar uma representação da instrução de consulta SQL real para uma etapa de Power Query aplicada selecionando a opção Exibir Consulta Nativa.

    Captura de tela mostrando o Power BI Desktop e a opção Exibir Consulta Nativa em Etapas Aplicadas.

    Captura de tela mostrando o Power BI Desktop e a janela Consulta Nativa. Uma instrução de consulta une duas tabelas de origem.

  • Examine o uso de colunas calculadas e alterações de tipo de dados: os modelos DirectQuery dão suporte à adição de cálculos e etapas do Power Query para converter tipos de dados. No entanto, um melhor desempenho costuma ser obtido materializando os resultados da transformação na fonte do banco de dados relacional, quando possível.

  • Não use a filtragem de data relativa do Power Query: é possível definir a filtragem de data relativa em uma consulta do Power Query. Por exemplo, para recuperar os pedidos de vendas que foram criados no último ano (em relação à data de hoje). Esse tipo de filtro é convertido em uma consulta nativa ineficiente, da seguinte forma:

    …
from [dbo].[Sales] as [_]
where [_].[OrderDate] >= convert(datetime2, '2018-01-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2019-01-01 00:00:00'))

    Uma abordagem de design melhor é incluir colunas de tempo relativas na tabela de data. Essas colunas armazenam valores de deslocamento relativos à data atual. Por exemplo, em uma RelativeYear coluna, o valor zero representa o ano atual, -1 representa o ano anterior etc. Preferencialmente, a RelativeYear coluna é materializada na tabela de datas. Embora seja menos eficiente, ela também pode ser adicionada como uma coluna calculada do modelo, com base na expressão usando as funções de DAX TODAY e DATE.

  • Mantenha as medidas simples: pelo menos inicialmente, é recomendável limitar medidas a agregações simples. As funções de agregação incluem SUM, COUNT, MIN, MAX e AVERAGE. Em seguida, se as medidas forem suficientemente responsivas, você poderá fazer experimentos com medidas mais complexas, mas prestando atenção ao desempenho de cada uma delas. Embora a função DAX CALCULATE possa ser usada para produzir expressões de medida sofisticadas que manipulam o contexto de filtro, ela pode gerar consultas nativas caras que não têm bom desempenho.

  • Evite relações em colunas calculadas: as relações de modelo só podem relacionar uma única coluna em uma tabela a uma única coluna em uma tabela diferente. No entanto, às vezes é necessário relacionar tabelas usando várias colunas. Por exemplo, as tabelas Sales e Geography estão relacionadas por duas colunas: CountryRegion e City. Para criar uma relação entre as tabelas, uma única coluna é necessária e, na Geography tabela, a coluna deve conter valores exclusivos. Concatenar o país/região e a cidade usando um separador de hífen poderia conseguir esse resultado.

    A coluna combinada pode ser criada com uma coluna personalizada do Power Query ou no modelo como uma coluna calculada. No entanto, isso deve ser evitado, pois a expressão de cálculo será inserida nas consultas de origem. Isso não é apenas ineficiente, mas geralmente impede o uso de índices. Em vez disso, adicione colunas materializadas na fonte do banco de dados relacional e considere indexá-las. Você também pode considerar a adição de colunas de chave substitutas a tabelas de dimensão, que é uma prática comum em designs de data warehouse relacionais.

    Há uma exceção para essa diretriz, que está relacionada ao uso da função DAX COMBINEVALUES. A finalidade dessa função é dar suporte a relações de modelo com várias colunas. Em vez de gerar uma expressão usada pela relação, ela gera um predicado de junção SQL com várias colunas.

  • Evite relações em colunas "Identificador Exclusivo": o Power BI não dá suporte nativo ao tipo de dados GUID (identificador exclusivo). Ao definir uma relação entre colunas desse tipo, o Power BI gera uma consulta de origem com uma junção envolvendo uma conversão. Normalmente, essa conversão de dados em tempo de consulta resulta em baixo desempenho. Até que esse caso seja otimizado, a única solução alternativa seria materializar colunas de um tipo de dados alternativo no banco de dados subjacente.

  • Oculte a coluna unidirecional das relações: a coluna unidirecional de uma relação deve estar oculta. (Geralmente, é a coluna de chave primária das tabelas de dimensão.) Quando oculto, ele não está disponível no painel Dados e, portanto, não pode ser usado para configurar um visual. A coluna de muitos lados poderá permanecer visível se ela for útil para agrupar ou filtrar relatórios pelos valores da coluna. Por exemplo, considere um modelo em que existe uma relação entre a tabela Sales e a tabela Product. As colunas de relação contêm os valores de SKU (unidade de manutenção de estoque) do produto. Se a SKU do produto precisar ser adicionada aos visuais, ela deverá estar visível apenas na Sales tabela. Quando essa coluna é usada para filtrar ou agrupar em um visual, o Power BI gera uma consulta que não precisa unir as tabelas Sales e Product.

  • Definir relações para impor a integridade: a propriedade Supor Integridade Referencial das relações DirectQuery determina se o Power BI gera consultas de origem usando uma INNER JOIN em vez de uma OUTER JOIN. Isso geralmente melhora o desempenho da consulta, embora dependa de especificidades da fonte do banco de dados relacional. Para obter mais informações, confira Pressupor configurações de integridade referencial no Power BI Desktop.

  • Evite o uso da filtragem de relação bidirecional: o uso da filtragem de relação bidirecional pode levar a instruções de consulta que não têm um bom desempenho. Somente use esse recurso de relação quando necessário, e esse geralmente é o caso ao implementar uma relação de muitos para muitos em uma tabela de ponte. Para obter mais informações, confira Relações com uma cardinalidade muitos para muitos no Power BI Desktop.

  • Limitar consultas paralelas: você pode definir o número máximo de conexões que o DirectQuery abre para cada fonte de dados subjacente. Isso controla o número de consultas enviadas simultaneamente à fonte de dados.

    • A configuração só é habilitada quando há, pelo menos, uma fonte do DirectQuery no modelo. O valor se aplica a todas as fontes do DirectQuery e às novas fontes do DirectQuery adicionadas ao modelo.
    • O aumento do valor de Máximo de Conexões por Fonte de Dados garante que mais consultas (até o número máximo especificado) possam ser enviadas à fonte de dados subjacente, o que é útil quando vários visuais estão em uma única página ou quando vários usuários acessam um relatório ao mesmo tempo. Depois que o número máximo de conexões é atingido, as consultas seguintes são colocadas na fila até que uma conexão fique disponível. O aumento desse limite resulta em mais carga na fonte de dados subjacente e, portanto, a configuração não garante a melhoria do desempenho geral.
    • Quando o modelo é publicado no Power BI, o número máximo de consultas simultâneas enviadas à fonte de dados subjacente também depende do ambiente. Diferentes ambientes (como o Power BI, o Power BI Premium ou o Servidor de Relatórios do Power BI) podem impor restrições de taxa de transferência distintas. Para obter mais informações sobre limitações de recursos de capacidade, consulte as licenças de capacidade do Microsoft Fabric e configure e gerencie capacidades no Power BI Premium.

Importante

Este artigo refere-se ao Power BI Premium ou suas assinaturas de capacidade (SKUs P). Atualmente, a Microsoft está consolidando as opções de compra e desativando os SKUs do Power BI Premium por capacidade. Em vez disso, os clientes novos e existentes devem considerar a compra de SKUs (assinaturas de capacidade do Fabric).

Para obter mais informações, consulte Atualização importante chegando ao de licenciamento do Power BI Premium e Perguntas frequentes do Power BI Premium.

Otimizar designs de relatório

Os relatórios baseados em um modelo semântico do DirectQuery podem ser otimizados de várias maneiras, conforme descrito na lista com marcadores a seguir.

  • Habilitar técnicas de redução de consulta: As opções e configurações do Power BI Desktop incluem uma página de Redução de Consulta. Essa página tem três opções úteis. É possível desabilitar o realce cruzado e a filtragem cruzada por padrão, embora isso possa ser substituído por meio da edição de interações. Também é possível mostrar um botão Aplicar em segmentações e filtros. As opções de segmentação ou filtro não serão aplicadas até que o usuário do relatório clique no botão. Se você habilitar essas opções, recomendamos que faça isso ao criar o relatório pela primeira vez.
  • Aplique os filtros primeiro: ao criar relatórios pela primeira vez, recomendamos que você aplique todos os filtros aplicáveis , no nível de relatório, página ou visual, antes de mapear campos para os campos visuais. Por exemplo, em vez de arrastar as medidas CountryRegion e Sales e depois filtrar por um determinado ano, aplique o filtro primeiro no campo Year. Isso ocorre porque cada etapa da criação de um visual enviará uma consulta e, embora seja possível fazer outra alteração antes que a primeira consulta seja concluída, isso ainda impõe uma carga desnecessária à fonte de dados subjacente. Ao aplicar os filtros no início, as consultas intermediárias geralmente são menos dispendiosas e mais rápidas. Além disso, deixar de aplicar os filtros antecipadamente pode fazer com que o limite de um milhão de linhas seja ultrapassado, conforme descrito em Sobre o DirectQuery.
  • Limite o número de visuais em uma página: quando uma página de relatório é aberta (e quando filtros de página são aplicados) todos os visuais em uma página são atualizados. No entanto, há um limite para o número de consultas que podem ser enviadas em paralelo, imposto pelo ambiente do Power BI e pela configuração do modelo Máximo de Conexões por Fonte de Dados, conforme descrito acima. Sendo assim, à medida que o número de visuais da página aumenta, maior é a chance de que eles sejam atualizados de maneira serial. Isso aumenta o tempo necessário para atualizar a página inteira e também aumenta a chance de os visuais exibirem resultados inconsistentes (para fontes de dados voláteis). Por esses motivos, recomendamos limitar o número de visuais em qualquer página e, em vez disso, ter mais páginas mais simples. Substituir vários visuais de cartão por um único visual de cartão com várias linhas pode levar a um layout de página semelhante.
  • Desativar a interação entre visuais: interações de realce cruzado e filtragem cruzada exigem que as consultas sejam enviadas para a origem subjacente. A menos que essas interações sejam necessárias, recomenda-se que sejam desativadas se o tempo necessário para responder às seleções dos usuários for excessivamente longo. Essas interações podem ser desligadas, quer seja para todo o relatório (conforme descrito acima para opções de Redução de consulta) ou caso a caso. Para obter mais informações, confira Como os visuais realizam filtragem cruzada entre si em um relatório do Power BI.

Além da lista de técnicas de otimização acima, observe que cada uma das seguintes funcionalidades de relatório pode causar problemas de desempenho:

  • Filtros de medida: visuais que contêm medidas (ou agregações de colunas) podem ter filtros aplicados a essas medidas. Por exemplo, o visual abaixo mostra as Vendas por Categoria, mas somente para categorias com mais de USD 15 milhões em vendas.

    Captura de tela mostrando o Power BI Desktop e dados tabulares com filtros aplicados.

    Isso poderá fazer com que duas consultas sejam enviadas à fonte subjacente:

    • A primeira consulta recuperará as categorias que atendem à condição (Vendas > USD 15 milhões)
    • A segunda consulta então recuperará os dados necessários para o visual, adicionando à cláusula WHERE as categorias que atenderam à condição.

    Isso geralmente terá um bom desempenho se houver centenas ou milhares de categorias, como neste exemplo. No entanto, o desempenho poderá ser prejudicado se o número de categorias for muito maior (e, de fato, a consulta falhará se houver mais de um milhão de categorias que atendem à condição, devido ao limite de um milhão de linhas mencionado acima).

  • Filtros topN: filtros avançados podem ser definidos para filtrar apenas os valores N superior (ou inferior) classificados por uma medida. Por exemplo, para exibir apenas as cinco categorias principais no visual acima. Assim como os filtros de medida, isso também faria com que duas consultas fossem enviadas à fonte de dados subjacente. No entanto, a primeira consulta retornará todas as categorias da fonte subjacente e, em seguida, os N principais serão determinados com base nos resultados retornados. Dependendo da cardinalidade da coluna envolvida, isso poderá causar problemas de desempenho (ou falhas de consulta devido ao limite de um milhão de linhas).

  • Mediana: geralmente, qualquer agregação (Soma, Contagem Distinta etc.) é transferida para a origem subjacente. No entanto, isso não é válido para a Mediana, pois não há suporte para essa agregação na fonte subjacente. Nesses casos, os dados dos detalhes são recuperados da fonte subjacente e o Power BI avalia a mediana dos resultados retornados. Isso será aceitável quando a mediana for calculada em um número relativamente pequeno de resultados, mas problemas de desempenho (ou falhas de consulta devido ao limite de um milhão de linhas) ocorrerão se a cardinalidade for grande. Por exemplo, a mediana da população do país/região pode ser razoável, ao contrário da mediana do preço de vendas.

  • Segmentações de seleção múltipla: Permitir seleção múltipla em segmentações e filtros pode causar problemas de desempenho. Isso ocorre porque, à medida que o usuário seleciona itens de segmentação adicionais (por exemplo, chegando a até dez produtos nos quais está interessado), cada nova seleção faz com que uma nova consulta seja enviada à fonte subjacente. Embora o usuário possa selecionar o próximo item antes da conclusão da consulta, isso resultará em carregamento extra na fonte subjacente. Essa situação pode ser evitada mostrando o botão Aplicar, conforme descrito nas técnicas de redução de consulta acima.

  • Totais visuais: por padrão, tabelas e matrizes exibem totais e subtotais. Em muitos casos, consultas adicionais precisam ser enviadas à fonte subjacente para obter os valores dos totais. Isso se aplica sempre que as agregações Contar Distinto ou Mediana são usadas e em todos os casos em que o DirectQuery é usado no SAP HANA ou no SAP Business Warehouse. Esses totais devem ser desativados (usando o painel Formato) quando não forem necessários.

Converter em um Modelo Composto

Os benefícios dos modelos de Importação e de DirectQuery podem ser combinados em um único modelo configurando o modo de armazenamento das tabelas de modelo. O modo do armazenamento de tabela pode ser de Importação ou DirectQuery, ou ambos, que é conhecido como Duplo. Quando um modelo contém tabelas com modos de armazenamento diferentes, ele é conhecido como um modelo Composto. Para obter mais informações, confira Usar modelos compostos no Power BI Desktop.

Muitos aprimoramentos funcionais e de desempenho podem ser obtidos com a conversão de um modelo de DirectQuery em um modelo Composto. Um modelo Composto pode integrar mais de uma fonte de DirectQuery e também pode incluir agregações. Tabelas de agregação podem ser adicionadas a tabelas de DirectQuery para importar uma representação resumida da tabela. Elas podem levar a melhorias consideráveis no desempenho quando os visuais consultam agregações de nível superior. Para obter mais informações, confira Agregações no Power BI Desktop.

Instruir os usuários

É importante instruir os usuários sobre como trabalhar de maneira eficiente com relatórios baseados em modelos semânticos no DirectQuery. Os autores de relatórios devem ser instruídos sobre o conteúdo descrito na seção Otimizar designs de relatório.

Recomendamos que você instrua seus consumidores de relatórios sobre seus relatórios baseados em modelos semânticos do DirectQuery. Pode ser útil para eles entenderem a arquitetura de dados geral, incluindo as limitações relevantes descritas neste artigo. Informe-os de que as respostas de atualizações e a filtragem interativa poderão, às vezes, ser lentas. Quando os usuários de relatório entendem por que ocorre a degradação do desempenho, é menos provável que eles percam a confiança nos relatórios e nos dados.

Ao entregar relatórios sobre fontes de dados voláteis, não deixe de instruir os usuários sobre o uso do botão Atualizar. Avise também que poderá ser possível ver resultados inconsistentes e que uma atualização do relatório poderá resolver inconsistências na página do relatório.

Conteúdo relacionado

Para obter mais informações sobre o DirectQuery, confira os seguintes recursos: