Is there a better way to change a report to use a different dataset

[TWrharvey]

Hi,

we have been using power bi for a number of years we have a large number of reports all linked with their own dataset spread across multiple workspaces. The datasource for the majority is a on-prem sql server analysis server (ssas) in live connection mode connected by a data gateway. This has worked great however often if a report is not accessed frequently the dataset can become dormant. We would rather just manage a single central dataset to make things easier.

                Ive been trying to migrate old reports to use a new single endorsed dataset the only way ive found to do this is to create a new report with the endorsed dataset as the source in power bi desktop then with the legacy report open copy all the report visual from the old report to the new report, recreate the filters and reimport any custom visuals. Publish then delete old report and dataset objects from powerbi workspace.

Is there a better way to change a report to use a different dataset, Ideally link it to a dataset in a different workspace from within the powerbi service?

See above how to I convert directly linked to ssas reports to linked by the central endorsed dataset that is then linked to the same ssas server.

Ive seen recommendations to transform data and edit datasource but I cant find that option on a live connected report. Ive opened old reports and new endorsed dataset-linked reports in the tabular editor tool but I can't seem to change the source here either.

we have hundreds of reports to change so a better solution would be greatly appreciated.

Many thanks in advance

Ross

[TWrharvey]

Hi

No if I press the data source settings on the old reports I want to update to have a central powerbi dataset. I only get the option to adjust to the ssas server/database settings.  if I then deploy to the powerbi service it creates its own unique dataset.

If I press the data source settings button on a new report that is linked to the central endorsed dataset I get the option to change the linked dataset.

That’s the issue I cant find a way other than copying and pasting the content from one report to the other to change a live connection to a powerbi dataset that is live connected.

Thanks

Ross

[DataInsights · Accepted solution]

@TWrharvey,

You could try the external tool Hot Swap Connections:

https://powerbi.tips/2020/08/hot-swap-report-connections-external-tools/ 

[TWrharvey]

Thanks this external tool looks like it may just do the trick. I had tried Tabular editor which worked fine to change data source on imported tables models but not ssas live connections. I am surprised microsoft don't give admins an easy way to adjust deployed reports to a single shared dataset.

[DataInsights]

@TWrharvey,

Were the reports created in Power BI Desktop? If so, open the pbix, navigate to Data source settings, and select the new dataset.