how to monitor the fallback behavior of Direct Lake semantic models.
introduction.
For me, Direct Lake is one of the highlight feature in Fabric. Yet, just because you set up a model in Direct Lake mode, by default, it does not necessarily mean that it’s running in Direct Lake mode all the time. This is because in particular circumstances Direct Lake mode transitions to Direct Query. Microsoft calls this behaviour fallback. As a result, your end users might experience (potentially quite considerable) performance downgrades. Reasons for a fallback to Direct Query can be e.g. surpassing the maximum number of row groups or parquet files in your underlaying delta tables. Another one is using unsupported Direct Lake features, like OLS, RLS or Data Warehouse views.
However, there is an option to disable such a fallback by setting the Direct Lake Behvior property to Direct Lake only. As Zoe describes in that link, you can either do it via third-party tools or now even in the Model Explorer directly. But what would a Direct Lake only model do, if it could not load the data (and at the same time a fallback to Direct Query was forbidden)? Well, it would just throw an error (see appendix). So maybe for your production reports, you may make the decision to rather keep that fallback safety net in place. Then, end consumers would at least see results (in the worst-case with a delay) rather than an error message.
Conveniently, there is the DMV / Info DAX function info.DELTATABLEMETADATASTORAGES() that shows you which tables in your model experience a fallback. You can run it e.g. in SSMS, tabular editor or even with semantic-link.
In this blog article, I will show you yet another way. We will use Power BI itself to run that DMV against a Direct Lake semantic model. With this approach, we can then even design a Power BI report. While such a report levels up our monitor capabilities, I would probably still go with tabular editor or SSMS for the inevitable exploratory analysis during development.
Important to mention, the DMV does not expose a simple true / false flag for whether a fallback has occurred. Instead there is an enum column indicating the fallback reason. First, I did not find any official documentation about what these enums mean. But luckily, I came across a Python script on Michael Kovalsky’s github where he assigns proper meanings to each value. We use his translations in our report, too.
[Edit: 2024-08-06]: There has a new DAX function been released that makes this manual translation obsolete. Check out Sandeep’s blog post for more information.
prerequisites.
1. A Fabric capacity and workspace
2. Power BI Desktop
1. What’s the issue?
As already described in the introduction, the issue is about the fallback behviour from Direct Lake to Direct Query. Below the error message that would pop up, if you chose to disable fallbacks on your model, but Direct Lake mode was not executable due to RLS/OLS being in place:
Now, in this article we describe a scenario where we deliberately do not disable the feature and thus put up with potential fallbacks to Direct Query. Our approach instead utilises a Power BI report monitoring the fallback behviour of our model.
2. The DMV
The DMV info.DELTATABLEMETADATASTORAGES() provides us with the information of whether a table has fallen back to Direct Query mode. The screenshot below originates from executing the DMV in SSMS:
3. The fallback reasons
First, I tried myself to map the enum reasons to proper text, since I could not find any official documentation. But then, I stumbled upon a Python script on Michael Kovalsky’s github where he assigned proper meanings. We use exactly those translations in a User Defined Dimension (udd) table in our Power BI model:
Fallback Reason ID | Fallback Reason |
0 | No reason for fallback |
1 | This table is not framed |
2 | This object is a view in the lakehouse |
3 | The table does not exist in the lakehouse |
4 | Transient error |
5 | Using OLS will result in fallback to DQ |
6 | Using RLS will result in fallback to DQ |
4. The Power BI report
Here the report. Note, the actual semantic model that is referenced by the meta report below, is based on the AdventureWorks sample. Chris Wagner has made a great tutorial video on how you can get that sample data into a Fabric Lakehouse.
The underlaying model is not only built upon the udd from above and the DMV info.DELTATABLEMETADATASTORAGES(), but also makes use of the following ones:
– $System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
– $System.DISCOVER_COMMANDS
– $System.DISCOVER_SESSIONS
– info.COLUMNS()
– info.Model()
– info.PARTITIONS()
– info.PARTITIONSTORAGES()
– info.TABLES()
Those tables are used for some other things that might be worth monitoring like sessions, queries or column temperatures. Below exemplarily, the M code snippets for info.DELTATABLEMETADATASTORAGES() and $System.DISCOVER_COMMANDS. We go for the same approach as we used in the article how to build meta reports on top of power bi semantic models with dynamic management views (DMVs). Note, the DISCOVER DMVs did not work on the default semantic model.
end.
That is it. I might be updating the report in the future with additional monitoring capabilities. Feel free to comment below, if you’d like a copy of the report. I’ll then send it to you in an e-mail.
To push your monitoring to the next level, you could implement a Data Activator reflex on one of the visuals to get notified when a fallback occurs. Note, at time of writing this post, the new card visual did not work with Data Activator. Since the report uses the new card visual, you might need to align that. I quickly chatted with Miguel Myers about this and he ensured once in GA, this is going to work as well.
appendix.
Here an example of a potential error message when setting the model mode to Direct Lake only. Worth noticing, not the whole report will break, but only the visuals that use column references that do not work with Direct Lake:
Great article. Could you please send me the PBIX file
Mail’s out :)
Hi, great article and approach! Could you please send me the report? Thank you in advence!
pbix files are on its way!