Exporting a report with latest data with powershell

[JeanMartinL]

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.

[josef78 · Accepted solution]

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 ..

[JeanMartinL]

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

[V-pazhen-msft]

@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

[JeanMartinL]

@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]

@JeanMartinL 

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

Paul Zheng _ Community Support Team

[JeanMartinL]

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]

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.