Tuesday, August 4, 2020

Refresh a Power BI Dataset with PowerShell and a Service Principal

After Installing the PowerShell Power BI Management cmdlets a few months back, I finally managed to get a dataset refresh working, initiated from PowerShell with a Service Principal. Yeej :-)


But as you can imagine, it didn't go without a fight (or 2, or maybe more), so I thought it would probably be good to share my experiences so you don't fall in the same traps as I did.

Use case

At Van Lanschot Kempen, we are doing daily refreshes of the corporate DWH, connected datamarts and extraction marts after that, all on-premises. Then the Power BI data models are refreshed on a time trigger (say 10 AM) to refresh the data from the datamart to Power BI. We would like that dataset refresh to be event-based. So whenever the datamart is refreshed, immediately refresh the corresponding Power BI dataset.

Setup

To get this working I've used the greatest part of this post How To Trigger A Power BI Dataset Refresh Using An Azure Data Factory Web Activity by Dave Ruijter (blog | @DaveRuijter). Although that's aimed at Azure Data Factory, most of the (setup) steps still apply to my scenario. But as you will see, others don't :-)

Permissions

At my company, I am the Power BI Service Administrator (and Power Platform Admin), so I have sufficient rights in the Power BI Admin Portal, but not in Office 365. So I needed the help of other colleagues who do have these rights.
Make sure that you have the right people aligned, or at least know where to go in case you need anything, before you start.

Steps taken

I'll outline the steps I took here to have a complete list of things I did to accomplish my setup. Again, for detailed explanations per step please visit Dave's blog post:
  1. Create a Service Principal and also create a secret with that
  2. Create an Azure Active Directory (AAD) group and add the Service Principal from Step 1
  3. Enable the tenant setting for Service Principals and add the group from Step 2
  4. Create a (V2!) Power BI workspace (or use an existing one)
  5. Add the Service Principal as an Admin to the workspace
  6. Use PowerShell to refresh a dataset
I think Step 6 actually deserves a chapter of its own, while it looks so easy afterwards, I think I struggled with this for more than 20 hours over a few weeks time.
Especially because Dave's setup uses ADF, the authentication to the REST API threw me off. In the end, I "just" had to pass the secret to a PSCredential and use Connect-PowerBIServiceAccount with the Service Principal.
Then the next step is getting an Access token, similar to the setup of using the Power BI REST API as Admin, but now using that header in the Invoke-RestMethod call, as shown below.
You can also get the code from my Github page.



As evidence, I also queried the REST API to show the refresh history :)



Next steps

At this time I do have a working solution, executing the script manually, with my secret in plain text. So there are still a few things to have a look at, which I haven't had the time for yet unfortunately:
  • Add the PowerShell script to a SQL Agent Job step, which is the specific use case for me
  • Make the solution secure, so don't save sensitive data inside the script or on a server
    • One option is to use parameters in SSIS as Brett Powell explains in his post
    • I'd like to investigate if I can use the Azure Key Vault from a SQL Agent Job and store the secret there, I'll probably have to authenticate to the Key Vault with the Agent user running the job
  • Incorporate the success of the refresh itself into the solution as a step, so I can take action if it fails
Let me know if you have any comments, or know how to improve certain steps of this process :)

Resources

12 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thx man , it helped.
    I was using this API : GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes
    instead of the one you've used : GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes
    The first one kept throwing failed authorization.

    ReplyDelete
  3. Hi there, i've managed to get on the same stage. my problem is scheduled refreshes after one hour do not work because the token is expired. Do you know if there is a way Powerbi can deal with that without me having to re-generate a token and update it via api?

    Thanks

    ReplyDelete
    Replies
    1. I think you still have to login somewhere, also if you're using a service principal. In Power BI you could fire up a function to get an access token refresh, as I mentioned in my blog post about using the REST API as an Admin. Not sure how you can do that with PowerShell though, sorry.

      Delete
  4. I am successfully able to login using Service Principal using above code. However, when I hit the workspace creation command after login, I am getting error. Could you please help?
    Command: New-PowerBIWorkspace -Name $workspaceName
    Error: Operation returned an invalid status code 'InternalServerError',Microsoft.PowerBI.Commands.Workspaces.NewPowerBIWorkspace

    ReplyDelete
    Replies
    1. Does the Service Principal have rights to create a workspace in the service? There is a tenant setting that could disable this. If so, is this SP a member of the allowed security group?

      Delete
  5. Hi, I have all set of permissions granted and also i have Tenant.Read.All API permission to my APP in azure AD, Also the Service Principal is added at the Power BI Admin Portal and granted as ADMIN to the workspace access as well.

    I am able to hit all developer level APIs, but not able to hit the Admin API (GET https://api.powerbi.com/v1.0/myorg/admin/reports/{reportId}/users).

    It throws below error.
    ----------------------------------------------------------------
    Invoke-RestMethod : The remote server returned an error: (401) Unauthorized.
    At line:19 char:1
    + Invoke-RestMethod -Uri $uri –Headers $headers –Method GET –Verbose
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
    ----------------------------------------------------------------

    Just FYI, i am using same piece of code which is shared in the blog. Kindly help me resolving the error. or suggest any developer level API to get user list whom a report is being shared.

    ReplyDelete
  6. I found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information. Meridian

    ReplyDelete
  7. I tried the same code. I was able to login successfully. But after login, when trying to retreive the workspaces with Get-PowerBIWorkspace -All command I am getting below error.

    Get-PowerBIWorkspace : Operation returned an invalid status code 'Unauthorized'
    At line:1 char:1
    + Get-PowerBIWorkspace -All
    + ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : WriteError: (Microsoft.Power...owerBIWorkspace:GetPowerBIWorkspace) [Get-PowerBIWorkspace], HttpOperationException
    + FullyQualifiedErrorId : Operation returned an invalid status code 'Unauthorized',Microsoft.PowerBI.Commands.Workspaces.GetPowerBIWorkspace

    ReplyDelete
    Replies
    1. To use the option -All you have to be a Service Admin in Power BI. So you need tenant level access for that. Do you have that?

      Delete
  8. I get this error when trying to refresh a dataset: The Body parameter was null, the request may be invalid when Method parameter is Post. Any help?

    ReplyDelete
    Replies
    1. Looks like you're trying to update something with a POST call then (instead of a GET)? Could you share the exact code you're using? Or at least the API you're using?

      Delete

Thanks for taking the time to provide feedback!

Cheers,
Nicky

Featured Post

Governance & Administration - Tenant Settings: Visual Cues

With all the Fabric announcements in the last months, some of the Admin announcements might have slipped through. As you might know, the Adm...