Replace data in PBIX file uploaded to PBIRS (on-prem)

[dandy-72]

In the recent past, I've used the REST API for Power BI (online) to upload a .PBIX file, and then replaced its data on demand in a C# app I've written with new data generated by some data source (the details of which don't matter; it's just a table).  Essentially, when I upload the PBIX on Power BI online, it creates two entries, a Report and a Dataset.  I submit a new dataset definition, push new rows into that dataset, rebind the report to the new dataset, then delete the old dataset that was created when doing the original upload.  That works (after much hair pulling, but that's another story).

I now need to do the equivalent with on-prem Power BI Report Server.  I managed to use its REST API to upload a PBIX, and I can see it when browsing (or enumerating Power BI Reports with the REST API), but unlike the online version, PBIRS does not automatically split this into a report and dataset.  I essentially need to replace the data in the PBIX that was uploaded with new data, just as I did with the online version.  Since I don't have these dataset and report objects, I have to assume the process isn't the same.  What REST APIs do I need to do this?  I tried to start by enumerating whatever datasets might exist after I've uploaded my PBIX as per https://learn.microsoft.com/en-us/rest/api/power-bi-report/data-sets/get-data-set and, while I get a 200 back (all okay), the list is empty.  In other words, uploading the PBIX doesn't automatically create a dataset.

I was hoping the procedure would be more or less the same...manually create a dataset definition, submit it, then fill it with data, and then bind it back to the report.  Is the procedure even the same?  Where do I start with this?

[dandy-72]

I should also mention that, assuming it can be done, I could just delete the data from the PBIX file that was uploaded, and replace it with new data - since my dataset consists of the same tables/columns, there really is no point (?) in submitting a brand new dataset.  I couldn't do that however with the online version.

[dandy-72]

Hi,

Thanks for the quick response. Couple of things:

Your first link is all about importing into Power BI online - I've already done this and I'm now working on equivalent functionality for the on-prem Power BI Report Server. Similarly, the Export API you've linked to is for the online version. Besides, I don't need to export anything.

When I wrote the code for the online version, I manually exported the PBIX to a template (.PBIT), which I then renamed to .zip and then had a look at DataModelSchema. Based on that, I defined my new dataset as a JSON string such as:

{
"name": "mydatasetname",
"tables": [
{
"name": "table1",
"columns": [
{
"name": "col1",
"dataType": "string"
},
{
"name": "col2",
"dataType": "string"
}
]
}
]
}

(yikes - sorry about that formatting - but you get the idea)

The report is simple enough that this definition was sufficient, as it has no calculated column and the like. I had this JSON definition hardcoded in my app, and used it to upload my new dataset definition, before pushing new rows into it (and then finally rebinding to the report).

I know nothing (yet) about TOM/XMLA, but I'm hoping that, in the end, as long as my report remains the same, I can produce a similar hardcoded definition for my dataset. It's not clear from your comment what form this dataset definition takes once defined with either TOM or XMLA, or what API I can use to upload it.

Ultimately my PBIX is rather simple; there's a single table, which has well under 20 columns (so I'm not too worried about those sorts of limitations). For all intents and purposes, the data is going to remain 'static' until a user explicitly clicks a button in my app to extract data from some outside source; my app's job is to then package up that data into a new set of rows for my dataset. The next time the user views the report (by clicking on the .PBIX when browsing PBIRS), the report should then show the new data.

Hopefully this better explains what I'm trying to do, and I hope you can confirm I only need to use TOM/XMLA (once) to define my dataset and then I can leave it at that moving forward (the dataset definition would remain the exact same). In the meantime, I'll start investigating TOM/XMLA.

Thanks.

[v-xiandat-msft]

Hi @dandy-72 ,

1. Upload PBIX to PBIRS using the Import API

2. Create a Dataset

• Unlike Power BI Online, PBIRS does not automatically split the uploaded PBIX into a report and dataset.
• To create a dataset, you’ll need to manually define it using either the Tabular Object Model (TOM) or XMLA.
• TOM and XMLA are suitable for creating regular datasets, but they have some limitations:
  • TOM (Tabular Object Model):
    • Works for Premium workspaces.
    • Requires the XMLA endpoint to have write activities enabled for the assigned capacity.
  • XMLA:
    • Also works for Premium workspaces.
    • Allows you to create and manage datasets programmatically.
    • Overcomes the limitations of the REST API (e.g., column count restrictions).
    • Requires familiarity with XMLA commands.
    • You can use tools like DAX Studio or SSMS to execute XMLA scripts.

3.Updating Data in the Dataset:

• Once you’ve created the dataset, you’ll need to push data into it.
• You can use the Push Datasets API to add rows to your dataset.
• The Push Datasets API is suitable for scenarios where you want to update data frequently.
• Keep in mind that there’s a limitation of 75 columns for push datasets.

4.Rebind the report to the new dataset using the Reports API.

5.Delete the old dataset if needed.

Below is the link will help you:

Upload a local PBIX file using the import API | Microsoft Power BI Blog | Microsoft Power BI

Reports - Export Report - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.