Refresh Individual Power BI Tables or Partitions with PowerShell

Power BI Service allows you to refresh your model completely but sometimes that's not what you want specifically when you are making changes and testing, you need something more granular that allows you to interact with just one or two objects, therefore, let's see how you can refresh a single table or more tables and objects with PowerShell.

In this blog we are going to use the TOM library to modify and interact with objects, in the next one we will see how to do the same with REST API.

First we need to load the Tabular Object Model library and declare some variables that allow us to create a connection string to connect to the dataset in Power BI Service.

You can install the TOM Library by using:

Set-ExecutionPolicy Unrestricted
Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted

Import-Module PowerShellGet

Install-Package Microsoft.AnalysisServices.retail.amd64 -Source "https://www.nuget.org/api/v2" -SkipDependencies

Run the above code in PowerShell as an Administrator.

If you installed TOM Library for the first time by using PowerShell it will sit in the folder:

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.retail.amd64.19.61.1.4\lib\net45

So you will need to use the command

Add-Type -Path "C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.retail.amd64.19.61.1.4\lib\net45\Microsoft.AnalysisServices.Tabular.dll"

If the TOM library was installed using C# & Visual Studio then you can use:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")

So your code will look like this:

# Load DLL

Add-Type -Path "C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.retail.amd64.19.61.1.4\lib\net45\Microsoft.AnalysisServices.Tabular.dll"

# Either the above or the below one:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")

$ServerXMLA = "powerbi://api.powerbi.com/v1.0/myorg/WORKSPACENAME"
$UserName = "PBI Service user Email"
$UserPass = "PBI Service user passs"

$ConnectionString = "DataSource=$($ServerXMLA);User ID=$($UserName);Password=$($UserPass)"

Next we need to create a Server object and connect to the target workspace/database using the Connect method of the Server.

$Server = New-Object Microsoft.AnalysisServices.Tabular.Server
$Server.Connect($ConnectionString)

Since we are using unattended method we won't see a login pop up but if you don't use $Username & $UserPass you will have to enter them manually each time you run the complete code.

Next we can find the database in the workspace and get the Model object:

$Database = $Server.Databases.FindByName("Contoso 500K")
$Model = $Database.Model

Now that we have the connection to the Model object we can get individual Tables, Columns, Measures and other objects by just accessing the collection of those objects.

For example I want to refresh the Products table completely so I can use the RequestRefresh Method and provide "Full" as the type of refresh, other refresh options are ClearValues, Calculate (dataset level), DataOnly, Defragment, Automatic.

$ProductsTable = $Model.Tables["Products"]
$ProductsTable.RequestRefresh("Full") #ClearValues, Calculate, DataOnly, Defragment, Automatic

If you run ClearValues on individual Table you can actually clear out the whole data model, and then you can fill up individual tables by just using DataOnly.

The ClearValues and DataOnly can also be performed at the data dataset level, but after DataOnly you also need to run Calculate so that relationships, hierarchies, calculated columns, calculated tables can also be populated.

After requesting the refresh you also need to save the model otherwise it is just a command that was issued in one line but not approved in another one.

$Model.SaveChanges()

Now just like that you can perform refresh only on specific tables.

For the whole model you can just run:

$Model.RequestRefresh("Full")
$Model.SaveChanges()

Complete code:

# Microsoft.AnalysisServices.Tabular = Tabular Object Model library available when you install Power BI, SSAS etc

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")

$ServerXMLA = "powerbi://api.powerbi.com/v1.0/myorg/WORKSPACENAME"
$UserName = "PBI Service user Email"
$UserPass = "PBI Service user passs"

$ConnectionString = "DataSource=$($ServerXMLA);User ID=$($UserName);Password=$($UserPass)"

$Server = New-Object Microsoft.AnalysisServices.Tabular.Server
$Server.Connect($ConnectionString)

$Database = $Server.Databases.FindByName("Contoso 500K")
$Model = $Database.Model

# Refresh single table

$ProductsTable = $Model.Tables["Products"]
$ProductsTable.RequestRefresh("Full") #ClearValues, Calculate, DataOnly, Defragment, Automatic

$CustomerTable = $Model.Tables["Customer"]
$CustomerTable.RequestRefresh("ClearValues")

Write-Output $Model.SaveChanges().XmlaResults

To refresh specific partitions we can use:

$TargetTable = $Model.Tables["Sales"]
$TargetPartition = $TargetTable.Partitions["2015"]
$TargetPartition.RequestRefresh("Full")

Write-Output $Model.SaveChanges().XmlaResults

Refreshing tables based on a CSV file

Now let's take a look at how we can automate this and specify workspace, dataset, and tables in a CSV file so that we can run the code once and based on the id in the CSV file multiple tables in different datasets can be refreshed.

We could use Excel file but loading Excle table into a multi dimensional array is a lot of code and I wanted to keep it simple.

Since we are automating stuff let's make things more robust so that instead of writing the password in $UserPass variable in the code we use a file that has ID and Password.

To create such file we need to run:

It will create a file that looks like this:

This will allow us to securely load ID and Password.

Next we can use the reverse of the previous code which is Import-Clixml to read ID & Password

Next we need to have a setup which looks like this and save it as a CSV file.

To read the CSV File and select relevant columns we use:

On the Import-CSV Module we can iterate using ForEach-Object and get WorkspaceID, DatasetID etc.

Import-CSV "C:\Users\antsharma\OneDrive\Desktop\Power BI Refresh Schedule.csv" | 
   ` Select-Object WorkspaceID, DatasetID, TableName, DatasetName |
   ` ForEach-Object {
        $WorkspaceName = $_.WorkspaceName
        $TableName = $_.TableName
        $DatasetName = $_.DatasetName
     }

$WorkspaceName, $TableName & $DatasetName store the respective objects from the currently iterated row.

Once we have this stored we can basically reuse the code used in the first part of the blog and call the Server object each time for different object, as shown below.

And if you run this you will be able to refresh multiple objects situated in different datasets, workspaces! Pretty cool, isnt' it?!

Complete code:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")

$FileName = "C:\Users\antsharma\OneDrive\Desktop\PBI Service Credentials.txt"

# $Credentials = Get-Credential
# $Credentials | Export-Clixml -Path $FileName

$FileCred = Import-Clixml -Path $FileName

$UserName = $FileCred.UserName
$UserPass = [System.Net.NetworkCredential]::new("", $FileCred.Password).Password

Import-CSV "C:\Users\antsharma\OneDrive\Desktop\Power BI Refresh Schedule.csv" | 
   ` Select-Object WorkspaceName, DatasetID, TableName, DatasetName |
   ` ForEach-Object {
        $WorkspaceName = $_.WorkspaceName
        $TableName = $_.TableName
        $DatasetName = $_.DatasetName

        $ServerXMLA = "powerbi://api.powerbi.com/v1.0/myorg/$($WorkspaceName)"
        $Server = New-Object Microsoft.AnalysisServices.Tabular.Server

        $ConnectionString = "DataSource=$($ServerXMLA);User ID=$($UserName);Password=$($UserPass)"
        $Server.Connect($ConnectionString)

        $Database = $Server.Databases.FindByName("$($DatasetName)")
        $Model = $Database.Model

        $TargetTable = $Model.Tables["$($TableName)"]
        $TargetTable.RequestRefresh("Full")

        $Model.SaveChanges()
        $Server.Disconnect()
     }
      

So, in this blog we have seen how to do this with TOM Library, in the next one we will perform the same operation using REST API.