Power BI Aggregations

No exame da Microsoft o termo “aggregations” aparece em apenas um tópico:

  • create and manage aggregations

Pra entender aggregations, ou agregações, você precisa entender de Data Granularity (que falamos no post passado). Isso porque agregagar nada mais é do que subir o nível do grão, ou seja, deixar menos detalhado. Portanto, se você tem vendas por DIA e não precisa de ver os dados nesse nível, você pode subir o grão para visão MENSAL, logo, agregar seus dados por MES.

Criar aggregations significa que você vai perder a visão detalhada dos seus dados? Pode ser que sim, mas pode ser que não… Se você simplesmente agrupar sua tabela, como mostro abaixo em “Create Aggregations” e ficar por isso mesmo, então nesse caso sim, você vai perder sua visão detalhada. No entanto, se você criar uma tabela de aggregation e mapear na sua agregação campos de uma outra tabela em que existam esses dados mais detalhados, então nesse caso você pode ter tanto os dados agregados quanto os detalhados, sendo que os detalhados vão ficar numa tabela detalhada, e os agregados numa tabela de aggregations.

Você pode usar relacionamentos entre tabelas agregadas e tabelas detalhadas. Existe também todo uma explicação quando se usa agregations com Import e DirectQuery, porque é muito comum que as pessoas agregem os dados usando Import e façam o detalhamento com DirectQuery, porém é preciso que tabelas que se relacionem com a tabela no modo IMPORT que elas sejam DUAL, e a tabela de aggregation você geralmente vai querer em modo import (por questões de performance).

Uma boa prática, quando estiver desenhando seu modelo de dados, onde você imagina que vá usar Aggregations, é inicialmente construir seu modelo como DirectQuery e depois alterar as tabelas desejadas para Import ou Dual, isso porque você pode mudar de DQ para Import, mas de IMPORT para DQ não é possível.

A Microsoft define algumas das vantagens de se usar Aggregations, sendo:

  • Melhor desempenho de consulta em big data. Cada interação com recursos visuais do Power BI envia consultas DAX ao conjunto de dados. Os dados agregados em cache usam uma fração dos recursos necessários para dados detalhados, portanto, você pode desbloquear big data que, de outra forma, estariam inacessíveis.
  • Atualização de dados otimizada. Tamanhos menores de cache reduzem os tempos de atualização, de modo que os dados chegam aos usuários mais rapidamente.
  • Arquiteturas equilibradas. O cache na memória do Power BI pode lidar com consultas agregadas, limitando as consultas enviadas no modo DirectQuery e ajudando você a atender aos limites de simultaneidade. As consultas de nível de detalhe restantes tendem a ser filtradas, consultas de nível transacional, que armazéns de dados e sistemas de big data normalmente lidam bem.

Create Aggregations

Antes de criar agregações é preciso definir qual vai ser a granularidade dos dados que você deseja trabalhar. Muitas vezes importamos mais registros do que realmente precisamos.

Um exemplo, aqui do meu trabalho, eu tenho, todo mês, mais de 50 mil registros por mês pra serem tratados, ou seja, se eu importar dados dos últimos 12 meses, estamos falando de 600 mil registros. Claro que essa é uma quantidade pequena para o Power BI, mas será que precisamos mesmos de todos esses registros?

Se meu relatório não trabalha a nível de transação, ou seja, se meu usuário não precisa ver cada um desses 50 mil registros, então isso significa que ele quer ver um nível menos detalhado. Muitas vezes ele só quer ver a um nível de DIA. Então eu posso agregar esses meus 50 mil registros a um nível diário, de forma que, todos registros que aconteceram no mesmo dia vão passar a ocupar apenas uma única linha. Isso é agregar.

Existem várias formas de se agregar esses dados.

  • Se você tem acesso ao banco de dados, você pode usar um GROUP BY no SQL.
  • Se você tem mais acesso ainda ao banco de dados, você pode criar uma VIEW com essa agregação.
  • Existem outras maneiras de agregar dados no banco de dados, na internet tem vários exemplos.
  • Agora, se o que te resta é trabalhar no Power Query, também dá. Nesse post aqui nosso foco será tratar agregações no Power Query.

Para esse exemplo, vamos trabalhar com a tabela [FactResellerSales] do AdventureWorksDW.

Primeiro preciso conhecer minha tabela e saber onde quero chegar. No meu caso aqui, quero agregar meus registros por ORDERDATEKEY e ver o resultado a nível de ORDERDATEKEY para o total de SALESAMOUNT e ORDERQUANTITY.

Para isso, basta selecionar Home > Group By e escolher a agregação que você deseja. Inclusive já falei sobre group by.

Na imagem abaixo está a definição do GROUP BY, onde eu agrego por ORDERDATEKEY, criando um contador de registros de linha de ORDERDATEKEY com OderDateCount e agrupando OrderQuantity e SalesAmount usando operação de SUM.

O resultado foi esse da imagem abaixo. Perceba que saímos de 999+ rows para apenas 40 rows. O resultado dessa agregação é simplesmente pra dizer que, por exemplo, no dia 29/09/2011 foram feitas 494 Orders, com uma quantidade de 1260 itens, gerando um total de R$ 882.899,94.

Nesse exemplo criei uma tabela agregada de FactResellerSales, vou chamar essa tabela de agg_FactResellerSales e vou manter a tabela original, a FactResellerSales.

Manage Aggregations

Depois que você criou essa tabela de Aggregation, você pode editar a Aggregation criada. Para editar a aggregation clique com botão direito na tabela de aggregations > Manage Aggregations. A tela abaixo vai se abrir.

Nessa telinha aqui você não vai alterar sua aggregation que fez lá no Power Query, você não vai mudar um campo agregado em SUM para um que mudar pra agregar por MAX… Nessa tela o que você vai realmente configurar é o mapeamento, o relacionamento, entre seus campos agregados e seus campos detalhados.

Pensa assim, se você só tem sua tabela agregada, se você não tem tabela de detalhamento, ou seja, seu cenário é mais simples, você só precisa da tabela já agregada no nível desejado… Nesse caso, não tem nem por que você mexer nessa telinha, afinal seu modelo já é direto na tabela agregada.

No entanto, se você tem um data model mais complexo, onde você tem uma tabela de agregados e uma tabela de detalhes, aí, nesse caso, você vai precisar realizar o mapeamento… Esse mapeamento é importante e imprescindível para que o Power BI seja capaz de identificar que, por exemplo, SUM QTD está relacionado ao campo Quantity usando uma agregação de SUM. Continua lendo que explico isso.

Agregado e Detalhado

Voltando ao meu data model, onde tenho a tabela agregada, agg_FactResellerSales, e minha tabela detalhada, FactResellerSales, o que quero agora é que, quando eu criar um visual no Power BI que ele primeiro pegue, se possível, o dado da tabela agregada, porque vai ser mais rápido, agora, caso contrário, pegue da tabela detalhada mesmo.

Primeiro passo é fazer o mapeamento. Pra que esse mapeamento seja possível é preciso que nossos campos estejam no mesmo tipo de dados (data type), ou seja, o que é whole number tem que ser whole number, o que é decimal tem que ser decimal, e por aí vai.

(dei uma renomeada nos campos na tabela agregada pra ficar mais fácil de entender o que é o que)

Um ponto muito importante:
A tabela de detalhes deve, obrigatoriamente, ter o Storage Mode do tipo DirectQuery.

Já a tabela de aggregation pode ser import ou DirectQuery, preferencialmente, Import, por questões de performance, mas pode ser um DirectQuery também, vai que tu fez um tunning bolado na sua tabela, então DQ também é uma opção.

Existem duas formas de lidar com tabelas de agregação e utilizá-las com outras tabelas de detalhamento… Ou você relaciona essa tabela no seu modelo, ou você não relaciona… Se você relacionar saiba que não é possível realizar um relacionamento direto entre a tabela de Aggregations (Aggs) e de Detalhamento (Details), pra relacionar usando relationships você precisa obrigatoriamente precisa relacionar a tabela Aggs com outras tabelas de Dimensão que filtrem a tabela Fato (denovo, você não tem como relacionar aggs diretamente com a fato (details)).

Aggregation detalhado usando Relationship

Na imagem abaixo temos um modelo que faz detalhamento da Aggs via relationships, perceba que Sales Agg não está ligada diretamente a tabela fato, Sales. Na verdade, Sales Agg está relacionada com CUSTOMER, DATE e PRODUCT SUBCATEGORY.

Esse modelo está todo em DirectQuery, mas, no momento em que eu alterar a tabela de Sales Agg pra IMPORT, o Power BI vai reclamar e vai pedir pra transformar essas 3 dimensões em tipo DUAL, e além dessas 3 dimensões, quaisquer outras tabelas que filtrarem essas dimensões. Logo, para que seja possível alterar Sales Agg para IMPORT eu preciso alterar GEOGRAPHY, CUSTOMER, DATE, PRODUCT CATEGORY e PRODUCT SUBCATEGORY para storage mode do tipo DUAL.

Essa parada de colocar algumas tabelas como DUAL e outras como DirectQuery e outras como IMPORT pode criar alguns pontos de WEAK RELATIONSHIPS no seu modelo de dados. Eu já escrevi sobre Weak Relationships, vai lá dar uma lida. Além disso, pra essa técnica de Aggregations e Details funcionar, você precisa obrigatoriamente de que seu modelo de dados não tenha Weak Relationships.

Basicamente o que você precisa saber pra garantir que não vai ter WR no seu modelo é o seguinte: (fonte: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations#strong-vs-weak-relationships)

Para garantir o bom uso e funcionamento do seu modelo de dados utilizando Aggregations é importante conhecer a granularidade dos seus dados. Usando a imagem anterior como exemplo, caso alguma fórmula DAX utilize alguma informação da tabela PRODUCT, então aggregation não vai funcionar. A tabela de produtos não está na granularidade que pode atingir a agregação. Se você observar os relacionamentos no modelo, PRODUCT SUBCATEGORY pode ter várias linhas de PRODUCT. A consulta não seria capaz de determinar a qual produto agregar. Nesse caso, a consulta reverte para DirectQuery e envia uma consulta SQL à fonte de dados.

No exemplo que usei no tópico anterior (agregado e detalhado) eu não tenho outras tabelas de dimensão, e nem quero criar. Vamos lembrar do nosso modelo:

Meu modelo está correto, tenho uma fato Details e uma tabela de Aggs. Eu já verifiquei e Aggs é tipo IMPORT enquanto Details é tipo DirectQuery. Também já validei o tipo de dados dos campos e está correto e, como nesse caso não posso usar relacionamentos, o jeito é agregar sem relationship mesmo, que explico no próximo tópico.

Aggregation detalhado sem usar Relationship

Esse modelo de usar Aggs e Details sem relacionamentos é muito utilizado em modelos de Big Data, porque pensa assim, relacionamentos, como 1:N, fazem um table-expansion de LEFT JOIN, então imagina isso numa tabela de bilhões de linhas… a performance é afetada.

Pra conseguir tratar isso, ou seja, criar Aggs sem relationships, o Power BI usa de GROUP BY no gerenciamento da Aggs, assim:

Perceba que, eu mandei um GROUP BY no ORDERDATEKEY, sendo que, se eu estivesse usando um relacionamento, isso não seria necessário… Se eu estivesse usando relacionamento eu poderia deixar esse campo completamente sem preencher, sem mexer nada nele, ou seja, pra modelos que estejam usando relacionamento, esse campo seria opcional.

Porém, para modelos sem relacionamento, esse campo é obrigatório.

Aggregation em funcionamento

Ok, definimos nosso Aggs usando o tipo sem relacionamento. Vamos ver então se ele funciona? Vou só fazer um teste rápido, mas vai dar pra perceber bem…

Pra esse pequeno teste utilizei o Performance Analyzer mesmo, perceba que nossas duas tabelas não executam DirectQuery. A tabela de cima roda DAX em cima da Aggs, que é IMPORT, e a tabela debaixo roda DAX na tabela DETAILS, que está mapeada pela Aggs. Perceba que nosso modelo de Aggregations funcionou, visto que a segunda tabela não fez consulta de DirectQuery.

Agora, nesse segundo exemplo, vou tirar o GROUP BY lá da minha configuração de Aggregations, vai ficar assim:

Perceba agora que nossa segunda tabela, ao ser verificada, roda uma consulta de DirectQuery! (e a tabela de Aggs voltou a aparecer, isso é outra coisa, a tabela de Aggs fica oculta uma vez que o Aggregation é mapeado para Details)

Considerações finais sobre Aggregations

Aggregations é um tópico extenso, extremamente importante e bastante complexo. Em aggregations você ainda pode configurar mais de uma tabela de Aggs e definir precedentes, então você pode ter tipo, uma tabela numa granularidade maior, depois uma outra um pouco menor, e assim em diante até chegar na sua tabela Details, e aí definir cada tabela de Aggs com um precedente maior que o outro, de forma que as com precedentes maiores são consultadas antes, e se o Power BI não conseguir encontrar o que precisa ali ele vai descendo o precedente até conseguir achar o dado, ou chegar na Details.

Existem também três formas de se verificar se seu modelo está usando as Aggs corretamente. Você pode utilizar o Performance Analyzer no Power BI Desktop mesmo e ver se aquele visual tá gerando algum tipo de consulta no DirectQuery, ou você pode usar o DAX Studio, ou o SQL Profiler. O detalhamento e uso dessas ferramentas foge ao escopo desse post.

Observações sobre Aggregations

  • Fontes de dados dimensionais, como data warehouses e data marts, podem usar agregações baseadas em relacionamento. Fontes de big data baseadas em Hadoop geralmente baseiam agregações em colunas GroupBy.
  • Os tipos possíveis de sumarização disponíveis em Manage Aggregations são:
    • Count
    • GroupBy
    • Max
    • Min
    • Sum
    • Count table rows
  • Para que um Manage Aggregation funcione, é preciso garantir que:
    • A coluna de detalhes deve ter o mesmo tipo de dados que a coluna de agregação, exceto para as funções de sumarização de COUNT e COUNT TABLE ROWS.
    • COUNT e COUNT TABLE ROWS estão disponíveis apenas para colunas de agregação de inteiros e não requerem um tipo de dados correspondente.
    • Agregações encadeadas cobrindo três ou mais tabelas não são permitidas. Por exemplo, as agregações na Tabela A não podem se referir a uma Tabela B que possui agregações que se referem 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 Import.
    • Aggregation não funciona com USERELATIONSHIP, pois Aggregation não funciona para relacionamentos inativos, mesmo que exista group by na chave estrangeira.
  • Para funcionar corretamente para agregações, as expressões RLS devem filtrar a tabela de agregação e também a tabela de detalhes.
  • Uma expressão RLS que filtra apenas a tabela de agregação VENDAS_Agg e não a tabela Details de VENDAS não é permitida.
  • Para agregações baseadas em colunas GroupBy (naquele modelo de Big Data), uma expressão RLS aplicada à tabela de Details pode ser usada para filtrar a tabela de Aggs, porque todas as colunas GroupBy na tabela de Aggs são cobertas pela tabela de Details. Por outro lado, um filtro RLS na tabela de Aggs não pode ser aplicado à tabela de Details, portanto, não é permitido.
  • Os usuários com acesso somente leitura ao datasets não podem consultar as tabelas de Aggs. Isso evita problemas de segurança quando usado com RLS. Os usuários e as queries referem-se à tabela de Details, não à tabela de Aggs (e nem precisam saber que a tabela de Aggs existe).
  • Aggregation requer obrigatoriamente STRONG Relationships.

Referências

https://powerbixpert.com/2020/07/04/power-bi-group-by/
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations
https://docs.microsoft.com/en-us/learn/modules/optimize-model-power-bi/6-aggregations
https://www.kasperonbi.com/5-reasons-to-use-aggregations-in-power-bi/
https://dax.tips/2019/10/18/creative-aggs-part-i-introduction/
https://www.youtube.com/watch?v=EhGF372t0sU – The How and Why of Power BI Aggregations
https://www.youtube.com/watch?v=kytYzjTXUHs – Configure Power BI Aggregations
https://www.youtube.com/watch?v=RdHSo43LkQg – How to use the Power BI Aggregations Feature
https://www.youtube.com/watch?v=V9aG-6wr1xQ – Why can’t I define my Power BI Aggregations???
https://www.youtube.com/watch?v=ivWGSnthTDw – Are you HITTING your Power BI aggregations???
https://www.sqlbi.com/tv/aggregations-in-power-bi/

Publicado por Pedro Carvalho

Apaixonado por análise de dados e Power BI.

Deixe uma resposta

%d blogueiros gostam disto: