Tech Talk Thursday: Automatically Updating Partitions in Power BI Semantic Models

Often when working with large semantic models, we see fact tables with many partitions.  A common pattern is to partition by a date grouping (ie month, quarter, etc).  Partitioning a fact table this way can provide many benefits: greater control over refreshes, faster refresh times, ability to re-run specific partitions if needed.

This approach also means that the partitions need to be updated.  For example, if your partitions are monthly, you may want to remove the oldest partition to keep the size of the semantic model manageable.  In this blog post, we’re going to look at one way to automate this process using XMLA and Powershell. 

In this article, we’ll demonstrate how to add and remove partitions programmatically in a self-contained Azure Runbook.  In our example scenario, we have a model called “AdventureWorks Demo” with monthly partitions configured in the pattern below.  The goal is to remove partition 2022-11 and add a new 2023-11 partition with the appropriate M code.  The current partition structure is displayed below:

Prerequisites:

Power BI Premium Capacity or Premium per User

We’ll be utilizing XMLA endpoints to update partitions, which are not available with a Pro license.  XMLA endpoints facilitate communication between client applications and our semantic models.  Ensure XMLA Read/Write is enabled in your Admin settings.  Additional documentation on XMLA endpoints can be found here Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft Learn

Tabular Editor 

Either 2.x (free version) or 3.x will work! Tabular Editor can script out the delete and create XMLA for us. Tabular Editor 3

Power BI Service Principal

We’ll use a service principal to authenticate.  Ensure that the setting to allow service principals to use Power BI APIs is enabled in your Admin settings and that the appropriate security group is added:

Azure Automation Account

We’ll need to create a runbook for our Powershell script.  Also, we’ll need to install the SqlServer module (PowerShell Gallery | SqlServer 22.1.1):

Azure Key Vault

We’ll use key vault to store unique identifiers and secrets securely.  Ensure that your automation account is added to the access control list for the key vault.

Creating the Runbook

First off, we need to create and configure the runbook that will contain our Powershell code.  Within the automation account, navigate to Runbooks > Create a runbook

Once we create the runbook, we’ll be greeted with the script editing interface.  Let's add some code!

I’ve attached the full code here, but wanted to take a minute to explain what’s going on in each code block.

The first few lines simply get our key vault values, create credentials, and set the server/database names.

The next block of code creates string and integer variables that will be used to pass into our XMLA script.  In our example scenario, recall we have monthly partitions and we’re only interested in keeping 12 months of data.  We can modify AddYears(-1) to remove partitions further back.  Also, depending on how your partitions are named, the “yyyy-MM” format string may need to be modified.

Finally, we have our XMLA script which is referencing the variables we created above and the Invoke-ASCmd line to execute the query against our semantic model.

One quick way to generate the XMLA required for manipulating partitions is by using Tabular Editor.  Right clicking a partition and using Script TMSL > Delete > To Clipboard will give you a good starting point.  To string together multiple operations, we simply need to wrap them in a sequence command Sequence command (TMSL) | Microsoft Learn

Once we’ve completed the script, we can test our runbook!  Click “Save” and the “Test pane” button at the top of the runbook:

Click “Start” to run the script:

After a moment you should see that the script executed successfully:

If we go back to Tabular Editor and refresh our metadata, we can verify that the 2022-11 partition was removed and the new 2023-11 partition was created successfully:

Finally, publish your runbook:

Closing thoughts and considerations

In this article we showed how to create a simple Powershell runbook that executes an XMLA script against a Power BI semantic model to update partitions.  

Depending on your use case and current ETL/ELT pipeline, you may want to call this runbook from a tool like Data Factory or Synapse.  To do so, you would need to add a webhook to the runbook.  You can then use a Web activity to trigger the runbook.  Note that if you are using the Web activity, it will simply show “Completed” as soon as the script is triggered and not actually complete.  

Alternatively, runbooks have a built-in scheduling tool where you can create a recurring schedule for your specific use case.