Introduction
Speak 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
Installing the Power BI cmdlets
Click here to view contentOpen Powershell (ISE or Command Prompt) as Administrator:
On the Command Prompt screen, enter the command “powershell”To initialize the PowerShell interface.
After that, type the command “MicrosoftPowerBIMgmt Install-Module”To install Cmdlets. A confirmation will probably appear if you trust this repository. Check the option “Yes for everyone”:
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!.
Creating the Powershell Script to Update Data
Click here to view content- 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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #1. Autenticação $Senha = "senha_dificil_123" $SenhaSegura = ConvertTo-SecureString $Senha -AsPlainText -Force $Credencial = New-Object System.Management.Automation.PSCredential($Usuario,$SenhaSegura) Connect-PowerBIServiceAccount -Credential $Credencial #2. Recupera o ID do Workspace e do Dataset $Workspace_ID = "8fda9bda-13c5-45ec-9a4f-3bcfe51ed57f" $Dataset_ID = "fd3df53a-9a3a-40ee-91f0-585c9fb52c67" #3. Monta a URL de atualização do DS $Url_Atualizacao = 'groups/' + $Workspace_ID + '/datasets/' + $Dataset_ID + '/refreshes' #5. Executa a atualização e notifica por e-mail em caso de falha $NotificacaoFalhaPorEmail = @{"notifyOption"="MailOnFailure"} Invoke-PowerBIRestMethod -Url $Url_Atualizacao -Method Post -Body $NotificacaoFalhaPorEmail |
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 made available with these 2 ID's:
| 1 2 3 | #2. Recupera o ID do Workspace e do Dataset $Workspace_ID = "ee72886a-26c8-4d59-97f6-e7b811adc507" $Dataset_ID = "3aaf8f29-f54b-4b9c-a171-d8d0ef2fcea7" |
If you want to update more than one dataset in the script, just do this:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #2. Recupera o ID do Workspace e do Dataset $Workspace_ID = "8fda9bda-13c5-45ec-9a4f-3bcfe51ed57f" #3. Monta a URL de atualização do DS $Url_Atualizacao1 = 'groups/' + $Workspace_ID + '/datasets/fd3df53a-9a3a-40ee-91f0-585c9fb52c67/refreshes' $Url_Atualizacao2 = 'groups/' + $Workspace_ID + '/datasets/fd3df53a-9a3a-40ee-91f0-449e4c453c13/refreshes' $Url_Atualizacao3 = 'groups/' + $Workspace_ID + '/datasets/fd3df53a-9a3a-40ee-91f0-fef43a0bc123/refreshes' #5. Executa a atualização e notifica por e-mail em caso de falha $NotificacaoFalhaPorEmail = @{"notifyOption"="MailOnFailure"} Invoke-PowerBIRestMethod -Url $Url_Atualizacao1 -Method Post -Body $NotificacaoFalhaPorEmail Invoke-PowerBIRestMethod -Url $Url_Atualizacao2 -Method Post -Body $NotificacaoFalhaPorEmail Invoke-PowerBIRestMethod -Url $Url_Atualizacao3 -Method Post -Body $NotificacaoFalhaPorEmail |
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 contentIdeally, 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 the “Operating system (CmdExec)” type, you will put the following command to execute:
powershell.exe -ExecutionPolicy Unrestricted -File "C:\Scripts\Atualiza Power BI.ps1"
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.
How to run the Powershell script through Integration Services (SSIS)
Click here to view contentTo do this, add a “Run Process” task, which is used to run commands at the operating system level:
At the setup screen for this task, type powershell.exe in the “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 the screen during the update:
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.
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!
Hello Dirceu, I'm wanting to run a script in ps inside the sql agent. My scenario, I do BKP through a script where it reads from a table then I run the script right after I call the agent of each server “I have 3” where each one reads what they need to restore the BKP in their instance, but the part of executing the script from powershell doesn't seem to be running when it's in the sql agent.
Hello Dirceu good morning. Is it possible to do something similar for Power BI Report Server?
Hi Dirceu.
Is this process for those who use data flow in Power BI?
Luciel,
This script is for the default PowerBI data set only.
Dataflow also has an API that you can use with powershell to update data as well, but the URL is different.
I'll see if Rafael will write about it, otherwise I create the article.