Category: Direct Lake

The Benefits Of Using Direct Lake Mode In Power BI

This is a blog post I’ve been meaning to write for a long time. Since Fabric launched there has been a lot of focus on Direct Lake mode in Power BI and a lot of people used it because it was the cool new thing. Arguably, we at Microsoft have been guilty of telling people to use it because it was the cool new thing without properly explaining what the benefits are of using it. Direct Lake doesn’t completely replace other storage modes: in a recent post I talked about when Import/DirectQuery composite models are the best choice; Marco wrote a good article on Direct Lake vs Import mode which makes the case for the continuing relevance of Import mode for many scenarios. So what are the main benefits of using Direct Lake mode? Here are the ones I think are important:

Faster and cheaper “refresh”

Refreshing an Import mode semantic model can be slow and can consume a lot of CUs on your capacity. Refreshing a Direct Lake semantic model (also known as framing) is almost instant and consumes minimal CUs. So Direct Lake is better, right? Well no, it’s more complicated than that. Even if refresh is cheap in Direct Lake mode you still need to get the data into OneLake in order to use it, and even if you have data in an existing lakehouse you may need to create a copy to apply additional transformations or to apply VOrder or other table-level optimisations, and it’s the cost and performance of this creation of a gold layer of tables that you should compare with Import mode refresh.

The good news is that it is often faster and cheaper to use another Fabric engine, like Spark, to load the data you need into your gold layer in OneLake than to do an Import mode refresh. For example, in this post on reddit my colleague David Browne gives a simple example of how loading a 20 million row fact table in Spark is half the cost in terms of CUs and marginally faster than refreshing the same table in Import mode; this other post on reddit has similar findings. Other BI projects I’ve seen have shown significant improvements in the time and cost of the operations needed before your end users can view reports with the latest data. Of course there are a lot of factors involved here (How frequently do you need to refresh? Are you using incremental refresh? Are you partitioning your fact table and increasing the amount of parallelism during a refresh? etc) and it is true that, at least at the time of writing, Direct Lake may be marginally slower to query than Import and will have different CU cost characteristics when queried. But the evidence is strong enough for me to say that you should do some testing to see whether Direct Lake will be faster and cheaper for your project.

There are other aspects of cost that need to be taken into account. For example, this “shifting left” of development effort from semantic model refresh to loading data into OneLake means that a specialised set of Power BI semantic model skills around Import mode refresh can be replaced by more commodity skills in areas such as Spark. You may also be able to eliminate a layer in your architecture by using Direct Lake: I’ve seen cases where a relational database is put on top of a data lake just to serve data for Import mode refreshes, something that isn’t necessary with Direct Lake.

My friends who work on internal Microsoft BI teams (who are, incidentally, some of the most sophisticated Fabric developers that I know) tell me that they prefer Direct Lake over Import because it makes them a lot more productive, again reducing costs. One reason is because it means that if they discover a bug in their semantic model that means they have to refresh, they can have their semantic model ready to be queried in seconds once the bug is fixed. They can also do things like run unit tests on feature branches before merging into their repo by spinning up a test semantic model in seconds, querying it then tearing it down again.

Avoidance of memory limits and timeouts during refresh

When you’re working with large Import mode semantic models it’s fairly common to run into errors when you hit memory limits or timeouts during refresh. This can be frustrating. These limits don’t exist when you’re loading data into OneLake for consumption by a Direct Lake model, and while there are plenty of other things that can go wrong when loading data depending on the Fabric workload you’re using, in general Import mode refreshes are harder to manage and require more specialised knowledge.

Reuse of data by multiple semantic models

Think how many Import mode semantic models there are in your tenant. Think how many copies of the same dimension tables, like the Date or Product or Customer dimension tables, there are across them. There may even be cases where the same fact table is duplicated across multiple semantic models. All of these duplicate Import mode tables need to be refreshed individually, which takes time and costs CUs, and refreshing them at different times may result in different semantic models containing different data.

On the other hand, if you plan ahead and use shortcuts judiciously, you can load all of your fact and dimension tables into OneLake once and use them in as many Direct Lake semantic models as you want. This can greatly reduce the CU cost and the time it takes before your data is ready to be queried by a report; it also means that all the semantic models contain exactly the same data. I haven’t seen many cases where people have taken advantage of this yet – probably because it does require you to plan ahead – but where it does happen it leads to huge efficiency gains. And with OneLake security you can apply security once and have it enforced consistently across all Direct Lake semantic models that use the same tables.

In the future it seems likely we’ll be moving away from large, complex semantic models that contain all the data an end user might possibly need to smaller, more focused models that work better for AI. At the same time, while a traditional Power BI report can only connect to one semantic model, the new Rayfin/Fabric apps can connect to multiple semantic models. This means it’s even more likely that the same tables will need to be present in several different semantic models.

Reuse of data by different Fabric engines

Finally, building on that last point, when all of your data is in OneLake with OneLake security applied, it’s not only available to be consumed via Direct Lake semantic models but also by any of the other Fabric engines: it can be queried in SQL via the SQL Endpoint, analysed using code in notebooks and so on. While it is possible to write the contents of an Import mode model to OneLake using the OneLake integration feature, it makes a lot more sense just to load the data into OneLake and use Direct Lake instead if you care about this.

Summary

Maybe the one thing I would disagree with Marco on in the article I referenced above is his statement that Direct Lake is only useful for the 2-3% of semantic models that are hard to manage in Import mode – those that are above 200-400GB in size. While I don’t think you should rip and replace any existing Import mode models that are currently working well, and while very large models will need DirectQuery fact tables, I think the reasons I’ve listed above mean that any new enterprise-scale project that is built completely on Fabric should at least consider using Direct Lake. I’m sure there are people out there who feel differently though, so let’s have a discussion in the comments!

[Thanks to David Browne, Justin Martin, Tamas Polner and Christian Wade for their help writing this post]

Power BI DirectQuery Mode: A Better Choice Than You Might Think

For as long as I’ve been using Power BI – which has been from the beginning – the advice about which storage mode to choose has been the same: use Import mode unless you have a really, really good reason to use DirectQuery mode and even then you’re probably wrong and should use Import mode. Import mode was always a lot faster and a lot easier to tune. Marco’s advice in this LinkedIn post from last year pretty much summed up my attitude and that of every other Power BI expert out there:

DirectQuery was only for situations where you either:

  • Had more data than you could fit in memory, which was very, very rare, or
  • Where you needed to see truly real-time data in your reports, which business stakeholders always claim they want but they rarely ever need

The addition of Direct Lake as a third storage mode in Fabric didn’t change the situation. While there are some really good reasons to use Direct Lake instead of Import mode (which I should really cover in a separate blog post because I don’t think they are explained properly anywhere), it didn’t change the advice around DirectQuery and I still didn’t think DirectQuery was a good option for most people.

I have now changed my mind. Import or Direct Lake should still be the default for most projects but DirectQuery is now the best choice when you’re working with larger data volumes that could still be handled by Import or Direct Lake. This is a controversial statement, I know, and it’s based on experiences with customers that I can’t talk about directly, and as always there are some important details, so let me explain myself.

My new advice is this. If:

  • You are starting a new project that is 100% on Fabric and all of your data will be loaded into a Fabric Lakehouse or Warehouse, and
  • You have the following:
    • Fact tables with more than a couple of billion rows in them, and
    • Dimension tables with more than a couple of million rows in them, and/or
    • Distinct count measures on columns with more than a couple of million distinct values in

Then you should start with a composite model design that uses:

  • DirectQuery mode fact tables on the Warehouse or SQL Endpoint of your Lakehouse
  • Dual mode dimension tables (which means they can swap between Import mode and DirectQuery mode when necessary)
  • Aggregation tables based on your fact tables that are either in Import mode or DirectQuery mode

Why am I recommending this? Well let me take Marco’s objections to DirectQuery mode one by one:

Is Import mode or Direct Lake mode always faster than DirectQuery? For the composite model scenario described above, no, not always. Even if your Import mode or Direct Lake mode model fits within the memory limits of the Fabric capacity that you’re using, then a composite model and DirectQuery fact tables can be almost as fast and may be faster. This is because:

  • If your query hits an aggregation table, which is potentially a lot smaller than the fact table, then it will be faster than if it hits an Import mode fact table. Of course if your aggregation tables are in Import mode then naturally your query will be faster than if it hits an Import mode fact table, so you could argue that this is an Import mode scenario anyway. But the native aggregation functionality is not available in Import mode or Direct Lake mode at the time of writing, it’s only available if the underlying table is in DirectQuery mode. And yes I know you can simulate aggregations in Import mode with some clever DAX but that clever DAX can also carry an overhead.
  • The Fabric Warehouse engine, which not only powers Fabric Warehouse but also the SQL Endpoint of a Lakehouse, has improved a lot in the last year and is now incredibly fast. When the new GPU acceleration and other upcoming performance features land then it will be even faster. For example, it’s already faster at doing distinct counts than the Vertipaq engine used by Import or Direct Lake models.
  • The Warehouse engine also has some architectural advantages over Vertipaq when it comes to concurrency. Now Vertipaq is already really, really good at concurrency and features like semantic model scale-out make it even better, but for the large data volumes mentioned above and when you have more than 5-10 concurrent users then Warehouse is better. It’s actually quite rare to have more than 5-10 genuinely concurrent users (ie users that are running queries at exactly the same time) in a BI solution but it does happen, for example at month-ends.

Is DirectQuery for the rich? Is DirectQuery always more expensive than Import mode or Direct Lake mode? Actually no, not if you’re using a composite model in Fabric for the scenarios we’re talking about.

Let’s say you’re building a large Import mode or Direct Lake mode model in Fabric and you hit the limits of the capacity SKU that you’re using. It could be that you’re hitting the memory limit for your SKU or one of the other Direct Lake guardrails. Or it could be that during your load testing (and you should always do load testing) you hit the CU limits of your capacity. What do you do? Well you can and should do some tuning to see if you can avoid hitting those limits. Or you can scale up to the next capacity size, although that might be expensive. The third option is to use a composite model with DirectQuery fact tables in the way I’ve described.

The first advantage of a composite model with DirectQuery fact tables here is that because only your dimension tables and any Import mode aggregation tables you have are subject to the memory limits for Power BI models that each capacity SKU enforces, you’re much less likely to hit those limits.

The second advantage of a composite model with DirectQuery is that is that if you hit your fact table then Power BI will generate SQL queries against the Warehouse engine and the SQL queries generated are counted as background operations which are then smoothed over 24 hours. DAX queries on Import mode or Direct Lake mode models are counted as interactive operations and are smoothed over 5 to 64 minutes. Queries against a DirectQuery table will still consume some interactive CUs and of course if you hit a Dual mode dimension table or an Import mode aggregation that will also consume interactive CUs, but the Storage Engine is where most of the CUs get burned in an Import or Direct Lake mode model. This is the main reason why a composite model approach can handle more concurrent users: you’re less likely to run into the CU limits for the capacity SKU you’re using because of the difference in how smoothing works.

What’s more, these advantages mean that even if you don’t hit the limits for the capacity SKU you’re using, you could still save money by using a composite model because it could allow you to use a smaller capacity.

You could argue that both of these advantages are purely accounting tricks, results of the rules that we at Microsoft have imposed on how Fabric capacities work, and you’d have a point. But it’s unlikely these rules will change anytime soon.

Is DirectQuery still difficult to manage? Yes, this objection still stands in my opinion. If you need to tune an Import model you need an expert in tuning Import models. They’re rare but they exist – you can call Marco for example or take one of his courses. If you need to tune a composite model like the one I’ve described you need someone who can tune an Import model, someone who can tune a DirectQuery model (which is really rare) and someone who can tune your Fabric Warehouse or SQL Endpoint. You might not have that combination of skills in your team, and if you do then it would make maintenance and development more expensive – which undermines the “DirectQuery is cheaper” argument a bit.

Finally, there are a couple of other important questions that need to be addressed.

I have Databricks, Snowflake or some other database and I’d like to use that instead of Fabric Warehouse or the Lakehouse SQL Endpoint. Is that a good idea? No, and I’m not just saying that because I work on the Fabric team and I want you to believe that Warehouse/SQL Endpoint is faster or better than them. It’s because the underlying architecture of Fabric and the connector that Power BI uses to connect to Fabric Warehouse/SQL Endpoint means that DirectQuery on Warehouse/SQL Endpoint is significantly faster and more scalable, even apart from the performance of the SQL queries themselves. More optimisations are planned to make the “better together” story even more compelling. And no, before anyone suggests it, we’re not deliberately trying to hobble the performance of other, non-Fabric databases – many other Microsoft data sources share the same architectural disadvantages when it comes to DirectQuery as Snowflake and Databricks. And while it is possible to make DirectQuery perform well for any data source, my point is that DirectQuery on Fabric Warehouse is a special case.

Also, much anecdotal evidence suggests that using Power BI in DirectQuery mode on non-Fabric sources can be more expensive than Import mode because you need to pay to use those other sources as well as pay for your Fabric capacity. DirectQuery mode on non-Fabric databases is more expensive in CU terms than DirectQuery on Fabric Warehouse/SQL Endpoint because of those architectural differences I mentioned and I’ve seen it even be more expensive than an Import model in terms of CU consumption. Meanwhile, as I’ve said, for larger volumes DirectQuery on Warehouse/SQL Endpoint can be cheaper than Import mode or Direct Lake.

Are there any other advantages to DirectQuery mode? Yes but maybe not ones you care about. Do you want genuinely dynamic calculated columns? It’s been possible for years with DirectQuery (see here – the example uses KQL but similar things are possible in SQL). One day I’ll get my demo showing how to do proper time zone conversion, handling daylight savings time, in DirectQuery. My colleague Mark Pryce-Maher has a nice demo of calling Fabric AI functions in a DirectQuery model. But these are all niche use cases.

Your size recommendations for when to use DirectQuery above are very vague. Can’t you be more precise? No, because so much depends on the design of your semantic model and reports and the nature of your data. In an ideal world you would still always choose Import mode or Direct Lake mode as your default and only shift to a DirectQuery composite model when necessary but doing that halfway through a project is quite disruptive. If you want to test the performance of Import mode, Direct Lake mode and DirectQuery mode for your project then go ahead, but if you don’t then for the volumes I’m talking about there’s a very good chance DirectQuery is the best option.

Last of all, I need to stress that this recommendation could change in the future. While Fabric Warehouse is getting a lot of improvements and optimisations, so is Direct Lake mode, so the price and performance characteristics of both will change a lot and that means the decision about which one is the best choice for larger data volumes may change too. I promise to update this post if and when that happens.

Role-Playing Dimensions In Fabric Direct Lake Semantic Models Revisited

Back in September 2024 I wrote a blog post on how to create multiple copies of the same dimension in a Direct Lake semantic model without creating copies of the underlying Delta table. Not long after that I started getting comments that people who tried following my instructions were getting errors, and while some bugs were fixed others remained. After asking around I have a workaround (thank you Kevin Moore) that will avoid all those errors, so while we’re waiting for the remaining fixes here are the details of the workaround.

Let’s say you have a Direct Lake on OneLake semantic model with two tables, a fact table called Conversation and a dimension table called Person. The Conversation fact table has one row for a conversation between two people, but at this point there is only one Person dimension in the model with a relationship from the FromPersonId column on Conversation to the PersonId column on Person:

How can you add a second copy of the Person dimension table without duplicating the data in OneLake?

In Power BI Desktop, while editing the semantic model, go to TMDL View and in the Data pane on the right hand side switch to the Model pane:

Expand Expressions and drag it into the TMDL pane to script it out. It should look something like this:

Then you need to make two changes:

  • On the line that starts “expression”, line 3 in the screenshot above, change the name of the expression to something new and unique
  • Delete the line that contains the lineage tag, line 8 in the screenshot above

Here’s what it should look like after:

Click Apply and this will create a duplicate Expression in the model. This is the trick that makes everything else work.

Next, create a new script in TMDL View and drag the Person dimension into it to script it out.

Then make the following changes to the script:

  • On the line that starts “table”, line 3 in the screenshot above, change the name of the table to something new and unique
  • One the line that starts “expressionSource”, line 31 in the screenshot above, change the name of the source expression to that of the new Expression created earlier
  • Delete all lines with lineage tags, ie those that start “lineageTag”
  • Add one line at the end with the text “changedProperty = Name”

Here’s what it should look like after:

Click Apply and this will create a copy of the dimension in the semantic model.

Then, back in Diagram View, you’ll see the new dimension table but with a warning saying that it hasn’t been refreshed. The next step is to refresh the model using the Schema and Data option:

At this point the new dimension table can be used like any other table, so you can create the relationship between the ToPersonId column on Conversation and the PersonId column on the new ToPerson dimension:

What Happens When Power BI Direct Lake Semantic Models Hit Guardrails?

Direct Lake mode in Power BI allows you to build semantic models on very large volumes of data, but because it is still an in-memory database engine there are limits on how much data it can work with. As a result it has rules – called guardrails – that it uses to check whether you are trying to build a semantic model that is too large. But what happens when you hit those guardrails? This week one of my colleagues, Gaurav Agarwal, showed me the results of some tests that he did which I thought I would share here.

Before I do that though, a bit more detail about what these guardrails are. They are documented in the table here, they vary by Fabric capacity SKU size and there are four of them which are limits on:

  • The number of Parquet files per Delta table
  • The number of rowgroups per Delta table
  • The number of rows per table
  • The total size of the data used by the semantic model on disk

There is also a limit on the amount of memory that can be used by a semantic model, something I have blogged about extensively, but technically that’s not a guardrail.

Remember also that there are two types of Direct Lake mode (documented here): the original Direct Lake mode called Direct Lake on SQL Endpoints that I will refer to as DL/SQL and which has the ability to fall back to DirectQuery mode, and the newer version called Direct Lake on OneLake that I will refer to as DL/OL and which cannot fall back to DirectQuery.

For his tests, Guarav built a Fabric Warehouse containing a single table. He then added more and more rows to this table to see how a Direct Lake semantic model built on this Warehouse behaved. Here’s what he found.

If you build a DL/SQL model that exceeds one of the guardrails for the capacity SKU that you are using then, when you refresh that model, the refresh will succeed and you will see the following warning message in the model Refresh History:

We noticed that the source Delta tables exceed the resource limits of the Premium or Fabric capacity requiring queries to fallback to DirectQuery mode. Ensure that the Delta tables do not exceed the capacity's guardrails for best query perf.

This means that even though the refresh has succeeded, because the model has exceeded one of the guardrails then it will always fall back to DirectQuery mode – with all the associated performance implications.

If your DL/SQL model exceeds one of the guardrails for the largest Fabric SKU, an F2048, then refresh will fail but you will still be able to query the model and the model will again fall back to DirectQuery mode. For his tests, Guarav loaded 52 billion rows into a table; the guardrail for the maximum number of rows in a table for an F2048 is 24 billion rows. The top-level message you get when you refresh in this case is simply:

An error occurred while processing the semantic model.

Although if you look at the details you’ll see a more helpful message:

We cannot refresh the semantic model because of a Delta table issue that causes framing to fail. The source Delta table '<oii>billionrows</oii>' has too many parquet files, which exceeds the maximum guardrails. Please optimize the Delta table. See https://go.microsoft.com/fwlink/?linkid=2316800 for guardrail details.

The DAX TableTraits() function, which another colleague, Sandeep Pawar, blogged about here, can also tell you the reason why a DL/SQL semantic model is falling back to DirectQuery mode. Running the following DAX query on the 52 billion row model:

EVALUATE TABLETRAITS()

…returned the following results:

This shows that the table called billionrows actually exceeds the guardrails for the number of files, the number of rowgroups and the number of rows.

What about DL/OL models though? Since they cannot fall back to DirectQuery mode, when you try to build or refresh a DL/OL semantic model that exceeds a guardrail you’ll get an error and you won’t be able to query your semantic model at all. For example here’s what I saw in Power BI Desktop when I tried to use the 52 billion row table in a DL/OL model:

Something went wrong connecting to this item. You can open the item in your browser to see if there is an issue or try connecting again.
We cannot refresh the semantic model because of a Delta table issue that causes framing to fail. The source Delta table 'billionrows' has too many parquet files, which exceeds the maximum guardrails. Please optimize the Delta table.

All of this behaviour makes sense if you think about it, even though I wouldn’t have known how things work exactly until I had seen it. Some behaviour may change in the future to make it more intuitive; if that happens I will update this post.

[Thanks to Gaurav for showing me all this – check out his podcast and the India Fabric Analytics and AI user group that he helps run on LinkedIn. Thanks also to Akshai Mirchandani and Phil Seamark for their help]

Performance Testing Power BI Direct Lake Models Revisited: Ensuring Worst-Case Performance

Two years ago I wrote a detailed post on how to do performance testing for Direct Lake semantic models. In that post I talked about how important it is to run worst-case scenario tests to see how your model performs when there is no model data present in memory, and how it was possible to clear all the data held in memory by doing a full refresh of the semantic model. Recently, however, a long-awaited performance improvement for Direct Lake has been released which means a full semantic model refresh may no longer page all data out of memory – which is great, but which also makes running performance tests a bit more complicated.

First of all, what is this new improvement? It’s called Incremental Framing and you can read about it in the docs here. Basically, instead of clearing all data out of memory when you do a full refresh of a Direct Lake model, the model now checks each Delta table it uses to see whether the data in it has actually changed. If it hasn’t changed then there’s no need to clear any data from that table out of memory. Since there’s a performance overhead to loading data into memory when a query runs this means that you’re less likely to encounter this overhead, and queries (especially for models where the data in some tables changes frequently) will be faster overall. I strongly recommend you to read the entire docs page carefully though, not only because it contains a lot of other useful information, but also because you might be loading data into your lakehouses in a way that prevents this optimisation from working.

Let me show you an example of this by revisiting a demo from a session I’ve done at several user groups and conferences on Power BI model memory usage (there are several recordings of it available, such as this one). Using a Direct Lake semantic model consisting of a single large table with 20 columns containing random numbers, if I use DAX Studio’s Model Metrics feature when there is no data held in memory and with the Direct Lake Behaviour setting in DAX Studio’s Options dialog set to ResidentOnly (to stop Model Metrics from loading data from all columns into memory when it runs):

Then when you run Model Metrics the size of each column in the semantic model is negligible and the Temperature and Last Accessed for all model columns are blank:

The, if I run a query that asks for data from just one column (in this case the column called “1”) from this table like this:

EVALUATE ROW("Test", DISTINCTCOUNT('SourceData'[1]))

Then rerun Model Metrics then the size in memory for that column changes, because of course it has been loaded into memory in order to run the query:

Zooming in on the Model Metrics table columns from the previous screenshot that show the size in memory:

And here are the Temperature and Last Accessed columns from the same screenshot which are no longer blank:

Since the query had to bring the column into memory before it could run, the DAX query took around 5.3 seconds. Running the same query after that, even after using the Clear Cache button in DAX Studio, took about only 0.8 seconds because the data needed for the query was already resident in memory.

OK, so far nothing has changed in terms of behaviour. However if you do a full refresh from the Power BI UI without making any changes to the underlying Delta tables:

And then rerun the Model Metrics, nothing changes and the data is still in memory! As a result the DAX query above still only takes about 0.8 seconds.

So how do you get that worst-case performance again? As mentioned in the docs here, you now need to do a refresh of type clearValues followed by a full refresh. You can’t do a refresh of type clearValues in the Power BI UI though, so the easiest way to do is to use a Fabric notebook and Semantic Link Labs. Here’s how. First install Semantic Link Labs:

%pip install semantic-link-labs

Then use the following code in a notebook cell to do a refresh of type clearValues followed by a full refresh:

import sempy_labs as labs
WorkspaceName = "Insert Workspace Name Here"
SemanticModelName = "Insert Semantic Model Name Here"
# run a refresh of type clearValues first
labs.refresh_semantic_model(dataset=SemanticModelName, workspace=WorkspaceName, refresh_type="clearValues")
# then a refresh of type full
labs.refresh_semantic_model(dataset=SemanticModelName, workspace=WorkspaceName, refresh_type="full")

After doing this on my model, Model Metrics shows that the column called “1” that was previously in memory is no longer resident:

…and the query above once again takes 5 seconds to run.

So, as you can see, if you’re doing performance testing of a Direct Lake model you now need to make sure you do a refresh of type clearValues and a full refresh of your model before each test to ensure no data is resident in memory and get worst-case performance readings, in addition to testing performance on a cold cache and a warm cache.

Changing The Source Lakehouse Of Power BI Direct Lake Models In Deployment Pipelines

If you’re using deployment pipelines with Direct Lake semantic models in Power BI you’ll have found that when you deploy your model from one stage to another by default the model still points to the Lakehouse it was originally bound to. So, for example, if you deploy your model from your Development stage to your test stage, the model in the Test stage still points to the Lakehouse in the Development stage. The good news is that you can use the deployment rules feature of deployment pipelines to make sure the model in the Test stage points to a Lakehouse in the Test stage and in this post I’ll show you how.

To illustrate how to do this I created a workspace to represent a dev environment with a Lakehouse containing one table, called MyTable, containing the following data:

I then created a second workspace to represent a test environment with a Lakehouse containing a table with the same name and the same schema but with different data:

Then, back in the dev workspace I created a custom semantic model pointing to the mydata table and built a report on it to show the data:

Here’s what the dev workspace looked like:

I then created a deployment pipeline, assigned the two workspaces to the Development and Test stages of it, and configured it so that the semantic model and report would be deployed from Development to Test:

I then did a deployment and, as mentioned, although the report and the semantic model were moved to the Test stage workspace, the model still pointed to the Lakehouse in the Development stage workspace so the report showed the same data as before:

To fix this I created a deployment rule using the “Data source rules” option. This is where things get tricky. When you create the rule you can see that the custom model on the From side has a Database and a Server property to know which Lakehouse it is pointing to:

This means that on the To side you need to choose the “Other” option in the dropdown box and enter values for the Database and Server:

What do these properties mean?

  • The Database property is the ID of the SQL Endpoint of the Lakehouse the model is pointing to and you can find it by opening the SQL Endpoint UI of the Lakehouse in the browser and copying it from the part of the URL that immediately follows “lakehouses”:
  • The Server property is the SQL connection string of the SQL Endpoint, which can be found by clicking the Gear icon on the menu in the SQL Endpoint UI to open the Settings pane:

I changed the settings in the data source rule to the values from the Lakehouse in the Test workspace and then redeployed. After doing that the report in my Test workspace showed the data from the Lakehouse in the Test workspace (note: I had to manually refresh the report first to do this to flush out stale data from the cache):

Finding The Size Of All Of The Columns Touched By A DAX Query

I had meant to follow up my recent post on how to find the columns touched by a DAX query by writing one on how to use this technique to find the size of these columns in memory, so you can find the total size of the columns that need to be paged into memory when a DAX query runs on a Direct Lake semantic model. Before I could do that, though, my colleague Michael Kovalsky messaged me to say that not only had he taken the query from that first post and incorporated it in Semantic Link Labs, he’d done the work to get column sizes too. All that’s left for me to do, then, is give you some simple examples of how to use it.

To use Semantic Link Labs you just need to create a new Fabric notebook and install the library:

%pip install semantic-link-labs

After that you can use sempy_labs.get_dax_query_dependencies to get the columns touched by any DAX query, for example:

import sempy_labs as labs
labs.get_dax_query_dependencies(
        dataset = 'InsertSemanticModelName',
        workspace = 'InsertWorkspaceName',
        dax_string = "InsertDAXQuery",
    )

This returns a dataframe with one row for each column touched by the query, plus various statistics about the size of each column in memory.

If you’re working with a Direct Lake semantic model, though, in order to get the correct sizes of each column in memory the query itself will need to have been run beforehand; you can ensure that this happens by setting the optional parameter put_in_memory to True:

import sempy_labs as labs
labs.get_dax_query_dependencies(
        dataset = 'InsertSemanticModelName',
        workspace = 'InsertWorkspaceName',
        dax_string = "InsertDAXQuery",
        put_in_memory = True
    )

Last of all, if you don’t want a dataframe but just want a single number representing the total memory needed by all columns touched by a query, you can use sempy_labs.get_dax_query_memory_size, for example like this:

import sempy_labs as labs
labs.get_dax_query_memory_size(
        dataset = 'InsertSemanticModelName',
        workspace = 'InsertWorkspaceName',
        dax_string = "InsertDAXQuery"
    )

Yet more evidence that, for any Power BI user, Semantic Link and Semantic Link Labs are the best reasons for you to flip the switch to enable Fabric. To find out more about what they are capable check out this user group presentation.

Role-Playing Dimensions In Fabric Direct Lake Semantic Models

Did you know you can add the same physical table in OneLake multiple times to the same Direct Lake semantic model?

Let’s say you have two tables in a Fabric Lakehouse. One is a fact table called Sales:

…and the other is a dimension table called Date:

Note that the Sales fact table has two date columns, OrderDate and ShipDate.

If you create a DirectLake semantic model using the Web Editor and add these two tables you could rename the Date table to Order Date and build a relationship between it and the OrderDate column on the Sales table:

What about analysing by Ship Date though? You could create a physical copy of the Date table in your Lakehouse and add that to the model, but there’s another option.

If you connect to the model using a tool like Tabular Editor, duplicate the Order Date table and rename the new table Ship Date:

You then have two tables in your semantic model connected to the same physical table in your Lakehouse, and you can create a relationship between this new table and the Sales table:

…and then use the new dimension in your reports:

This is handy for handling role-playing dimensions, which are most often Date dimensions but may be other types of dimension too. Indeed, I’ve sometimes found the need to add the same fact table to a model more than once. The benefit of only having one physical copy is reduced refresh time, lower storage costs (although for most role playing dimensions the savings will be negligible) and simpler ETL.

UPDATE April 2026: if you’re trying this technique and getting errors, see the workaround in this blog post https://blog.crossjoin.co.uk/2026/04/05/role-playing-dimensions-in-fabric-direct-lake-semantic-models-revisited/

Which Columns Are Loaded Into Memory When In Power BI Direct Lake Mode?

As you probably know, in Power BI Direct Lake mode column data is only loaded into memory when it is needed by a query. I gave a few examples of this – and how to monitor it using DMVs – in this blog post from last year. But which columns are loaded into memory in which circumstances? I was thinking about this recently and realised I didn’t know for sure, so I decided to do some tests. Some of the results were obvious, some were a surprise.

Test semantic model and methodology

For my tests I loaded the following tables of data into a Fabric Lakehouse:

…and created a Direct Lake custom semantic model that looked like this:

Before every test I refreshed the model to make sure there was no data in memory (be aware that in the future it may be that refreshing a model does not purge all column data from memory). Then, for each test, I ran a single DAX query from DAX Studio and afterwards ran the following query to see whether the dictionaries for each column were resident in memory (again, see my previous post for background):

EVALUATE 
SELECTCOLUMNS(
INFO.STORAGETABLECOLUMNS(),
"TABLE_ID", [TABLE_ID],
"COLUMN_ID", [COLUMN_ID],
"DICTIONARY_ISRESIDENT", [DICTIONARY_ISRESIDENT])

Note that I’m using one of the new DAX Info functions, INFO.STORAGETABLECOLUMNS(), instead of the older DMV syntax I was using last year; I think the DAX Info functions are a lot more convenient to use.

[I also looked at whether column segments were paged into memory using the INFO.STORAGETABLECOLUMNSEGMENTS() but found that its results were consistent with INFO.STORAGETABLECOLUMNS() so I didn’t include its results in this post and assumed that if a column’s dictionary was resident in memory, so were the associated column segments. It’s also worth mentioning that the latest versions of DAX Studio have some great new functionality in the Model Metrics for telling you which columns are resident in memory in a Direct Lake model]

Here’s what the query above returned immediately after a refresh, before any other queries had been run:

The DICTIONARY_ISRESIDENT column tells you whether a column’s dictionary is resident in memory. Each table in a Direct Lake model (as in an Import mode model) has a hidden column called RowNumber, and for a Direct Lake model this column is always resident in memory. As you can see, all other columns are not resident in memory at this point.

Counting rows in a table

For my first test I created a measure that counted the rows in the Sales table:

Order Count = COUNTROWS('Sales')

I then ran a query that returned just the result of this measure:

EVALUATE
CALCULATETABLE(
    ROW(
    "Order Count", [Order Count]
    )
)

I then ran my query to see what had been loaded into memory, and…

…nothing had changed! No new column data had been loaded into memory at all. I assume this is because Power BI can resolve this query using the RowNumber column from the Sales table.

I then added the Order_Status column from the Sales table to the query:

EVALUATE
SUMMARIZECOLUMNS(
    Sales[Order_Status],
    "Order Count", [Order Count]
)
ORDER BY 
    Sales[Order_Status] ASC

After this query, the Order_Status column was resident in memory as you might expect, but no other column was:

Relationships

Instead of using the Order_Status column, I then looked at the impact of using a column from a different table. I created a query that showed Order Count by Customer Name:

EVALUATE
SUMMARIZECOLUMNS(
    Customer[Customer_Name],
    "Order Count", [Order Count]
)
ORDER BY 
    Customer[Customer_Name] ASC

After this, the Customer_Name column was in memory along with the two CustomerID columns used in the relationship between the Customer and Sales table:

Any time your query references columns in different tables, the columns used in the relationships between those tables must also be resident in memory.

Measures

I then defined another measure:

Sales Amount = SUM(Sales[Sales_Amount])

And ran the following query:

EVALUATE
SUMMARIZECOLUMNS(
    Sales[Order_Status],
    "Sales Amount", [Sales Amount]
)
ORDER BY 
    Sales[Order_Status] ASC

After this, the Order_Status and Sales_Amount columns were resident in memory:

No surprises here: as you would expect, if a column is referenced by a measure then it needs to be resident in memory.

Measures that use relationships also work as expected. I created the following measure:

Order Count Citrus = 
CALCULATE([Order Count], 'Product'[Category]="Citrus")

…and the following query:

EVALUATE
SUMMARIZECOLUMNS(
    Customer[Customer_Name],
    "Order Count Citrus", [Order Count Citrus]
)
ORDER BY 
    Customer[Customer_Name] ASC

After this query, all the columns used in the measure and all the relationships needed by the query and the measure were resident in memory:

I was curious to know what the effect of efficient versus inefficent DAX in measures might be on which columns were loaded into memory though. I created the following two measures to see the impact of the antipattern of filtering on an entire table in the second parameter of Calculate():

Order Count Status X V1 = 
CALCULATE([Order Count], 'Sales'[Order_Status]="X")

Order Count Status X V2 = 
CALCULATE([Order Count], FILTER('Sales', 'Sales'[Order_Status]="X"))

My first query used the first of these measures, the efficient version:

EVALUATE
SUMMARIZECOLUMNS(
    Sales[Order_Status],
    "Order Count Status X V1", [Order Count Status X V1]
)
ORDER BY 
    Sales[Order_Status] ASC

After this query the Customer_Name column, the two Customer_ID columns and the Order_Status column were resident in memory:

But what about the second, inefficient version of the measure?

EVALUATE
SUMMARIZECOLUMNS(
    Customer[Customer_Name],
    "Order Count Status X V2", [Order Count Status X V2]
)
ORDER BY 
    Customer[Customer_Name] ASC

The same columns were resident in memory:

I don’t think this means this version of the measure is any less efficient than it is in Import mode, just that the two versions of the measure need the same columns to be resident in memory to run.

Conclusion

Knowing which columns your query needs to have resident in memory is important for two reasons in Direct Lake mode: it helps understand query performance, because loading column data into memory takes time (Teo Lachev published some test results in a blog post a few days ago which show this clearly); it also helps you understand model memory usage and where you stand relative to the memory limits for the capacity SKU you’re using. As these results show it’s better to test to see which columns your Power BI reports need to have resident in memory rather than rely on guesswork.

It is also possible to use the DISCOVER_CALC_DEPENDENCIES DMV (or the INFO.CALCDEPENDENCY function) to see which tables, columns, measures and relationships are referenced by a DAX query as I described here; I’ve been playing around with this for Direct Lake too, and will report my findings in a future blog post.

Power BI Semantic Model Memory Errors, Part 1: Model Size

You probably know that semantic models in Power BI can use a fixed amount of memory. This is true of all types of semantic model – Import, Direct Lake and DirectQuery – but it’s not something you usually need to worry about for DirectQuery mode. The amount of memory they can use depends on whether you’re using Shared (aka Pro) or a Premium/Fabric capacity, and if you’re using a capacity how large that capacity is. In Shared/Pro the maximum amount of memory that a semantic model can use is 1GB; if you are using a capacity then the amount of memory available for models in each SKU is documented in the table here in the Max Memory column:

What counts as “memory usage” though? More importantly, how can you breach this limit and what do all of the different memory-related error messages that you might see mean? In this series I will try to answer these questions, and in this post I will look at one particular error you see when your model needs to use more memory than it is allowed to.

First of all it’s important to understand that the amount of memory used by a semantic model is not the same as the amount of data “in” the model. The diagram below shows how model memory usage can be broken down. The data in the columns and tables of your model, along with supporting objects like relationships (represented by the blue box in the diagram below) makes up just one part of the overall model memory usage. In addition, more memory is needed to store data associated with row-level security, user sessions, caches and so on (represented by the orange box in the diagram below).

Both Import mode and Direct Lake models can page data in and out of memory as required, so the whole model may not be in memory at any given time. However, in order for a query to run, the data it needs must be in memory and cannot be paged out until the query has finished with it. Therefore out of all the memory consumed by a semantic model, at any given time, some of that memory is “evictable” because it isn’t in use while some of it is “non-evictable” because it is being used. Evictable memory may be paged out of memory for a variety of reasons, for example because the model is nearing its allowed memory limit.

Queries that are running on the model (the purple boxes in the diagram above) also consume memory. Each query has a limit on the amount of memory it can use – I mentioned the Query Memory Limit in this post but I will revisit it later on in this series – but the memory used by queries does not contribute directly to the overall memory use of a semantic model. However a query that is running will force parts of the model to be in memory for a certain amount of time, and this memory will be non-evictable while in use.

In summary then, the total amount of memory used by a semantic model is made up of two groups:

  1. The data in the tables in your model (the blue box above)
  2. Supporting data for RLS security roles, sessions and caches (the orange box above)

When the sum of these two groups exceeds the total amount of memory allowed for your model, and no data can be evicted from memory to reduce this sum, then you’ll get an error.

To illustrate this I created a new F2 capacity, which has a 3GB limit on the amount of memory used by a semantic model, loaded a table (called SourceData) with 3.5 million rows of random numbers stored as text into a Lakehouse, then created a new custom Direct Lake semantic model on it. I set the Direct Lake Behavior property on the model to “Direct Lake only” to prevent fallback to DirectQuery mode.

After creating the model I used DAX Studio’s Model Metrics feature with the “Read statistics from data” option turned off to find the amount of data stored in memory (ie the blue box value).

Unsurprisingly, at this stage, the size of the model was very small: only 8KB.

I then turned the “Read statistics from data” option on, knowing that this would force data to be paged into memory. This showed the total potential size of the model to be 4.25GB:

I was initially confused by this because this is already well over the 3GB limit, but it was pointed out to me that what is probably happening is that DAX Studio runs a number of DMV queries to get the data needed to calculate this value and when this happens different parts of the model are paged in and out of memory. It was certainly very slow for DAX Studio to calculate the Model Metrics when I did this which fits with the paging in/out theory.

Finally, I ran a simple DAX query to get the top 10 rows from the SourceData table:

EVALUATE TOPN(10, SourceData)

This query ran for about ten seconds and then failed with the following error message:

Resource Governing: We cannot complete the requested operation because there isn’t enough memory (consumed memory 4620 MB, memory limit 3072 MB). Either reduce the size of your dataset, such as by limiting the amount of in-memory data, or host the dataset on a Fabric or Premium capacity with a sufficient memory size. See https://go.microsoft.com/fwlink/?linkid=2159753 to learn more.

[The error code associated with this message is 0xC13E0006 or -1052901370]

This is the error that you get when your model needs to use more memory than it is allowed to use for the capacity SKU it is running on. The query references every column from the only table in the model, which means the whole table – which is the whole model – would have to be paged in to memory for the query to run, but the whole model requires more memory than is available on an F2 capacity.

If you aren’t getting this exact error message then something slightly different might be happening. In future posts in this series I will look at some of these other errors including the query memory limit and the command memory limit.

[Thanks to Marius Dumitru and Akshai Mirchandani for the information in this post]

Update: Now this series is concluded, here are the other posts in this series: