DirectQuery no Power BI

No Power BI Desktop ou no serviço do Power BI, você pode se conectar a várias fontes de dados diferentes de maneiras diferentes. Você pode importar dados para o Power BI, que é a maneira mais comum de obter dados. Você também pode se conectar diretamente a alguns dados em seu repositório de origem original, que é chamado DirectQuery. Este artigo discute principalmente as funcionalidades do DirectQuery.

Este artigo descreve:

• As diferentes opções de conectividade de dados do Power BI.
• Diretrizes sobre quando usar o DirectQuery em vez de importar.
• Limitações e implicações do uso do DirectQuery.
• Recomendações para usar o DirectQuery com êxito.
• Como diagnosticar problemas de desempenho do DirectQuery.

O artigo se concentra no fluxo de trabalho do DirectQuery quando você cria um relatório no Power BI Desktop, mas também aborda a conexão por meio do DirectQuery no serviço do Power BI.

Modos de conectividade de dados do Power BI

O Power BI se conecta a um grande número de fontes de dados variadas, que abrangem:

• Serviços online como Salesforce e Dynamics 365.
• Bancos de dados como SQL Server, Access e Amazon Redshift.
• Arquivos simples no Excel, JSON e outros formatos.
• Outras fontes de dados, como Spark, sites e Microsoft Exchange.

Você pode importar dados dessas fontes para o Power BI. Para algumas fontes, você também pode se conectar usando o DirectQuery. Para obter um resumo das fontes que dão suporte ao DirectQuery, consulte fontes de dados do Power BI. As fontes habilitadas para DirectQuery são principalmente fontes que podem fornecer um bom desempenho de consulta interativa.

Você deve importar dados para o Power BI sempre que possível. A importação aproveita o mecanismo de consulta de alto desempenho do Power BI e proporciona uma experiência altamente interativa e completa.

Se você não conseguir cumprir suas metas importando dados, por exemplo, se os dados forem alterados com frequência e os relatórios precisarem refletir os dados mais recentes, considere usar o DirectQuery. O uso do DirectQuery só é possível quando a fonte de dados subjacente pode fornecer consultas interativas em menos de cinco segundos para uma consulta de agregação típica e pode processar a carga de consulta gerada. Considere cuidadosamente as limitações e implicações do uso do DirectQuery.

Funcionalidades do DirectQuery e de importação do Power BI evoluem ao longo do tempo. As alterações que fornecem mais flexibilidade ao usar dados importados permitem que você importe com mais frequência e elimine algumas das desvantagens de usar o DirectQuery. Independentemente dos aprimoramentos, ao usar o DirectQuery, o desempenho da fonte de dados subjacente é sempre uma consideração importante. Se essa fonte de dados subjacente for lenta, o uso do DirectQuery nessa fonte permanecerá impraticável.

As seguintes seções abrangem essas três opções para se conectar a dados: importação, DirectQuery e conexão dinâmica. O restante do artigo se concentra no DirectQuery.

Conexões de importação

Quando você se conecta a uma fonte de dados como o SQL Server e importa dados no Power BI Desktop, as seguintes condições de conectividade estão presentes:

• Quando você usa Obter dados pela primeira vez, cada conjunto de tabelas que você seleciona define uma consulta que retorna um conjunto de dados. Essas consultas podem ser editadas antes do carregamento dos dados, por exemplo, para aplicar filtros, agregar os dados ou unir tabelas diferentes.

• Após o carregamento, todos os dados definidos por essas consultas são importados para o cache do Power BI.

• A criação de um visual no Power BI Desktop resulta em consulta dos dados armazenados em cache. O repositório do Power BI garante que a consulta seja rápida e, portanto, que todas as alterações no visual sejam refletidas imediatamente.

• Os visuais não refletem alterações nos dados subjacentes no armazenamento de dados. Você precisa reimportar para atualizar os dados.

• Publicar o relatório no serviço do Power BI como um arquivo .pbix cria e carrega um modelo semântico que inclui os dados importados. Em seguida, você pode agendar a atualização de dados para reimportar os dados diariamente, por exemplo. Dependendo da localização da fonte de dados original, poderá ser necessário configurar um gateway de dados local para a atualização.

• Ao abrir um relatório existente no serviço do Power BI ou criar um relatório, os dados importados são consultados novamente, garantindo a interatividade.

• Você pode fixar visuais ou páginas de relatório inteiras como blocos de dashboard no serviço do Power BI. Os blocos são atualizados automaticamente sempre que o modelo semântico subjacente é atualizado.

Conexões do DirectQuery

Quando você usa o DirectQuery para se conectar a uma fonte de dados no Power BI Desktop, as seguintes condições de conectividade de dados estão presentes:

• Você usa Obter dados para selecionar a fonte. Para fontes relacionais, você ainda pode selecionar um conjunto de tabelas e cada uma ainda define uma consulta que retorna logicamente um conjunto de dados. Para fontes multidimensionais como o SAP BW (SAP Business Warehouse), selecione apenas a fonte.

• Após o carregamento, nenhum dado é importado para o repositório do Power BI. Quando você cria um visual no Power BI Desktop, as consultas são enviadas para a origem subjacente para recuperar os dados necessários. O tempo necessário para atualizar o visual depende do desempenho da fonte de dados subjacente.

• As alterações nos dados subjacentes não são imediatamente refletidas em nenhum visual existente. Ainda é necessário fazer a atualização. O Power BI Desktop reenvia as consultas necessárias para cada visual e atualiza o visual conforme necessário.

• Publicar o relatório no serviço do Power BI cria e carrega um modelo semântico, o mesmo que para importação. No entanto, esse modelo semântico não inclui dados.

• Abrir um relatório existente ou criar um relatório no serviço do Power BI consulta a fonte de dados subjacente para recuperar os dados necessários. Dependendo da localização da fonte de dados original, poderá ser necessário configurar um gateway de dados local para obter os dados.

• Você pode fixar visuais ou páginas inteiras de relatórios como blocos do dashboard. Para garantir que a abertura de um dashboard seja rápida, os blocos são atualizados automaticamente de acordo com um agendamento, por exemplo, a cada hora. Você pode controlar a frequência de atualização dependendo da frequência com que os dados são alterados e da importância de ver os dados mais recentes.

• Ao abrir um dashboard, os blocos refletem os dados na hora da última atualização, não necessariamente as últimas alterações feitas na fonte subjacente. Você pode atualizar um dashboard aberto para garantir que ele seja atual.

Conexões dinâmicas

Ao se conectar ao SQL Server Analysis Services, você pode optar por importar os dados ou usar uma conexão dinâmica com o modelo de dados selecionado. O uso de uma conexão dinâmica é semelhante ao DirectQuery. Nenhum dado é importado e a fonte de dados subjacente é consultada para atualizar visuais.

Por exemplo, ao usar a importação para se conectar ao SQL Server Analysis Services, você define uma consulta na fonte externa do SQL Server Analysis Services e importa os dados. Se você realizar uma conexão dinâmica, não definirá uma consulta e todo o modelo externo será exibido na lista de campos.

Essa situação também se aplica quando você se conecta às seguintes fontes, exceto que não há nenhuma opção para importar os dados:

• Modelos semânticos do Power BI, por exemplo, ao se conectar a um modelo semântico do Power BI que já foi publicado no serviço, com a finalidade de criar um relatório com base nele.

• Microsoft Dataverse.

Quando você publica relatórios do SQL Server Analysis Services que usam conexões dinâmicas, o comportamento no serviço do Power BI é semelhante aos relatórios do DirectQuery das seguintes maneiras:

• Ao abrir um relatório existente no serviço do Power BI ou criar um relatório, a fonte subjacente do SQL Server Analysis Services é consultada, possivelmente exigindo um gateway de dados local.

• Os blocos de dashboard são atualizados automaticamente de acordo com um agendamento, por exemplo, a cada hora.

Uma conexão dinâmica também difere do DirectQuery de várias maneiras. Por exemplo, nas conexões dinâmicas, a identidade do usuário que abre o relatório sempre é transmitida para a fonte subjacente do SQL Server Analysis Services.

Casos de uso do DirectQuery

A conexão com o DirectQuery pode ser útil nos cenários a seguir. Em vários desses casos, deixar os dados no local de origem inicial é necessário ou benéfico.

O DirectQuery no Power BI oferece os maiores benefícios nos seguintes cenários:

• Os dados são alterados com frequência e você precisa de relatórios quase em tempo real.
• Você precisa lidar com os dados grandes sem precisar agregar previamente.
• A fonte subjacente define e aplica regras de segurança.
• Aplicação de restrições de soberania de dados.
• A fonte é uma fonte multidimensional que contém medidas, como o SAP BW.

Os dados são alterados com frequência, e você precisa de relatórios quase em tempo real

Os modelos com os dados importados podem ser atualizados no máximo uma vez por hora, ou com uma frequência maior com as assinaturas do Power BI Pro ou do Power BI Premium. Se os dados estiverem continuamente em alteração e for necessário que os relatórios mostrem os dados mais recentes, o uso da importação com a atualização agendada poderá não atender às suas necessidades. Você pode transmitir os dados diretamente para o Power BI, embora haja limites para os volumes de dados compatíveis com esse caso.

O uso do DirectQuery significa que abrir ou atualizar um relatório ou um dashboard sempre mostra os dados mais recentes na fonte. Além disso, os blocos de dashboard podem ser atualizados com mais frequência, por exemplo, a cada 15 minutos.

Os dados são muito grandes

Se os dados forem muito grandes, não será viável importar todos eles. O DirectQuery não exige uma grande transferência de dados, pois os dados são consultados in-loco. No entanto, dados grandes também podem tornar o desempenho das consultas nessa fonte subjacente lento demais.

Você nem sempre precisa importar os dados detalhados completos. O Editor do Power Query facilita a pré-agregação de dados durante a importação. Tecnicamente, é possível importar exatamente os dados agregados necessários para cada visual. Embora o DirectQuery seja a abordagem mais simples para dados grandes, a importação de dados agregados poderá oferecer uma solução caso a fonte de dados subjacente seja muito lenta para o DirectQuery.

Esses detalhes estão relacionados apenas ao uso do Power BI. Para obter mais informações sobre como usar modelos grandes no Power BI, consulte grandes modelos semânticos no Power BI Premium. Não há restrição para a frequência com que os dados podem ser atualizados.

A fonte subjacente define regras de segurança

Quando os dados são importados, o Power BI se conecta à fonte de dados usando as credenciais do usuário atual do Power BI Desktop ou as credenciais definidas como parte da configuração da atualização agendada do serviço do Power BI. Ao publicar e compartilhar relatórios que têm dados importados, você precisa ter cuidado para só compartilhá-lo apenas com usuários que tenham a permissão para ver esses dados, ou você precisa definir a segurança em nível de linha como parte do modelo semântico.

O DirectQuery permite que as credenciais de um visualizador de relatórios passem para a fonte subjacente, que aplica regras de segurança. O DirectQuery dá suporte ao SSO (logon único) para fontes de dados do SQL do Azure e por meio de um gateway de dados para servidores SQL locais. Para obter mais informações, confira Visão geral do logon único (SSO) para gateways de dados local no Power BI.

Houver aplicação de restrições de soberania de dados

Algumas organizações têm políticas em relação à soberania de dados, resultando na impossibilidade de os dados deixarem as instalações da organização. Esses dados apresentam problemas para soluções baseadas na importação de dados. Com o DirectQuery, os dados permanecem no local da fonte de dados subjacente. No entanto, mesmo com o DirectQuery, o serviço do Power BI mantém alguns caches de dados no nível do visual, devido à atualização agendada dos blocos.

A fonte de dados subjacente usa medidas

Uma fonte de dados subjacente, como SAP HANA ou SAP BW, contém medidas. As medidas significam que os dados importados já estão em um determinado nível de agregação, conforme definido pela consulta. Um visual que solicita dados em uma agregação de nível superior, como TotalSales por Ano, agrega ainda mais o valor agregado. Essa agregação é adequada para medidas aditivas, como Sum e Min, mas pode ser um problema para medidas não aditivas, como Average e DistinctCount.

Obter facilmente os dados de agregação corretos necessários para um visual diretamente da origem exige o envio de consultas por visual, como no DirectQuery. Ao conectar-se com o SAP BW, a escolha do DirectQuery permite esse tratamento das medidas. Para obter mais informações, confira DirectQuery e SAP BW.

Atualmente, o DirectQuery no SAP HANA a trata como uma fonte relacional e produz um comportamento semelhante à importação. Para obter mais informações, confira DirectQuery e SAP HANA.

Limitações do DirectQuery

O uso do DirectQuery tem algumas implicações potencialmente negativas. Algumas dessas limitações diferem ligeiramente dependendo da fonte exata que você usa. As seções a seguir listam implicações gerais do uso do DirectQuery e limitações relacionadas ao desempenho, segurança, transformações, modelagem e relatórios.

Implicações gerais

Veja as seguintes implicações gerais e limitações do uso do DirectQuery:

• Se os dados forem alterados, você precisará atualizar para mostrar os dados mais recentes. Considerando o uso dos caches, não há nenhuma garantia de que o visual sempre mostre os dados mais recentes. Por exemplo, um visual pode mostrar as transações do dia anterior. Uma alteração de segmentação de dados pode atualizar o visual para mostrar transações nos últimos dois dias, incluindo transações recentes recém-chegadas. Mas retornar a segmentação de dados para o valor original pode fazer com que ela volte a mostrar o valor anterior armazenado em cache. Selecione Atualizar para limpar todos os caches e atualizar todos os visuais na página para exibir os dados mais recentes.

• Se os dados forem alterados, não haverá nenhuma garantia de consistência entre os visuais. visuais diferentes, na mesma página ou em páginas diferentes, podem ser atualizados em diferentes momentos. Se os dados da fonte subjacente forem alterados, não haverá nenhuma garantia de que cada visual mostrará os dados no mesmo ponto no tempo.

  Na verdade, considerando que, às vezes, mais de uma consulta é necessária para apenas um visual (por exemplo, para obter os detalhes e os totais), não há nenhuma garantia de consistência, mesmo em apenas um visual. Garantir essa consistência exige a sobrecarga da atualização de todos os visuais sempre que qualquer visual é atualizado, em conjunto com o uso de recursos caros como o isolamento do instantâneo na fonte de dados subjacente.

  Esse problema pode ser atenuado em grande parte selecionando Atualizar para atualizar todos os visuais da página. Mesmo com o uso do modo de importação, há um problema semelhante para garantir a consistência ao importar dados de mais de uma tabela.

• Você precisa atualizar no Power BI Desktop para refletir as alterações de esquema. Depois que um relatório é publicado, Atualizar no serviço do Power BI atualiza os visuais no relatório. Mas se o esquema de origem subjacente for alterado, o serviço do Power BI não atualizará automaticamente a lista de campos disponíveis. Se tabelas ou colunas forem removidas da fonte de dados subjacente, isso poderá resultar em uma falha de consulta após a atualização. Para atualizar os campos no modelo para refletir as alterações, você precisa abrir o relatório no Power BI Desktop e escolher Atualizar.

• Há um limite de 1 milhão de linhas que podem ser retornadas em qualquer consulta. Há um limite fixo de 1 milhão de linhas que podem ser retornadas em qualquer consulta individual para a fonte de dados subjacente. Esse limite geralmente não tem nenhuma implicação prática, e os visuais não exibirão um número tão grande de pontos. No entanto, o limite pode ser atingido nos casos em que o Power BI não otimiza por completo as consultas enviadas e solicita um resultado intermediário que excede o limite.

  Isso pode ocorrer durante a criação de um visual, no caminho para um estado final mais razoável. Por exemplo, a inclusão de Customer e TotalSalesQuantity poderia alcançar esse limite se houvesse mais de 1 milhão de clientes, até que algum filtro fosse aplicado. O erro retornado seria: O conjunto de resultados de uma consulta a uma fonte de dados externa excedeu o tamanho máximo permitido de '1000000' linhas.

  Observação

  As capacidades Premium permitem que você exceda o limite de um milhão de linhas. Para obter mais informações, consulte Contagem Máxima de Conjuntos de Linhas Intermediárias.

• Não é possível alterar do modo de importação para o modo DirectQuery. Você poderá alternar um modelo do modo DirectQuery para o modo de importação se importar todos os dados necessários. Não é possível voltar para o modo DirectQuery, principalmente devido ao conjunto de recursos ao qual o modo DirectQuery não dá suporte. Os modelos do DirectQuery em fontes multidimensionais, como o SAP BW, também não podem ser alternados do DirectQuery para importação, devido ao tratamento diferente das medidas externas.

Implicações de desempenho e carga

Ao usar o DirectQuery, a experiência geral depende do desempenho da fonte de dados subjacente. Se a atualização de cada visual (por exemplo, depois de alterar um valor de segmentação de dados) levar menos de cinco segundos, a experiência será razoável, embora possa parecer lenta em comparação com a resposta imediata com os dados importados. Se a lentidão da fonte de dados fizer com que visuais individuais levem mais do que dezenas de segundos para ser atualizados, a experiência se tornará extremamente ruim. As consultas podem, até mesmo, atingir o tempo limite.

Juntamente com o desempenho da fonte de dados subjacente, a carga colocada na origem também afeta o desempenho. Cada usuário que abre um relatório compartilhado e cada bloco de dashboard que é atualizado enviam, pelo menos, uma consulta por visual à fonte subjacente. A fonte de dados precisa ser capaz de lidar com essa carga de consulta, mantendo, ao mesmo tempo, um desempenho razoável.

Implicações de segurança

A menos que a fonte de dados subjacente use o SSO, um relatório do DirectQuery sempre usará as mesmas credenciais fixas para se conectar à origem depois que ela for publicada no serviço do Power BI. Imediatamente após publicar um relatório do DirectQuery, você precisa configurar as credenciais do usuário a ser usado. Até que você configure as credenciais, a abertura do relatório no serviço do Power BI resultará em um erro.

Depois de fornecer as credenciais do usuário, o Power BI usará essas credenciais para quem abrir o relatório, o mesmo que para dados importados. Cada usuário vê os mesmos dados, a menos que a Segurança em Nível de Linha seja definida como parte do relatório. Você precisa prestar a mesma atenção ao compartilhamento do relatório do que aos dados importados, mesmo que haja regras de segurança definidas na fonte de dados subjacente.

• Conectar-se a modelos semânticos do Power BI e ao Analysis Services no modo DirectQuery sempre usa SSO, portanto, a segurança é semelhante à das conexões dinâmicas com o Analysis Services.

• Não há suporte para credenciais alternativas ao fazer conexões do DirectQuery com o SQL Server no Power BI Desktop. Você pode usar as suas credenciais atuais do Windows ou credenciais de banco de dados.

• Você pode usar várias fontes de dados em um modelo do DirectQuery usando modelos compostos. Ao usar várias fontes de dados, é importante entender as implicações de segurança de como os dados são movidos entre as fontes de dados subjacentes.

Limitações de transformação de dados

O DirectQuery limita as transformações de dados que você pode aplicar no Editor do Power Query. Com os dados importados, você pode aplicar facilmente um conjunto sofisticado de transformações para limpar e remodelar os dados antes de usá-los para criar visuais. Por exemplo, você pode analisar documentos JSON ou dinamizar dados de uma coluna para um formulário de linha. Essas transformações são mais limitadas no DirectQuery.

Quando você se conecta a uma fonte de dados OLAP (processamento analítico online), como o SAP BW, não é possível definir nenhuma transformação e todo o modelo externo é obtido da fonte de dados. Para fontes relacionais como o SQL Server, ainda é possível definir um conjunto de transformações por consulta, mas essas transformações são limitadas por questões de desempenho.

Todas as transformações precisam ser aplicadas em cada consulta à fonte de dados subjacente, em vez de uma vez na atualização de dados. É preciso que as transformações possam ser razoavelmente convertidas em apenas uma consulta nativa. Se você usar uma transformação muito complexa, receberá um erro informando que ela deve ser excluída ou que o modelo de conexão deve ser alternado para importar.

Além disso, a caixa de diálogo Obter Dados ou o Editor do Power Query usam subseleções nas consultas geradas e enviadas para recuperar dados de um visual. A consulta definida no Editor do Power Query precisa ser válida nesse contexto. Em particular, não é possível usar uma consulta com expressões de tabela comuns, nem uma que invoca procedimentos armazenados.

Limitações de modelagem

O termo modelagem, nesse contexto, significa o ato de refinar e enriquecer os dados brutos, como parte da criação de um relatório que os utiliza. Exemplos de modelagem incluem:

• Definir relações entre tabelas.
• Adicionar novos cálculos, como colunas calculadas e medidas.
• Renomear e ocultar colunas e medidas.
• Definir hierarquias.
• Definir a formatação de coluna, resumo padrão e ordem de classificação.
• Agrupar ou realizar clustering de valores.

Você ainda pode fazer muitos desses enriquecimentos de modelo ao usar o DirectQuery e usar o princípio de enriquecer os dados brutos para aprimorar o consumo posterior. No entanto, algumas funcionalidades de modelagem não estão disponíveis ou são limitados ao usar o DirectQuery. As limitações são aplicadas para evitar problemas de desempenho.

As limitações a seguir são comuns a todas as fontes do DirectQuery. Mais limitações podem se aplicar a fontes individuais.

• Nenhuma hierarquia de datas interna: ao importar dados, cada coluna de data/datetime também tem uma hierarquia de data interna disponível por padrão. Por exemplo, se você importar uma tabela de pedidos de vendas que inclua uma coluna OrderDate e usar OrderDate em um visual, poderá escolher o nível de data apropriado a ser usado, como ano, mês ou dia. Essa hierarquia de data interna não está disponível com o DirectQuery. Se houver uma tabela Data disponível na fonte de dados subjacente, como é comum em muitos data warehouses, você poderá usar as funções de inteligência temporal da DAX (Data Analysis Expressions), como de costume.

• Suporte de data/hora somente para o nível de segundos: Para modelos semânticos que usam colunas de tempo, o Power BI emite consultas para a fonte subjacente do DirectQuery apenas até o nível de detalhes de segundos, não milissegundos. Remova os dados de milissegundos das colunas de origem.

• Limitações em colunas calculadas: as colunas calculadas são limitadas a serem intralinha e, sendo assim, só podem fazer referência a valores de outras colunas da mesma tabela, sem o uso de nenhuma função de agregação. Além disso, as funções escalares DAX permitidas, como LEFT(), são limitadas às funções que podem ser enviadas por push para a fonte de dados subjacente. As funções variam de acordo com as funcionalidades exatas da origem. As funções para as quais não há suporte não são listadas no preenchimento automático durante a criação do DAX para uma coluna calculada e resultam em um erro caso sejam usadas.

• Não há suporte para as funções DAX pai-filho: no modo DirectQuery, não é possível usar a família de funções DAX PATH() que geralmente processam estruturas pai-filho, como gráficos de contas ou hierarquias de funcionários.

• Sem clustering: ao usar o DirectQuery, você não pode usar a funcionalidade de clustering para localizar grupos automaticamente.

Limitações de relatórios

Quase todos os recursos de relatórios têm suporte para modelos do DirectQuery. Desde que a fonte subjacente ofereça um nível adequado de desempenho, você pode usar o mesmo conjunto de visualizações que para dados importados.

Uma limitação geral é que o comprimento máximo dos dados em uma coluna de texto para modelos semânticos do DirectQuery é de 32.764 caracteres. A criação de um relatório de textos mais longos do que isso resultará em erro.

Os seguintes recursos de relatório do Power BI podem causar problemas de desempenho em relatórios baseados no DirectQuery:

• Filtros de medidas: os visuais que usam medidas (ou agregações de colunas) podem conter filtros nessas medidas. Por exemplo, o gráfico a seguir mostra SalesAmount por Categoria, mas só incluindo as categorias com mais de 20 milhões em vendas.

  

  Esta abordagem faz com que duas consultas sejam enviadas à fonte subjacente:

  • A primeira consulta recupera as categorias que atendem à condição SalesAmount superior a 20 milhões.
  • A segunda consulta recupera os dados necessários para o visual, incluindo as categorias que atendem à condição WHERE.

  Essa abordagem geralmente funciona bem se houver centenas ou milhares de categorias, como neste exemplo. O desempenho poderá diminuir se o número de categorias for muito maior. A consulta falhará se houver mais de um milhão de categorias.

• Filtros de principais N valores: filtros avançados podem ser definidos para realizar a filtragem somente dos N valores superiores ou inferiores classificados de acordo com uma medida. Por exemplo, os filtros podem incluir as dez principais categorias. Esta abordagem faz novamente com que duas consultas sejam enviadas à fonte de dados subjacente. No entanto, a primeira consulta retorna todas as categorias da fonte subjacente e, em seguida, os TopN são determinados com base nos resultados retornados. Dependendo da cardinalidade da coluna envolvida, essa abordagem poderá causar problemas de desempenho ou falhas de consulta, devido ao limite de um milhão de linhas.

• Mediana: qualquer agregação, como Sum ou Count Distinct, é enviada por push à fonte subjacente. No entanto, geralmente a agregação median não é compatível com a fonte subjacente. Para median, os dados de detalhes são recuperados da fonte subjacente e a mediana é calculada com base nos resultados retornados. Essa abordagem é razoável quando a mediana deve ser calculada em relação a um número relativamente pequeno de resultados.

  Problemas de desempenho ou falhas de consulta poderão surgir se a cardinalidade for grande devido ao limite de um milhão de linhas. Por exemplo, cunsultar a Mediana da População do País/Região pode ser razoável, ao contrário da Mediana do Preço de Vendas.

• Filtros de texto avançados como 'contém': a filtragem avançada em uma coluna de texto permite filtros como contains e begins with. Esses filtros podem causar degradação no desempenho para algumas fontes de dados. Em particular, não use o filtro padrão contains se precisar de uma correspondência exata. Embora os resultados possam ser os mesmos, dependendo dos dados reais, o desempenho poderá ser drasticamente diferente devido ao uso de índices.

• Segmentações de seleção múltipla: por padrão, as segmentações só permitem fazer uma seleção. Permitir a seleção múltipla de filtros pode causar problemas de desempenho. Por exemplo, se o usuário selecionar dez produtos de interesse, cada nova seleção resultará no envio de consultas para a fonte de dados. Embora o usuário possa selecionar o próximo item antes da conclusão da consulta, essa abordagem resulta em uma carga extra na fonte subjacente.

• Totais em elementos tabulares: por padrão, tabelas e matrizes exibem totais e subtotais. Em muitos casos, obter os valores desses totais exige o envio de consultas separadas para a fonte de dados subjacente. Esse requisito se aplicará sempre que você usar a agregação DistinctCount ou em todos os casos em que o DirectQuery no SAP BW ou SAP HANA for usado. Você pode desativar esses totais usando o painel Formatar.

Recomendações do DirectQuery

Esta seção fornece diretrizes de alto nível sobre como usar o DirectQuery com êxito, considerando as respectivas implicações.

Desempenho da fonte de dados subjacente

Valide se os visuais simples são atualizados em cinco segundos, fornecendo uma experiência interativa razoável. Se os visuais levarem mais de 30 segundos para serem atualizados, é provável que outros problemas após a publicação do relatório tornem a solução inviável.

Se as consultas estiverem lentas, examine as consultas que estão sendo enviadas à fonte subjacente e o motivo do desempenho lento. Para obter mais informações, confira Diagnóstico de desempenho.

Este artigo não aborda a grande variedade de recomendações de otimização de banco de dados em todo o conjunto de possíveis fontes subjacentes. As seguintes práticas de banco de dados padrão que se aplicam à maioria das situações:

• Para aprimorar o desempenho, baseie as relações em colunas de inteiros em vez de unir colunas de outros tipos de dados.

• Crie os índices apropriados. A criação de índices geralmente significa o uso de índices de repositório de coluna em fontes de dados que dão suporte a eles, por exemplo, o SQL Server.

• Atualize as estatísticas necessárias na fonte de dados.

Design de modelo

Ao definir o modelo, siga estas diretrizes:

• Evite consultas complexas no Editor do Power Query. O Editor do Power Query converte uma consulta complexa em uma só consulta SQL. A consulta individual é exibida na subseleção de cada consulta enviada a essa tabela. Se essa consulta for complexa, poderá resultar em problemas de desempenho em todas as consultas enviadas. Você pode obter a consulta SQL real para um conjunto de etapas clicando com o botão direito do mouse na última etapa em Etapas aplicadas no Editor do Power Query e escolhendo Exibir Consulta Nativa.

• Mantenha as medidas simples. Pelo menos inicialmente, limite as medidas a agregações simples. Se as medidas funcionarem de maneira satisfatória, você poderá definir medidas mais complexas, mas preste atenção ao desempenho.

• Evite relações em colunas calculadas. Em bancos de dados em que você precisa fazer junções de várias colunas, o Power BI não permite basear relações em várias colunas como a chave primária ou a chave estrangeira. A solução alternativa comum é concatenar as colunas usando uma coluna calculada e basear a junção nessa coluna.

  Embora essa solução alternativa seja razoável para dados importados, para o DirectQuery, ela resulta em uma junção em uma expressão. Esse resultado geralmente impede o uso de qualquer índice e leva a um baixo desempenho. A única solução alternativa é realmente materializar as várias colunas em apenas uma coluna na fonte de dados subjacente.

• Evite relações em colunas 'uniqueidentifier'. O Power BI não dá suporte nativo ao tipo de dados uniqueidentifier. A definição de uma relação entre colunas do tipo uniqueidentifier resulta em uma consulta com uma junção que envolve uma conversão. Novamente, essa abordagem geralmente resulta em um baixo desempenho. A única solução alternativa seria materializar colunas de um tipo alternativo na fonte de dados subjacente.

• Ocultar a coluna 'para' em relações. A coluna to em relações é normalmente a chave primária na tabela to. Essa coluna deve estar oculta, mas, se oculta, ela não aparece na lista de campos e não pode ser usada em visuais. Geralmente, as colunas em que as relações se baseiam são, na verdade, colunas do sistema, por exemplo, chaves alternativas em um data warehouse. Ainda assim, é melhor ocultar essas colunas.

  Caso a coluna tenha significado, introduza uma coluna calculada que seja visível e que tenha uma expressão simples de ser igual à chave primária, por exemplo:

      ProductKey_PK   (Destination of a relationship, hidden)
      ProductKey (= [ProductKey_PK],   visible)
      ProductName
      ...
  

• Examine todas as alterações de colunas calculadas e de tipos de dados. Você pode usar tabelas calculadas ao usar o DirectQuery com modelos compostos. Essas funcionalidades não são necessariamente prejudiciais, mas resultam em consultas que contêm expressões em vez de referências simples a colunas. Essas consultas podem resultar na não utilização de índices.

• Evite filtragem cruzada bidirecional em relações. O uso de filtragem cruzada bidirecional pode levar a instruções de consulta com desempenho insatisfatório. Para obter mais informações sobre filtragem cruzada bidirecional, confira Habilitar filtragem cruzada bidirecional para DirectQuery no Power BI Desktop ou baixe o white paper Filtragem cruzada bidirecional. Os exemplos no artigo são para o SQL Server Analysis Services, mas os pontos fundamentais também se aplicam ao Power BI.

• Experimente com a configuração Pressupor integridade referencial. A configuração Pressupor integridade referencial em relações permite que as consultas usem instruções INNER JOIN em vez de OUTER JOIN. Essas diretrizes geralmente aprimoram o desempenho de consulta, embora ele dependa das especificações da fonte de dados.

• Não use a filtragem de data relativa no Editor do Power Query. É possível definir a filtragem de data relativa no Editor do Power Query. Por exemplo, você pode filtrar as linhas em que a data está nos últimos 14 dias.

  

  No entanto, esse filtro é convertido em um filtro com base em uma data fixa, como a hora em que a consulta foi criada, como você pode ver na consulta nativa.

  

  Provavelmente, esses dados não são o que você desejava. Para garantir que o filtro seja aplicado com base na data do momento em que o relatório é executado, aplique o filtro de data no relatório. Você pode criar uma coluna calculada que calcula o número de dias atrás usando a função DAX DATE() e usar essa coluna calculada no filtro.

Design de relatório

Ao criar um relatório que usa uma conexão DirectQuery, siga estas diretrizes:

• Considere usar as opções de redução de consulta: o Power BI fornece opções de relatório para enviar menos consultas e desabilitar algumas interações que resultam em uma experiência insatisfatória, caso as consultas resultantes levem muito tempo para ser executadas. Essas opções se aplicam quando você interage com seu relatório no Power BI Desktop e também se aplicam quando os usuários consomem o relatório no serviço do Power BI.

  Para acessar essas opções no Power BI Desktop, acesse Arquivo>Opções e configurações>Opções e selecione Redução de consulta.

  

  As seleções na tela Redução de consulta permitem mostrar um botão Aplicar para segmentações ou seleções de filtro. Nenhuma consulta é enviada até que você selecione o botão Aplicar na segmentação ou filtro. Em seguida, as consultas usam suas seleções para filtrar os dados. Esse botão permite que você faça várias seleções de segmentação e de filtro antes de aplicá-las.

• Aplique filtros primeiro: aplique todos os filtros adequados sempre no início da criação de um visual. Por exemplo, em vez de arrastar TotalSalesAmount e ProductName e, em seguida, filtrar para um ano específico, aplique o filtro em Ano logo no início.

  Cada etapa da criação de um visual envia uma consulta. Embora seja possível fazer outra alteração antes que a primeira consulta seja concluída, essa abordagem ainda mantém uma carga desnecessária na fonte de dados subjacente. Ao aplicar os filtros no início, as consultas intermediárias serão geralmente menos dispendiosas. Além disso, a não aplicação dos filtros no início pode fazer com que o limite de um milhão de linhas seja alcançado.

• Limite o número de visuais em uma página: ao abrir uma página ou alterar uma segmentação ou um filtro no nível da página, todos os visuais de uma página são atualizados. Há um limite para o número de consultas paralelas. À medida que o número de visuais aumenta, alguns visuais são atualizados serialmente, o que aumenta o tempo necessário para atualizar a página. Assim, é recomendável limitar o número de visuais em apenas uma página e, em vez disso, ter mais páginas mais simples.

• Considerar desativar a interação entre visuais: por padrão, as visualizações em uma página de relatório podem ser usadas para realizar filtro cruzado e destaque cruzado de outras visualizações na página. Por exemplo, se você selecionou 1999 no gráfico de pizza, é feito o realce cruzado do gráfico de colunas para mostrar as vendas por categoria para 1999.

  

  A filtragem cruzada e o realce cruzado no DirectQuery exigem que as consultas sejam enviadas para a fonte subjacente. Você deverá desativar essa interação se o tempo necessário para responder às seleções dos usuários for demasiadamente longo.

  Você pode usar as configurações de Redução de consulta para desabilitar o realce cruzado em todo o relatório 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.

Número máximo de conexões

Você pode definir o número máximo de conexões abertas pelo DirectQuery para cada fonte de dados subjacente, que controla o número de consultas enviadas simultaneamente para cada fonte de dados.

O DirectQuery abre um número máximo padrão de 10 conexões simultâneas. Para alterar o número máximo do arquivo atual no Power BI Desktop, acesse Arquivo>Opções e Configurações>Opções e selecione DirectQuery na seção Arquivo Atual do painel esquerdo.

A configuração só é habilitada quando há, pelo menos, uma fonte do DirectQuery no relatório atual. O valor se aplica a todas as fontes do DirectQuery e às novas fontes do DirectQuery adicionadas a esse relatório.

O aumento de Máximo de conexões por fonte de dados permite o envio de mais consultas, até o número máximo especificado, para a fonte de dados subjacente. Essa abordagem é útil quando muitos visuais estão em uma só página ou muitos 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 do limite resulta em mais carga na fonte subjacente e, portanto, a configuração não garante o aprimoramento do desempenho geral.

Depois de publicar um relatório no serviço do Power BI, o número máximo de consultas simultâneas também depende dos limites fixos definidos no ambiente de destino em que o relatório é publicado. O Power BI, o Power BI Premium e o Servidor de Relatórios do Power BI impõem limites distintos. A tabela a seguir lista os limites superiores das conexões ativas por fonte de dados para cada ambiente do Power BI. Esses limites se aplicam a fontes de dados de nuvem e fontes de dados locais, como SQL Server, Oracle e Teradata.

Ambiente	Limite superior por fonte de dados
Power BI Pro	Dez conexões ativas
Power BI Premium	Depende da limitação de SKU do modelo semântico
Servidor de Relatórios do Power BI	Dez conexões ativas

DirectQuery no serviço do Power BI

Todas as fontes de dados do DirectQuery são compatíveis com o Power BI Desktop e algumas fontes também estão disponíveis diretamente de dentro do serviço do Power BI. Por exemplo, é possível que um usuário empresarial use o Power BI para se conectar aos próprios dados no Salesforce e obtenha um dashboard imediatamente, sem usar o Power BI Desktop.

Somente as duas seguintes fontes de dados habilitadas para DirectQuery estão disponíveis diretamente no serviço do Power BI:

• Spark
• Azure Synapse Analytics (antigo SQL Data Warehouse)

Mesmo para essas duas fontes, ainda é melhor iniciar o uso do DirectQuery dentro de Power BI Desktop. Embora seja fácil inicialmente fazer a conexão no serviço do Power BI, há limitações para aprimorar ainda mais o relatório resultante. Por exemplo, não é possível criar cálculos nem usar muitos recursos analíticos, nem atualizar os metadados para refletir alterações no esquema subjacente.

O desempenho de um relatório DirectQuery no serviço do Power BI depende do grau da carga colocada sobre a fonte de dados subjacente. A carga depende de:

• O número de usuários que compartilham o relatório e o dashboard.
• A complexidade do relatório.
• Se o relatório define a segurança em nível de linha.

Comportamento do relatório no serviço do Power BI

Quando você abre um relatório no serviço do Power BI, todos os visuais na página visível atualmente são atualizados. Cada visual exige pelo menos uma consulta à fonte de dados subjacente. Alguns visuais podem exigir mais de uma consulta. Por exemplo, um visual pode mostrar valores de agregação de duas tabelas de fatos diferentes, conter uma medida mais complexa ou conter os totais de uma medida não aditiva, como Contagem Distinta. O acesso de uma nova página atualiza esses visuais. A atualização envia um novo conjunto de consultas para a fonte subjacente.

Cada interação do usuário no relatório pode resultar na atualização de visuais. Por exemplo, a seleção de outro valor em uma segmentação exige o envio de um novo conjunto de consultas para atualizar todos os visuais afetados. O mesmo é verdadeiro ao clicar em um visual para realizar realces cruzados de outros visuais ou alterar um filtro. Da mesma forma, a edição de um relatório exige o envio de consultas para cada etapa no caminho para produzir o visual final.

Ocorre o cache dos resultados. A atualização de um visual é instantânea se exatamente os mesmos resultados foram obtidos recentemente. Se a segurança em nível de linha não estiver definida, esses caches não serão compartilhados entre os usuários.

O uso do DirectQuery impõe algumas limitações importantes em algumas das funcionalidades que o serviço do Power BI oferece para relatórios publicados:

• Não há suporte para os Insights Rápidos: os insights rápidos do Power BI pesquisam diferentes subconjuntos do modelo semântico durante a aplicação de um conjunto de algoritmos sofisticados para descobrir informações que possam ser interessantes. Como os insights rápidos exigem consultas de alto desempenho, esse recurso não está disponível em modelos semânticos que usam o DirectQuery.

• O uso de Explorar no Excel resulta em baixo desempenho: você pode explorar um modelo semântico usando a funcionalidade Explorar no Excel, que permite criar tabelas dinâmicas e gráficos dinâmicos no Excel. Essa funcionalidade é compatível com modelos semânticos que usam DirectQuery, mas o desempenho é mais lento do que criar visuais no Power BI. Se o uso do Excel for importante para seus cenários, leve esse problema em consideração ao decidir entre usar ou não o DirectQuery.

• O Excel não mostra hierarquias: por exemplo, quando você usa Analisar no Excel, o Excel não mostra nenhuma hierarquia definida em modelos do Azure Analysis Services ou modelos semânticos do Power BI que usam DirectQuery.

Atualização do painel

No serviço do Power BI, você pode fixar visuais individuais ou páginas inteiras em dashboards como blocos. Os blocos baseados em modelos semânticos do DirectQuery são atualizados automaticamente pelo envio de consultas às fontes de dados subjacentes em um agendamento. Por padrão, os modelos semânticos são atualizados a cada hora, mas você pode definir os intervalos de agendamento de atualização entre semanalmente e a cada 15 minutos como parte das configurações do modelo semântico.

Se nenhuma segurança em nível de linha estiver definida no modelo, cada bloco será atualizado uma vez e os resultados compartilhados entre todos os usuários. Se você usar a segurança em nível de linha, cada bloco exigirá que consultas separadas por usuário sejam enviadas para a fonte de dados subjacente.

Pode haver um grande efeito multiplicador. Um dashboard com dez blocos, compartilhado com 100 usuários, criado em um modelo semântico usando o DirectQuery com a segurança em nível de linha, fará com que, pelo menos, mil consultas sejam enviadas à fonte de dados subjacente a cada atualização. Preste atenção especial ao uso da segurança em nível de linha e da configuração do agendamento de atualização.

Tempos limite de consulta

Um tempo limite de quatro minutos é aplicado a consultas individuais no serviço do Power BI. As consultas que levam mais de quatro minutos falham. Esse limite tem como finalidade evitar problemas causados por tempos de execução excessivamente longos. Você deve usar o DirectQuery apenas para fontes de dados que possam fornecer desempenho de consulta interativa.

Diagnósticos de desempenho

Esta seção descreve como diagnosticar problemas de desempenho ou obter informações mais detalhadas para permitir que os relatórios sejam otimizados.

Comece a diagnosticar problemas de desempenho no Power BI Desktop, em vez de no serviço do Power BI. Problemas de desempenho geralmente se baseiam no desempenho da fonte subjacente. Você pode identificar e diagnosticar problemas com mais facilidade no ambiente mais isolado do Power BI Desktop.

Essa abordagem inicialmente elimina alguns componentes, como o gateway do Power BI. Se os problemas de desempenho não ocorrerem no Power BI Desktop, investigue as especificidades do relatório no serviço do Power BI.

O Analisador de desempenho do Power BI Desktop é uma ferramenta útil para identificar problemas. Tente isolar os problemas em um visual individual, em vez de muitos visuais em uma página. Se ao usar apenas um visual em uma página do Power BI Desktop o desempenho for lento, use o Analisador de desempenho para analisar as consultas que o Power BI Desktop envia para a fonte de dados subjacente.

Você também pode exibir rastreamentos e informações de diagnóstico que algumas fontes de dados subjacentes emitem. Mesmo que não haja rastreamentos da origem, o arquivo de rastreamento pode conter detalhes úteis de como uma consulta é executada e como você pode aprimorá-la. Você pode usar o processo a seguir para exibir as consultas que o Power BI envia e os respectivos tempos de execução.

Usar SQL Server Profiler para ver consultas

Por padrão, o Power BI Desktop registra eventos durante uma determinada sessão em um arquivo de rastreamento chamado FlightRecorderCurrent.trc. O arquivo de rastreamento está na pasta Power BI Desktop do usuário atual, em uma pasta chamada AnalysisServicesWorkspaces.

Para algumas fontes do DirectQuery, esse arquivo de rastreamento inclui todas as consultas enviadas para a fonte de dados subjacente. As seguintes fontes de dados enviam consultas ao log:

• SQL Server
• Banco de Dados SQL do Azure
• Azure Synapse Analytics (antigo SQL Data Warehouse)
• Oracle
• Teradata
• SAP HANA

Você pode ler os arquivos de rastreamento usando o SQL Server Profiler, parte do download gratuito SQL Server Management Studio.

Para abrir o arquivo de rastreamento para a sessão atual:

1. Durante uma sessão de Power BI Desktop, selecione Arquivo>Opções e configurações>Opções e, em seguida, selecione Diagnóstico.

2. Em Coleção de Despejo de Memória, selecione Abrir pasta de rastreamentos/despejo de memória.

   

   A pasta Power BI Desktop\Traces é aberta.

3. Navegue até a pasta pai e, em seguida, até a pasta AnalysisServicesWorkspaces, que contém uma pasta de workspace para cada instância aberta do Power BI Desktop. Essas pastas são nomeadas com um sufixo de inteiro, como AnalysisServicesWorkspace2058279583. A pasta de workspace é excluída quando a sessão associada do Power BI Desktop é encerrada.

   Dentro da pasta de workspace da sessão atual do Power BI, a pasta \Data contém o arquivo de rastreamento FlightRecorderCurrent.trc. Anote o local dela.

4. Abra o SQL Server Profiler e selecione Arquivo>Abrir>Arquivo de Rastreamento.

5. Navegue até ou insira o caminho para o arquivo de rastreamento da sessão atual do Power BI e abra FlightRecorderCurrent.trc.

O SQL Server Profiler exibe todos os eventos da sessão atual. A captura de tela a seguir realça um grupo de eventos para uma consulta. Cada grupo de consulta tem os seguintes eventos:

• Eventos Query Begin e Query End, que representam o início e o fim de uma consulta DAX gerada alterando um visual ou filtro na interface do usuário do Power BI ou filtrando ou transformando dados no Editor do Power Query.

• Um ou mais pares de eventos DirectQuery Begin e DirectQuery End, que representam uma consulta enviada à fonte de dados subjacente, como parte da avaliação da consulta DAX.

Várias consultas DAX podem ser executadas em paralelo, de modo que os eventos de diferentes grupos posam ser intercalados. Você pode usar o valor ActivityID para determinar quais eventos pertencem ao mesmo grupo.

As seguintes colunas também são pertinentes:

• TextData: os detalhes textuais do evento. Para eventos Query Begin e Query End, o detalhe é a consulta DAX. Para eventos DirectQuery Begin e DirectQuery End, o detalhe é a consulta SQL enviada à fonte de dados subjacente. O TextData para o evento selecionado no momento também aparece no painel na parte inferior da tela.
• EndTime: a hora em que o evento foi concluído.
• Duração: o tempo, em milissegundos, necessário para executar a consulta DAX ou SQL.
• Erro: indica se ocorreu um erro e, nesse caso, o evento também será exibido em vermelho.

Para capturar um rastreamento para ajudar a diagnosticar um possível problema de desempenho:

1. Abra uma sessão individual do Power BI Desktop, para evitar a confusão de ter várias pastas de workspace.

2. Execute o conjunto de ações desejadas no Power BI Desktop. Inclua algumas ações adicionais, para garantir que os eventos desejados sejam despejados no arquivo de rastreamento.

3. Abra o SQL Server Profiler e examine o rastreamento. Lembre-se de que o fechamento do Power BI Desktop exclui o arquivo de rastreamento. Além disso, as ações adicionais no Power BI Desktop não são exibidas imediatamente. Você precisa fechar e reabrir o arquivo de rastreamento para ver novos eventos.

Mantenha as sessões individuais razoavelmente pequenas, talvez 10 segundos de ações, não centenas. Essa abordagem facilita a interpretação do arquivo de rastreamento. Também há um limite no tamanho do arquivo de rastreamento. Em sessões longas, há a possibilidade de remoção dos eventos iniciais.

Entender o formato das consultas

O formato geral de consultas do Power BI Desktop usa subseleções para cada tabela referenciada. A consulta do Editor do Power Query define as consultas de subseleção. Por exemplo, suponha que você tem as seguintes tabelas TPC-DS no SQL Server:

Executando a seguinte consulta:

SalesAmount (SUMX(Web_Sales, [ws_sales_price]*[ws_quantity]))
by Item[i_category]
for Date_dim[d_year] = 2000

Isso resulta no seguinte visual no Power BI:

Atualizar esse visual produz a consulta SQL na imagem a seguir. Há três consultas de subseleção para Web_Sales, Item e Date_dim, que retornam todas as colunas na respectiva tabela, embora o visual referencie apenas quatro colunas.

O Editor do Power Query define as consultas de subseleção exatas. Não foi detectado impacto desse uso de consultas de subseleção no desempenho de fontes de dados compatíveis com o DirectQuery. Fontes de dados como o SQL Server simplesmente otimizam as referências para as outras colunas.

O Power BI usa esse padrão porque o analista fornece a consulta SQL diretamente. O Power BI usa a consulta conforme fornecida, sem nenhuma tentativa de reescrevê-la.

Conteúdo relacionado

Para obter mais informações sobre o DirectQuery no Power BI, confira:

• Usar o DirectQuery no Power BI Desktop

Este artigo descreve aspectos do DirectQuery que são comuns entre todas as fontes de dados. Confira os seguintes artigos para obter detalhes sobre fontes de dados específicas:

• DirectQuery e SAP HANA
• DirectQuery e SAP BW
• Usar modelos semânticos do DirectQuery para Power BI e o Analysis Services