Comparing Semantic Modelling Options in Fabric

[mohsinr12]

Choosing the right Semantic Modeling in Fabric for Power BI & Excel workloads

Power BI in Fabric provides developers with multiple options for selecting a semantic layer. Various considerations can help you choose the appropriate semantic modelling strategy to meet your business objectives. The choice of semantic modelling strategy involves several factors. This discussion will examine the implications of each mode for both BI and Excel users.

Import Mode is a commonly used option that offers several advantages for users. Refer to the link below for more information about Import Mode.

Semantic model modes in the Power BI service - Power BI | Microsoft Learn

When to Use the Import Mode:

• Performance: Ideal for high performance and fast queries due to in-memory storage.
• Calculations: Efficient for complex calculations, aggregations, or transformations.
• Data Volume: Suitable for data within Power BI's in-memory storage limits (see table below).
• Refresh: Best for periodic data refreshes like daily or hourly updates.
• Customization: Supports full DAX functionality for flexible model customization.

Import mode could also reduce costs by caching datasets in Fabric memory, avoiding expensive queries to back sources, especially on per-query cost platforms. Be aware of SKU limits and guidelines for Power BI workloads. Test your models to ensure they fit within the memory and performance limits of your chosen SKU.

Composite Mode & Direct Lake Mode

The Power BI Composite model enables the combination of data from multiple sources, utilizing both import and Direct Query storage modes within one dataset. This facilitates leveraging the capabilities of both models, offering a versatile approach to data modeling.

Direct Lake mode

In Power BI is a feature that permits direct querying of data stored in a data lake, such as OneLake, without the necessity of importing the data into Power BI. This mode uses cloud storage and allows real-time data access, which is suitable for situations where data is frequently updated or too large for efficient importation.

When to Use the Composite Model or Direct Lake Mode:

Composite model:

• Combining Real-Time and Historical Data: For scenarios requiring the integration of real-time data (via DirectQuery) with historical data (using import), the composite model is highly suitable.
• Large Datasets: When confronted with extensive datasets that cannot be fully imported due to memory constraints, it is advisable to employ DirectQuery for the majority of the data while importing smaller, frequently accessed datasets.
• Performance Optimization: By importing frequently queried data and utilizing DirectQuery for data accessed less frequently, performance can be optimized and load times reduced.
• Data Security and Compliance: In instances where certain data sources have stringent security requirements that preclude data importation, DirectQuery can be used for those sources while other data are imported.
• Complex Data Models: For data models necessitating the combination of multiple data sources with varying storage modes, the composite model offers the required flexibility.

Direct Lake Mode:

• No Scheduled Refresh Needed: Eliminates the need for scheduled refreshes, as Power BI updates automatically when new data is written to OneLake.
• Optimize Queries at Source: Utilizes Delta tables for efficient querying and applies V-Order indexing on frequently queried columns to optimize read performance.
• Partitioning Strategy: Implements effective partitioning in Fabric Lakehouse to enhance query performance by reducing the amount of data scanned during retrieval.

Feature/Functionality	Direct Lake	Direct Query	Import
Data Storage	Connects directly to Delta tables stored in OneLake	Queries data directly from the source without importing it into Power BI	Data is imported into Power BI and stored in a compressed format
Performance	Optimized for large volumes of data, enabling fast loading into memory and high-performance queries	Dependent on the performance of the underlying data source and network latency	High performance due to in-memory storage of data
Data Refresh	Eliminates the need for traditional data refreshes, performs low-cost framing operations	No need for data refreshes, queries are executed in real-time against the source	Requires regular data refreshes, which can be resource-intensive
DAX Functionality	Full DAX functionality	Limited DAX functionality, especially for time intelligence and complex calculations	Full DAX functionality
Real-Time Data Access	No	Yes	No
Use Case	Ideal for large, dynamic datasets requiring frequent updates and high performance	Suitable for scenarios where real-time data access is crucial, and the data source can handle the query load	Suitable for scenarios where data changes infrequently and high query performance is needed

 Key Differences Between Direct Lake Mode and Composite Model

Feature	Direct Lake Mode	Composite Model (Import + Direct Query)
Data Storage	Reads directly from Microsoft Fabric OneLake Delta tables	Uses Import Mode (cached data) + Direct Query
Performance	Faster than Direct Query, eliminates refresh	Requires scheduled refresh for Import Mode
Query Execution	Cached queries but live updates when needed	Tries Import Mode first, falls back to Direct Query if needed
Data Updates	Real-time updates without refresh	Cached data needs refresh to update
Excel Behavior	When the requested data is not available in the in-memory cache, DirectQuery is triggered in Excel, reducing performance benefits.	Excel defaults to DirectQuery when using Composite Model due to its inability to leverage Power BI’s Import Mode efficiently.
Best Use Case	Large datasets in Fabric OneLake, Power BI reports with real-time needs	Balances performance & flexibility for hybrid scenarios

