A Power BI Composite Model is a dataset configuration that allows some tables to be stored in Import mode (data physically copied into Power BI’s VertiPaq in-memory engine for fast query performance) while other tables use DirectQuery mode (queries sent live to the source system at report rendering time). Before Composite Models, a Power BI dataset had to be entirely in Import mode or entirely in DirectQuery — a constraint that forced BI architects to choose between query performance (Import) and data currency (DirectQuery). Composite Models eliminate this binary choice: a finance dashboard can store twelve months of historical P&L data in Import mode for instant trend queries while simultaneously connecting to the Oracle EBS GL in DirectQuery mode for current-day transaction visibility — all in a single dataset consumed by a single report.
Composite Model Architecture Patterns
| Table Type | Storage | Query Behaviour | Finance Use Case |
|---|---|---|---|
| Aggregation table (Import) | Pre-aggregated monthly totals in VertiPaq | Sub-second query from memory | 12-month trend; YTD vs prior year comparisons |
| Detail table (DirectQuery) | No stored data — live query to Oracle EBS or SQL Server | Seconds — depends on source DB performance | Current-day GL postings; intraday AP transactions |
| Dimension tables (Import) | Entity, Account, Period hierarchies in VertiPaq | Sub-second — shared by both Import and DQ tables | Filter context for all report slicers and hierarchies |
User-Defined Aggregations
Power BI’s User-Defined Aggregations feature works natively with Composite Models — allowing the BI architect to pre-compute and store monthly or quarterly summaries of the DirectQuery detail table as an Import table, and instructing Power BI to automatically route queries to the aggregation table when the query grain matches the aggregation level. A report visual displaying monthly revenue by entity is served from the fast Import aggregation table; a visual drilling to daily journal-line detail falls through to the DirectQuery source. This automatic routing is invisible to the report consumer — they experience consistent sub-second performance at the summary level and acceptable latency at drill-through without being aware that two different storage modes are being used behind the scenes.
Composite Models and Oracle EBS in GCC Deployments
Composite Models are particularly valuable in GCC enterprise Power BI deployments where Oracle EBS is the source for financial actuals — because EBS’s GL tables contain multi-year transaction history that is expensive to query via DirectQuery at summary grain, but the current-period’s in-progress transactions are needed in near-real-time for daily financial operations reporting. A Composite Model configuration that imports the closed-period GL summaries (fast, pre-aggregated, refreshed daily) and DirectQuery’s the current open period’s transactions (live, no refresh required) gives the finance dashboard both historical performance and current-day visibility without the trade-offs of either pure mode.
What Goes Wrong in Practice
The most common Composite Model design failure is mixing Import and DirectQuery tables without defining user-defined aggregations — relying on Power BI’s automatic query routing to handle the transition between summary and detail queries. Without explicit aggregation definitions, Power BI may fall through to DirectQuery for queries that could have been served by the Import tables, producing unpredictable query performance and user experience inconsistency. Composite Models require explicit aggregation design alongside the storage mode configuration.
How Loop Wise Solutions Designs Composite Models
We design Composite Models with explicit aggregation table specifications — documenting which grain levels are pre-aggregated in Import tables, which detail queries fall through to DirectQuery, and the expected query routing behaviour for each report visual. Aggregation table designs are validated by running representative report queries and confirming through Performance Analyzer that the expected routing occurs.
