DirectQuery no Power BI

No Power BI Desktop ou no serviço do Power BI, você pode se conectar a muitas 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 os recursos do DirectQuery.

Este artigo descreve:

• As diferentes opções de conectividade de dados do Power BI.
• Orientação sobre quando usar o DirectQuery em vez de importar.
• Limitações e implicações do uso do DirectQuery.
• Recomendações para o uso bem-sucedido do DirectQuery.
• 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.

Nota

O DirectQuery também é um recurso do SQL Server Analysis Services. Esse recurso compartilha muitos detalhes com o Direct Query no Power BI, mas também há diferenças importantes. Este artigo aborda principalmente o DirectQuery com Power BI, não o SQL Server Analysis Services.

Para obter mais informações sobre como usar o DirectQuery com o SQL Server Analysis Services, consulte Usar o DirectQuery para modelos semânticos do Power BI e Analysis Services (visualização). Você também pode baixar o PDF DirectQuery no SQL Server 2016 Analysis Services.

Modos de conectividade de dados do Power BI

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

• 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 o 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 DirectQuery. Para obter um resumo das fontes que suportam DirectQuery, consulte Fontes de dados suportadas pelo DirectQuery. As fontes habilitadas para DirectQuery são principalmente fontes que podem oferecer um bom desempenho de consulta interativa.

Você deve importar dados para o Power BI sempre que possível. A importação tira proveito do mecanismo de consulta de alto desempenho do Power BI e fornece uma experiência altamente interativa e com todos os recursos.

Se você não conseguir atingir 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 DirectQuery é viável somente quando a fonte de dados subjacente pode fornecer resultados de consulta interativos em menos de cinco segundos para uma consulta agregada típica e pode lidar com a carga de consulta gerada. Considere cuidadosamente as limitações e implicações do uso do DirectQuery.

Os recursos de importação do Power BI e DirectQuery evoluem com o tempo. As alterações que fornecem mais flexibilidade ao usar dados importados permitem importar com mais frequência e eliminar algumas das desvantagens do uso do DirectQuery. Independentemente das melhorias, o desempenho da fonte de dados subjacente é uma consideração importante ao usar o DirectQuery. Se uma fonte de dados subjacente estiver lenta, o uso do DirectQuery para essa fonte permanecerá inviável.

As seções a seguir abrangem as três opções de conexão com dados: importação, DirectQuery e conexão ao vivo. O restante do artigo se concentra no DirectQuery.

Importar conexões

Quando você se conecta a uma fonte de dados como o SQL Server e importa dados no Power BI Desktop, os seguintes resultados ocorrem:

• Quando você obtém dados inicialmente, cada conjunto de tabelas selecionado define uma consulta que retorna um conjunto de dados. Você pode editar essas consultas antes de carregar os dados, por exemplo, para aplicar filtros, agregar os dados ou unir tabelas diferentes.

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

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

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

• A publicação do 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, por exemplo, reimportar os dados todos os dias. Dependendo do local da fonte de dados original, pode ser necessário configurar um gateway de dados local para a atualização.

• Abrir um relatório existente ou criar um novo relatório no serviço do Power BI consulta os dados importados novamente, garantindo a interatividade.

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

Conexões DirectQuery

Quando você usa o DirectQuery para se conectar a uma fonte de dados no Power BI Desktop, os seguintes resultados ocorrem:

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

• Após o carregamento, nenhum dado é importado para o repositório do Power BI. Em vez disso, quando você cria um visual, o Power BI Desktop envia consultas para a fonte de dados subjacente para recuperar os dados necessários. O tempo que demora para atualizar o elemento visual depende do desempenho da origem de dados subjacente.

• Quaisquer alterações nos dados subjacentes não são refletidas imediatamente nos visuais existentes. Ainda é necessário atualizar. O Power BI Desktop reenvia as consultas necessárias para cada visual e atualiza o visual conforme necessário.

• A publicação do 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 novo relatório no serviço do Power BI consulta a fonte de dados subjacente para recuperar os dados necessários. Dependendo do local da fonte de dados original, pode ser necessário configurar um gateway de dados local para obter os dados.

• Você pode fixar elementos visuais ou páginas de relatório inteiras como blocos de painel. Para garantir que a abertura de um painel seja rápida, os blocos são atualizados automaticamente de acordo com uma programação, 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.

• Quando você abre um painel, os blocos refletem os dados no momento da última atualização, não necessariamente as alterações mais recentes feitas na fonte subjacente. Você pode atualizar um painel aberto para garantir que ele esteja atualizado.

Ligações em tempo real

Quando você se conecta ao SQL Server Analysis Services, pode optar por importar os dados ou usar uma conexão em tempo real com o modelo de dados selecionado. O uso de uma conexão ao vivo é semelhante ao DirectQuery. Nenhum dado é importado e a fonte de dados subjacente é consultada para atualizar elementos visuais.

Por exemplo, ao usar a importação para se conectar ao SQL Server Analysis Services, você define uma consulta em relação à fonte externa do SQL Server Analysis Services e importa os dados. Se você se conectar ao vivo, 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á opção para importar os dados:

• Modelos semânticos do Power BI, por exemplo, conectando-se a um modelo semântico do Power BI que já foi publicado no serviço, para criar um novo relatório sobre ele.

• Microsoft Dataverse.

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

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

• Os blocos do painel são atualizados automaticamente em uma programação, como a cada hora.

Uma conexão ao vivo também difere do DirectQuery de várias maneiras. Por exemplo, as conexões em tempo real sempre passam a identidade do usuário que abre o relatório para a fonte subjacente do SQL Server Analysis Services.

Casos de uso do DirectQuery

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

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

• Os dados mudam com frequência e você precisa de relatórios quase em tempo real.
• Você precisa lidar com dados grandes sem ter que pré-agregar.
• A fonte subjacente define e aplica regras de segurança.
• Aplicam-se restrições de soberania de dados.
• A fonte é uma fonte multidimensional que contém medidas, como o SAP BW.

Os dados mudam com frequência e você precisa de relatórios quase em tempo real

Pode atualizar modelos com dados importados no máximo uma vez por hora, mais frequentemente com subscrições do Power BI Pro ou Power BI Premium. Se os dados estiverem mudando continuamente e for necessário que os relatórios mostrem os dados mais recentes, usar a importação com atualização agendada pode não atender às suas necessidades. Você pode transmitir dados diretamente para o Power BI, embora haja limites para os volumes de dados suportados para esse caso.

Usar o DirectQuery significa que abrir ou atualizar um relatório ou painel sempre mostra os dados mais recentes na fonte. Os blocos do painel também podem ser atualizados com mais frequência, a cada 15 minutos.

Os dados são muito grandes

Se os dados forem muito grandes, não é viável importá-los todos. O DirectQuery não requer uma grande transferência de dados, porque consulta os dados no local. No entanto, dados grandes também podem tornar o desempenho de consultas em relação a essa fonte subjacente muito lento.

Nem sempre é necessário importar 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 que você precisa para cada visual. Embora o DirectQuery seja a abordagem mais simples para dados grandes, a importação de dados agregados pode oferecer uma solução se a fonte de dados subjacente for muito lenta para o DirectQuery.

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

A fonte subjacente define regras de segurança

Quando você importa dados, o Power BI se conecta à fonte de dados usando as credenciais do Power BI Desktop do usuário atual ou as credenciais configuradas para atualização agendada do serviço do Power BI. Ao publicar e compartilhar relatórios que importaram dados, você deve ter cuidado para compartilhar apenas com usuários autorizados a ver os dados ou deve definir 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 logon único (SSO) para fontes de dados SQL do Azure e por meio de um gateway de dados para servidores SQL locais. Para obter mais informações, consulte Visão geral do logon único (SSO) para gateways no Power BI.

Aplicam-se restrições de soberania de dados

Algumas organizações têm políticas em torno da soberania de dados, o que significa que os dados não podem sair das instalações da organização. Estes dados apresentam problemas para soluções baseadas na importação de dados. Com o DirectQuery, os dados permanecem no local de origem subjacente. No entanto, mesmo com o DirectQuery, o serviço do Power BI mantém alguns caches de dados no nível visual, devido à atualização agendada de blocos.

A fonte de dados subjacente utiliza 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 um nível agregado de nível mais alto, como TotalSales por ano, agrega ainda mais o valor agregado. Essa agregação é boa 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 agregados corretos necessários para um visual diretamente da fonte requer o envio de consultas por visual, como no DirectQuery. Quando você se conecta ao SAP BW, escolher DirectQuery permite esse tratamento de medidas. Para obter mais informações, consulte DirectQuery e SAP BW.

Atualmente, o DirectQuery sobre SAP HANA trata os dados da mesma forma que uma fonte relacional e produz um comportamento semelhante à importação. Para obter mais informações, consulte 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 as implicações gerais do uso do DirectQuery e as limitações relacionadas ao desempenho, segurança, transformações, modelagem e relatórios.

Implicações gerais

Seguem-se algumas implicações e limitações gerais da utilização do DirectQuery:

• Se os dados forem alterados, você deverá atualizar para mostrar os dados mais recentes. Dado o uso de caches, não há garantia de que os elementos visuais sempre mostrem os dados mais recentes. Por exemplo, um visual pode mostrar transações no dia anterior. Uma alteração na segmentação de dados pode atualizar o visual para mostrar as transações dos últimos dois dias, incluindo as transações recentes recém-chegadas. Mas retornar a segmentação de dados ao seu valor original pode resultar em que ela mostre novamente o valor anterior armazenado em cache. Selecione Atualizar para limpar todos os caches e atualizar todos os elementos visuais na página para mostrar os dados mais recentes.

• Se os dados forem alterados, não há garantia de consistência entre os elementos visuais. Visuais diferentes, seja na mesma página ou em páginas diferentes, podem ser atualizados em momentos diferentes. Se os dados na fonte subjacente estiverem mudando, não há garantia de que cada visual mostre os dados no mesmo ponto no tempo.

  Dado que mais de uma consulta pode ser necessária para um único visual, por exemplo, para obter os detalhes e os totais, nem mesmo a consistência dentro de um único visual é garantida. Para garantir essa consistência, seria necessária a sobrecarga de atualizar todos os elementos visuais sempre que qualquer visual fosse atualizado, juntamente com o uso de recursos caros, como isolamento de instantâneo na fonte de dados subjacente.

  Você pode atenuar esse problema em grande medida selecionando Atualizar para atualizar todos os elementos visuais na página. Mesmo para o modo de importação, há um problema semelhante de manter a consistência quando você importa dados de mais de uma tabela.

• Você deve 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 elementos 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 as tabelas ou colunas forem removidas da origem subjacente, isso poderá resultar em falha de consulta após a atualização. Para atualizar os campos no modelo para refletir as alterações, você deve abrir o relatório no Power BI Desktop e escolher Atualizar.

• Um limite de 1 milhão de linhas pode retornar em qualquer consulta. Há um limite fixo de 1 milhão de linhas que podem retornar em qualquer consulta única para a fonte subjacente. Esse limite geralmente não tem implicações práticas, e os visuais não exibirão tantos pontos. No entanto, o limite pode ocorrer nos casos em que o Power BI não otimiza totalmente as consultas enviadas e solicita algum resultado intermediário que exceda o limite.

  O limite também pode ocorrer durante a construção de um visual, no caminho para um estado final mais razoável. Por exemplo, incluir Customer e TotalSalesQuantity pode atingir esse limite se houver mais de 1 milhão de clientes, até que você aplique algum filtro. O erro que retorna é: O conjunto de resultados de uma consulta à fonte de dados externa excedeu o tamanho máximo permitido de '1000000' linhas.

  Nota

  As capacidades Premium permitem-lhe exceder o limite de um milhão de linhas. Para obter mais informações, consulte Max intermediate row set count.

• Não é possível alterar um modelo de importação para o modo DirectQuery. Você pode 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 que o modo DirectQuery não suporta. Para fontes multidimensionais como o SAP BW, também não é possível alternar do DirectQuery para o modo de importação, devido ao tratamento diferente de medidas externas.

Implicações de desempenho e carga

Quando você usa 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, demorar menos de cinco segundos, a experiência é razoável, embora possa parecer lenta em comparação com a resposta imediata com dados importados. Se a lentidão da fonte faz com que os visuais individuais levem mais de dezenas de segundos para serem atualizados, a experiência se torna irrazoavelmente ruim. As consultas podem até expirar.

Juntamente com o desempenho da fonte subjacente, a carga colocada na fonte também afeta o desempenho. Cada usuário que abre um relatório compartilhado e cada bloco de painel que é atualizado envia pelo menos uma consulta por visual para a fonte subjacente. A fonte deve ser capaz de lidar com essa carga de consulta, mantendo um desempenho razoável.

Implicações para a segurança

A menos que a fonte de dados subjacente use SSO, um relatório DirectQuery sempre usa as mesmas credenciais fixas para se conectar à fonte depois de publicado no serviço do Power BI. Imediatamente após publicar um relatório DirectQuery, você deve configurar as credenciais do usuário a serem usadas. Até configurar as credenciais, tentar abrir o relatório no serviço do Power BI resulta num erro.

Depois de fornecer as credenciais do usuário, o Power BI usa essas credenciais para quem abre o relatório, o mesmo que para os dados importados. Todos os usuários veem os mesmos dados, a menos que a segurança em nível de linha seja definida como parte do relatório. Você deve prestar a mesma atenção ao compartilhamento do relatório que para dados importados, mesmo que haja regras de segurança definidas na fonte subjacente.

• A conexão com modelos semânticos do Power BI e Analysis Services no modo DirectQuery sempre usa SSO, portanto, a segurança é semelhante às conexões em tempo real com o Analysis Services.

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

• Você pode usar várias fontes de dados em um modelo DirectQuery usando modelos compostos. Quando você usa várias fontes de dados, é importante entender as implicações de segurança de como os dados se movem entre as fontes de dados subjacentes.

Limitações da transformação de dados

O DirectQuery limita as transformações de dados que pode aplicar no Power Query Editor. Com dados importados, você pode facilmente aplicar um conjunto sofisticado de transformações para limpar e remodelar os dados antes de usá-los para criar elementos 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 processamento analítico on-line (OLAP) como o SAP BW, não é possível definir nenhuma transformação e todo o modelo externo é retirado da origem. Para fontes relacionais como o SQL Server, você ainda pode definir um conjunto de transformações por consulta, mas essas transformações são limitadas por motivos de desempenho.

Quaisquer transformações devem ser aplicadas em cada consulta à fonte subjacente, em vez de uma vez na atualização de dados. As transformações devem ser capazes de se traduzir razoavelmente em uma única 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 mudou para importação.

Além disso, a caixa de diálogo Obter Dados ou o Editor do Power Query usa subseleções dentro das consultas que geram e enviam para recuperar dados para um visual. As consultas definidas no Power Query Editor devem ser válidas neste contexto. Em particular, não é possível usar uma consulta com expressões de tabela comuns, nem uma que invoque procedimentos armazenados.

Limitações de modelagem

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

• Definição de relações entre tabelas.
• Adicionar novos cálculos, como colunas e medidas calculadas.
• Renomear e ocultar colunas e medidas.
• Definição de hierarquias.
• Definição de formatação de coluna, resumo padrão e ordem de classificação.
• Agrupamento ou agrupamento 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 melhorar o consumo posterior. No entanto, alguns recursos de modelagem não estão disponíveis ou são limitados com 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. Poderão aplicar-se mais limitações a fontes individuais.

• Sem hierarquia de data interna: com dados importados, cada coluna data/data/hora também tem uma hierarquia de data interna disponível por padrão. Por exemplo, se você importar uma tabela de ordens de venda 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 Date disponível na fonte subjacente, como é comum em muitos data warehouses, você poderá usar as funções de inteligência de tempo DAX (Data Analysis Expressions) como de costume.

• Suporte de data/hora apenas para o nível de segundos: para modelos semânticos que usam colunas de tempo, o Power BI emite consultas à 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ó podem ser intra-linha, ou seja, podem referir-se apenas a valores de outras colunas da mesma tabela, sem utilizar quaisquer funções agregadas. Além disso, as funções escalares DAX permitidas, como LEFT(), são limitadas às funções que podem ser enviadas para a fonte subjacente. As funções variam dependendo das capacidades exatas da fonte. As funções que não são suportadas não são listadas no preenchimento automático ao criar a consulta DAX para uma coluna calculada e resultam em um erro se usado.

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

• Sem clustering: quando você usa o DirectQuery, não pode usar o recurso de clustering para localizar grupos automaticamente.

Limitações de relatórios

Quase todos os recursos de relatório são suportados para modelos 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 de dados em uma coluna de texto para modelos semânticos DirectQuery é de 32.764 caracteres. A comunicação de textos mais longos resulta num erro.

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

• Filtros de medida: os elementos 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 apenas para categorias com mais de 20 milhões de vendas.

  

  Essa abordagem faz com que duas consultas sejam enviadas para a fonte subjacente:

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

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

• Filtros TopN: Você pode definir filtros avançados para filtrar apenas os valores superiores ou inferiores N classificados por alguma medida. Por exemplo, os filtros podem incluir as 10 principais categorias. Essa abordagem envia novamente duas consultas para a fonte subjacente. No entanto, a primeira consulta retorna todas as categorias da fonte subjacente e, em seguida, as TopN são determinadas com base nos resultados retornados. Dependendo da cardinalidade da coluna envolvida, essa abordagem pode levar a problemas de desempenho ou falhas de consulta devido ao limite de um milhão de linhas nos resultados da consulta.

• Mediana: Qualquer agregação, como Sum ou Count Distinct, é enviada por push para a fonte subjacente. No entanto, geralmente o median agregado não é suportado pela fonte subjacente. Para median, os dados detalhados são recuperados da fonte subjacente e a mediana é calculada a partir dos resultados retornados. Esta abordagem é razoável para calcular a mediana num número relativamente pequeno de resultados.

  Problemas de desempenho ou falhas de consulta podem surgir se a cardinalidade for grande devido ao limite de um milhão de linhas. Por exemplo, consultar a População Mediana de País/Região pode ser razoável, mas o Preço Mediano de Venda pode não ser razoável.

• Filtros de texto avançados como "contém": a filtragem avançada numa coluna de texto permite filtros como contains e begins with. Esses filtros podem resultar em desempenho degradado 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 pode ser drasticamente diferente devido aos índices.

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

• Totais em visuais de tabela: Por padrão, tabelas e matrizes exibem totais e subtotais. Em muitos casos, obter os valores para esses totais requer o envio de consultas separadas para a fonte subjacente. Esse requisito se aplica sempre que você usa DistinctCount agregação ou em todos os casos que usam DirectQuery sobre SAP BW ou SAP HANA. Você pode desativar esses totais usando o painel Formatar .

Recomendações do DirectQuery

Esta seção fornece orientação de alto nível sobre como usar o DirectQuery com êxito, dadas suas implicações.

Desempenho da fonte de dados subjacente

Valide que os visuais simples sejam atualizados em cinco segundos, para fornecer uma experiência interativa razoável. Se os elementos 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 forem lentas, examine as consultas enviadas para a fonte subjacente e o motivo do desempenho lento. Para obter mais informações, consulte Diagnóstico de desempenho.

Este artigo não cobre a ampla gama de recomendações de otimização de banco de dados em todo o conjunto de potenciais fontes subjacentes. As seguintes práticas de banco de dados padrão se aplicam à maioria das situações:

• Para um melhor desempenho, baseie as relações em colunas inteiras 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 armazenamento de colunas em fontes que oferecem suporte a eles, por exemplo, o SQL Server.

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

Design do modelo

Ao definir o modelo, siga estas orientações:

• Evite consultas complexas no Power Query Editor. O Power Query Editor traduz uma consulta complexa numa única consulta SQL. A consulta única aparece na subseleção de cada consulta enviada para essa tabela. Se essa consulta for complexa, poderá resultar em problemas de desempenho em todas as consultas enviadas. Pode obter a consulta SQL real para um conjunto de passos clicando com o botão direito do rato no último passo em Passos aplicados no Power Query Editor e escolhendo Ver Consulta Nativa.

• Mantenha as medidas simples. Pelo menos inicialmente, limitar as medidas a agregados simples. Se as medidas funcionarem de forma satisfatória, você pode definir medidas mais complexas, mas preste atenção ao desempenho.

• Evite relações em colunas calculadas. Em bancos de dados onde 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 chave estrangeira. A solução alternativa comum é concatenar as colunas usando uma coluna calculada e basear a junção nessa coluna.

  Essa solução alternativa é razoável para dados importados, mas para o DirectQuery resulta em uma junção em uma expressão. Esse resultado geralmente impede o uso de quaisquer índices e leva a um desempenho ruim. A única solução é realmente materializar as várias colunas em uma única coluna na fonte de dados subjacente.

• Evite relações em colunas 'uniqueidentifier'. O Power BI não suporta nativamente um tipo de uniqueidentifier dados. A definição de uma relação entre uniqueidentifier colunas resulta em uma consulta com uma junção que envolve uma transmissão. Mais uma vez, essa abordagem geralmente leva a um desempenho ruim. A única solução é materializar colunas de um tipo alternativo na fonte de dados subjacente.

• Oculte a coluna 'para' nas relações. A to coluna sobre relacionamentos é geralmente a chave primária na to tabela. Essa coluna deve estar oculta, mas se estiver oculta, não aparece na lista de campos e não pode ser usada em elementos visuais. Muitas vezes, as colunas nas quais as relações se baseiam são, na verdade , colunas do sistema, por exemplo, chaves substitutas em um data warehouse. Ainda é melhor esconder essas colunas.

  Se a coluna tiver 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 colunas calculadas e alterações de tipo de dados. Você pode usar tabelas calculadas ao usar o DirectQuery com modelos compostos. Esses recursos não são necessariamente prejudiciais, mas resultam em consultas que contêm expressões em vez de simples referências a colunas. Essas consultas podem resultar em índices não sendo usados.

• Evite a filtragem cruzada bidirecional nas relações. O uso da filtragem cruzada bidirecional pode levar a instruções de consulta que não têm um bom desempenho. Para obter mais informações sobre filtragem cruzada bidirecional, consulte Habilitar filtragem cruzada bidirecional para DirectQuery no Power BI Desktop ou baixe o white paper Filtragem cruzada bidirecional. Os exemplos no documento são para o SQL Server Analysis Services, mas os pontos fundamentais também se aplicam ao Power BI.

• Experimente definir Assumir integridade referencial. A configuração Assumir integridade referencial em relacionamentos permite que as consultas usem INNER JOIN em vez de OUTER JOIN instruções. Essas diretrizes geralmente melhoram o desempenho da consulta, embora dependam das especificidades da fonte de dados.

• Não utilize a filtragem de dados relativos no Power Query Editor. É possível definir a filtragem de data relativa no Power Query Editor. Por exemplo, você pode filtrar para as linhas onde a data está nos últimos 14 dias.

  

  No entanto, esse filtro se traduz 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.

  

  Esses dados provavelmente não são o que você quer. Para garantir que o filtro seja aplicado com base na data no 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 DAX DATE() função 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 o uso de opções de redução de consulta: o Power BI fornece opções de relatório para enviar menos consultas e desabilitar determinadas interações que causam uma experiência ruim se as consultas resultantes levarem muito tempo para serem 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 aceder a estas opções no Power BI Desktop, aceda a Opções e definições>de Ficheiros>e selecione Redução de consultas.

  

  As seleções na tela Redução de consultas permitem que você mostre um botão Aplicar para segmentações de dados ou seleções de filtros. Nenhuma consulta será enviada até que você selecione o botão Aplicar no filtro ou na segmentação de dados. 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 de dados e filtros antes de aplicá-las.

• Aplicar filtros primeiro: sempre aplique todos os filtros aplicáveis no início da criação de um visual. Por exemplo, em vez de arrastar TotalSalesAmount e ProductName e, em seguida, filtrar para um determinado ano, aplique o filtro em Ano 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 deixa carga desnecessária na fonte subjacente. A aplicação antecipada de filtros geralmente torna essas consultas intermediárias menos dispendiosas. A não aplicação antecipada dos filtros pode resultar no cumprimento do limite de um milhão de linhas.

• Limitar o número de elementos visuais em uma página: quando você abre uma página ou altera uma segmentação de dados ou filtro de nível de página, todos os elementos visuais na página são atualizados. Há um limite para o número de consultas paralelas. À medida que o número de elementos visuais aumenta, alguns elementos visuais são atualizados em série, o que aumenta o tempo necessário para atualizar a página. Portanto, é melhor limitar o número de elementos visuais em uma única página e, em vez disso, ter mais páginas mais simples.

• Considere desativar a interação entre elementos visuais: por padrão, as visualizações em uma página de relatório podem ser usadas para filtrar e destacar as outras visualizações na página. Por exemplo, se você selecionar 1999 no gráfico de pizza, o gráfico de colunas será realçado de forma cruzada 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ê deve desativar essa interação se o tempo necessário para responder às seleções dos usuários for excessivamente 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, consulte Como os elementos visuais filtram uns aos outros 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 que o DirectQuery abre 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, vá para Opções de Arquivo e Opções de Configurações e>selecione DirectQuery na seção Arquivo> Atual do painel esquerdo.

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

Aumentar o Máximo de conexões por fonte de dados permite enviar mais consultas, até o número máximo especificado, para a fonte de dados subjacente. Essa abordagem é útil quando muitos elementos visuais estão em uma única página ou muitos usuários acessam um relatório ao mesmo tempo. Quando o número máximo de conexões é atingido, outras consultas são enfileiradas até que uma conexão fique disponível. Um limite mais alto resulta em mais carga na fonte subjacente, portanto, não é garantido que a configuração melhore o 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 de limites fixos definidos no ambiente de destino onde o relatório é publicado. O Power BI, o Power BI Premium e o Servidor de Relatório do Power BI impõem limites diferentes. A tabela abaixo 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 na nuvem e fontes de dados locais, como SQL Server, Oracle e Teradata.

Environment	Limite superior por fonte de dados
Power BI Pro	10 conexões ativas
Power BI Premium	Depende da limitação de SKU do modelo semântico
Power BI Report Server	10 conexões ativas

Nota

A configuração número máximo de conexões DirectQuery se aplica a todas as fontes DirectQuery quando você habilita metadados avançados, que é a configuração padrão para todos os modelos criados no Power BI Desktop.

DirectQuery no serviço Power BI

Todas as fontes de dados DirectQuery são suportadas pelo Power BI Desktop e algumas fontes também estão disponíveis diretamente no serviço do Power BI. Um usuário corporativo pode usar o Power BI para se conectar a seus dados no Salesforce, por exemplo, e obter imediatamente um painel, sem usar o Power BI Desktop.

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

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

Mesmo para essas duas fontes, ainda é melhor iniciar o uso do DirectQuery no 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, no serviço não é possível criar cálculos, usar muitos recursos analíticos ou atualizar os metadados para refletir as alterações no esquema subjacente.

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

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

Relatar comportamento no serviço do Power BI

Quando você abre um relatório no serviço do Power BI, todos os elementos visuais na página atualmente visível são atualizados. Cada visual requer pelo menos uma consulta à fonte de dados subjacente. Alguns elementos visuais podem exigir mais de uma consulta. Por exemplo, um visual pode mostrar valores agregados de duas tabelas de fatos diferentes, ou conter uma medida mais complexa, ou conter totais de uma medida não aditiva como Count Distinct. Mover para uma nova página atualiza esses elementos 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 dos elementos visuais. Por exemplo, selecionar um valor diferente em uma segmentação de dados requer o envio de um novo conjunto de consultas para atualizar todos os visuais afetados. O mesmo vale para selecionar um visual para destacar outros elementos visuais ou alterar um filtro. Da mesma forma, criar ou editar um relatório requer o envio de consultas para cada etapa do caminho para produzir o visual final.

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

Usar o DirectQuery impõe algumas limitações importantes em alguns dos recursos que o serviço do Power BI oferece para relatórios publicados:

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

• Usar Explorar no Excel resulta em desempenho insatisfatório: você pode explorar um modelo semântico usando o recurso Explorar no Excel, que permite criar tabelas dinâmicas e gráficos dinâmicos no Excel . Esse recurso é suportado para modelos semânticos que usam DirectQuery, mas o desempenho é mais lento do que a criação de elementos visuais no Power BI. Se o uso do Excel for importante para seus cenários, leve em conta esse problema ao decidir se deseja usar 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 Power BI, você pode fixar visuais individuais ou páginas inteiras em painéis como blocos. Os blocos baseados em modelos semânticos do DirectQuery são atualizados automaticamente enviando consultas às fontes de dados subjacentes em uma agenda. Por padrão, os modelos semânticos são atualizados a cada hora, mas você pode configurar a 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 for definida no modelo, cada bloco será atualizado uma vez e os resultados serão compartilhados entre todos os usuários. Se você usar segurança em nível de linha, cada bloco exigirá que consultas separadas por usuário sejam enviadas para a fonte subjacente.

Pode haver um grande efeito multiplicador. Um painel com 10 blocos, compartilhado com 100 usuários, criado em um modelo semântico usando DirectQuery com segurança em nível de linha, resulta em pelo menos 1000 consultas sendo enviadas para a fonte de dados subjacente para cada atualização. Considere cuidadosamente o uso da segurança em nível de linha e a configuração da agenda de atualização.

Tempos limite de consulta

Um tempo limite de quatro minutos aplica-se a consultas individuais no serviço do Power BI. As consultas que demoram mais de quatro minutos falham. Este limite destina-se a evitar problemas causados por tempos de execução excessivamente longos. Você deve usar o DirectQuery somente para fontes que possam fornecer desempenho de consulta interativa.

Diagnóstico de desempenho

Esta seção descreve como diagnosticar problemas de desempenho ou como obter informações mais detalhadas para otimizar seus relatórios.

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

Essa abordagem inicialmente elimina determinados componentes, como o gateway do Power BI. Se os problemas de desempenho não ocorrerem no Power BI Desktop, você poderá investigar 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 todos os problemas em um visual, em vez de muitos elementos visuais em uma página. Se um único visual em uma página do Power BI Desktop estiver lento, use o Analisador de desempenho para analisar as consultas que o Power BI Desktop envia para a fonte 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 vestígios da origem, o arquivo de rastreamento pode conter detalhes úteis de como uma consulta é executada e como você pode melhorá-la. Você pode usar o processo a seguir para exibir as consultas que o Power BI envia e seus tempos de execução.

Usar o 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 para o 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 para o log:

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

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

Para abrir o arquivo de rastreamento da sessão atual:

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

2. Em Crash Dump Collection, selecione Open crash dump/traces folder.

   

   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 espaço de trabalho para cada instância aberta do Power BI Desktop. Essas pastas são nomeadas com um sufixo inteiro, como AnalysisServicesWorkspace2058279583. A pasta de espaço de trabalho é excluída quando a sessão associada do Power BI Desktop termina.

   Dentro da pasta de espaço de trabalho para a sessão atual do Power BI, a pasta \Data contém o arquivo de rastreamento FlightRecorderCurrent.trc . Anote a localização.

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

5. Navegue até ou insira o caminho para o arquivo de rastreamento para a 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 destaca um grupo de eventos para uma consulta. Cada grupo de consulta tem os seguintes eventos:

• A Query Begin e evento, que representam o início e Query End o fim de uma consulta DAX gerada pela alteração de um visual ou filtro na interface do usuário do Power BI ou pela filtragem ou transformação de dados no Editor do Power Query.

• Um ou mais pares de e DirectQuery End eventos, que representam consultas enviadas para a fonte de DirectQuery Begin dados subjacente como parte da avaliação da consulta DAX.

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

As seguintes colunas também são de interesse:

• TextData: O detalhe textual do evento. Para Query Begin e Query End eventos, o detalhe é a consulta DAX. Para DirectQuery Begin e DirectQuery End eventos, o detalhe é a consulta SQL enviada para a fonte 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: a duração, em milissegundos, necessária para executar a consulta DAX ou SQL.
• Erro: Se ocorreu um erro, caso em que o evento também é exibido em vermelho.

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

1. Abra uma única sessão do Power BI Desktop para evitar a confusão de várias pastas de espaço de trabalho.

2. Faça o conjunto de ações de interesse no Power BI Desktop. Inclua mais algumas ações, para garantir que os eventos de interesse sejam liberados no arquivo de rastreamento.

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

Mantenha sessões individuais razoavelmente pequenas, talvez 10 segundos de ações, não centenas. Essa abordagem facilita a interpretação do arquivo de rastreamento. Há também um limite no tamanho do arquivo de rastreamento. Para sessões longas, há uma chance de eventos iniciais serem descartados.

Compreender o formato das consultas

O formato geral das consultas do Power BI Desktop usa subseleções para cada tabela a que fazem referência. A consulta do Editor do Power Query define as consultas de subseleção. Por exemplo, suponha que você tenha 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

Resultados no seguinte visual no Power BI:

A atualização desse visual produz a consulta SQL na imagem a seguir. Há três consultas de subseleção para Web_Sales, , e Date_dim, que cada uma retorna todas as colunas na respetiva tabela, Itemembora o visual faça referência a apenas quatro colunas.

O Editor do Power Query define as consultas de subseleção exatas. Não foi demonstrado que esse uso de consultas de subseleção afete o desempenho das fontes de dados suportadas pelo DirectQuery. Fontes de dados como o SQL Server otimizam as referências às outras colunas.

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

Conteúdos relacionados

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

• Usar DataQuery no Power BI Desktop

Este artigo descreveu aspetos do DirectQuery que são comuns em todas as fontes de dados. Consulte os seguintes artigos para obter detalhes sobre fontes específicas:

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