Actiknow
Business Intelligence & Analytics

Power BI Composite Models: When Import and DirectQuery Belong in One Semantic Model

Learn when to combine Import, DirectQuery, Dual and Hybrid tables in Power BI, and how to manage relationships, speed, security and governance.

Business intelligence analyst reviewing a power bi dashboard in a modern office
Contents hide

The business problem: fast historical reporting and fresh operational data

A CFO wants a monthly performance dashboard to open immediately. An operations director needs to see open orders and stock changes throughout the day. Both want to filter by product, region and customer using the same definitions. The obvious temptation is to make everything live. That can make a fast, stable executive report slower without improving the decisions it supports.

Power BI composite models offer a more selective approach. Teams can import data that benefits from in-memory performance and use DirectQuery for tables where source-side access or freshness is genuinely required. The flexibility is valuable, but the trade-off is not free: relationships, query plans, refresh timing, security and operational costs become harder to reason about.

The right question is not whether the product supports mixed storage. It is whether a documented reporting requirement justifies the complexity, and whether the resulting model still produces trustworthy business metrics.

Finance and analytics professionals reviewing power bi reports together in an office

What is a Power BI composite model?

A composite semantic model contains more than one source group. Common examples mix Import and DirectQuery tables, or combine DirectQuery connections to multiple sources. Importing tables from several systems does not, by itself, create a composite model: imported tables belong to the Import source group. Newer combinations involving Direct Lake exist, but their authoring paths and restrictions differ from the traditional Import-plus-DirectQuery pattern.

Import stores model data in Power BI’s in-memory engine and refreshes it on a defined schedule. DirectQuery sends relevant queries to the underlying source when a report is used. Dual tables can act as cached tables for some queries and participate in DirectQuery paths for others. Hybrid tables combine imported partitions with a DirectQuery partition, typically for recent data, where the required feature and capacity conditions are satisfied.

Microsoft’s composite-model feature documentation describes supported combinations and tooling. Confirm the current connector, tenant and licensing restrictions before committing to a design.

Decide at the metric level before choosing storage

Start with four business requirements: required freshness, acceptable response time, data volume at the required grain, and the security boundary. A metric refreshed nightly may be perfectly suitable for executive decisions even if the underlying operational system changes every minute. Conversely, a warehouse exception queue may need more recent records than a scheduled import provides.

Write the requirements in operational terms. ‘Net sales through the last closed accounting day’ is a meaningful contract. ‘Real-time sales dashboard’ is not. Similarly, ‘95% of typical region-filter interactions within five seconds during normal concurrency’ is testable; ‘fast performance’ is not. Use these requirements to decide which tables need DirectQuery.

Do not assume that adding a DirectQuery fact makes every visual live. Visual caches, source update cadence and the user’s refresh behavior can affect what appears. Each important metric should have a stated as-of time and owner.

Data analyst examining business intelligence dashboards at an office workstation

The four storage modes in practice

Import: the default for stable, reusable analytics

Use Import for historical facts, conformed dimensions, complex calculations and executive dashboards whose refresh schedule meets the business need. It usually gives the strongest interactive performance and broad modeling flexibility. The cost is a copied dataset, refresh operations and potentially larger capacity requirements. Import is often the simplest answer even when a source offers DirectQuery.

DirectQuery: use selectively for large or frequently changing facts

Use DirectQuery for specific fact tables when data must remain in the source, when full import is impractical, or when a business question genuinely requires a shorter freshness interval. Each report interaction can trigger source work. Query latency, indexing, partition pruning, network and gateway configuration, concurrency and source workload all affect the user experience.

Dual: a bridge for dimensions in the same source group

Dual storage can be effective for dimensions that need to work with imported queries and with DirectQuery facts from the same source. Date, Product and Location are typical candidates. However, Dual does not turn unrelated systems into one source group. Cross-source relationships still require special attention, and the dimension cache must be refreshed appropriately.

Hybrid: recent records queried, history imported

A Hybrid fact table can keep historical partitions imported and use DirectQuery for the newest partition. This may help with high-volume time-series data, but it introduces partition management, incremental refresh policy, and compatibility considerations. A Hybrid table is a particular partitioning design, not a generic synonym for composite models.

Illustrative design: settled sales plus open orders

Consider a hypothetical distributor. Its finance warehouse publishes approved, settled sales each night. Its order management system updates unfulfilled orders during the day. Leadership wants a single report with closed-period revenue and margin, plus a current operational view of late orders. This is an architecture illustration, not an Actiknow client case study or a measured performance claim.

The imported layer might include FactSalesClosed, FactReturnsClosed, DimDate, DimProduct and DimRegion. It holds approved measures such as Net Sales and Gross Margin. A narrow DirectQuery view, FactOpenOrders, could expose current open order units and late-order counts. The DirectQuery view should be designed at a documented grain, for example one row per open order line, or at an approved aggregate grain if detailed interaction is not needed.

The first rule is to avoid mixing economic states. Closed sales and open orders should not silently become a measure called ‘Total Revenue’. One represents recognized or posted activity; the other is a pipeline or fulfillment state. If leaders want a combined measure, define the inclusion rules and double-count prevention explicitly.

The second rule is to make timing visible. A subtitle such as ‘Closed sales through 06 Nov; open orders queried on interaction’ is more useful than an unqualified ‘Updated now’. The third is to keep product and region keys conformed so that a filter does not silently exclude live records missing from an imported dimension.

Cross-source relationships are not ordinary star-schema relationships

Within one source group, a standard one-to-many relationship can be evaluated as a regular relationship. Across source groups, Power BI uses limited relationships, even if the model diagram shows a one-to-many cardinality. Limited relationships behave differently at query time, including when related keys do not match. They can also generate expensive filters or subqueries.

Imagine a new SKU exists in the DirectQuery inventory table but has not yet reached the imported Product dimension. A visual grouped by Product Category may omit the new SKU’s inventory. The source-side total can therefore exceed the visible category total. This is a relationship and synchronization issue, not necessarily a bad SUM expression.

High-cardinality cross-source keys create another risk. A filter involving tens of thousands of product or customer identifiers may require Power BI to transmit large sets of values to the source. Long text keys make the query larger still. Microsoft’s composite model guidance recommends low-cardinality cross-source relationship columns, simple relationship paths and, where possible, dimensions located with their DirectQuery facts.

Test relationships as data contracts: count unmatched keys in both directions; compare source totals to visuals with and without dimension grouping; test nulls and newly created keys; and verify that filters do not produce ambiguous paths. A relationship that can be drawn in Desktop is not necessarily an architecture that should be deployed.

Analytics team discussing power bi reports and data relationships in a conference room

Model the business semantics before writing DAX

Every fact table needs a grain statement. Examples include one row per invoice line, one row per open order line, and one row per daily inventory snapshot. The model also needs conformed keys, approved definitions, data cutoff rules and a clear owner for each KPI. These decisions are especially important when historical data is imported at one grain and operational data is queried at another.

Keep the first measures simple. For a validated closed-sales fact, illustrative DAX is Net Sales = SUM(FactSalesClosed[NetAmount]). For a live open-order fact, Open Order Units = SUM(FactOpenOrders[OpenUnits]). A ratio such as Margin % should use DIVIDE([Gross Margin], [Net Sales]) and have an agreed treatment of zero denominators. These examples assume the underlying columns are already cleansed and reconciled.

A measure catalog should document the definition, source tables, grain, allowed filters, freshness, reconciliation query and accountable business owner. If the model extends an existing enterprise semantic model, prefer centrally governed measures over local copies. Local measures that reference remote measures can behave unexpectedly with calculation groups and cross-source filters; regression tests should cover them.

Performance engineering: follow the complete query path

A slow composite report can be caused by inefficient source SQL, gateway latency, Power Query transformations that do not fold, expensive DAX, high-cardinality cross-source relationships, too many visuals or source concurrency limits. Increasing BI capacity will not repair every one of these problems.

1. Capture a representative baseline

Measure cold and warm page open, a common slicer change, drill-through and simultaneous viewers. Use Power BI Performance Analyzer for visual-level timing and correlate it with database query logs. Record the exact filters, user persona and data snapshot so results are reproducible.

2. Optimize the source and the relationship keys

Push selective filters and sensible aggregation to source views where appropriate. Maintain database statistics and relevant indexes or clustering according to the platform. Prefer stable integer keys over long text joins. Keep DirectQuery Power Query steps foldable; transformations that cannot be pushed down can become expensive or unsupported.

3. Reduce unnecessary queries

A report page with many high-cardinality visuals can produce a large number of source requests. Consider Apply buttons for slicers, remove low-value cross-highlighting and disable unnecessary interactions. Test imported aggregation tables for frequently used summary queries, while preserving DirectQuery detail only where needed. Aggregations require their own refresh and reconciliation controls.

4. Revisit the model before buying more capacity

If a cross-source filter generates huge lists of customer IDs, consider a source-side dimension or pre-aggregated table. If the business only needs hourly data, an incremental Import strategy may be simpler. If strict cross-source consistency is essential, move the integration into a governed warehouse layer rather than asking the report model to perform complex distributed joins.

Microsoft’s DirectQuery documentation explains source latency, cache behavior, query limits and concurrency. Treat these as architecture constraints to test, not troubleshooting footnotes.

Business intelligence professional monitoring dashboard performance and reporting metrics

Freshness is not the same as consistency

A mixed-storage report has at least two clocks: the last successful import refresh and the time a DirectQuery result was retrieved. Upstream data pipelines add their own processing times. A report can be available while two visuals reflect different cutoffs. That may be acceptable, but it must be explicit.

Display metric-specific as-of timestamps and define refresh dependencies. Coordinate Dual dimension refresh with the source changes that introduce new keys. If a business process updates an order from open to closed, make sure the transition does not temporarily count in both metrics or neither, unless that behavior is part of an approved reporting contract.

Use three reconciliation layers: imported facts against approved batch extracts; DirectQuery measures against source-side control queries using the same filters; and cross-source grouped results against unmatched-key counts. A single grand-total comparison is not enough to detect missing categories or inconsistent dimension mappings.

Security, permissions and data movement

Imported data is copied into the semantic model, where workspace permissions and model row-level security matter. DirectQuery may use configured source credentials or, when supported and configured, single sign-on to pass user identity to the source. Do not assume both paths enforce the same rules. Review where RLS is defined, how gateway credentials work and which source policies apply to each table.

Composite queries can transmit filtered values between sources when evaluating relationships. This is important for sensitive identifiers, data residency and contractual restrictions. Review query logs, exported data, connection permissions and the potential movement of values across systems.

Test security with real personas: a finance analyst, a regional manager, an operations viewer and a restricted user. Verify detail, totals, drill-through, export and underlying source access. When extending another Power BI semantic model, confirm tenant settings, Build and Read permissions, and model-chain access. Security tests must use the published service, not only Desktop.

Governance: prevent a second version of every KPI

The long-term risk is metric drift. A sales measure defined in the warehouse, another in a shared semantic model and a third in a local composite model can all carry the same label but use different exclusions or cutoffs. Assign one accountable owner for each business metric, document approved definitions and version changes, and use controlled deployment for model updates.

Monitor not only dashboard responsiveness but also source query volume, data warehouse spend, refresh failures, gateway health, schema changes and access errors. A model that improves freshness while multiplying source cost or producing incomplete category totals has not solved the business problem.

Actiknow’s business intelligence implementation and consulting services cover data integration, modeling, dashboard development and refresh mechanisms. The relevant first step is an architecture and KPI review, not an automatic recommendation to add DirectQuery.

When composite models are the wrong answer

Prefer an Import model if the data fits and refresh timing meets the decision cadence. Prefer a single-source DirectQuery design if most questions require source-side access and the database can sustain interactive queries. Prefer warehouse-side integration if complex joins across multiple operational systems are central to the reporting requirements.

Separate semantic models or pages may also be clearer when historical and operational metrics need fundamentally different freshness and security contracts. A single report is not automatically better if it obscures what the numbers mean.

A four-week pilot with measurable gates

Week 1: define the reporting contract

Choose one business journey and two or three important measures. Document source owners, table grain, freshness, sensitivity, expected users, latency objectives and the baseline performance of the current design. Establish authoritative control queries before building the composite model.

Week 2: build the smallest mixed-storage solution

Add one DirectQuery fact and only the required dimensions. Keep relationships simple, validate query folding, create a small report page and expose as-of timestamps. Record the source SQL and model storage mode for each table. Avoid adding more data sources until the first use case works.

