Power BI Composite Models and Aggregations: Scale to Billions of Rows
Build high-performance composite models with aggregation tables for sub-second queries on billion-row datasets in Power BI.
Composite models with aggregations enable Power BI to query billions of rows with Import-like performance. This guide covers aggregation table design, storage modes, and query routing. Our enterprise data architecture implements composite models processing 50 billion+ row fact tables with sub-second visual rendering. Scale Power BI beyond traditional limits without compromising user experience.
Frequently Asked Questions
What are aggregations in Power BI and when should I use them?
Aggregations are pre-summarized Import mode tables that Power BI automatically queries instead of hitting large DirectQuery detail tables. Example: 10 billion row FactSales (DirectQuery) + 100 million row AggSales_Daily (Import with daily granularity). User queries sales by month—Power BI routes to AggSales_Daily (fast Import query) instead of FactSales DirectQuery. User drills to transaction detail—Power BI automatically switches to FactSales DirectQuery. Use aggregations when: (1) Fact tables exceed 1 billion rows making Import impractical, (2) Most queries are summary level (80% monthly/daily aggregates, 20% detail), (3) DirectQuery performance unacceptable (multi-second visual rendering). Setup: create aggregation table matching fact table schema but summarized (SUM, COUNT, MIN, MAX), set storage mode to Import, define aggregation mappings (GroupBy columns match dimensions, Detail Table points to source). Power BI Aggregation Manager auto-detects when to use aggregations—no DAX changes needed. Performance improvement: 10-100x faster for aggregate queries. Cost: aggregation storage (typically 1-5% of detail table size), refresh time for aggregation tables. Best practice: start with date granularity aggregations (daily, monthly), add product/customer aggregations based on query patterns. Monitor using Performance Analyzer—verify queries hit aggregations not detail table.
How do I create aggregation tables in Power BI composite models?
Aggregation creation workflow: (1) Identify slow DirectQuery tables and common query patterns via Premium Metrics app, (2) Create aggregation table in source database or Power Query—SELECT Date, ProductKey, CustomerKey, SUM(Amount) as TotalAmount FROM FactSales GROUP BY Date, ProductKey, CustomerKey, (3) Load aggregation as Import mode table in Power BI model, (4) Configure aggregation mappings: Model → Manage Aggregations → select aggregation table → map columns to detail table (GroupBy for dimensions, Sum/Count/Min/Max for measures, DetailTable=FactSales), (5) Set detail table to DirectQuery storage mode, (6) Test—query should automatically use aggregation when applicable. Multiple aggregation layers possible: AggSales_Daily (Import), AggSales_Monthly (Import), FactSales (DirectQuery)—Power BI chooses most detailed aggregation matching query. Automatic vs manual: Power BI auto-creates aggregations in some scenarios (Direct Lake automatic aggregations in Fabric), manual creation gives more control. Validation: use DAX Studio or Performance Analyzer to verify query plan shows aggregation table usage. Common mistakes: (1) Aggregation grain too detailed (daily when monthly sufficient)—wastes Import storage, (2) Missing columns—query cannot use aggregation if filters columns not in aggregation, (3) Incorrect mappings—GroupBy vs Sum confusion causes wrong results. Testing: compare query results with aggregations enabled vs disabled—should return identical results, just faster. Document aggregation strategy for maintenance—future developers need to know which tables are aggregations.
What is the difference between composite models and dual storage mode?
Composite models allow mixing storage modes (Import, DirectQuery, Dual) within single Power BI dataset—previously all tables same mode. Dual storage mode is specific table configuration in composite models. Dual table behavior: acts as Import when queried alone or with other Import/Dual tables, acts as DirectQuery when queried with DirectQuery tables. Use case: dimension tables set to Dual—join to Import fact tables as Import, join to DirectQuery fact tables as DirectQuery. Example: DimProduct (Dual) + FactSales_Summary (Import) + FactSales_Detail (DirectQuery). Query sales summary by product—DimProduct acts as Import (joins to FactSales_Summary Import). Drill to detail—DimProduct acts as DirectQuery (joins to FactSales_Detail DirectQuery). Benefits: (1) Dimension tables loaded once (smaller than facts), (2) Flexible join behavior, (3) Reduced model complexity. Gotcha: Dual tables count toward Import storage and refresh time—not free. When to use Dual: shared dimension tables in composite models with mixed storage modes. When to use DirectQuery for dimensions: dimensions larger than capacity memory, real-time dimension updates required. Best practice: dimension tables Dual, fact tables Import (aggregations) or DirectQuery (detail), relationship between facts and Dual dimensions seamless. Alternative: separate Import and DirectQuery models—cleaner architecture but requires two datasets and limited cross-querying. Composite models with Dual storage mode provide best of both worlds—unified model with flexible performance optimization.