Leveraging Aggregation Tables

DirectQuery mode is a great tool for overcoming the scalability issues of Import mode, or for providing real-time reporting to business users. However, DirectQuery can be slow and place a lot of strain on the backend server system, since every visual refresh and interaction causes queries to be sent to the source system. Aggregation tables and dual-mode storage tables were designed to fix the speed issues associated with using DirectQuery against large datasets in the millions, billions, and trillions of rows. In essence, aggregation tables pre-aggregate data and store it in Import mode. Power BI uses these aggregation tables behind the scenes when possible to limit the number of queries sent to the data source.

This recipe demonstrates how to create and configure an aggregation table for use in Power BI reports.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power BI Desktop.
2. Create a DirectQuery query...