Greg Beaumont's Data & Analytics Blog

Fabric (Power BI) semantic models are a pivotal part of the Fabric IQ layer and are critical components for AI and analytics solutions on the Fabric platform. Too often, customers attempting to optimize query performance and cost are forced to choose between Import, DirectQuery, or Direct Lake mode for their Fabric and Power BI semantic models. This post explains a method that you can leverage to get the best of Import and DirectQuery on the same fact table. I am calling this design a “Hybrid Columns” model, and the technique uses existing generally available features without a hack or a workaround.

Use Case

Import or Direct Lake models usually offer the best query performance for the vast majority of semantic model designs, but certain niche scenarios are better in DirectQuery mode:

• High cardinality fields on very large fact tables such as GUIDs, free text comments, session IDs, Date/Time, latitude/longitude can result in large storage footprints with Import Mode (or Direct Lake). These are often long text values that are rarely repeated on the table.
• Filter context in a report for a large table generating a row-level text evaluation such as searching partial text on a degenerate dimension. Example would be filtering for a partial last name, searching for a specific word in a comments column, or manually entering in a partial claim ID to a filter search field with a “contains” context.
• Import (or Direct Lake) post-compression model size is large due to numerous columns, high cardinality, and high volumes of data.

What if you could have the best of both worlds with hybrid columns in a fact table and you no longer needed to compromise? I’m not referring to hybrid tables (which is also a supported feature for separate Import and DirectQuery partitions), but entire fact tables that will switch to DirectQuery mode when specific columns are used in queries that will run faster in DirectQuery mode. In short, you can have most of your queries and visuals run in Import mode and then DirectQuery mode will run when you need it for specific troublesome columns.

Best yet, creating a hybrid columns architecture uses the generally available Aggregations feature for Fabric and Power BI semantic models. I’m calling this architecture “Hybrid Columns” because it uses the Aggregations feature in a manner slightly different from the intended use case for the feature. I wrote up a deep dive on Aggregations per the intended use cases a few years ago at this link. Another way to describe this hybrid columns technique would be “non-aggregated Aggregations.”

Architectural Details

Here’s a diagram to help visualize the differences between Import (or DirectLake), DirectQuery, Composite, Hybrid Table, and Hybrid Column models:

As I was creating the content for this post, Microsoft architect James Serra published a blog post detailing the differences between Import mode, Direct Lake mode, DirectQuery mode, Composite models, and Hybrid tables. He also covers the intended use of Aggregations. The post can be found at this link.

The “Hybrid Columns” architecture that I am detailing in this article has not been traditionally included in Power BI and Fabric documentation, but it is a re-purposing of the Aggregations feature. If we were to zoom in on diagram 0.1 above, a hybrid column fact table can be visualized as this:

Hybrid columns for a fact table in a star schema would appear to the end user as a single table, and the content viewers would not know that some queries are running in DAX while others are running in SQL. I’ll show how it really works further down, but to the end user the design would appear as this:

What’s actually happening within the semantic model is that there are two versions of the fact table:

• The visible version is in DirectQuery mode and contains all of the columns, including the problematic columns for import models (i.e. GUIDs or free text comments) which will trigger a DirectQuery SQL query when part of the query.
• The hidden Aggregation table which is in Import mode at the same grain as the visible DirectQuery version, and contains the columns which work well in import mode.

The visible DirectQuery table will display to the end users, but the semantic model will query the hidden Aggregation Import table unless the problematic columns are used in a query. Here’s how the design appears from an engineering perspective. Note that for my descriptive naming convention Hybrid Columns, the grain of both tables is the same (same number of rows):

Again, the only difference between the concept I am calling “Hybrid Columns” and the traditional use of the “Aggregations” feature is that the grain of the fact table is the same for Hybrid Columns. The direct query version of the fact table also contains the problematic columns that could cause issues for an import mode table. If possible, rolling everything up to a higher grain for the hidden import table (intended use case of the Aggregations feature) will provide even better performance for queries and also result in smaller semantic model storage footprints per my past article at this link.

