If you want to refresh a Power BI dataset, we all know where to find the refresh button in Power BI Desktop as well as in the Power BI Service. By clicking it, you will trigger the entire dataset to refresh. But sometimes it is more convenient to trigger a single table to refresh. If you want to do this, you can do a simple right-click on a table in Power BI Desktop, but how does this work in the Power BI Service? In this blogpost I will describe how you can trigger a single table refresh in the Power BI Service over XMLA endpoints. Please know, this does require Power BI Premium (either Premium per User or Premium Capacity is fine).

Why do you want to refresh a single table?

Imagine that you work with a million-row dataset, but you just changed that one thing in a specific table. Then you probably do not want to refresh the entire dataset, as this might take a while. In this case it can be very useful if you can trigger just that one table to refresh.

Let’s take another scenario. Imagine that you want to approach a near real time scenario. In that case, you might want to refresh your fact table multiple times during the day, for example every 30 minutes. In that case, you want the refresh to run and complete fast! Refreshing all your dimensional tables might be unnecessary, as they are not likely to be changed often. Of course, incremental refresh will definitely help here!

As Power BI always checks the validity of your data model during a refresh, this slows down your refresh. Other than that, refreshing all the dimensions, which generates unnecessary traffic, also slows down the refresh obviously. So, there is more that you can do! Trigger only what matters to refresh during the day, which is your fact table, and execute a full (scheduled) refresh overnight for example. But how to do this?

Tabular Model Scripting Language

With Tabular Model Scripting Language (TMSL), you can automate and script tasks to be executed on every Tabular Model with compatibility level 1200 or higher. With that, you can directly conclude that the approach described works for both (Azure) Analysis Services and Power BI Premium.

With TMSL, you can create, alter, backup, delete and refresh objects in your Tabular Model. So, for our use case we will use the Refresh commands. There are various types of refresh that you can trigger using the refresh commands. Such as full refreshes, data only or other optional parameters that can be applied. In the end, the TMSL script will be in a JSON format that we have to push to the endpoint of our dataset. Below a sample TMSL that will process a single table (Date) in the specified dataset (AdventureWorksTabular1200).

{  
  "refresh": {  
    "type": "automatic",  
    "objects": [  
      {  
        "database": "AdventureWorksTabular1200",  
        "table": "Date"  
      }  
    ]  
  }  
}

ASCmd PowerShell cmdlets

Now that we know how we can trigger that refresh, we only need to find a way to push this TMSL JSON to the endpoint. This is what we will do using PowerShell. As part of the SQL Server PowerShell cmdlets, we can leverage the ASCmd library, which enables database administrators to execute an XMLA script, TMSL script, Data Analysis Expressions (DAX) query, Multidimensional Expressions (MDX) query, or Data Mining Extensions (DMX) statement against an instance of Analysis Services. Knowing that Power BI uses the Analysis Services engine under the hood, we can assume that this also works for Power BI. Spoiler: it does!

With Invoke-ASCmd, we can invoke the earlier used TMSL example to be executed on our data model. As said, this is what we will do in PowerShell. but first we must specify the endpoint of Power BI where the dataset is located. As the approach described communicates over the XMLA endpoint of Power BI, which needs to have read and write functionality enabled, we can grab the XMLA endpoint from the Workspace Settings in the Power BI service.

In the sample that Microsoft shared in the documentation, they saved the TMSL JSON in a file on the computer. In my example, I decided to make it as flexible as possible, by including it in the PowerShell script, as well as specifying the XMLA endpoint for Power BI. Below I will elaborate on how to use the script and how it works.

Use the script

First, you need to specify a few parameters to run the script. These three are listed in the top section of the script:
– $WorkspaceName: the name of the workspace in the Power BI service
– $DatasetName: the name of the dataset in the Power BI service (usually similar to the pbix filename)
– $TableName: the name of the table in your dataset that you want to refresh.

These variables will be used to dynamically specify the XMLA Endpoint connection for the workspace, as I concatenate the specified workspace name with the $PbiBaseConnection variable in the script.

The specified $DatasetName and $TableName are used in the TMSL script, that is also defined as a variable. The TMSL JSON will be automatically generated based on the given variables. Please also know that the optional parameters for refresh type are set to “automatic” in this script. You can change the variable if you like.

# TMSL Script
$TmslScript = 
@"
    {  
      "refresh": {  
        "type": "automatic",  
        "objects": [  
          {  
                "database": "$DatasetName",  
                "table": "$TableName"
          }  
        ]  
      }  
    }  
"@

Finally, the generated TMSL JSON, will be executed over the earlier specified XMLA Endpoint. This is what you will find in the Try{} part of the script. To send this TMSL JSON, we use the Invoke-ASCmd command from the SQL Server PowerShell cmdlets. During script execution, you will be prompted to authenticate to the Tabular Model. In case of Power BI, you can use OAuth authentication and sign in with your user account. Please know that you need to have sufficient privileges on workspace level to execute this refresh.

Finally, we want to check whether our Table refreshed successfully. Therefore, I used Tabular Editor. When I connect to the XMLA Endpoint of the dataset in Tabular Editor, I can easily navigate to the table and underlying partitions that I triggered to refresh. As I open the partition section, I can see the Last Processed date and time of this specific table. Please know, that this date and time are in UTC timezone, so you might notice a little difference with your current time.

Something thing to keep in mind, is that this refresh will not show up in the regular refresh metrics of the Power BI Service. So, you have to use another approach like described above to trace if your table has processed or not.

Wrap up

I believe it is very powerful to trigger a single table to refresh in Power BI Desktop as well as in the Power BI service. For scenarios where you want to quickly update your fact table or any other table in large data models, this will help you to speed up this process.

The full script including notes in the script can be found on my GitHub. The script that I shared, includes a TMSL JSON that simply triggers a refresh from the specified table. But please know, that you can execute whatever TMSL JSON / XMLA script you prefer in the very same way. using the ASCmd commands in PowerShell.

Finally, running this from PowerShell is super useful if you want to trigger the refresh ad-hoc. But if you want this refresh to be executed multiple times per day, automatically, you have to find a way to trigger this script to run. The authentication step in this script will be your challenge in this case, especially when using two-factor authentication. A little spoiler here, soon I will also post a new blog on how you can execute a single table refresh in Power BI using Azure Data Factory.

Links

• Documentation Tabular Model Scripting Language
• Documentation TMSL refresh commands
• Documentation SQL Server PowerShell cmdlets
• Documentation Invoke-ASCmd commands
• Power BI dataset connectivity Workspace connection URL
• GitHub: PowerShell script to trigger refresh in specified table