Creating a report using MS API call on PBI Service

[jight]

Hi,

I want to create a report using data pull from GET https://api.powerbi.com/v1.0/myorg.

I was able to create the connection using our service principle which extract token and gets an API result I wanted on PBI desktop.

However, once I published on to our Serivce this method of connection stopped working. 

All the researches suggested that I shouldn't be hard-code an access token but I can't replicate a successful API connection on PBI service.

Does anyone have any suggestion on how to pull API data on PBI service using SP?

Thank you

[v-pnaroju-msft · Accepted solution]

Thankyou, @na12063, @oussamahaimoud and @tayloramy for your responses.

Hi @jight,

Based on our understanding, the issue may be related to the authentication pattern currently being used inside Power Query. The access token is being dynamically generated within the M query using Service Principal credentials and is then passed into the Web.Contents() function. 

Since you already have Fabric capacity available, one possible approach would be to use a Fabric Notebook or Data Pipeline, or an Azure Function or Logic App to handle the Service Principal authentication and Power BI REST API calls externally. The results can then be stored in a Lakehouse or Warehouse table. After that, you can connect Power BI to the stored data instead of calling the REST API directly from Power Query.

For refresh monitoring, you can use APIs such as the Get Refresh History API. Additionally, tenant wide monitoring APIs require Power BI or Fabric admin permissions, whereas workspace level APIs can still work provided the Service Principal has access to those workspaces and the required API permissions have been granted.

Please refer to the links below for further reference:
Datasets - Get Refresh History - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn
Embed Power BI content in an embedded analytics application with service principal and an applicatio...

We hope the information provided helps resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi @jight,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[jight]

Hi,

Thanks for following it up. I'm still stuck unfortunately. 

Ideally, I would extract these data directly from using our admin account but the access is limited to our IT team only. 

The method that works on Desktop doesn't work on our work PBI server and we can't install things randomly on our organisation machines.

[v-pnaroju-msft]

Hi @jight,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @na12063, @oussamahaimoud and @tayloramy for your responses.

Hi @jight,

Based on our understanding, the issue may be related to the authentication pattern currently being used inside Power Query. The access token is being dynamically generated within the M query using Service Principal credentials and is then passed into the Web.Contents() function. 

Since you already have Fabric capacity available, one possible approach would be to use a Fabric Notebook or Data Pipeline, or an Azure Function or Logic App to handle the Service Principal authentication and Power BI REST API calls externally. The results can then be stored in a Lakehouse or Warehouse table. After that, you can connect Power BI to the stored data instead of calling the REST API directly from Power Query.

For refresh monitoring, you can use APIs such as the Get Refresh History API. Additionally, tenant wide monitoring APIs require Power BI or Fabric admin permissions, whereas workspace level APIs can still work provided the Service Principal has access to those workspaces and the required API permissions have been granted.

Please refer to the links below for further reference:
Datasets - Get Refresh History - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn
Embed Power BI content in an embedded analytics application with service principal and an applicatio...

We hope the information provided helps resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[tayloramy]

Hi @jight, 

What API data are you after? 
I would recommend either using a pre built accellerator like FUAM as mentioned by @na12063, or using a notebook to pull data specifically from the API and store it in a lakehouse. 

What sort of licensing do you have? Do you have a capacity and Fabric, or just Power BI? That will also change what is possible.  

[jight]

This is for my organisation so I can't install something easily. I got Pro license and both capacity and Fabric but I'm only using PBI for this. 

I'm trying to create a status report for all our DFs and models' refresh status. I don't have admin access to extract refresh data from the MS server directly, so the alternative I can think of is extracting it using API. 

Jaimy

[tayloramy · Accepted solution]

Hi @jight, 

I would recommend first using a notebook to get all the data you care about in one place, and then report off of it. 

FIrst you can list all the items in a workspace using the list items endpoint: 

https://learn.microsoft.com/en-us/rest/api/fabric/core/items/list-items?tabs=HTTP

Then you can filter the response to only datasets, and from there you can get the refresh history: 
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history

Once you have this data all in a table, then building the report in power bi is easy. 

[oussamahaimoud]

Hi @jight,

Hope you're doing well!

Wrap your Power BI REST API call in an Azure Function that:

Handles the SP authentication internally

Returns just the data as JSON

Then in Power BI, connect to the Azure Function URL using Anonymous or API Key auth, both are fully supported for refresh in the Service.

[na12063]

I will recommend you setup FUAM
GitHub - GT-Analytics/fuam-basic: FUAM is a solution to enable a holistic monitoring on top of Power...