In case you haven’t already seen the blog post on the Power BI blog or the discussion on LinkedIn, we at Microsoft published a new white paper last week to help you decide which storage mode to use when you’re using Power BI to create semantic models and reports on data stored in Azure Databricks. You can find the announcement and the link to the paper here.
While the paper was commissioned by Microsoft it was written by an independent expert, Liping Huang, who has extensive expertise with Power BI and Databricks and who has worked for both Microsoft and Databricks in the past. Liping is someone I have a huge amount of professional respect for and I think she did a great job on a very tricky project. I think the end result is very honest and fair. I’d also like to call out Eiki Sui from my team (if you can read Japanese check out his blog – if you don’t then browser translation tools work well) for providing the initial inspiration and managing the project on the Microsoft side.
So what does the white paper say? Which storage mode is best? Unsurprisingly, it depends and you need to read the whole thing. In order to keep the scope manageable the paper doesn’t test every single possible storage mode scenario and it also focuses on performance and excludes cost and governance considerations. Direct Lake on OneLake comes out on top in the majority of tests but a Composite Model with a DirectQuery fact table, Dual mode dimension tables and Import mode aggregations is best for larger data volumes, similar to what I described in this blog post. Of course your data, semantic models and reports will be different so the main aim of the paper is to help you run your own tests to decide what works best for you.
Hi Chris, it is a good whitepaper.
Performance is a complex topic in its own right. But I kinda wish these discussions would introduce financial cost into the equation. The paper seems to overlook the runway costs of the four options, and assumes we might have a blank check to use any approach we like. That is not likely. It is going to be expensive to pay for continuously synchronized/duplicated blob data in databricks and onelake, for the sake of a slightly better user experience in onelake-native semantic models. And when the databricks directquery is added to the paper, that is probably one of the most expensive strategies of them all. Any semantic model that generates these directquery requests to a databricks warehouse all month long is going to consume expensive resources (on both of the platforms at the same time).
The most prominent thing that I got from the paper is that mirrored UC tables (with shortcuts) is being fully endorsed by Microsoft. In the “conclusions” at the end, it says these mirrored scenarios are being optimized at the moment, and the changes will be released in the latter half of 2026. That is exciting.
We are really eager to start using direct lake models. But we have a large community of Excel users, and don’t wish to forfeit the “pivottable analyze” ribbon (session-scoped MDX). Sorry to keep nagging about this. Can you please help me understand why it is not a priority? If query-scoped calcs and sets are supported, then what is so awful challenging about session-scoped ones? Do you think it would be possible for the Excel team to place these things (session-scoped definitions) into the body of the query itself, when sending queries to a PBI direct-lake model? It is really discouraging to lose features that we have relied on for decades. I think customers need to be able to understand the hold-up on something like this. I was sad to see that directlake on onelake went GA without fixing the Excel ribbon.
See the docs: Limitations : Analyze in Excel pivot tables = Session-scoped MDX statements, such as named sets, calculated members, default members, etc. are not supported.
I would have loved to include price/performance comparisons in the white paper, and while I agree that some options are very likely to be more expensive than others, the amounts that customers pay for Power BI, Fabric and Databricks can vary a lot so we decided that we couldn’t come up with any clear conclusions.
And I agree with you that session-scoped sets and calculated members etc would be nice to have but there are genuine technical reasons why it’s hard to support them with Direct Lake models and not many other customers are asking for this.
I understand that there are technical reasons why this isn’t implemented . But some of the technical solutions might be very simple, like adding “WITH” statements to the beginning of the MDX queries, and just sending along the definitions of these sets and measures in a “query-scoped” manner.
It is a very heavy lift for our Excel users to otherwise create self-service sets and measures. They would basically have to create an entirely custom model (composite or import model) of their own in PBI. I’m guessing that the reason why many of the reporting analysts aren’t asking for this is because they like being personally empowered to do this work in PBI desktop, and they do not want to empower their end users (in Excel) to do it since it might take away from their job security. Something like that. These sets and measures were once common features used for self-service BI in the past. Nowadays the meaning of self-service BI has changed, such that the Excel users must pair up with a PBI user to build a report. It seems like a small step backwards, IMO.