Scaling with aggregations and composite models

So far, we have discussed how Import mode offers the best possible speed for semantic as Direct Lake from Fabric capacity is catching up. However, sometimes, high data volumes and their associated refresh limitations may lead you to select DirectQuery mode instead, especially for large detail data in a fact table. We may, at this point, return to Chapter 5, Optimization for Storage Modes, to understand the storage options for Power BI semantic models.

We also discussed how the AAS engine is designed to aggregate data efficiently because Business Intelligence (BI) solutions typically aggregate data most of the time. When we use DirectQuery, we want to push these aggregations down to the source where possible to avoid Power BI having to bring all the data over to compute them. With very large tables containing tens of millions or billions of rows, these aggregations can be costly and time-consuming, even when the source has been optimized...