What to Take Away From This:

The key piece here is the trade off between size and speed. Import works incredibly fast on relatively small datasets, but becomes unusable on big data sets. In opposition, direct query is not optimal on small datasets, but has the ability to handle massive datasets (1B+ rows), since we only grab what we need.

Aggregations give you an easy tool to work conveniently with the best of both worlds.

When is Direct Query Slow?

Before we get into details about aggregations we’ve got to understand the cases that direct query suffers in performance. The types of datasets that direct query is typically applied to are big, and detailed. I’ll emphasize again that using direct query, Power BI sends a query to your database engine for every visual every time a report change is made. That means your engine will have to summarize over your big detailed tables multiple times. This is computationally expensive, and will cause major performance issues.

Enter Aggregated Tables:

These repetitive, expensive summations, are the reason for the common practice of aggregated tables. An aggregated table is a table with a few specific metrics aggregated over a few specific dimensions. Typically a 1B row table can be crunched down into a 1M row aggregate table with a few dimensions. Even further, these aggregate tables can always be made small enough that we can Import them!

Now we’ve got two different tables with the “same” data. For many this is all that is required for a viable solution, but Power BI is renown for its interactive and user friendly sandbox environment. We can’t trust all users to use the right table in the right place, and further more, even experienced developers may not utilize the right table at the right time while writing complex measures.

Now for the big unveil.

Aggregations:

Aggregations in Power BI ensure that your aggregated table will always be used at the right time, and in the right place. This is what it all boils down to.

Setting up aggregations in Power BI adds an additional layer of logic to your data model which replaces items in your queries to your detail table with with items querying your aggregated table whenever possible. You work with your aggregate table hidden, Power BI manages it for you. It takes the guessing game out of which tables to use; there will only be one to choose from.