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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JeanMartinL
Advocate II
Advocate II

Exporting a report with latest data with powershell

Hello,

I'm trying to export or clone a report using powershell but I need it to contain the latest refreshed data. 

This is my current test scenario:

1) Create a report linked to a single table (SQL Server, Excel, etc.) with 1 record

2) Deploy report using Write-RsRestCatalogItem (Report shows 1 row on Report Server)

3) Export report using Out-RsRestCatalogItem (Local pbix shows 1 row)

4) Add another row in table

5) Refresh report either with the UI or with Start-RsCacheRefreshPlan (Report now shows 2 rows on Report Server)

6) Export report using Out-RsRestCatalogItem (Local pbix still shows 1 row)

 

This seems to be by design (source) but is there a way to grab the refresh data at this point? Or is there a way to force a refresh to a pbix file using powershell?

 

Here is the context of my request in case someone has a better idea. I have 2 Report Servers that are separated by an unbelievably slow link. I need to have a copy of a report on the other server, that report process about 1GB of data but thanks to the tabular format the pbix compresses nicely to about 80MB, this will still probably take about 30 minutes to transfer but it's still much better than trying to refresh the report directly on the other server. My plan was to refresh the report on Server A which is local to the data source and then automatically copy the compressed report to Server B. However this can only work if the report contains the latest version of the data.

 

Thanks!

 

Edit (2021-01-27): Clarified that the table needs to be from an external source such as SQL Server or Excel.

1 ACCEPTED SOLUTION
josef78
Memorable Member
Memorable Member

As I know, this way is not possible. Because after upload pbix to server, is saved to DB as CatalogItem, and splited to DataModel and ReportDefinition. For Reporting is used only DataModel (whis is loaded to SSAS) and ReportDefinition for client layout.

 

When refreshing data, only DataModel is updated.

 

CatalogItem never to be updated on server side,  and therefore, when you downloading pbix back from server, is still same as you uploaded.

 

To meet your requirements I do not know tested solution, but probably there are more ways (but no any simple). E.g. two options in my think

-you can rebuild model to dedicated model in SSAS (which bring additional benefits as incremental update), but you can replicate SSAS DB to another site (there more way how replicate), but transfered will be only compressed model.

-or can replicate SQL DB with ReportServerDB to secondary replica in another site, only compressed model will be transfered

-and additional one, but barbarian, you can try move only selected rows from CatalogItemExtendedContent table using SSIS, but I never tested it ..

 

 

View solution in original post

7 REPLIES 7
josef78
Memorable Member
Memorable Member

As I know, this way is not possible. Because after upload pbix to server, is saved to DB as CatalogItem, and splited to DataModel and ReportDefinition. For Reporting is used only DataModel (whis is loaded to SSAS) and ReportDefinition for client layout.

 

When refreshing data, only DataModel is updated.

 

CatalogItem never to be updated on server side,  and therefore, when you downloading pbix back from server, is still same as you uploaded.

 

To meet your requirements I do not know tested solution, but probably there are more ways (but no any simple). E.g. two options in my think

-you can rebuild model to dedicated model in SSAS (which bring additional benefits as incremental update), but you can replicate SSAS DB to another site (there more way how replicate), but transfered will be only compressed model.

-or can replicate SQL DB with ReportServerDB to secondary replica in another site, only compressed model will be transfered

-and additional one, but barbarian, you can try move only selected rows from CatalogItemExtendedContent table using SSIS, but I never tested it ..

 

 

Thank you Josef, that's the best answer so far.

V-pazhen-msft
Community Support
Community Support

@JeanMartinL 

 

You might need to deploy and overwrite the report instead of refresh on Report Server. My test:

 

1. Create table column with value 1, deploy or save report to Report Server

2. Deleted all records in the source table, enter value 5

3. Save and overwrite the report on Report Server.

4. Download and export shows value 5.


Paul

@V-pazhen-msft 

Thank you for you response but I'm not sure I understand what you are doing. I assume that between step 2 and 3 you refreshed the report in Desktop? If it's the case, I can confirm that this indeed work but it is not what I'm looking for. The only other thing I can think of is if this was an "Enter Data" table but having the data embedded in the report would not be an appropriate example, I see that I wasn't clear in my initial post but I was talking about an actual database table (anything external would work, SQL Server, Oracle, Excel, csv, etc.), I will edit it so that it's clearer.

 

Keep in mind that my end goal is to automate the process and as far as I know I cannot automate a refresh in Desktop. Actually it seems like the only thing I cannot automate, everything else (Refreshing on server, downloading a report, uploading a report, overwriting a report) can be done with powershell and/or the REST API.

 

Thanks!

V-pazhen-msft
Community Support
Community Support

@JeanMartinL 

I am not sure is that only happens when using power shell, did you try just download the pbix or edit in Desktop.

V-pazhen-msft_0-1611647257203.png

 

Paul Zheng _ Community Support Team

 

Hello @V-pazhen-msft,

Yes this also happens using the UI.

1) Created and deployed the report with values 1,2 in the tables

2) Deleted all records in the table, added value 5

3) Refresh report on Report Server (value 5 is now displayed in report server)

4) Both downloading or editing the report display value 1,2.

 

I've now tried the following methods to access the report, all of them got me the initial uploaded report and not the one with the latest data.

1) Downloading the report from Report Server

2) Edit in Power BI from Report Server

3) Open from Report server in PBI Desktop

4) Out-RsRestCatalogItem from powershell

5) /PowerBIReports({Id})/Content/$value from REST API

 

If there was a way to programmatically call a refresh from a pbix file I could still work with this limitation, but as far a know it's not possible (without resorting to mouse macros and such).

 

Thanks!

JeanMartinL
Advocate II
Advocate II

For the record, I also tried with the REST API using: /PowerBIReports({Id})/Content/$value

I get the same behavior, this is not a surprise since I believe the powershell methods uses the API behind the scene. I'm looking through the API documentation but so far nothing looks helpful for my needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors