Agregações definidas pelo usuário
As agregações no Power BI podem aprimorar o desempenho da consulta em modelos semânticos grandes do DirectQuery. Usando agregações, você armazena os dados em cache no nível agregado na memória. As agregações no Power BI podem ser configuradas manualmente no modelo de dados, conforme descrito neste artigo. Nas assinaturas Premium, habilite automaticamente o recurso Agregações automáticas nas Configurações do modelo.
Criar tabelas de agregação
Dependendo do tipo de fonte de dados, uma tabela de agregações pode ser criada na fonte de dados como uma tabela ou uma exibição, uma consulta nativa. Para ter o melhor desempenho, crie uma tabela de agregações como uma tabela de importação criada no Power Query. Depois, você usa a caixa de diálogo Gerenciar agregações no Power BI Desktop para definir as agregações das colunas de agregação com as propriedades: resumo, tabela de detalhes e coluna de detalhes.
Fontes de dados dimensionais, como data warehouses e data marts, podem usar agregações baseadas em relação. As fontes de Big Data baseadas em Hadoop geralmente baseiam as agregações em colunas GroupBy. Este artigo descreve as diferenças típicas de modelagem do Power BI para cada tipo de fonte de dados.
Gerenciar agregações
No painel Dados de qualquer exibição do Power BI Desktop, clique com o botão direito do mouse na tabela de agregações e selecione Gerenciar agregações.
A caixa de diálogo Gerenciar agregações mostra uma linha para cada coluna na tabela, na qual você pode especificar o comportamento de agregação. No exemplo a seguir, as consultas à tabela de detalhes Sales são redirecionadas internamente para a tabela de agregação Sales Agg.
Nesse exemplo de agregação baseada em relação, as entradas GroupBy são opcionais. Com exceção de DISTINCTCOUNT, elas não afetam o comportamento da agregação e servem principalmente para facilitar a leitura. Sem as entradas GroupBy, as agregações ainda são atingidas com base nas relações. Isso é diferente do exemplo de Big Data mais adiante neste artigo, no qual as entradas GroupBy são necessárias.
Validações
A caixa de diálogo Gerenciar agregações impõe validações:
- A Coluna de Detalhes precisa ter o mesmo tipo de dados da Coluna de Agregação, exceto pelas funções Resumo Contar e Contar linhas da tabela. Contar e Contar linhas da tabela só estão disponíveis para colunas de agregação de inteiros e não exigem um tipo de dados correspondente.
- Não são permitidas agregações encadeadas que abranjam três ou mais tabelas. Por exemplo, as agregações na Tabela A não podem se referir a uma Tabela B que tenha agregações referentes a uma Tabela C.
- Não são permitidas agregações duplicadas, em que duas entradas usam a mesma função Resumo e referem-se à mesma Tabela de Detalhes e Coluna de Detalhes.
- A Tabela de Detalhes precisa usar o modo de armazenamento DirectQuery, não Importação.
- Não há suporte ao agrupamento por uma coluna de chave estrangeira usada por uma relação inativa e à dependência da função USERELATIONSHIP para ocorrências de agregação.
- As agregações baseadas em colunas GroupBy podem usar as relações entre tabelas de agregação, mas não há suporte para criar relações entre as tabelas de agregação no Power BI Desktop. Se necessário, você pode criar relações entre tabelas de agregação usando uma ferramenta de terceiros ou uma solução de script por meio de pontos de extremidade XMLA (XML for Analysis).
A maioria das validações são impostas desabilitando os valores da lista suspensa e mostrando o texto explicativo na dica de ferramenta.
As tabelas de agregação são ocultadas
Usuários com acesso somente leitura ao modelo semântico não podem consultar tabelas de agregação. O acesso somente leitura evita preocupações de segurança quando usado com a RLS (Segurança em Nível de Linha). Os consumidores e as consultas referem-se à tabela de detalhes, não à tabela de agregação; eles não precisam saber nada sobre a tabela de agregação.
Por esse motivo, as tabelas de agregação ficam ocultas na exibição de Relatório. Se a tabela ainda não estiver oculta, a caixa de diálogo Gerenciar agregações a definirá como oculta quando você selecionar Aplicar tudo.
Modos de armazenamento
O recurso de agregação interage com os modos de armazenamento no nível da tabela. As tabelas do Power BI podem usar os modos de armazenamento DirectQuery, Importação ou Duplo. O DirectQuery consulta o back-end diretamente, enquanto a Importação armazena os dados em cache na memória e envia consultas para os dados armazenados em cache. Todas as fontes de dados de Importação e do DirectQuery não multidimensionais do Power BI podem funcionar com agregações.
Para definir o modo de armazenamento de uma tabela agregada como Importação para acelerar as consultas, selecione a tabela agregada na exibição de Modelo do Power BI Desktop. No painel Propriedades, expanda Avançado, clique na lista suspensa em Modo de armazenamentoe selecione Importação. A alteração da importação é irreversível.
Para saber mais sobre os modos de armazenamento de tabelas, confira Gerenciar o modo de armazenamento no Power BI Desktop.
RLS para agregações
Para que funcionem corretamente em agregações, as expressões RLS devem filtrar a tabela de agregação e a tabela de detalhes.
No exemplo a seguir, a expressão RLS na tabela Geography funciona para agregações, porque Geography está no lado da filtragem das relações com a tabela Sales e a tabela Sales Agg. As consultas que atingem a tabela de agregação e as consultas que não atingem têm a RLS aplicada com êxito.
Uma expressão RLS na tabela Product filtra apenas a tabela de detalhes Sales, não a tabela agregada Sales Agg. Como a tabela de agregação é outra representação dos mesmos dados na tabela de detalhes, não é seguro responder a consultas na tabela de agregação, porque o filtro RLS não pode ser aplicado. Não é recomendável filtrar somente a tabela de detalhes, pois as consultas de usuário dessa função não se beneficiam das ocorrências de agregação.
Não é permitido o uso de uma expressão RLS que filtre somente a tabela de agregação Sales Agg e não a tabela de detalhes Sales.
Para agregações baseadas em colunas GroupBy, uma expressão RLS aplicada à tabela de detalhes pode ser usada para filtrar a tabela de agregação, pois todas as colunas GroupBy na tabela de agregação são abrangidas pela tabela de detalhes. Por outro lado, um filtro RLS na tabela de agregação não pode ser aplicado à tabela de detalhes e, portanto, não é permitido.
Agregações baseadas em relações
Os modelos dimensionais normalmente usam agregações baseadas em relações. Os modelos do Power BI provenientes de data warehouses e data marts se parecem com esquemas floco de neve/estrela, com relações entre tabelas de dimensões e tabelas de fatos.
No exemplo a seguir, o modelo obtém dados de uma só fonte de dados. As tabelas estão usando o modo de armazenamento do DirectQuery. A tabela de fatos Vendas contém bilhões de linhas. A definição do modo de armazenamento de Sales como Importação para armazenamento em cache consumiria uma sobrecarga considerável de memória e recursos.
Em vez disso, crie a tabela de agregação Sales Agg. Na tabela Sales Agg, o número de linhas é igual à soma de SalesAmount agrupado por CustomerKey, DateKey e ProductSubcategoryKey. A tabela Sales Agg está em uma granularidade maior do que Sales e, portanto, em vez de bilhões, ela pode conter milhões de linhas, que são mais fáceis de serem gerenciadas.
Se as tabelas de dimensões a seguir forem as mais comumente usadas para as consultas com alto valor empresarial, elas poderão filtrar Sales Agg usando relações um-para-muitos ou muitos para um.
- Geografia
- Cliente
- Data
- Subcategoria de produto
- Categoria de produto
A imagem a seguir mostra este modelo.
A tabela a seguir mostra as agregações para a tabela Agregação de vendas.
Observação
A tabela Sales Agg, como qualquer tabela, tem a flexibilidade de ser carregada de diversas maneiras. A agregação pode ser executada no banco de dados de origem usando processos ETL/ELT ou pela expressão M para a tabela. A tabela agregada pode usar o modo de armazenamento Importação com ou sem a atualização incremental dos modelos semânticos ou usar o DirectQuery e ser otimizada para consultas rápidas usando índices columnstore. Essa flexibilidade permite arquiteturas equilibradas que podem distribuir a carga de consulta para evitar gargalos.
A alteração do modo de armazenamento da tabela agregada Sales Agg para Importação abre uma caixa de diálogo informando que as tabelas de dimensões relacionadas podem ser definidas com o modo de armazenamento Duplo.
A definição das tabelas de dimensões relacionadas como Duplo permite que elas funcionem como Importação ou DirectQuery, dependendo da subconsulta. No exemplo:
- As consultas que agregam métricas da tabela Sales Agg no modo Importação e fazem o agrupamento por atributos das tabelas relacionadas no modo Duplo podem ser retornadas por meio do cache na memória.
- As consultas que agregam métricas da tabela Sales do DirectQuery e fazem o agrupamento por atributos por das tabelas relacionadas no modo Duplo podem ser retornadas no modo DirectQuery. A lógica de consulta, incluindo a operação GroupBy, é transmitida para o banco de dados de origem.
Para obter mais informações sobre o modo de armazenamento Duplo, confira Gerenciar o modo de armazenamento no Power BI Desktop.
Relações regulares versus relações limitadas
As ocorrências de agregação com base em relações exigem relações regulares.
As relações regulares incluem as seguintes combinações de modo de armazenamento, em que as duas tabelas são provenientes da mesma fonte:
| Tabela do lado muitos | Tabela do lado um |
|---|---|
| Dupla | Dupla |
| Importar | Importar ou Dupla |
| DirectQuery | DirectQuery ou Dupla |
Uma relação de origem cruzada será considerada regular somente se as duas tabelas forem definidas para Importação. Relações muitos para muitos sempre serão consideradas limitadas.
Para ocorrências de agregação de origem cruzada que não dependem de relações, confira Agregações baseadas em colunas GroupBy.
Exemplos de consulta de agregação baseada em relação
A seguinte consulta atinge a agregação, porque as colunas na tabela Data estão na granularidade que pode atingir a agregação. A coluna SalesAmount usa a agregação Sum.
A consulta a seguir não atinge a agregação. Apesar de solicitar a soma de SalesAmount, a consulta executa uma operação GroupBy em uma coluna na tabela Product, que não está na granularidade que pode atingir a agregação. Se você observar as relações no modelo, uma subcategoria de produto poderá ter várias linhas de Product. A consulta não pode determinar com qual produto deve ser feita a agregação. Nesse caso, a consulta é revertida para DirectQuery e envia uma consulta SQL à fonte de dados.
As agregações não são apenas para cálculos simples que executam uma soma simples. Cálculos completos também podem se beneficiar. Conceitualmente, um cálculo complexo é dividido em subconsultas para cada SUM, MIN, MAX e COUNT. Cada subconsulta é avaliada para determinar se ela pode atingir a agregação. Esta lógica não se aplica a todos os casos devido à otimização do plano de consulta, mas em geral ela deve ser aplicada. O exemplo a seguir atinge a agregação:
A função COUNTROWS pode se beneficiar das agregações. A consulta a seguir atinge a agregação porque há uma agregação Contar linhas da tabela definida para a tabela Sales.
A função AVERAGE pode se beneficiar das agregações. A consulta a seguir atinge a agregação, porque AVERAGE torna-se internamente um SUM dividido por um COUNT. Como a coluna UnitPrice tem agregações definidas para SUM e COUNT, a agregação é atingida.
Em alguns casos, a função DISTINCTCOUNT pode se beneficiar de agregações. A consulta a seguir atinge a agregação, porque há uma entrada GroupBy para CustomerKey, que mantém a distinção de CustomerKey na tabela de agregação. Essa técnica ainda pode atingir o limite de desempenho, em que mais de dois a cinco milhões de valores distintos podem afetar o desempenho da consulta. No entanto, ela pode ser útil em cenários em que há bilhões de linhas na tabela de detalhes, mas de dois a cinco milhões de valores distintos na coluna. Nesse caso, a DISTINCTCOUNT poderá ser executada mais rapidamente do que o exame da tabela com bilhões de linhas, mesmo se ela tiver sido armazenada em cache na memória.
As funções de inteligência de dados temporais DAX (Data Analysis Expressions) têm reconhecimento de agregação. A consulta a seguir atinge a agregação porque a função DATESYTD gera uma tabela de valores CalendarDay e a tabela de agregação tem em uma granularidade que é coberta por colunas de agrupamento na tabela Data. Este é um exemplo de filtro com valor de tabela para a função CALCULATE, que pode funcionar com agregações.
Agregação baseada em colunas GroupBy
Os modelos de Big Data baseados em Hadoop têm características diferentes do que modelos dimensionais. Para evitar junções entre tabelas grandes, os modelos de Big Data geralmente não usam relações, mas desnormalizam atributos de dimensão em tabelas de fatos. Você pode descobrir esses modelos de Big Data para uma análise interativa usando agregações baseadas em colunas GroupBy.
A tabela a seguir contém a coluna numérica Movimento a ser agregada. Todas as outras colunas são atributos pelos quais é feito o agrupamento. A tabela contém dados de IoT e um grande número de linhas. O modo de armazenamento é DirectQuery. As consultas na fonte de dados agregadas em todo o modelo são lentas devido ao grande volume.
Para habilitar a análise interativa nesse modelo, adicione uma tabela de agregação que faz o agrupamento pela maioria dos atributos, mas exclui os atributos de alta cardinalidade como longitude e latitude. Isso reduz drasticamente o número de linhas, e é pequeno o suficiente para se ajustar confortavelmente em um cache na memória.
Defina os mapeamentos de agregação da tabela Driver Activity Agg na caixa de diálogo Gerenciar agregações.
Em agregações baseadas em colunas GroupBy, as entradas GroupBy não são opcionais. Sem elas, as agregações não são atingidas. Isso é diferente de usar agregações baseadas em relações, em que as entradas GroupBy são opcionais.
A tabela a seguir mostra as agregações para a tabela Agregação de atividade do driver.
Defina o modo de armazenamento da tabela agregada Driver Activity Agg como Importação.
Exemplo de consulta de agregação GroupBy
A consulta a seguir atinge a agregação porque a coluna Activity Date é abrangida pela tabela de agregação. A função COUNTROWS usa a agregação Linhas da tabela contadas.
Especialmente para os modelos que contêm atributos de filtro em tabelas de fatos, é uma boa ideia usar agregações Contar linhas da tabela. O Power BI pode enviar consultas ao modelo usando COUNTROWS nos casos não explicitamente solicitados pelo usuário. Por exemplo, a caixa de diálogo de filtro mostra a contagem de linhas para cada valor.
Técnicas de agregação combinadas
Você pode combinar as relações e as técnicas de colunas GroupBy para agregações. As agregações baseadas em relações podem exigir que as tabelas de dimensões desnormalizadas sejam divididas em várias tabelas. Se isso for caro ou impraticável para determinadas tabelas de dimensões, replique os atributos necessários na tabela de agregação para essas dimensões e use relações para outras.
Por exemplo, o modelo a seguir replica Month, Quarter, Semester e Year na tabela Sales Agg. Não há nenhuma relação entre Sales Agg e a tabela Date, mas há relações com Customer e Product Subcategory. O modo de armazenamento de Agregação de vendas é Importação.
A tabela a seguir mostra as entradas definidas na caixa de diálogo Gerenciar agregações da tabela Agregação de vendas. As entradas GroupBy em que Date é a tabela de detalhes são obrigatórias para atingir agregações de consultas agrupadas pelos atributos de Date. Como no exemplo anterior, as entradas GroupBy para CustomerKey e ProductSubcategoryKey não afetam as ocorrências de agregação, com exceção de DISTINCTCOUNT, devido à presença de relações.
Exemplos de consulta de agregação combinada
A consulta a seguir atinge a agregação porque a tabela de agregação abrange CalendarMonth, e CategoryName é acessível por meio de relações um-para-muitos. SalesAmount usa a agregação SUM.
A consulta a seguir não atinge a agregação porque a tabela de agregação não abrange CalendarDay.
A consulta de inteligência de dados temporais não atinge a agregação porque a função DATESYTD gera uma tabela de valores CalendarDay e a tabela de agregação não abrange CalendarDay.
Precedência de agregação
A precedência de agregação permite que várias tabelas de agregação sejam consideradas por uma única subconsulta.
O seguinte exemplo é um modelo composto que contém várias fontes:
- A tabela Driver Activity do DirectQuery contém mais de um trilhão de linhas de dados de IoT originados de um sistema de Big Data. Ela atende a consultas de detalhamento para exibir leituras de IoT individuais em contextos de filtro controlados.
- A tabela Agregação de atividade do driver é uma tabela de agregação intermediária em modo DirectQuery. Ela contém mais de um bilhão de linhas no Azure Synapse Analytics (antigo SQL Data Warehouse) e é otimizada na origem usando índices columnstore.
- A tabela de Importação Driver Activity Agg2 está em uma alta granularidade, porque os atributos GroupBy por são poucos e de baixa cardinalidade. O número de linhas poderia ser tão baixo quanto milhares; portanto, ele pode se ajustar facilmente em um cache na memória. Esses atributos são usados por um importante dashboard executivo, portanto, as consultas referentes a eles devem ser rápidas.
Observação
Tabelas de agregação do DirectQuery que usam fonte de dados diferente da usada na tabela de detalhes só serão compatíveis se a tabela de agregação for de uma fonte do SQL Server, do SQL do Azure ou do Azure Synapse Analytics (antigo SQL Data Warehouse).
O volume de memória deste modelo é relativamente pequeno, mas usa um modelo enorme. Ele representa uma arquitetura equilibrada, porque distribui a carga de consulta entre os componentes da arquitetura, utilizando-os de acordo com os pontos fortes.
A caixa de diálogo Gerenciar agregações de Driver Activity Agg2 define o campo Precedence como 10, que é maior do que para Driver Activity Agg. A configuração de precedência mais alta significa que as consultas que usam agregações vão considerar Driver Activity Agg2 primeiro. As subconsultas que não estão na granularidade e que podem ser respondidas por Driver Activity Agg2 podem considerar Driver Activity Agg. As consultas de detalhes que não podem ser respondidas por nenhuma tabela de agregação podem ser direcionadas à Driver Activity.
A tabela especificada na coluna Detail Table é Driver Activity, não Driver Activity Agg, porque as agregações encadeadas não são permitidas.
A tabela a seguir mostra as agregações para a tabela Agregação de atividade do driver 2.
Detectar se as consultas atingem ou ignoram agregações
O SQL Profiler pode detectar se as consultas são retornadas do mecanismo de armazenamento de cache na memória ou enviadas por push para a fonte de dados pelo DirectQuery. Você pode usar o mesmo processo para detectar se as agregações estão sendo atingidas. Para obter mais informações, confira Consultas que atingem ou ignoram o cache.
O SQL Profiler também fornece o evento estendido Query Processing\Aggregate Table Rewrite Query.
O snippet de código JSON a seguir mostra um exemplo da saída do evento quando uma agregação é usada.
- matchingResult mostra que a subconsulta usou uma agregação.
- dataRequest mostra as colunas GroupBy e as colunas agregadas usadas pela subconsulta.
- mapping mostra as colunas na tabela de agregação para as quais foram mapeadas.
Manter os caches sincronizados
As agregações que combinam o modo de armazenamento DirectQuery, Importação e/ou Duplo poderão retornar dados diferentes se o cache na memória não for mantido em sincronia com os dados de origem. Por exemplo, a execução de consulta não tenta mascarar os problemas de dados filtrando os resultados do DirectQuery para que correspondam aos valores armazenados em cache. Há técnicas estabelecidas para lidar com esses problemas na origem, se necessário. As otimizações de desempenho devem ser usadas apenas de maneiras que não comprometam sua capacidade de atender aos requisitos empresariais. É sua responsabilidade conhecer seus fluxos de dados e o design de acordo.
Considerações e limitações
As agregações não dão suporte a Parâmetros de consulta M dinâmicos.
A partir de agosto de 2022, devido a alterações na funcionalidade, o Power BI vai ignorar as tabelas de agregação no modo de importação com fontes de dados habilitadas para SSO (logon único) devido a possíveis riscos de segurança. Para garantir o desempenho ideal da consulta com agregações, é recomendável desabilitar o SSO para essas fontes de dados.
Comunidade
O Power BI tem uma comunidade vibrante em que MVPs, profissionais de BI e colegas compartilham conhecimentos em grupos de discussão, vídeos, blogs e muito mais. Ao aprender sobre agregações, confira estes recursos adicionais: