Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to use service principle to refresh a single table in PBI dataset?

Hi team,

 

I learned that I can use service principle to refresh a single table in PBI dataset, that's what we need in our project, we want to automatically refresh single table in datasets base on our schedules. I plan to automate the execution of the PowerShell script via automation runbook finally.  Currently I tried 2 ways, but neither has worked, need your kind suggestion, thanks in advance.


1). Use Invoke-ProcessTable

With this method, I could successfully refresh a single table in dataset, but 2 shortcomings:

1). An authentication window will pop up when running this command at the first time

2). Could only refresh the single table under my owned datasets, it seems I didn't use service principle to refresh but my personal id.

method1.jpg

 

2). This command finished without error, but the single table was not refreshed at all.

RefreshSingleTable_error.jpg

8 REPLIES 8
tackytechtom
Super User
Super User

Hi @Anonymous ,

 

Does it work if you login according to the following snippet?

param
(
    [parameter(Mandatory = $true)] [String] $sptenantid,
    [parameter(Mandatory = $true)] [String] $spclientid,
    [parameter(Mandatory = $true)] [String] $spsecret 
)
  
Write-Host "build up credentials of service principal"
$spsecretsecurestring = ConvertTo-SecureString $spsecret -AsPlainText -Force
$spcredentials = New-Object -TypeName PSCredential -ArgumentList $spclientid, $spsecretsecurestring
 
Write-Host "login to power bi with service principal"
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $spcredentials -Environment Public -Tenant $sptenantid

 

Let me know, if this works 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Hi Tom,
Thank you! Seems it works well, I could login with service principal, pls. refer to below screenshot.
pic0314.jpg

@Anonymous ,

 

Awesome! How about the refresh, did that work out, too?

 

Please, do not forget to accept the answer as a solution so others have it easier to find it later on 🙂

 

Lastly, I'd like to share ths blog post with you:

 

https://www.tackytech.blog/how-to-make-a-service-principal-the-owner-of-a-power-bi-dataset/

 

Essentially, it covers how to change the ownership of a dataset to a service principal. It uses a PowerShell script as well where I actually took the snippet from. There might be some other code pieces that help you sort things out (i.e. getting an access token for your data source, thiough be aware, in that case the owner of the dataset should be the service principal). Just thought I share it as it might helpm you anyway 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Hi Tom,

Many thanks for your suggestion and reply!

It's a pity that refresh doesn't work, would you mind to suggest what I am missing here?
I could run this XMLA query successfully in SSMS and the table could be refreshed well (with my personal id).

But if I run the same Query in PowerShell, no error message, but the table was not refreshed (even though I used the service principal to take over the dataset).

I did these preparation works before I running this PowerShell script:

 

1). Enabled 'read write' (under XMLA Endpoint) in PBI admin portal

2). Added service principal to Azure AD security group

3). Enabled Service Principals to use Power BI APIs in PBI admin portal

4). Added Service Principal to Power BI Workspace as Admin

5). Installed the ADOMD library

 

Do you have any suggestion about this problem? thanks a lot!

 

pic-0315.jpg

@Anonymous ,

 

Could it be because you are using "Command.CommandText"?

 

How about if you try it with the refreshes API. Currently, I have no way to test and debug it, but maybe something like this?

Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/[workspaceID]/datasets/[datasetID]/refreshes" -Body ($body | ConvertTo-Json -Depth 80) -Method Post

 

with the $body like the below:

{
    "objects":
        [
            {
                "table":"YourTableName"
            }
        ]
}  

 

Hope we are getting closer 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Hi Tom,

 

Good news, seems we are closer, thanks a lot! 
I could run the script with your mentioned code above, as noticed the dataset which owned by myself will be refreshed successfully, but got error if the dataset is owned by someone else. After I took over the dataset with service principal, I got the missing credentials error message, seems after took over the dataset, I also have to setup the credentials for all the data sources with service principal, have no idea currently, need to investigate how to do.

error message.jpg

 

Hi @Anonymous ,

 

That is great news!

 

The blog post also shows how to update the credentials by fetching an access token from Azure and then updating the source. It does it for the audience of a sql database (https://database.windows.net/). It depends a bit on your sources but that could work as well?

 

Also, I think it'd be great if you keep posting your code so others can follow thiis. I am pretty sure this be of a huge help for other as well!

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Hi Tom,

Thanks a lot for your help and sorry for my late response.

I am struggling with how to update the credentials for source sharepoint list, not succeed yet.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors