Agregações definidas pelo usuário
As agregações no Power BI podem melhorar o desempenho da consulta em modelos semânticos grandes do DirectQuery. Usando agregações, você armazena 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. Para subscrições Premium, ative automaticamente a funcionalidade Agregações automáticas nas Definições do modelo.
Criação de 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 exibição, consulta nativa. Para obter o melhor desempenho, crie uma tabela de agregações como uma tabela de importação criada no Power Query. Em seguida, use a caixa de diálogo Gerenciar agregações no Power BI Desktop para definir agregações para colunas de agregação com propriedades de resumo, tabela de detalhes e coluna de detalhes.
Fontes de dados dimensionais, como data warehouses e data marts, podem usar agregações baseadas em relacionamentos. As fontes de big data baseadas em Hadoop geralmente baseiam agregações em colunas GroupBy. Este artigo descreve as diferenças típicas de modelagem de dados do Power BI para cada tipo de fonte de dados.
Gerir agregações
No painel Dados de qualquer modo de 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 da tabela, onde 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 .
Neste exemplo de agregação baseada em relacionamento, as entradas GroupBy são opcionais. Com exceção de DISTINCTCOUNT, eles não afetam o comportamento de agregação e são principalmente para legibilidade. Sem as entradas do GroupBy, as agregações ainda seriam atingidas, com base nas relações. Isso é diferente do exemplo de big data mais adiante neste artigo, onde 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 deve ter o mesmo tipo de dados que a Coluna de Agregação, exceto para as funções de Resumo das linhas da tabela Contagem e Contagem . As linhas da tabela Contagem e Contagem só estão disponíveis para colunas de agregação inteira 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 referir-se a uma Tabela B que tenha agregações referentes a uma Tabela C.
- Agregações duplicadas, em que duas entradas usam a mesma função de Sumarização e se referem à mesma Tabela de Detalhes e Coluna de Detalhes, não são permitidas.
- A Tabela de Detalhes deve usar o modo de armazenamento DirectQuery, não Importar.
- Não há suporte para o agrupamento por uma coluna de chave estrangeira usada por um relacionamento inativo e a dependência da função USERELATIONSHIP para agregação de acertos.
- As agregações baseadas em colunas GroupBy podem usar relações entre tabelas de agregação, mas as relações de criação entre tabelas de agregação não são suportadas 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 XML for Analysis (XMLA).
A maioria das validações é imposta desativando os valores suspensos e mostrando texto explicativo na dica de ferramenta.
As tabelas de agregação estão ocultas
Os usuários com acesso somente leitura ao modelo não podem consultar tabelas de agregação. O acesso somente leitura evita problemas de segurança quando usado com RLS (segurança em nível de linha). Os consumidores e as consultas referem-se à tabela de detalhes, não à tabela de agregação, e não precisam saber sobre a tabela de agregação.
Por esse motivo, as tabelas de agregação ficam ocultas no modo 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, Importar ou Duplo . O DirectQuery consulta o back-end diretamente, enquanto Import armazena em cache dados na memória e envia consultas para os dados armazenados em cache. Todas as fontes de dados DirectQuery não multidimensionais e de Importação do Power BI podem trabalhar com agregações.
Para definir o modo de armazenamento de uma tabela agregada como Importar para acelerar as consultas, selecione a tabela agregada na vista Modelo do Power BI Desktop. No painel Propriedades, expanda Avançado, solte a seleção em Modo de armazenamento e selecione Importar. Alterar a importação é irreversível.
Para saber mais sobre os modos de armazenamento de tabela, consulte Gerenciar modo de armazenamento no Power BI Desktop.
RLS para agregações
Para funcionar corretamente para 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 filtrante das relações com a tabela Sales e a tabela Sales Agg. Consultas que atingem a tabela de agregação e consultas que não têm RLS aplicado com êxito.
Uma expressão RLS na tabela Produto filtra apenas a tabela Sales detalhada, não a tabela Sales Agg agregada. Como a tabela de agregação é outra representação dos dados na tabela de detalhes, seria inseguro responder a consultas da tabela de agregação se o filtro RLS não puder ser aplicado. Filtrar apenas a tabela de detalhes não é recomendado, porque as consultas do usuário dessa função não se beneficiam de agregação de acertos.
Uma expressão RLS que filtra apenas a tabela de agregação Sales Agg e não a tabela de detalhes Sales não é permitida.
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, porque todas as colunas GroupBy na tabela de agregação são cobertas pela tabela de detalhes. Por outro lado, um filtro RLS na tabela de agregação não pode ser aplicado à tabela de detalhes, portanto, não é permitido.
Agregação baseada em relações
Os modelos dimensionais normalmente usam agregações baseadas em relacionamentos. Os modelos do Power BI de armazéns de dados e data marts assemelham-se a esquemas de estrelas/flocos de neve, com relações entre tabelas de dimensões e tabelas de factos.
No exemplo a seguir, o modelo obtém dados de uma única fonte de dados. As tabelas estão usando o modo de armazenamento DirectQuery. A tabela de fatos de vendas contém bilhões de linhas. Definir o modo de armazenamento de Vendas como Importar para cache consumiria memória considerável e sobrecarga de recursos.
Em vez disso, crie a tabela de agregação do Sales Agg . Na tabela Sales Agg, o número de linhas é igual à soma de SalesAmount agrupada por CustomerKey, DateKey e ProductSubcategoryKey. A tabela Sales Agg tem uma granularidade maior do que Sales, portanto, em vez de bilhões, ela pode conter milhões de linhas, que são mais fáceis de gerenciar.
Se as tabelas de dimensão a seguir forem usadas com mais frequência para consultas com alto valor comercial, elas poderão filtrar o Agg de Vendas, usando relações um-para-muitos ou muitos-para-um .
- Geografia
- Cliente
- Date
- Subcategoria de Produtos
- Categoria do Produto
A imagem seguinte mostra este modelo.
A tabela a seguir mostra as agregações para a tabela Sales Agg .
Nota
A tabela Sales Agg , como qualquer tabela, tem a flexibilidade de ser carregada de várias maneiras. A agregação pode ser realizada 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 Importar, com ou sem atualização incremental para modelos semânticos, ou pode usar DirectQuery e ser otimizada para consultas rápidas usando índices columnstore. Essa flexibilidade permite arquiteturas equilibradas que podem espalhar a carga de consulta para evitar gargalos.
Alterar o modo de armazenamento da tabela Agg de vendas agregada para Importar abre uma caixa de diálogo informando que as tabelas de dimensão relacionadas podem ser definidas para o modo de armazenamento Dual.
Definir as tabelas de dimensão relacionadas como Dual permite que elas atuem como Import ou DirectQuery, dependendo da subconsulta. No exemplo:
- As consultas que agregam métricas da tabela Agg de vendas no modo de importação e agrupam por atributo (s) das tabelas Dual relacionadas podem ser retornadas do cache na memória.
- As consultas que agregam métricas da tabela de Vendas do DirectQuery e agrupam por atributo (s) das tabelas Duplas relacionadas podem ser retornadas no modo DirectQuery. A lógica de consulta, incluindo a operação GroupBy, é passada para o banco de dados de origem.
Para obter mais informações sobre o modo de armazenamento duplo, consulte Gerenciar o modo de armazenamento no Power BI Desktop.
Relações regulares vs. limitadas
Os acertos de agregação baseados em relacionamentos exigem relacionamentos regulares.
As relações regulares incluem as seguintes combinações de modo de armazenamento, em que ambas as tabelas são de uma única fonte:
| Mesa nos vários lados | Mesa do lado 1 |
|---|---|
| Duplo | Duplo |
| Importar | Importação ou Dual |
| DirectQuery | DirectQuery ou Dual |
O único caso em que uma relação entre fontes é considerada regular é se ambas as tabelas estiverem definidas como Importar. As relações muitos-para-muitos são sempre consideradas limitadas.
Para agregações entre fontes que não dependem de relações, consulte Agregações baseadas em colunas GroupBy.
Exemplos de consulta de agregação baseada em relacionamento
A consulta a seguir 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 Soma .
A consulta a seguir não atinge a agregação. Apesar de solicitar a soma de SalesAmount, a consulta está executando 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 pode ter várias linhas de Produto . A consulta não seria capaz de determinar a qual produto agregar. Nesse caso, a consulta reverte para DirectQuery e envia uma consulta SQL para a fonte de dados.
As agregações não são apenas para cálculos simples que executam uma soma simples. Cálculos complexos também podem ser beneficiados. Conceitualmente, um cálculo complexo é dividido em subconsultas para cada SOMA, MIN, MAX e CONTAGEM. Cada subconsulta é avaliada para determinar se pode atingir a agregação. Essa lógica não é verdadeira em 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 de agregações. A consulta a seguir atinge a agregação porque há uma agregação de linhas da tabela Count definida para a tabela Sales .
A função MÉDIA pode beneficiar de agregações. A consulta a seguir atinge a agregação porque AVERAGE internamente é dobrado para uma SOMA dividida por uma COUNT. Como a coluna PreçoUnitário tem agregações definidas para SOMA 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, onde mais de dois a cinco milhões de valores distintos podem afetar o desempenho da consulta. No entanto, pode ser útil em cenários em que há bilhões de linhas na tabela de detalhes, mas dois a cinco milhões de valores distintos na coluna. Nesse caso, o DISTINCTCOUNT pode ter um desempenho mais rápido do que a varredura da tabela com bilhões de linhas, mesmo que ela tenha sido armazenada em cache na memória.
As funções de inteligência de tempo DAX (Data Analysis Expressions) reconhecem a 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 está em uma granularidade que é coberta para colunas agrupadas na tabela Data. Este é um exemplo de um filtro com valor de tabela para a função CALCULATE, que pode trabalhar com agregações.
Agregação com base em colunas GroupBy
Os modelos de big data baseados em Hadoop têm características diferentes dos 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 para tabelas de fatos. Você pode desbloquear esses modelos de big data para 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 para agrupar por. A tabela contém dados de IoT e um grande número de linhas. O modo de armazenamento é DirectQuery. Consultas na fonte de dados que agregam em todo o modelo lentidão devido ao grande volume.
Para habilitar a análise interativa nesse modelo, você pode adicionar uma tabela de agregação que agrupa 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 caber confortavelmente em um cache na memória.
Você define os mapeamentos de agregação para a 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 eles, as agregações não são atingidas. Isso é diferente de usar agregações baseadas em relações, onde as entradas GroupBy são opcionais.
A tabela a seguir mostra as agregações para a tabela Driver Activity Agg .
Você pode definir o modo de armazenamento da tabela Agg de atividade do driver agregada como Importar.
Exemplo de consulta de agregação GroupBy
A consulta a seguir atinge a agregação, porque a coluna Data da atividade é coberta pela tabela de agregação. A função COUNTROWS usa a agregação de linhas da tabela Contada.
Especialmente para modelos que contêm atributos de filtro em tabelas de fato, é uma boa ideia usar agregações de linhas de tabela Contar. O Power BI pode enviar consultas ao modelo usando COUNTROWS nos casos em que não é explicitamente solicitado 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 técnicas de relações e colunas GroupBy para agregações. Agregações baseadas em relacionamentos podem exigir que as tabelas de dimensão desnormalizadas sejam divididas em várias tabelas. Se isso for caro ou impraticável para determinadas tabelas de dimensão, você poderá replicar os atributos necessários na tabela de agregação para essas dimensões e usar relações para outras.
Por exemplo, o modelo a seguir replica Mês, Trimestre, Semestre e Ano na tabela Sales Agg . Não há relação entre o Sales Agg e a tabela Data , mas há relações com Cliente e Subcategoria de Produto. O modo de armazenamento do Sales Agg é Importar.
A tabela a seguir mostra as entradas definidas na caixa de diálogo Gerenciar agregações para a tabela Sales Agg . As entradas GroupBy onde Date é a tabela de detalhes são obrigatórias, para atingir agregações para consultas que agrupam pelos atributos Date . Como no exemplo anterior, as entradas GroupBy para CustomerKey e ProductSubcategoryKey não afetam os acertos de agregação, exceto para 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 SOMA .
A consulta a seguir não atinge a agregação, porque a tabela de agregação não cobre CalendarDay.
A consulta de inteligência de tempo a seguir não atinge a agregação, porque a função DATESYTD gera uma tabela de valores CalendarDaye a tabela de agregação não cobre CalendarDay.
Precedência da agregação
A precedência da agregação permite que várias tabelas de agregação sejam consideradas por uma única subconsulta.
O exemplo a seguir é um modelo composto que contém várias fontes:
- A tabela DirectQuery de atividade do driver contém mais de um trilhão de linhas de dados de IoT provenientes de um sistema de big data. Ele serve consultas de detalhamento para exibir leituras individuais de IoT em contextos de filtro controlados.
- A tabela Driver Activity Agg é uma tabela de agregação intermediária no modo DirectQuery. Ele contém mais de um bilhão de linhas no Azure Synapse Analytics (anteriormente SQL Data Warehouse) e é otimizado na origem usando índices columnstore.
- A tabela Driver Activity Agg2 Import está em uma granularidade alta, porque os atributos group-by são poucos e baixa cardinalidade. O número de linhas pode ser tão baixo quanto milhares, para que possa caber facilmente em um cache na memória. Esses atributos são usados por um painel executivo de alto perfil, portanto, as consultas referentes a eles devem ser o mais rápidas possível.
Nota
As tabelas de agregação do DirectQuery que usam uma fonte de dados diferente da tabela de detalhes só terão suporte se a tabela de agregação for de uma fonte do SQL Server, Azure SQL ou Azure Synapse Analytics (anteriormente SQL Data Warehouse).
A pegada de memória deste modelo é relativamente pequena, mas desbloqueia um modelo enorme. Ele representa uma arquitetura equilibrada porque distribui a carga de consulta entre os componentes da arquitetura, utilizando-os com base em seus pontos fortes.
A caixa de diálogo Agregações gerenciadas para Driver Activity Agg2 define o campo Precedência 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 consideram a atividade do driver Agg2 primeiro. As subconsultas que não estão na granularidade que pode ser respondida pelo Driver Activity Agg2 podem considerar o Driver Activity Agg em vez disso. As consultas detalhadas que não podem ser respondidas por nenhuma das tabelas de agregação podem direcionar para a Atividade do driver.
A tabela especificada na coluna Tabela de Detalhes é Atividade do Driver, não Atividade do Driver Agg, porque agregações encadeadas não são permitidas.
A tabela a seguir mostra as agregações para a tabela Driver Activity Agg2 .
Detetar se as consultas acertaram ou erraram agregações
O SQL Profiler pode detetar 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 detetar se as agregações estão sendo atingidas. Para obter mais informações, consulte Consultas que atingem ou perdem o cache.
O SQL Profiler também fornece o Query Processing\Aggregate Table Rewrite Query evento estendido.
O trecho 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 a(s) coluna(s) GroupBy e coluna(s) agregada(s) a subconsulta usada.
- O mapeamento mostra as colunas na tabela de agregação para as quais foram mapeadas.
Mantenha os caches sincronizados
As agregações que combinam os modos de armazenamento DirectQuery, Import e/ou Dual podem retornar dados diferentes, a menos que o cache na memória seja mantido em sincronia com os dados de origem. Por exemplo, a execução de consultas não tenta mascarar problemas de dados filtrando os resultados do DirectQuery para corresponder aos valores armazenados em cache. Existem técnicas estabelecidas para lidar com tais questões na fonte, se necessário. As otimizações de desempenho devem ser usadas apenas de maneiras que não comprometam sua capacidade de atender aos requisitos de negócios. É sua responsabilidade conhecer seus fluxos de dados e projetar de acordo.
Considerações e limitações
As agregações não suportam parâmetros de consulta M dinâmicos.
A partir de agosto de 2022, devido a alterações na funcionalidade, o Power BI ignora tabelas de agregação de modo de importação com fontes de dados habilitadas para logon único (SSO) 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 onde MVPs, profissionais de BI e colegas compartilham conhecimentos em grupos de discussão, vídeos, blogs e muito mais. Ao aprender sobre agregações, não deixe de conferir estes recursos adicionais: