How to Retrieve Raw Data from Power BI Dataset Linked to On-Premises Database via API?

[DineshKumaran]

I am trying to retrieve raw data from a Power BI dataset in a specified workspace and convert it into an .xlsx format. To retrieve the data, I have an app registered in Azure as with the following permissions: Workspace.Read.All Dataset.ReadWrite.All Report.Read.All I am using either the Power BI REST API or the Power BI API library, but my dataset contains data from an on-premises database, and I am unable to fetch the raw data but I am able to retrieve workspace names,report names and dataset names but not the raw data. Challenges I am facing: The dataset is connected to an on-premises data source in the specified workspace. The Power BI REST API does not seem to provide access to raw data for such datasets. DAX queries do not work since the dataset is sourced from an on-premises database. I need a way to extract the raw data programmatically in C#. Questions: Is there a way to retrieve raw data from this dataset using an API? Are there any workarounds, alternative APIs, or methods to extract the data? Any guidance would be greatly appreciated!

[v-sathmakuri]

Hi @DineshKumaran ,

I hope this information proves helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @DineshKumaran ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sathmakuri]

Hi @DineshKumaran ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ibarrau  for prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ibarrau]

Hi. First of all, if you requirement is to get an excel sheet from data in a database, then you shouldn't think in power bi as the tool to solve it. There other tools for this, writting scripts in db or with sqlcmd, etc. Second, keeping data onpremise is not an issue for power bi rest api. The problem could be the connection. If you are using direct query it can be an issue, but if you import data, then the data is hosted inside power bi when refreshed, not the onpremise db. If you want to get data from a dataset with Power Bi rest api, you can use the following request:
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group

Be aware of reading all the pre requisites and considerations. The request let's you query with dax the data inside the dataset. It has limitations regarding amount of fields, but it could help.

I hope that helps,

[DineshKumaran]

So, If I am not wrong you are suggesting importing the data into a new workspace so that it becomes available in Power BI rather than relying on the on-premises database. From this new workspace, we can execute queries to retrieve the data.

I'm just wondering—if this is the approach, can I split the data into pages while importing and directly export them as XLSX files from new workspace?

Also, for direct database queries/scripts, I am restricted from establishing a database connection due to security constraints.

Note: I'm doing all this because I am automating a Power BI report by logging into the Power BI website and exporting the data to Excel. However, due to frequent changes in element IDs, I am trying to switch to an API-based . Also the report I am trying to get through API is from shared database.

[ibarrau]

I'm not suggesting importing data in a new workspace. I was assuming you already have a Power Bi Dataset published in a workspace because you have shown the Rest API of Power Bi Service that involves having a workspace with the dataset. The Power Bi Rest API allows you to read a Dataset published at Service. So the approach pre require the power bi dataset in a workspace. Otherwise if you don't have anything in a workspace you should think in an alternative tool or script to get the data and export it directly to excel without power bi.

I hope that make sense

[DineshKumaran]

Yes, I do have a dataset published under a workspace with premium capacity. So, to confirm, does this mean that retrieving raw data from the dataset for an on-premises data source is not possible through the API?

[ibarrau · Accepted solution]

Yes. The Power Bi Rest API interacts with Power Bi only. It won't query a data source, it query Power Bi Dataset/semantic model.

Regards