Forum Discussion

EasyMaintenance's avatar
EasyMaintenance
New Member
2 years ago

Change PowerBI Data Source Server without Refreshing Report in PBI Desktop

I have a large number of PowerBI reports where the Source Server and DB is "ServerA.Database1". I have set up data replication so that the tables and data necessary for reporting are now available in "ServerB.Database1". When I change the Server in PowerBI from ServerA to ServerB and click "Apply Changes", PowerBI Desktop starts a "Load" process that refreshes the data using ServerB as the source.

 

The problem is this can take 10-20 minutes for large datasets with anywhere from 1-10 queries per report, and I need to do this for 150 reports. I would prefer to just update the Server values, not refresh the data in PowerBI desktop, upload the report with the updated connection to my PowerBI Online Dev instance, then trigger the data refresh in the PowerBI Online Dev instance. Then validate the data later after the online refresh is complete. That way I don't have to waste time waiting for the Desktop application to reload each query and I can modify the Server of many reports in a short amount of time.

 

How can I do this? I tried de-selecting the "Include In Report Refresh" option on all of the data tables in the reports, but PBI Desktop still wants to reload the entire report after only toggling this checkbox for all the tables in a report. I'm also on a GCC High instance of PBI Online in case that changes anything.

Need Help

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey EasyMaintenance ,

     

    the only way I can think of, "manipulating" the Power Query query using Tabular Editor via the XMLA endpoint, but this requires a Premium Capacity with the tenant setting XMLA set to read/write.

     

    These readings provide more details: 

    Keep in mind that you need to download the pbix from the service, that you have the changed "program code" locally again.

     

    Hopefully this helps to tackle your challenge.

     

    Regards,

    Tom

     

     

     

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi EasyMaintenance ,

    Modifying a query means reloading that query and anything else that references it unless the loading for those has been disabled as well. That is regardless of whether they're included in the refresh or not. If you want to shorten the refresh time in Desktop, add a filter condition in the query using a parameter that you can modify in the server. For example, 

    = if FilterThis = "Yes" then Table.SelectRows(PreviousStep, each [Date Column] <= #date(2020,1,1)) else  PreviousStep

    This will filter the rows to be loaded thus will shorten the query loading time. Once published, you can then modify the parameter to other values and refresh the dataset.