An approach to custom partitioning a Power BI Dataset and Refresh using the Enhanced Refresh API

A key feature to optimize your #powerbi dataset refresh is to partition your dataset tables, this will allow a faster & reliable refresh of new data simply because with partitions you can divide the table data into logical parts that can be managed & refreshed individually.

More information about dataset partitioning in this document.

You have two options to partition your tables:

• Enable the Incremental Refresh feature
• Manually/Automatically define your own partitions using the XMLA Endpoint, see this awesome video on Demystifying Custom Partitioning?by Guy in a Cube for more information

In this article I'll demonstrate a simple but effective way to automatically create & manage partitions, how to refresh them individually and link the refresh operation to upstream ETL pipelines. This process may be easily adapted to your own scenario:

All the source code & samples can be downloaded from this github repo: RuiRomano/pbicustompartitioning (github.com)

We start from a simple Power BI Desktop file connecting to the Contoso sample database:

Let's start by publishing this dataset to a Premium workspace:

Using the workspace XMLA Endpoint you can use DAX Studio and View Metrics to inspect the default partitions:

My goal is to create monthly partitions to the "Sales" table so I can individually refresh each month, I have data since 2018 so I need to create 48 partitions where each partition will filter an individual month.

You can create these partitions manually using SSMS but that's a lot of work... A much efficient solution is to script this operation using PowerShell using the TOM Library, I created the script "Script - CreatePartitions.ps1" that will dynamically create all the partitions where each partition filters the appropriate month that will be folded to the database.

The cmdlet "Add-ASTablePartition" is responsible to create all the partitions that exists in a helper module I created to wrap the TOM Library: TOMHelper.psm1.

The M code above is obtained from the Advanced Editor in the Power Query window, for future changes you can develop/test in there and copy a new version to the script:

After you run the script all the partitions will be created:

Now that we have our monthly partitioning it's time to refresh and my favourite option to refresh a Power BI Dataset is using the Enhanced Refresh API mainly because it allows you to:

• Asynchronously refresh your dataset without need to keep an active connection
• Granular table / partition refresh
• Control the Max Parallelism of the queries sent to the data sources
• Cancel the Refresh

For me the most important feature is the "Granular table/partition refresh" because it allows you to batch your dataset refresh that means executing your refresh in multiple and smaller refresh commands and transactions that will allow you to:

• Align your dataset table refresh with data availability on the source
• Minimize the risk of running out of memory because you are not refreshing the entire dataset in a large transaction
• Enable near-real time scenarios by refreshing a single partition with daily/hourly data

For example, in our scenario we may want to create two refresh batches:

• Batch that only refresh the dimension tables with max parallelism
• Batch that refreshes the last 3 months of data with a controlled max parallelism due to the large data volumes involved in the fact tables

I'll show you two options to refresh the dataset using the Enhanced Refresh API and both can be easily reused and linked with other pipelines:

• PowerShell script using the MicrosoftPowerBIMgmt module
• Azure Logic Apps - Preferred option due to its ease of creation and flexibility to link to other Azure Pipelines like Azure Data Factory, Azure Synapse Analytics, ...

The PowerShell option is useful when you're dealing with on-prem pipelines or need more flexibility, I created the script Script - DatasetRefresh - API.ps1 as an example that can easily be adapted to your scenario. The script does the following actions:

• Declares two refresh batches Dimensions & Facts with different refresh configurations
• For each batch executes the Enhanced Refresh API and waits for the execution to finish, because you cannot have more than one refresh command running in parallel

If you are in the cloud, then Azure Logic App is a better option, its low code, easy to deploy, secure by default and it's very easy to design a workflow to refresh your dataset and link it to other upstream/downstream data pipelines.

I created a simple & generic logic app that:

• Is triggered by an HTTP Request
• Has a parameter activity with JSON configuration to specify the refresh options (max parallelism, retry count, commit mode) and the tables/partitions you want to refresh
• Executes the dataset refresh using the HTTP Action & Enhanced Refresh API
• Wait for the Dataset Refresh to end

In this scenario I created two logic apps with different configuration but same implementation:

• "PBI-CustomPartitioning-Dimensions" - Refresh all the dimension tables
• "PBI-CustomPartitioning-Facts" - Refresh the last 3 months of the "Sales" table

Both logic apps could be triggered on different times and have different refreshing configurations, to change you just need to edit the "Refresh Parameters" action at the beginning of the flow:

The logic app gets triggered by an HTTP GET request, this means that to trigger your dataset refresh (facts or dimensions or both) you just need to execute an HTTP call from the upstream/downstream pipeline to the URL on the trigger action:

To create the logic app please follow the following steps:

Create a new Logic App on your Azure Subscription with the following parameters:

Open the "Logic App Code View" and copy the contents of the file LogicApp - Refresh.json

Enable the Managed Identity of the Logic App, this will automatically create a service principal for the logic app that will be the identity used to refresh the dataset:

You also need to authorize this Managed Identity to:

• Be allowed to execute Power BI APIs (adding the service principal to the tenant setting 'Allow service principals to use Power BI APIs')
• Be a workspace member where the dataset is deployed

More information on Logic Apps Managed Identity here.

Finally, if you switch to the Logic App Designer you should be able to see the logic app workflow and change the "Refresh Parameters" action to point it to your dataset.