Hi friends,
Today we will show you how we can refresh a dataset published in Power BI from a Power Shell Script that we would invoke at the end of our ETL process.
We will use the Power BI libraries for power shell to connect to our power Bi portal and send an instruction to refresh a data set. This could be useful to improve our ETL processes, refreshing our on-line datasets used in Power Bi portal before loading data into our data-warehouse and/or our OLAP/Tabular database send an instruction to.
Note that if the dataset is not in a workspace assigned to Premium capacity, then you will be limited to eight refreshes per day. Datasets in workspaces assigned to Premium will support up to 48 refreshes a day.
In order to perform this operation, we will need to create an Azure application with access to our Power BI portal, to do so, follow this steps:
Connect to https://dev.powerbi.com/apps and log in as a Power BI admin user:
Create a new application using this information:
-App Type: Native app
-Redirect URL: “urn:ietf:wg:oauth:2.0:oob” (without quotes)
-Level of access: check all dataset APIs permissions
Then we need to assign permissions to our Power Bi admin user to use the application. To do that connect to Azure portal and enter the “Azure Active Directory” tool following these steps:
1.- Enter in the App Registry (marked in yellow)
2.- Then click on “All applications” to see all the applications you own, and select the application that will refresh our dataset (marked in yellow):
3.- Add our user as the owner of the application:
4.- At this point we must grant permissions to our application to access active directory:
We must grant access to the datasets of Power BI too:
Once this steps are done, it’s time to code (let’s rock !)
To create the script we must install and add the following libraries to our script:
Microsoft.IdentityModel.Clients.ActiveDirectory.dll
Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll
We do that with thiscommand: https://docs.microsoft.com/es-es/powershell/azure/install-azurerm-ps?view=azurermps-5.5.0
# Install the Azure Resource Manager modules from the PowerShell Gallery Install-Module -Name AzureRM -AllowClobber
In our code we need to parametrize the client Id (that is our app client ID), the dataset ID that we can take it from power BI portal when we click on the dataset options (app.powerbi.com/groups/{groupID}/settings/datasets/{datasetID} ). Note that the group Id must be “me” in the script because we are using “My workspace” workspace.
The entire code with comments are here:
# Parameters - fill these in before running the script! # ===================================================== # An easy way to get group and dataset ID is to go to dataset settings and click on the dataset # that you'd like to refresh. Once you do, the URL in the address bar will show the group ID and # dataset ID, in the format: # app.powerbi.com/groups/{groupID}/settings/datasets/{datasetID} $groupID = "me" # the ID of the group that hosts the dataset. Use "me" if this is your My Workspace $datasetID = "d9b4fd0c-7ac9-4e5d-be42-76686ce6b2db" # the ID of the dataset that hosts the dataset # AAD Client ID # To get this, go to the following page and follow the steps to provision an app # https://dev.powerbi.com/apps # To get the sample to work, ensure that you have the following fields: # App Type: Native app # Redirect URL: urn:ietf:wg:oauth:2.0:oob # Level of access: all dataset APIs $clientId = "Client ID" # End Parameters ======================================= # Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD function GetAuthToken { $adal = "C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.1\Automation\Microsoft.IdentityModel.Clients.ActiveDirectory.dll" $adalforms = "C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.1\Networking\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll" [System.Reflection.Assembly]::LoadFrom($adal) | Out-Null [System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null $redirectUri = "urn:ietf:wg:oauth:2.0:oob" $resourceAppIdURI = "https://analysis.windows.net/powerbi/api" $authority = "https://login.microsoftonline.com/common/oauth2/authorize"; $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority $UserCred = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList “UserName@domain.com“, “myPassword” $authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $clientId, $UserCred).Result return $authResult } # Get the auth token from AAD $token = GetAuthToken # Building Rest API header with authorization token $authHeader = @{ 'Content-Type'='application/json' 'Authorization'=$token.CreateAuthorizationHeader() } # properly format groups path $groupsPath = "" if ($groupID -eq "me") { $groupsPath = "myorg" } else { $groupsPath = "myorg/groups/$groupID" } # Refresh the dataset $uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes" Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST –Verbose # Check the refresh history $uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes" Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET –Verbose
And you’re all set!
This post was possible thanks to PatAltimore with his work in https://github.com/Azure-Samples/powerbi-powershell/blob/master/manageRefresh.ps1 We only did some changes to his code to make it work with our libraries. Thanks Pal!
Stay tuned for more news on our blog and subscribe to our newsletter if you want to receive our new posts in your mail, get course discounts… 🙂
Now I want to schedule multiple data set refreshes in a way once previous data set refresh finished only then next data set refresh begins. Could you please provide help in this.
In this case I will check through a pseudo infinite while loop if the previows dataset finished to refresh, this could be done with the method:
https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getrefreshhistory
And only when the dataset finish its refresh then will pass to the next dataset
Hola Jose, he intentado hacer lo que indicas en tu código para lograr la actualización de conjunto de datos en Power BI con Powershell; sin embargo, cuando lo ejecuto me sale el siguiente error:
You cannot call a method on a null-valued expression.
At line:36 char:1
+ $authHeader = New-Object -TypeName @{
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
¿Cómo podría solucionarlo?
¡Gracias!
DC
En que línea de código exactamente te da ese error?
En esta línea:
$authHeader = @{
“Content-Type”=”application/json”
“Authorization”=$token.CreateAuthorizationHeader()
}
Lo más probable es que te esté dando algún problema la función GetAuthToken, yo probaría a debugar el código a ver que te está devolviendo la variable $authResult.
Aparentemente es null y no debería.
Sí sale vacía, ¿porqué estaría pasando esto?
Estas son las líneas que estoy usando (he cambiado previamente mis credenciales):
$groupID = “me”
$datasetID = “12ab3cd4-xxxx-678h-9012-ij34k56lmn78”
$clientId = “1234a5b6-xxxx-8901-2f23-456789012345”
function GetAuthToken
{
$adal = “C:\Program Files\WindowsPowerShell\Modules\Azure\5.3.0\Automation\Microsoft.IdentityModel.Clients.ActiveDirectory.dll”
$adalforms = “C:\Program Files\WindowsPowerShell\Modules\Azure\5.3.0\Networking\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll”
[System.Reflection.Assembly]::LoadFrom($adal) | Out-Null
[System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null
$redirectUri = “urn:ietf:wg:oauth:2.0:oob”
$resourceAppIdURI = “https://analysis.windows.net/powerbi/api”
$authority = “https://login.microsoftonline.com/common/oauth2/authorize”;
$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority
$UserCred = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList “micuentade@powerbi”, “micontraseñadepbi”
$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $clientId, $UserCred).Result
return $authResult
}
# Token de autenticación de AAD
$token = GetAuthToken
Lo único que se me ocurre es que haya algun problema con los ensamblados que usas, prueba con un try catch a ver si arroja algún error más descriptivo.
He aplicado el try catch y me sale este mensaje:
Inicio del programa
WARNING: Mensaje:
You cannot call a method on a null-valued expression.