What about Direct Lake mode? Per the documentation at this link, Direct Lake mode for semantic models does not currently support the Aggregations feature at the time of this article. The hybrid columns architecture in this article is not intended to be a primary or frequently used architectural pattern. Instead, the intent is that hybrid columns can be an option to solve the issues listed earlier in this article. Direct Lake mode can switch to the SQL endpoint under certain circumstances per the documentation at this link, but this capability is different from the hybrid columns method. If you are running into performance issues with a Direct Lake semantic model similar to those discussed in this article, converting it to a hybrid columns design that uses DirectQuery against the Lakehouse or Warehouse SQL endpoint with a hidden Import mode Aggregation might be an option.

A visualization of the expected benefits that a Hybrid Columns architecture could provide to a solution with some problematic queries is below. Notice that Import Models are very fast until a problematic query is introduced which does a partial text search on a large degenerate dimension column. The DirectQuery model handles the problematic query, but the other queries are slower than Import Mode. The Hybrid Columns architecture provides a “goldilocks option” which has the best of both model types:

Testing

For those of you who are experienced semantic model developers, you should immediately recognize the benefits of running Import mode for the majority of queries and DirectQuery mode for special use cases such as partial free text searches and displaying free text comments columns. That being said, I went ahead and ran a few tests comparing Import mode, DirectQuery mode, and my Hybrid Columns design for a semantic model with some high cardinality text columns on a large fact table. A summary of this article along with the results of those tests and instructions for setting up hybrid columns can be viewed in the following video:

Implementation

A guide to building Aggregations can be found at this link. You can also review my instructions and video tutorial from a few years ago about traditional Aggregations at this link. For using the feature as a Hybrid Columns architecture, implement with the following details:

• The primary fact table is in DirectQuery mode.
• Any dimension tables connecting to the primary fact table preferably use relationships in Dual mode.
• Add the Import table, which is a version of the primary fact table minus the problematic columns, at the same grain as the primary fact table. If it is possible to aggregate the table for a true “Aggregations” table, you will see added benefits by making the table even more compact.
• What if you need to count the rows in the fact table, and you’d like to avoid reverting the DirectQuery every time that happens? When selecting the SUMMARIZATION options for the Manage aggregations settings, there is an option for Count table rows. If you are using a Hybrid Columns architecture, you’ll need to add a new column to the table with an integer value of “1” that will be mapped to this setting. Not elegant, but it allows COUNTROWS() expressions to run in Import mode. If you are using a true Aggregations table that is at a higher grain, you’ll need to have a column that counts the underlying rows that constitute each aggregated row for this purpose, too.

Again, I walk through the design and implementation of a test use case in the video embedded above which can be viewed at this link.

Considerations

• DAX expressions such as MEDIAN() or STDEV.P() which do not simply aggregate values, perform max or min operations, or sum up row counts will still trigger DirectQuery mode. There’s no way I’ve found to specify that the Aggregation Import table is at the same grain as the DirectQuery fact table.
• The DirectQuery table, per the Aggregations documentation, can be any supported source for DirectQuery mode including Fabric SQL endpoints (used for the demo), Snowflake, Databricks SQL endpoint, SQL server, Oracle, etc.
• If you can have a true Aggregations table at a higher grain, you will see smaller model sizes and better query performance. The Hybrid Tables architecture should be reserved for scenarios where a table cannot be rolled up to a higher grain.
• I would not recommend using Hybrid Tables (or Aggregations in general) unless you have scenarios such as those mentioned in this article that need performance improvements. Less complexity is always better if everything works.
• The Aggregations feature isn’t required to achieve a Hybrid Columns architecture, but it makes everything easier and cleaner. Manually creating a hidden Import table and using DAX expressions to evaluate use of the visible DirectQuery table or hidden Import table could also achieve the same outcome with some additional configurations to account for some of the limitations listed above. In my opinion a manual approach feels more like a hack and should be reserved for expert semantic model developers who are willing to manage the additional complexity overhead.