This week, in the announcement about support for Fabric Pipelines in Workspace Monitoring, I noticed that it came with an Operations Agent that actively monitors and analyses Pipeline activity. And that got me thinking, since Workspace Monitoring also contains Power BI activity data, why not create an Operations Agent to actively monitor Power BI too?
I decided to test a really simple scenario. I created a semantic model that contained several measures, one of which returned an error. I then created a report from that semantic model with two pages: one with several working visuals and one with a visual that used the broken measure and which therefore returned an error.
I published this report to a workspace with Workspace Monitoring enabled.
I then created an Operations Agent in the same workspace and connected it to the KQL Database associated with Workspace Monitoring:
Here are my Agent Instructions:
*** Operational Instructions ***
1. Alert me when a DAX query on any of the Power BI semantic models in this workspace returns an error, for example when the DAX in a measure, except when the error is the result of a query being cancelled
2. When you alert me, give me the following information:
a) The username of the user that generated the error
b) The date and time of the error
c) The error message
d) The IDs of the semantic model, the report and the visual that generated the error
e) The OperationID of the query
*** Semantic Instructions ***
1. The SemanticModelLogs table contains information about Power BI activity
2. Every time there is an error, there will be an event with the OperationName "Error"
3. The EventText column for these Error events contains the error message
4. Errors generated by query cancellations, which can be ignored, have an error message in the EventText column that starts with the text "The operation was cancelled by the user"
5. The Timestamp column contains the date and time of the events
6. The ExecutingUser column contains the username of the user that generated the error
7. The ApplicationContext column contains a JSON fragment which gives the IDs of the semantic model (DatasetId), report (ReportId inside the Sources array) and visual (VisualId inside the Sources array)
8. The OperationID column contains the OperationID
Here’s the Playbook that the Operations Agent generated:
And here’s the KQL generated for the DAX Query Error Event Alert:
//DaxQueryErrorEvent: one row per non-cancelled DAX query error event from SemanticModelLogs
DaxQueryErrorIsNonCancelledError=true,//byconstruction: filtered to non-cancelled errors
ExecutingUser=['ExecutingUser'],
ErrorTimestamp=['Timestamp'],
ErrorMessage=['EventText'],
DatasetId=DatasetId,
ReportId=ReportId,
VisualId=VisualId,
OperationId=['OperationId'];
DaxQueryErrorEvent
I have to admit it took several iterations to get the Agent Instructions right so that working KQL was generated, and I would not have been successful without testing the KQL myself and understanding why it wasn’t working. It was not particularly hard to do this though.
I then saved and started the Operations Agent.
Next I opened the report that contained the broken visual, so that I saw an error in the report, and waited. After a few minutes I got the following message in Teams:
So I got a Teams notification when a report containing a broken visual was rendered, which is great and proves the point that Operations Agents can be used to monitor Power BI when used with Workspace Monitoring. You’ll notice that the Teams message doesn’t include the error message, the semantic model, report or visual IDs or any of the other information I requested (although the KQL query generated by the Playbook does return that information) but I guess I need to spend more time tuning the Agent Instructions. Other things I can imagine doing with an Operations Agent include monitoring for failed semantic model refreshes and slow DAX queries. Definitely something I need to spend more time investigating.
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]
A few months ago I wrote about a rare error – the “Maximum allowable memory allocation” error – that may occur when the physical machine, or node, that a semantic model is running on in the Power BI Service comes under memory pressure. Recently, someone I was working with who was doing some load testing showed me a related error:
The operation was canceled and the database was evicted to balance the CPU load on the node. Please try again later.
This error is so rare that a web search returns no results, but since at least one other colleague at Microsoft has seen it I thought it would be good to blog about it.
Put simply, this error is the CPU version of the “Maximum allowable memory allocation” error in that when CPU usage on a node gets too high then a semantic model (maybe the one that is causing the high CPU usage, maybe not, and indeed maybe there is no single culprit) is picked to be moved to a different, quieter node. When this happens any queries or refreshes that are running on the selected semantic model are cancelled and this error is returned.
It’s easy to see how load testing can trigger this error, since load testing involves running a lot of concurrent DAX queries and therefore generates high CPU usage on the semantic model. As with the “Maximum allowable memory allocation” error, if you see this error once you may just be unlucky but if you see it repeatedly in normal production use you are probably the cause, in which case enabling semantic model scale out may help but the real solution is to tune your semantic model to reduce CPU usage. How to do that is out of the scope of this blog but as with so many things in Power BI you should start by looking at the DAX in any calculated columns (if you’re getting the error during a refresh) or in your measures (if you’re getting the error when users query the semantic model), and by looking at how your data is modelled.
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.
Continuing my series of not-reviews of the latest Power BI-related books (and there are fewer and fewer of them being published) that I receive free copies of, here’s the second edition of a book that for some reason I missed first time around: “Extreme DAX”. The first few chapters cover familiar territory: data modelling, calculated columns, measures, time intelligence calculations etc. Of course there are several other books available that explain these concepts with the canonical text being “The Definitive Guide To DAX”; “Extreme DAX” does a good job with the basics and includes the latest additions to the language such as UDFs but I can’t help but think it would have been better to skip all this and assume that the reader knows it all already. Luckily things get a lot more extreme as the book progresses and it shifts to showing how to solve complex – but nevertheless real-life – Power BI problems with DAX. There’s a whole chapter on Auto-Exist for example which is pretty hardcore. For me this is where the real value of the book lies and there’s enough good content here for any advanced Power BI developer to want to buy it. Definitely worth checking out.
If you have a lot of Power BI semantic models that are scheduled to refresh at the same time in the Service then you may find that some of them fail with the following error:
You’ve exceeded the capacity limit for dataset refreshes. Try again when fewer datasets are being processed.
[Note: “dataset” is the old name for a Power BI semantic model. Someone should update the error message.]
What causes it? Each Fabric or Power BI Premium capacity SKU can support (and “support” is the operative word here, as we shall see) a certain number of concurrent semantic model refreshes. These limits are documented here in the Model Refresh Parallelism column of the table on that docs page:
The error itself is documented here and I’ve mentioned it myself in a previous post here, but the interesting thing about the limit on the number of concurrent refreshes there’s a lot more to it than you might expect – Power BI is very forgiving.
Before I go any further, it’s important to make clear that this error is nothing to do with how many CUs you are using on your capacity at the time of the error, although the limits are in place to stop you overloading your capacity: running multiple semantic model refreshes at the same time could cause a sizeable increase in CU consumption even after smoothing.
For example, to investigate how this limit is applied I created an F2 capacity, added a workspace to that capacity, and uploaded several identical Power BI semantic models to that workspace. I used some Power Query magic to control how long those semantic models took to refresh.
For my first test I configured two semantic models so they took 120 seconds to refresh and started a manual refresh on both at the same time. Now, looking at the table above, you might think that because an F2 supports one concurrent semantic model refresh then I would get an error but no, both semantic models refreshed successfully and both refreshes took 120 seconds. The published limit is the number of semantic models that Power BI guarantees that can be refreshed concurrently; in practice the limit may be exceeded.
Next, I started a manual refresh on six semantic models that were all configured to take 120 seconds to refresh. Again, they all refreshed successfully and all took 120-122 seconds to refresh. Finally I started a manual refresh on fifteen semantic models that again were configured to take 120 seconds to refresh and this is where I saw something different. All of the semantic models refreshed successfully in the end, and none showed the warning triangle in the first screenshot above. Most of the semantic models took 120-122 seconds to refresh but some took longer. For example, take a look at this Refresh History for one of the models:
The overall refresh was successful but took 305 seconds, not 120 seconds. This is explained by the refresh failing immediately with the “You’ve exceeded the capacity limit for dataset refreshes” error, then the Service waiting a minute to retry the refresh (for more information on automatic refresh retries see here) which resulted in the same error occuring again, then the Service waiting for a further two minutes before retrying the refresh again, at which point it succeeded and took 122 seconds.
So you can see what I mean when I say Power BI is very forgiving about these limits. It’s also worth mentioning that scheduled refreshes don’t always happen at exactly the time they are scheduled for. The Service may wait several minutes after the scheduled time before it tries the first refresh. This is what is meant by the statement in the docs here that “You can schedule and run as many refreshes as required at any given time, and the Power BI service runs those refreshes at the time scheduled as a best effort.“
In other tests with the same number of semantic models but longer refresh times, I was able to observe a scenario where a refresh scheduled for 17:30 did not start until almost eight minutes after that time and then failed nine times before it succeeded; note that the amount of time the Service waited to retry after the second failure went up to five minutes:
Of course Power BI can’t keep retrying indefinitely and eventually refreshes will fail with the “You’ve exceeded the capacity limit for dataset refreshes” error. Here’s the Refresh History for a semantic model where refresh ultimately failed after four retries (this took a lot of concurrent, slow refreshes to repro):
If you’re encountering this error then the solution is obvious: reduce the number of refreshes that are happening at any given time. But how do you know which refreshes are scheduled for when and how long they will take? The Refresh Schedule page for your capacity in the Admin Portal gives you a summary of the number of semantic models that are predicted to be refreshed in a 30 minute time slot and how long they are likely to take. The Fabric Monitoring Hub gives you details of historical activity. And if you have Workspace Monitoring or Log Analytics configured on your workspace you can get a lot of detail on what happens when refreshes are run, including seeing when the “You’ve exceeded the capacity limit” error occurs and refreshes retry.
Once you know what is being refreshed and when, you need to do two things. First see if you can reduce the number of times any given semantic model is refreshed. It’s pretty common for users to configure their model to refresh multiple times a day even if the actual data source only changes once a day, for example, so easy wins may be possible. Second, tuning the amount of time refreshes take can also reduce the amount of concurrent refreshes: if your semantic models refresh quickly it’s less likely it will overlap with other refreshes. Tuning data sources, tuning Power Query, increasing refresh parallelism, removing unnecessary columns or tables, tuning the DAX used in calculated columns and tables or replacing those calculated columns and tables with pre-calculated data in in the data source, and using incremental refresh are some of the things you will need to look at. Scaling up to a larger capacity, or buying an additional (possibly smaller) capacity and moving some workspaces over to it will also of course also solve the problem because the limits are per capacity.
In summary, what this shows is that the published, supported limits on the number of concurrent semantic model refreshes in the Power BI Service are a lot lower than what is achievable in practice. This is very important in self-service BI scenarios because it means refreshes are a lot less likely to fail than they would otherwise. But if you are refreshing a lot of semantic models, exceed the published limits on a regular basis and find some of your refreshes fail then you have no choice but to take some of the actions described above to get back under the limits.
This is a very late addition to the series of posts I wrote back in 2024 and which started here on Power BI memory errors. It’s about a very rare error that is hard to deal with and often temporary but since people do run into it from time to time I decided to write about it so there is some useful information available about it online.
The error, which can occur when you refresh a semantic model or render a report, has two associated error messages:
The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.
or more commonly:
You have reached the maximum allowable memory allocation for your tier. Consider upgrading to a tier with more available memory
The error number associated with this error is 0xC11C0005 or -1055129595.
What causes it? This needs a bit of explanation and what follows is an over-simplification…
When you publish a Power BI semantic model to the Service it runs on one of hundreds of physical machines – nodes – alongside other semantic models published by other people. The Service always tries to put your semantic model on a node that has enough memory and CPU available for it to be queried or refreshed; if it decides that isn’t the case, the semantic model will be moved to a different node. The tricky thing is that the amount of memory or CPU available depends on whether the other semantic models on the same node are being refreshed or queried at any given time and how resource-intensive those queries and refreshes are. The limits on memory consumption that I wrote about in the previous posts in this series are there to stop any one semantic model consuming too much memory and causing problems for the other semantic models on the same node. While the algorithms used to determine which semantic models should be held together on a given node are very sophisticated (and are being improved all the time), sometimes something unexpected happens and the necessary resources aren’t available for a refresh or query. The errors above happen when the node your semantic model is being held on is under memory pressure.
What can you do about it? That’s a hard question to answer but it depends on whether your semantic model is part of the problem or not. If you only get this error once (and as I said, it’s a very rare error indeed) then you can ignore it – it’s just bad luck. However if you get this error repeatedly then it’s very likely that your semantic model is causing a memory spike and even if you aren’t hitting any other memory limit you are probably coming close and you should do some tuning. If you get this error when rendering a report you should look at the DAX queries generated by your visuals and work out whether you can reduce their memory usage by remodelling your data or rewriting the DAX in your measures. If you get this error when refreshing your semantic model you should see if you can reduce its size by remodelling your data or reduce memory consumption in other ways, for example by removing calculated columns or calculated tables and replacing them with columns and tables in your data source. For more information on how to measure memory consumption for a query or refresh, see the other posts in this series.
Did you know that you can configure your Power BI semantic model so that it automatically binds to a data source connection when you publish?
To illustrate how to do this, I created an Import mode Power BI semantic model in Power BI Desktop connected to the Products table in the ContosoSales sample database in the Azure Data Explorer help cluster. Anyone can connect to this source, you just need a Microsoft Account to authenticate. Here’s the M code from my semantic model:
let
Source = AzureDataExplorer.Contents(
"help",
null,
null,
[
MaxRows = null,
MaxSize = null,
NoTruncate = null,
AdditionalSetStatements = null
]
),
ContosoSales = Source
{[Name = "ContosoSales"]}
[Data],
Products1 = ContosoSales
{[Name = "Products"]}
[Data]
in
Products1
I then published the model to the Service but of course at that point I couldn’t refresh the model there without the extra step of connecting the newly published model to the source. As you would expect, going to the Settings pane for the semantic model gave me the option to link my data source to a connection in the Service
No surprises so far. I deleted the published semantic model and then did two things.
First I went to the Manage Connections page in the Service and created a new Shareable Cloud Connection for the Azure Data Explorer help cluster. I made a note of the connection ID:
Second, I opened my model in Power BI Desktop, scripted out the semantic model in TMDL View, then added the following Binding Hint to the model:
The name of the binding hint is a JSON representation of the connection. It consists of two parts: the kind, which is the type of connection (in this case a connection to Azure Data Explorer) and the path, which is a semi-colon delimited list of all the required parameters of the function used to connect to the source (in this case AzureDataExplorer.Contents). How do you work out what the kind and path are? Originally I worked it out through trial and error, looking at the diagnostic logs and metadata from the functions used to access data, and then I realised all the information was shown in the first screenshot above when the semantic model was not linked to a connection – the kind is shown as extensionDataSourceKind and the path is shown as extensionDataSourcePath. For reference, here’s what the name of a Binding Hint for a Snowflake connection looks like:
The connectionId is simply the connection ID from the Shareable Cloud Connection that the semantic model should be linked to.
I then hit Apply and was prompted to upgrade the model to a compatibility level of 1608 (Binding Hints are only available at that compatibility level and above) and clicked Yes:
Having done this, I then republished the semantic model and when I checked the Settings pane it was automatically connected to the Shareable Cloud Connection I had created and could be refreshed immediately:
You can add multiple binding hints if you have multiple connections. You can also add multiple binding hints for the same data source. All in all, this is a nice little feature that might be useful if you are programmatically generating and publishing semantic models and want to avoid an extra API call to bind your model to a data source.
[Update May 2026 – after talking to some of the engineers, I’ve been told that the Fabric list connections API is the best way to get the Kind and Path for a connection]
Since March 2026, Power BI semantic models have started showing warnings in their Refresh History in the Service. This has scared a few people but in fact all that is happening is that errors which were there all along and which don’t prevent refreshes from completing are now being flagged. Documentation on this feature can be found here but let’s see an example of the type of errors that can cause these warnings.
Consider the following semantic model that consists of a calculated table called Table With Error and a physical table called Sales with two physical columns called Product and Sales, two calculated columns called Sales Forecast and VAT Forecast, and two measures called Sales Amount and Tax Amount.
Here are the definitions of the calculated columns:
Sales Forecast = 'Sales'[Sales] * 1.1
VAT Forecast = 'Sales'[VAT] * 1.1
Here are the definitions of the measures:
SalesAmount=SUM('Sales'[Sales])
TaxAmount=SUM(Sales[Tax])
And here is the the definition of the calculated table:
Table With Error =
FILTER(
'TableThatDoesNotExist',
'TableThatDoesNotExist'[ColumnThatDoesNotExist]>1
)
There are some problems here: the VAT Forecast calculated column, the Tax Amount measure and the Table With Error calculated table all return errors because they refer to tables or columns that do not exist. You can see these errors in Power BI Desktop easily, for example in the Data pane where these items have warning triangles next to them:
…or if you look at their definitions:
None of these errors stop you from refreshing or publishing but of course you can’t use any of these items in your reports.
If you do publish and refresh this semantic model via the UI (although this does not happen if you refresh via the XMLA Endpoint) you’ll see the message “Refresh completed with warnings”:
If you click the Show link in the Details column and then the Show link in the yellow box that appears, you’ll see a dialog showing the errors for all the broken items:
If you see warnings like this you should probably go and either fix the items that are causing them or delete them. Errors like this happen frequently when you delete items in your semantic model that have measures, calculated columns or calculated tables that depend on them; there are plenty of other similar scenarios that will cause errors too.
I’ve been working with Microsoft BI tools for 28 years now and for all that time Microsoft has been consistent in its belief that semantic models are a good thing. Fashions have changed and at different times the wider BI industry has agreed and disagreed with this belief; right now, semantic models are cool again because everyone has realised how important they are for AI. As a result, some of Microsoft’s partners and competitors (and sometimes it’s not clear which is which) have invested in building their own semantic models and/or metrics stores, some of which don’t work at all with Power BI, some of which only work with significant limitations, and a very small number which are fully supported and work with only minor limitations. This naturally raises the question of whether Power BI will ever work properly with any or all of them. The answer is no, and in this blog post I’ll explain why.
The first thing to make clear is that the reasons why some semantic models work well with Power BI and others don’t are purely technical. It is not because Microsoft has some grand plan to stifle competing BI tools. If you look at Fabric as a whole, you’ll see that Microsoft works closely with Databricks, Snowflake, DBT and many other companies to ensure that it integrates closely with them and gives customers the option to work with whichever other tools they want to use. In Power BI there are connectors to a wide range of data sources, not just Microsoft ones. Over the last year the Power BI team has spoken to all major vendors of third-party semantic models about integration with Power BI and it has been clear about what is and isn’t technically feasible. The door remains open for future collaboration and Microsoft respects the motives of these other vendors, in particular those who are developing open standards.
To understand the technical issues, let’s look at the architecture of a simple Power BI solution that uses an Import mode semantic model – as the vast majority of Power BI solutions do:
In this case the data from the data sources is copied into the Power BI semantic model, which also contains information on how the different tables of data should be joined to each other, measures (defined in the DAX language) describing how data should be aggregated and how more complex business calculations should be performed, which columns are visible and which ones are hidden, and a lot more. When the Power BI report is rendered it sends queries, again in the DAX language, to the semantic model to get the data it needs for each visual.
How could a third-party semantic model be used instead here? Power BI reports connect to Power BI semantic models using the XMLA protocol, and that means that Power BI reports can also connect to older Azure Analysis Services and SQL Server Analysis Services semantic models too. Some vendors have come up with a solution whereby they implement support for XMLA and tell their customers to connect to their semantic models using the SQL Server Analysis Services connector. This works up to a point but as you can imagine, using the SQL Server Analysis Services connector to connect to something that isn’t SQL Server Analysis Services is not supported and not wholly reliable. It’s worth noting that using a third-party semantic model as a data source for an Import mode Power BI semantic model is not an option either because if Power BI imports metrics like percentage shares or time intelligence calculations it will not be able to aggregate data and get the correct result. Most metrics need to be calculated after the base data has been aggregated to work properly.
There are two other storage modes available for Power BI semantic models: Direct Lake and DirectQuery. Direct Lake only works with data stored in, or which can be reached via a shortcut from, Fabric OneLake so we don’t need to discuss it here. In DirectQuery mode the Power BI semantic model doesn’t store any data and instead, when it is queried, it generates SQL queries to get the data it needs from a data source on demand.
Other vendors of third-party semantic models have taken the approach of suggesting the use of Power BI in DirectQuery mode and having it run SQL against their semantic model. Apart from the fact that DirectQuery mode is usually slower and less cost-effective than Import mode or Direct Lake mode, your first reaction to this would probably be that putting one semantic model on top of another semantic model doesn’t make any architectural sense and you’d be right. There are several serious problems that emerge when you try to use Power BI in this way.
For example, a Power BI semantic model assumes that you have your data modelled as a star schema and that it will be able to generate SQL that joins dimension tables to fact tables. Not all third-party semantic models support something as basic as this yet. What’s more a Power BI semantic model assumes that it will be where all metrics will be calculated, which means that despite some interesting workarounds by third-party vendors (such as making the SQL SUM() function not actually sum up values) you can never be sure that you’ll get the correct values for a metric defined in a third-party semantic model, for example for subtotals or grand totals. There are a lot of other, similar problems that the Power BI team have made these third-party semantic model vendors aware of. These problems are not specific to Power BI semantic models either: no other semantic model would work well with another semantic model as its source.
If you can’t use Power BI semantic models on top of third-party semantic models, is it an option to synchronise calculations defined in a third-party semantic model to a Power BI semantic model? Yes, that is certainly possible and supported, and some of our partners (such as our friends at Tabular Editor) have already started down this path. DAX is a very rich language for defining metrics and Microsoft has invested a lot recently in making changes to Power BI semantic models programmatically as easy as possible. Without a doubt any metrics defined in a third-party semantic model can be reproduced in DAX, although since DAX is a much better fit for defining metrics than SQL you’ll probably find that some of the metrics you need can only be defined in DAX. In which case, rather than defining some of your metrics in a third-party semantic model and some in a Power BI semantic model, why not define all of them in your Power BI semantic model?
The final point to make is that Power BI semantic models can be used with a wide range of BI tools, not just Power BI reports. Apart from Microsoft tools like Excel and Fabric Paginated Reports, Tableau and several other non-Microsoft tools that you might think of as competitors to Power BI can also be used as a front-end for Power BI semantic models and this is supported. There is nothing stopping other BI tools from implementing connectivity to Power BI semantic models in the future. In Fabric you can even query a Power BI semantic model in SQL and extract data into a Pandas Dataframe in Python using the Semantic Link library. Anyone arguing that Power BI semantic models are somehow not “open” is wrong.
I’ll be honest, I think a lot of the reason why organisations that already use Power BI extensively consider third-party semantic models is because some people – not the Power BI users themselves, often people from a data engineering or database background – think of Power BI as just a visualisation tool and don’t realise that it also has the most mature, capable, widely used semantic model available in the market today. It is designed for both self-service and enterprise BI scenarios. Microsoft has no plans to make Power BI’s front end work properly with anything other than its own semantic models because that would be a huge amount of work with few benefits to customers: these third-party semantic models all behave differently and are at different levels of maturity, so any changes made in Power BI to accommodate them would risk breaking existing functionality or limit the use of advanced features. 35 million users view Power BI reports every month and those users query 20 million Power BI semantic models. Microsoft’s strategy is to continue to invest and strengthen Power BI semantic models for those customers. So if Power BI is how you want your end users to consume data, then Power BI semantic models, not any other third-party semantic model or metrics store, are the right place to store your metrics definitions and your business logic.
