Composite Models: Combining DirectQuery & Import Modes

Historically in Power BI, a data model had to be strictly Import Mode or strictly DirectQuery Mode. In 2018, Microsoft revolutionized Power BI architecture with the release of Composite Models, allowing a single dataset to seamlessly blend in-memory Import tables with live DirectQuery connections.

1. The Architecture of Composite Models

In an enterprise composite model, data architects structure tables according to their update frequency and data volume:

2. Table Storage Modes in Composite Models

Each table in a composite model can be set to one of three storage modes in the Model Properties pane:

1. 1
   Import: Data is fully loaded into VertiPaq memory.
2. 2
   DirectQuery: Data remains in the source database; queries are generated on-the-fly.
3. 3
   Dual: The table can act as either Import or DirectQuery depending on the context of the query.

The Power of Dual Storage Mode

When an Import table queries a Dual table, the Dual table acts like an Import table (avoiding cross-network SQL queries). When a DirectQuery table queries a Dual table, the Dual table acts like a DirectQuery table (avoiding cross-boundary data joins). Setting dimension tables to Dual is an industry best practice in composite models!

3. Managing Data Source Privacy & Cross-Source Joins

When combining data from an on-premises SQL Server and a cloud data source (e.g., Salesforce):

• Power BI may need to send data from one source to another to evaluate a join.
• This triggers Data Source Privacy Levels:
• Private: Sensitive corporate data (e.g., HR salaries). Cannot be passed to any other data source.
• Organizational: Internal enterprise data. Can be joined with other Organizational sources.
• Public: Publicly accessible data (e.g., weather feeds, census stats). Can be combined freely.