Week 3: test correctness, concurrency and security

Reconcile at total and grouped levels, including unmatched keys and late-arriving dimension records. Test peak concurrency and source load, plus cold and warm cache behavior. Validate RLS and upstream semantic model permissions with restricted users. Log issues with reproducible filter selections.

Week 4: decide whether to scale, simplify or stop

Compare results with the original contract. Retain the composite design only if fresher or larger data materially supports the business decision and accuracy, latency, security and operating cost are acceptable. Otherwise use Import, source-side aggregation, Hybrid partitions where supported, or a better integrated warehouse model.

Executive acceptance scorecard

Accuracy: all critical measures reconcile to approved source queries at agreed cutoffs, and unmatched dimension keys are quantified.

Performance: common interactions meet the agreed service objective under representative concurrency.

Freshness: each metric satisfies its documented as-of requirement.

Security: all personas see only authorized data.

Operability: the team can detect refresh failure, slow source queries and abnormal source cost.

A five-second interaction target may be a reasonable starting objective for many dashboards, but it is a target to validate, not a guaranteed product result. Record baseline and pilot measurements under comparable conditions. A dashboard that is faster but omits new products fails the accuracy gate.

Business intelligence consultant analyzing reporting metrics at an office workstation

Troubleshooting: what to check first

If grouped totals shrink, inspect unmatched keys, source-group boundaries and limited relationships. If a slicer is slow, examine cross-source filter cardinality and generated SQL. If only some users fail, test RLS, gateway identity and upstream model permissions. If numbers change unexpectedly after refresh, compare the as-of time of each table and source extract.

If a local calculation differs from a centrally governed measure, review remote calculation groups, filters and relationship paths. If performance degrades only under concurrency, correlate Power BI capacity behavior with source query history. Diagnose the layer responsible before changing storage modes.

For the presentation and consumption layer, Actiknow’s custom BI dashboards and reporting page describes its approach to integrating data sources and validating reporting accuracy and performance. These controls matter regardless of whether the final semantic model is composite.

Frequently asked questions

1. Are composite models always faster than DirectQuery?

No. Imported or aggregated queries can be faster, but cross-source joins may be slower than a well-designed single-source model. Measure the real workload.

2. Do two imported sources create a composite model?

No. Multiple imported tables still belong to the Import source group. Composite models require supported mixed modes or multiple DirectQuery source groups.

3. Can one Power BI report use Import and DirectQuery?

Yes. Supported composite semantic models can include tables using both modes, subject to connector and feature restrictions.

4. When should a dimension use Dual?

When it must work efficiently with imported queries and DirectQuery facts from the same source group. Validate its cache refresh and relationships.

5. What is a limited relationship?

A relationship without all regular relationship semantics, including cross-source group relationships. It can affect unmatched rows, filtering and query performance.

6. Does DirectQuery guarantee real-time reporting?

No. Source update timing, caches, report refresh behavior and query latency all matter. Define a freshness requirement rather than assuming instant results.

7. Can I extend an enterprise semantic model?

Yes, in supported scenarios. Check tenant settings, permissions, model-chain constraints and the behavior of local and remote measures.

8. Is Hybrid the same as composite?

No. Hybrid describes partitioned Import and DirectQuery storage for a fact table. It is one possible mixed-storage design.

9. Why does a live fact not match an imported dimension?

The dimension may be stale or have missing keys. Compare key sets and timestamps, then test source totals against grouped visual results.

10. Should every business use composite models?

No. Use them when a measurable freshness, scale or governance need outweighs complexity. Many teams are better served by a governed Import model.

Final decision: approve the business case, not the feature

Composite models can be a sensible way to combine fast historical reporting with selected operational queries. They should be chosen because a simpler design fails a clear business requirement, not because mixed storage is technically possible. Begin with a narrow pilot, validate cross-source totals and security, and keep a simpler fallback.

If your team is evaluating a mixed-storage reporting architecture, you can discuss your Power BI architecture with Actiknow. A focused review of KPI definitions, source constraints and actual report workloads can establish whether the additional complexity is justified.

Technical references

Microsoft documentation consulted: Use composite models in Power BI; Composite model guidance in Power BI Desktop; DirectQuery in Power BI. Confirm current product and licensing details for your environment before deployment.