article 19164

Power BI Refresh, Memory Usage And Semantic Model Scale Out

In my recent posts on the Command Memory Limit error and the partialBatch mode for Power BI semantic model refresh, I mentioned that one way to avoid memory errors when refreshing large semantic models was to run use refresh type clearValues followed by a full refresh – but that the downside of doing this was that your model would not be queryable until the full refresh had completed. Immediately afterwards some of my colleagues (thank you Alex and Akshai) pointed out that there was in fact a way to ensure a model remained queryable while using this technique: using Semantic Model Scale Out. How? Let me explain…

Problem Recap

As I described here, when you run a full refresh on a semantic model that already has data loaded into it, the original version of the model stays in memory while the new data is loaded in, which means that the overall amount of memory required for the refesh is around double the amount needed to store the model. Since there are limits on the amount of memory that a model can use which vary depending on whether you’re using a capacity or not and what size capacity you’re using, if you have a large model then you might find refresh fails with a memory error. Running a refresh of type clearValues first removes all the data from the model, reducing its memory usage, and improves the chances of a full refresh using less than the allowed maximum amount of memory and therefore succeeding. Removing all the data from your model, though, means that your reports won’t show any data until you do a full refresh.

How can Semantic Model Scale Out help?

Semantic Model Scale Out (also known as Query Scale Out) is primarily a performance feature: by creating multiple replicas of your semantic model, one which is used for refreshes and one or more that are used to answer queries, it prevents refreshes from interfering with report performance and allows report queries to be distributed over multiple physical nodes. All of these replicas of the semantic model need to be kept in sych, which means that when the model that is used for refresh has new data loaded into it, all the other versions of the model need to be replaced by this new version. By default this synchronisation happens automatically but you can also turn this off and control the synchronisation manually.

As a result, if you turn on Semantic Model Scale Out and turn off automatic synchronisation, you can run a refresh of type clearValues to clear the data from your model and then run a full refresh without affecting your end users – who won’t see the new data until you do a manual synchronisation.

Example

Let’s see a simple illustration of how this works. Using the same semantic model I used in this post, hosted on an F64 capacity, I created a report with a single card visual:

With Scale Out turned off, I ran a refresh of type clearValues. This was very fast and after it had finished – as you would expect – the card visual showed a blank value because all of the data had been cleared from the model:

I then ran a full refresh and when it completed the card showed data again.

Next, I turned on Scale Out in the settings pane of the semantic model:

…and turned off automatic replica synchronisation using the script here.

My colleague Michael Kovalsky has very kindly added functionality to Semantic Link Labs to handle manual replica synchronisation, which means it was extremely simple to run the refreshes and synchronise replicas from a Fabric notebook. After installing Semantic Link Labs:

%pip install semantic-link-labs

…I used the following Python code in a notebook cell to run a refresh of type clearValues, followed by a full refresh, followed by a manual replica sychronisation:

import sempy_labs as labs
WorkspaceName = "Model Memory Tests"
SemanticModelName = "ModelMemoryDemo3"
# 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")
# then a manual replica sync
labs.qso_sync(dataset=SemanticModelName, workspace=WorkspaceName)

And as expected the card visual shown above showed data all through the refreshes.