Using PowerShell and the API with Agent or SSIS to Automate Updating a Power BI Data Set

Views: 1.120
Reading Time: 7 minutes

Introduction

Hey guys!
In this article I am sharing with you, I would like to help you with a very common BI problem and need to update Power BI reporting data automatically more than once a day. Usually I see many scenarios where the BI team creates the automatic routine to update data in the database, set to run at specific times, and then schedule that data update in the Power BI service at a time where probably The data has already been updated by the ETL / DW routine.

So when the routine takes longer than expected, your data is out of sync, and really, keeping that sync between bank routine execution times and Power BI upgrade is a very complicated task.

Wouldn't it be BETTER if you had the possibility that the routine itself that loads and processes data also starts updating Power BI data sets? It's time to learn how to do it isso

The tip of this article refers to data sets where the storage mode is Import, which copies the data from the source and takes it to the Power BI service. If you are using DirectQuery, there is no need to automate data refresh as the query is done dynamically at the data source. Just as the Power BI scheduled update can only automatically update one 8 data set times per day on the Pro account (and 48 times Premium), the on-demand update is also within that same limit
Updating data using the Power BI API only works if your user is a Workspace administrator. Otherwise, the script will display an error message when it runs.
If this script is not working and you get the error message “Connect-PowerBIServiceAccount: Failed to populate environments in settings” when trying to connect to the Power BI service, read the article Connect-PowerBIServiceAccount: Failed to populate environments in settings

Installing the Power BI cmdlets

Click here to view content
The first step in successfully interacting with the Power BI service through Powershell is to install cmdlets that facilitate communication with the service's API. In addition to updating the dataset, you can perform various automations and controls using this feature as Rafael Mendonça, one of the world's leading Power BI experts, shows us in the article [POWER BI] - Cmdlets for PowerShell - Administering Power Bi like a Pro!.

Open Powershell (ISE or Command Prompt) as Administrator:

At the Command Prompt screen, type the command “powershell”To initialize the PowerShell interface.

After that, type the command “MicrosoftPowerBIMgmt Install-Module”To install the Cmdlets. A confirmation will likely appear if you trust this repository. Check "Yes for all":

After that, the cmdlets are already installed and we can start using them. If you want to use the PowerShell ISE interface to install cmdlets, feel free. The installation follows the same steps. If you would like to learn more about these cmdlets and some installation details such as installing modules separately and also examples using PowerShell ISE, I recommend reading this article. [POWER BI] - Cmdlets for PowerShell - Administering Power Bi like a Pro!.

If your PowerShell finds an error while trying to run the Install-Module command, it is because your machine is still using PowerShell 4.0 or earlier. If this is your case, you will need to install the Windows Management Framework 5.1 to add this module to your PowerShell.

Creating the Powershell Script to Update Data

Click here to view content
In our data update script we will use the following methods:

  • Connect-PowerBIServiceAccount: Serves to connect to the Power BI service using the username and password of this Power BI account
  • Invoke-PowerBIRestMethod: Using to make a Power BI API call that will perform certain actions according to the parameters used

Below, I will provide a script to update a specific dataset:

To identify the Workspace and Dataset ID, simply go to the Power BI side menu, open the workspace where that dataset is located, and then click the dataset you want to update:

When you open the dataset, you can get the Workspace and dataset ID from the URL:

Replace the script I provided with these 2 ID's:

If you want to update more than one dataset in the script, just do this:

After making this change, save the script with the name you want, but the file extension must be . Ps1.

How to run Powershell script from SQL Server Agent

Click here to view content
After you create the upgrade script, you now simply run it from some tool to automate this execution. This tool can be SQL Server Agent, Windows Task Scheduler, SQL Server Integration Services (SSIS), Pentaho, or any other tool that can execute operating system level commands. In this article I will demonstrate how to do this right through a SQL Server Agent job and within an SSIS package.

Ideally, this Power BI upgrade process should be in the same job as updating and processing the database data:

And in this new step, which will be of type “Operating system (CmdExec)”, you will put the following command to execute:
powershell.exe -ExecutionPolicy Unrestricted -File "C:\Scripts\Atualiza Power BI.ps1"

The -ExecutionPolicy switch is for Powershell to be able to execute the script in an execution context without any restrictions that could cause an error during script processing.

Remember to change the path of the .ps1 file to the location where you saved your script. After scheduling the job, your Power BI report is already being automatically updated at the end of processing.

The user running the SQL Agent service must have the necessary permissions to run this Powershell script.

How to run the Powershell script through Integration Services (SSIS)

Click here to view content
In addition to SQL Server Agent, you can also run the PowerShell script directly from SSIS to update your report data.

To do this, add a “Run Process” task, which is used to execute operating system level commands:

At the setup screen for this task, type powershell.exe “Executable” parameter and -ExecutionPolicy Unrestricted -File “C: \ Scripts \ Update Power BI.ps1” in the “Arguments” parameter. Optionally change the "Window Style" setting to "Hidden" so that the window does not appear on screen during the upgrade:

Example:

If your SSIS package is already running automatically by SQL Server Agent, you don't have to do anything else. Otherwise, add to your SQL Server Agent job one more step to run the package that will upgrade Power BI:

When the job runs, Power BI data will be updated at the end of the job.

The user running the SQL Agent service must have the necessary permissions to run this Powershell script.

Conclusion

Using this Powershell script that I shared in the article, you can now automatically update Power BI data right after the data has been processed in the database, without having to try to synchronize the time that data processing ends with the schedule. Power BI update starts there in the service.

Example of automatic update using Powershell:

Remember again that updates using the API remain within the limit of 8 Pro account daily updates (and 48 on Premium account). That is, use wisely .. lol

Hope you enjoyed this tip and see you next time!