Composite models in Power BI: combine import and DirectQuery

What are Composite Models in Power BI

Composite models in Power BI give you the flexibility to bring together different data connection methods in a single model, combining the benefits of both Import and DirectQuery storage. This means you can build reports that use both cached data and live connections, opening up new ways to analyze your business that simply weren’t possible before. It’s important to know that, before composite models came along, Power BI users could only use one storage mode per dataset, which really limited what you could do and often meant finding complicated workarounds just to bring in data from multiple systems.

For many organizations—especially those that need both historical and real-time data—composite models have been a game changer. Imagine a retail business that wants to look at past sales trends (using Import mode for speed) right alongside current inventory levels (using DirectQuery for live updates). Without composite models, you’d have to create separate reports or build complex ETL processes, which would slow things down and add extra maintenance.

A composite model lets you integrate data from all sorts of sources, like SQL Server databases, on-premises Analysis Services, and cloud options such as Azure Analysis Services. By working in Power BI Desktop, you can blend data from these different systems, set up relationships between them, and choose the right storage mode for each table. This way, your reports can balance performance, data freshness, and scale—tailored to what your business needs at the moment.

Something else to keep in mind: composite models also make data governance easier by letting you define business logic right inside enterprise semantic models. This means you can standardize rules, calculations, and security roles across departments and data sources, so everyone in the company is working from the same playbook.

The move to composite models is a major step forward for Power BI’s architecture. By supporting features like many-to-many relationships and user-defined aggregations, composite models provide a strong foundation for enterprise semantic models—helping organizations promote consistency, governance, and reuse in their business intelligence efforts.

Understanding Storage Modes in Composite Models

In Power BI, composite models use three main storage modes: Import, DirectQuery, and Dual. Each one brings its own set of strengths and trade-offs when it comes to performance, how fresh your data is, and how flexible your modeling can be.

Choosing the right storage mode for each table is key in composite models. Consider:

• Data volume
• Refresh frequency
• Real-time insight needs
• Licensing (Power BI Pro or Premium may be required for some features)

Creating Your First Composite Model

To build your first composite model in Power BI Desktop:

• Connect to multiple data sources (e.g., SQL Server, Azure Analysis Services, cloud CRM).
• Mix Import and DirectQuery connections within the same report for flexible integration.

For example, a business analyst might connect to a cloud-based CRM system with DirectQuery for the latest customer info, while also importing historical sales data from a data warehouse. This setup delivers both real-time and historical insights in one workspace.

• Add tables to your model and set their storage modes (Import, DirectQuery, or Dual) based on performance and usage.
• Document storage mode decisions, especially for Dual tables, and monitor their performance as your model grows.

Establishing relationships between tables from different sources is crucial. Power BI supports many-to-many relationships, enabling advanced analysis but requiring attention to data integrity and query execution.

• Test your model with realistic data and queries to identify bottlenecks or misconfigurations early.
• Address challenges like configuring relationships across storage modes, managing security, and avoiding ambiguous joins.

Dual Storage Mode: The Bridge Between Import and DirectQuery

Dual storage mode is a standout feature in composite models. It allows a table to function as both Import and DirectQuery, automatically adjusting based on report usage. This is especially useful for dimension tables used in filters and slicers.

Example:

• If a Product table (set to Dual) is used with Import tables, Power BI retrieves data from memory for speed.
• If combined with a DirectQuery fact table (like live sales), Power BI queries the product data directly from the source for consistency.

This dynamic switching is managed by Power BI’s query engine, helping you avoid data duplication and the need for separate dimension tables.

Dual storage mode also:

• Improves memory efficiency
• Routes queries intelligently
• Supports scenarios where some data must always be current (e.g., compliance data), while other data can be cached

Note: Dual mode can add complexity and, if misconfigured, may cause performance issues or inconsistencies. Plan, test, and use Power BI diagnostics to ensure proper behavior.

Performance Optimization with Composite Models

To optimize composite model performance:

• Use aggregations (summary tables in Import mode) to handle common queries quickly.
• Only query DirectQuery tables for detailed drilldowns.

Example:
A retailer creates an aggregation table summarizing daily sales by region and product. High-level trends use Import-mode aggregations for instant results; transaction details trigger DirectQuery.

Other optimization strategies:

• Configure efficient relationships
• Minimize cross-source joins
• Use DAX calculations wisely
• Manage memory carefully with mixed Import/DirectQuery tables

Monitor performance with Power BI’s Performance Analyzer or SQL traces on source databases. Plan data refresh strategies to keep data current without overloading systems. Incremental refresh (in Power BI Premium) is especially helpful for large Import tables.

Enterprise Use Cases and Implementation Strategies

By integrating power platform consulting services, businesses can enhance their analytical capabilities with seamless data integration and governance. This strategic approach ensures that enterprise systems are both comprehensive and adaptable to evolving business needs.

Composite models excel in enterprise settings where you need to:

• Extend centralized semantic models with departmental or operational data
• Support self-service analytics while maintaining governance and consistency

Common scenarios:

• Combine sales data (Import) with real-time inventory (DirectQuery)
• Merge corporate financials with external market feeds
• Integrate data quickly after mergers or acquisitions

Implementation strategies:

• Start with Analysis Services or Azure Analysis Services as your foundation
• Add DirectQuery or Import tables as needed for agility
• Set up data stewardship processes to manage definitions, calculations, and security as your business evolves

Many organizations align governance with frameworks like COBIT or ISO/IEC 27001 to meet regulatory and internal standards.

Limitations and Considerations

While composite models offer many advantages, keep these in mind:

• Not all data sources are supported for DirectQuery in composite models
• Some advanced modeling features are unavailable when mixing storage modes
• Relationships between tables from different sources often default to many-to-many and may have restrictions
• Calculated columns and some DAX functions might not work on DirectQuery tables
• Security and governance become more complex across multiple systems
• Performance and maintainability can be affected as model complexity grows

For regulated industries, ensure compliance with data security and privacy requirements (e.g., HIPAA, GDPR) across all sources. Set up Row-Level Security (RLS) and coordinate with IT and compliance teams.

Best Practices for Composite Model Success

• Collaborate with business stakeholders to define report/dashboard requirements
• Choose the right mix of Import, DirectQuery, and Dual tables
• Use Dual mode wisely for shared dimensions
• Implement aggregations to reduce live queries
• Regularly review usage and performance, cleaning up unnecessary tables/relationships
• Document relationship setups, especially for cross-source joins
• Monitor and adjust storage modes, relationships, and DAX calculations as needed
• Leverage Power BI tools like Model View, Performance Analyzer, and usage metrics
• Set clear governance policies for data access, quality, and permissions
• Provide training and support for your team
• Consider establishing a Center of Excellence (CoE) for Power BI to share best practices and maintain documentation

By following these guidelines, you’ll be well on your way to building scalable, high-performing composite models that deliver powerful analytics and real business value across your organization.

Frequently Asked Questions

What are composite models in Power BI?

Composite models allow you to combine Import and DirectQuery storage modes in a single Power BI dataset, enabling flexible reporting with both cached and live data.

Can I use any data source with composite models?

Not all data sources are supported for DirectQuery in composite models. Check Microsoft’s documentation to confirm compatibility.

What is Dual storage mode?

Dual storage mode lets a table act as Import or DirectQuery depending on the query context, optimizing performance and data freshness.

How do I optimize performance in composite models?

Use aggregations, efficient relationships, and incremental refresh. Monitor performance with Power BI’s built-in tools and adjust your model as needed.

Are there limitations with composite models?

Yes, such as limited DAX functions on DirectQuery tables, relationship restrictions, and increased complexity in governance and security. Plan your architecture accordingly.