What about excel users?

In organizations, many users access Power BI datasets through Excel. This is a significant source of self-service BI workloads. Therefore, it is crucial to consider this when designing a BI solution, as end users will utilize Excel for data analysis from the Power BI Semantic model. Although all three semantic models can be used by Excel users, composite models and direct lake semantic models have faced challenges and limitations that should be considered if both BI and Excel users are involved.

1. Excel’s Limitations with Composite Model & Direct Lake Mode

Issue	Impact on Composite Mode	Impact on Direct Lake Mode
Forces Direct Query	Always defaults to Direct Query	May default to Direct Query if cache is bypassed
Ignores Import Mode caching	Cannot leverage Import Mode in Excel	Excel does not fully leverage Power BI’s cached datasets, requiring performance testing.
Filters trigger live queries	Every filter applies new Direct Query request	Excel does not leverage Power BI’s in-memory cache efficiently, often defaulting to live queries (DirectQuery).
Hierarchies behave unpredictably	No clear behavior for Excel-created hierarchies	Needs further testing

Key Takeaway:

• Excel struggles to differentiate between Import, Composite, and Direct Lake models.
• Hierarchies in Excel are problematic due to Power BI’s optimization for report visuals, not pivot tables.
• Regional datasets in Composite Mode act as Direct Query.
• Testing is needed to verify caching behavior in Direct Lake Mode.
• Direct Lake Mode is faster for cached summaries but relies on Direct Query for detailed data.
• Composite Model starts in Import Mode but often triggers Direct Query in Excel.
• Applying filters in large datasets forces Direct Query in both modes.
• Excel hierarchies cause Power BI to trigger Direct Query in both modes.
• Transaction-level drilldowns slow down performance in Direct Lake and Composite Model.
• If Direct Lake behaves like Direct Query in Excel, caching is ineffective.
• If Composite and Direct Lake models act the same, Excel may overlook Power BI's optimizations.
• Pre-aggregations in Power BI could reduce Direct Query triggers when working with summary data.

Possible Solutions for Improving Excel Performance

• Use Pre-Aggregation Tables in Power BI: Cache tables in Import Mode before live queries.
• Optimize Queries for Excel: Apply filters to reduce table scan sizes.
• Partition & Index Direct Query Tables: Enhance performance if Direct Query is necessary.
• If Excel forces Direct Query, consider additional caching techniques.
• Test hierarchies separately to understand their behavior in Excel.
• Optimizations like pre-aggregations and better query structuring can help mitigate issues.

Comparison Table: Choosing the Right Semantic Model Based on User Preference

Considerations	Import Mode	Composite Mode	Direct Query	Direct Lake Mode
Usage Type	🟢 Best for Power BI and Excel users	🟡 Power BI Reports with some Excel interaction	🟡 Best for Excel-driven Analysis (Live queries, but slower performance)	🟢 Best for Fabric OneLake users
Primary Use via Power BI Reports	🟢 Fastest for reports (Preloaded in-memory for quick insights)	🟡 Yes, but some Direct Query fallback	🟡 Yes, enables real-time data access but may be slower depending on query complexity and source performance	🟢 Yes, optimized for Power BI in Fabric (Leverages OneLake directly)
Primary Use via Power BI + Some Excel	🟢 Fully supported (Preloaded, no dependency on live queries)	🟡 Good, but Excel may trigger Direct Query mode	🟡 Best suited for Excel users (Live queries, but may impact performance)	🟡 Excel forces Direct Query fallback (Loses some Direct Lake benefits)
Primary Use via Excel (Ad-hoc Analysis)	🟢 Works well (Data preloaded, no live queries needed)	🔴 Excel forces Direct Query	🟡 Best for real-time queries	🔴 Limited support, behaves like Direct Query
Data Refresh	🟡 Scheduled refresh supported	🟡 Mix of Import (cached) + Direct Query	🟢 Live queries, no refresh needed	🟢 No refresh needed, queries OneLake directly
Data Volume Support	🟢 Best for small to medium datasets	🟡 Handles large datasets with aggregations	🟡 Handles large datasets but slower	🟡 Handles large datasets but requires efficient caching strategies
Performance	🟢 Fastest (In-memory cache)	🟡 Balanced (Import + Direct Query fallback)	🟡 Dependent on source performance	🟢 Optimized in Power BI. 🔴 Excel forces Direct Query fallback.
Excel Behavior	🟢 Works fine, as data is preloaded	🔴 Excel treats as Direct Query only	🟡 Works as expected, but slower than Import Mode	🔴 Excel forces Direct Query, limiting Direct Lake benefits