Performance difference between default semantic model and custom (new) semantic model?

Question

Hi,&nbsp;Is there a difference in performance between using the Lakehouse's default semantic model, or creating a new direct lake semantic model (also known as custom semantic model)?&nbsp;Ref. this blog: "The Default dataset is also performance optimized. I have seen in some cases, default dataset perform faster in cold cache compared to custom dataset. I am not sure why."&nbsp;Thank you!&nbsp;

andyddc · Answer

Hi&nbsp;frithjof_v&nbsp;I can't imagine why the default semantic model would perform better in cold cache tests vs a custom semantic model. &nbsp;Sandeep (fabric guru blog) has conducted a lot of tests with direct lake to form that FAQ, so I would say do your own testing as well to see if you get the same results with your data.

andyddc · Answer

Adding relationships in the Warehouse can certainly help performance as although unique and foreign keys are not actually enforced, they help the query optimiser. With Lakehouses I have not noticed any performance differences if I create relationships in the semantic model, but of course the only reason to join tables is to propagate filters anyway.

Well I think Chris Webbs blog is pretty good,but it may not cover the default semantic model. Maybe we can reach out to MS to clarify about server timings (I will do that) https://blog.crossjoin.co.uk/2023/07/09/performance-testing-power-bi-direct-lake-mode-datasets-in-fabric/

if you do any perf testing then please let us all know how that goes, the more info we can collate together the better

frithjof_v · Answer

I also tried to clear the cache on the Custom semantic model.I thought I could do this via DAX studio, or by refreshing the custom semantic model in Power BI service, however I am a little confused by the results I got.The ones with duration &lt; 2500 ms were run after I used DAX studio to clear the cache on the direct lake semantic model in the Fabric workspace.&nbsp;The ones with duration &gt; 4000 ms were run after I used the refresh semantic model button in the Fabric workspace in Power BI service.&nbsp;It seems that DAX studio is not able to fully clear the cache on the direct lake semantic models. At least the query times are slower after I refresh the semantic model in Fabric (Power BI service).

frithjof_v · Answer

I realized I can get information about the current temperature of the data columns.&nbsp;I tried running the following DMV query in DAX studio. I got it from&nbsp;this great blog:&nbsp;&nbsp;&nbsp;select DIMENSION_NAME, COLUMN_ID, DICTIONARY_SIZE, DICTIONARY_TEMPERATURE, DICTIONARY_LAST_ACCESSED
from $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS
order by DICTIONARY_TEMPERATURE desc&nbsp;&nbsp;&nbsp;I get an error message if I try running this query on the default semantic model.&nbsp;&nbsp;However I can run the query on the custom semantic model:My observation is that it seems like only columns in the table for which I updated the data, gets 0 temperature (gets evicted from cache).&nbsp;Before I update data in the Random table:Right after I updated data in the Random table:(I used dataflow gen2 to update the data in the Random table. The semantic model has auto-refresh enabled, so the semantic model's delta table metadata gets updated when data changes.)&nbsp;&nbsp;When I click the refresh button on the custom semantic model, all the columns get 0 temperature:&nbsp;

frithjof_v · Answer

I believe I managed to query the default semantic model in cold state now.First, I updated&nbsp;(replaced) the data in all the Lakehouse tables included in the semantic model, this should set the temperature of all columns to zero.I also let the semantic model stay unused overnight, for good measure.Then I refreshed the visual in my report. Now I got a DAX query duration of 4819 ms for the default semantic model. This is quite similar to the durations I got for the cold state custom semantic model yesterday.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;However, I also tried refreshing the visual in the report which is connected to the custom semantic model. Then, at first attempt, I got a DAX query time of 9100 ms (new all-time-high!). Yesterday, I had disabled the auto-refresh on the custom semantic model. So it had stayed unused, and not refreshed, for approx. 10 hours. Still, I'm surprised that the Performance Analyzer showed a DAX query duration which is around 100% longer than yesterday.&nbsp;&nbsp;I then manually refreshed the custom semantic model in Power BI service (Fabric workspace). Then I refreshed the visual again, and the DAX query time was now 6890 ms. Still around 50% longer than the measurements I got yesterday.&nbsp;&nbsp;&nbsp;&nbsp;Then, I repeated this procedure (refresh the custom semantic model, then refresh the visual) several times. During these runs, the durations I got was similar to the durations I got yesterday.Maybe the Fabric capacity just needed to "warm up" before it was able to produce similar DAX query durations like yesterday?&nbsp;&nbsp;I guess my key findings at this point are:&nbsp;Updating data in a single table in the semantic model, seems to only evict the columns from that table from cache (column temperature gets zero). Columns from other tables in the semantic table, still have high temperature. This is great for the end-user experience, as high column temperatures means visuals render more quickly&nbsp;😀I prefer the custom semantic model over the default semantic model, becauseit allows server timings in DAX studioit allows column temperatures to be reviewed by using DMVsit allows manual refresh (and scheduled refresh)the modeling experience of the custom semantic model is found inside the semantic model item itself (which is intuitive)I&nbsp;disfavor the default semantic model, becauseit seems to have limited functionalities compared to the custom semantic modelthe modeling experience of the default semantic model is unintuitively placed in the SQL analytics endpoint, and not in the semantic model item itselfI want to remove the default semantic model from the workspace, but I don't think it's possibleI don't understand the purpose of the default semantic modelIt's difficult to compare the cold state performance of the default semantic model vs. the custom semantic model, because I cannot manually refresh the default semantic model.&nbsp;Any corrections or additions are greatly appreciated&nbsp;😀

Forum Discussion

Performance difference between default semantic model and custom (new) semantic model?

10 Replies

Related content

Performance Difference

How to improve performance of Semantic model

Would Enabling Large Semantic Model Storage Format Improve Refresh Performance?

Shared Semantic Model - Connectivity Issue from different tenants

Combining Data from Different Semantic Models