Aggregations in Power BI for Large Datasets

A fact table with billions of rows cannot be imported comfortably, and in DirectQuery every visual becomes a query against it. Yet most report visuals ask the same kind of question: totals by month, by region, by category. Aggregations exploit that. According to Microsoft's documentation, they improve query performance over large DirectQuery models by caching data at an aggregated level in memory (Microsoft Learn: user-defined aggregations). Queries at that level are answered from memory; anything more detailed goes to the source as before.

How an aggregation works

The model keeps two representations of the same facts:

• The detail table, the full fact table, in DirectQuery mode. The Manage aggregations dialog requires the detail table to be DirectQuery, not import.
• The aggregation table, a summarised copy, ideally imported, with columns mapped to the detail table in the Manage aggregations dialog. Each mapping has a summarisation: Sum, Count, Min, Max, GroupBy or Count table rows.

Report authors never see the aggregation table: it is hidden, and queries refer to the detail table, which Power BI rewrites to use the aggregation when it can. When the aggregation table is set to import, Power BI offers to set the related dimension tables to Dual, so they can serve both the in-memory aggregation and DirectQuery detail queries.

The grain decides everything

The key design decision is which columns to group by. Too coarse, and few queries can use it; too fine, and the table is barely smaller than the detail. Real data shows how quickly this goes wrong. Starting from 527,761 sales lines from an online retailer (UCI: Online Retail), here are three candidate aggregation tables:

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
sales = raw[~raw["InvoiceNo"].str.startswith("C") & ~raw["StockCode"].isin(services)
            & ~raw["StockCode"].str.startswith("gift_") & (raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)]
sales = sales.assign(Date=sales["InvoiceDate"].dt.normalize(), Revenue=sales["Quantity"] * sales["UnitPrice"])
print(f"detail table: {len(sales)} rows")

candidates = []
for grain in (["Date", "Country"], ["Date", "CustomerID"], ["Date", "StockCode"]):
    agg = (sales.groupby(grain, dropna=False)
           .agg(Revenue=("Revenue", "sum"), DetailRows=("Revenue", "size")).reset_index())
    candidates.append({
        "grain": " x ".join(grain),
        "rows": len(agg),
        "times smaller": round(len(sales) / len(agg), 1),
        "revenue matches detail": round(agg["Revenue"].sum(), 2) == round(sales["Revenue"].sum(), 2),
        "detail rows covered": agg["DetailRows"].sum(),
    })
print(pd.DataFrame(candidates).to_string(index=False))

detail table: 527761 rows
            grain   rows  times smaller  revenue matches detail  detail rows covered
   Date x Country   1527          345.6                    True               527761
Date x CustomerID  16950           31.1                    True               527761
 Date x StockCode 275326            1.9                    True               527761

• Date by country is 345 times smaller than the detail and answers every question about revenue by day, month, year and country.
• Date by customer is 31 times smaller and can also answer distinct customer counts, because customer ID is kept as a group-by column.
• Date by product is barely half the size of the detail. With thousands of products each selling on many days, there is little to aggregate. It would consume memory without saving much work.

All three reconcile: their revenue equals the detail total, and the "Count table rows" style column, here DetailRows, accounts for every detail row. That check is worth running on any aggregation table, because a silent mismatch means reports give different answers depending on whether the aggregation was used. Microsoft's IoT example makes the same point from the other side: leave out high-cardinality attributes such as longitude and latitude, and the table becomes small enough to fit comfortably in memory.

Relationship-based and GroupBy-based aggregations

In a star schema, aggregations usually work through relationships. The aggregation table holds dimension keys, such as a date key and a customer key, and the dimension tables filter it. Queries grouped by any attribute of those dimensions, such as month or country, can hit it. The GroupBy entries in the dialog are then optional, except for distinct counts.

Big data models often denormalise attributes into one wide table without relationships. There, aggregations are based on GroupBy columns, and the GroupBy entries are mandatory: without them the aggregation is never used. The star schema design makes the relationship-based approach simpler to maintain.

Which queries use the aggregation

These follow the rules in Microsoft's documentation, which also describes checking hits with SQL Profiler: the Aggregate Table Rewrite Query event reports whether a subquery matched an aggregation.

Several aggregation tables: precedence

A model can have more than one aggregation table, for example a tiny imported table for an executive dashboard and a larger one at a finer grain. The Precedence setting decides which is tried first; a query that neither can answer goes to the detail table. Chained aggregations, where one aggregation table refers to another, are not allowed.

Security and freshness

• Row-level security must filter both tables. A role that filters only the detail table cannot use the aggregation, and a filter on the aggregation table alone is not allowed. See row-level security in Power BI.
• Keep caches in sync. An imported aggregation reflects its last refresh, while DirectQuery detail is current. Refresh them together, or reports may show slightly different numbers depending on the path a query takes.

Automatic aggregations

Designing aggregations by hand takes modelling skill. Automatic aggregations use machine learning on the model's query log to build and maintain an in-memory aggregation cache for DirectQuery models (Microsoft Learn: automatic aggregations). They are available for Premium per capacity, Premium per user and Embedded models, not Pro, only in the Power BI service, and only for supported sources such as Azure SQL Database, SQL Server 2019 or later, Snowflake, BigQuery, Databricks and Redshift. User-defined and automatic aggregations can be combined in one model.

For the broader choice between import, DirectQuery and composite models, see composite models and DirectQuery, and for other speed fixes, Power BI performance tuning.

Data modelling and performance optimisation are part of the Power BI course.

Sources

• User-defined aggregations (Microsoft Learn) — Microsoft Learn
• Automatic aggregations overview (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository