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, as user interactions with Power BI reports can generate high volumes of queries, DirectQuery Power BI solutions can overwhelm the resource limits of many data sources, which can entail query queuing and poor user experiences. Aggregation tables and dual-mode storage tables were designed to fix the performance issues associated with using DirectQuery against large semantic models in the millions, billions, and trillions of rows.

Aggregation tables provide Power BI with a more performant, less resource intensive alternative to processing queries. As queries from report visuals are received by the semantic model, the model determines whether to use an aggregation table and its related tables or if, given the columns and metrics required, it’s necessary to query the larger, more granular source...