Switching Power Bi Report Connections ( On Premises Data Sources to Semantic Models)

 

There are few situations / use cases , where we would want to change the power bi report's data source from RDBMS / other data sources to Power BI data sources.

 a) When we transition from Power Bi Report Server ( On Premises ) to Power BI Service ( Cloud)

 b) We start small and build the report . At this time , there is one to mapping with the report and the data source. Later on , you want to divide the responsibilities of maintaining the data model and maintaining the reports / dashboards.

I found out this hack , where we change the data sources and maintain the same visuals , without recreating it.

I wish , I knew this method , when I transitioned from PBI report server to PBI Service.

Here are the steps to switch from RDBMS / other on-premises data source .

Let us say , we create the Power Bi Report connecting to SQL Server table “zipcodes” and this is called zipcode.pbix

1. Duplicate the PBI file

        Copy the file “ZipCodes.pbix” to “ZipCodesPBService.pbix”

2. Publish / Upload the original PBIX  file to the  Power Bi Service

 

 

 

3. Open the duplicated file ( ZipCodesPBService.pbix) in Power BI Desktop

 

4. Click on “Transform Data” in the ribbon.

 

 

5. Delete all the queries associated with the report.

          In our example , delete the “zipcodes” query

 

 

6. Click “Close & Apply” to exit the Power Query Editor

         Ignore the error message for now. This error is only transient.

 

 

7.  Click on “Get Data” and select “Power BI Semantic Models” .

 

 

 

 

8. Choose the PBI Semantic model ( ZipCodes) that was uploaded in Step 1 and click “Connect”

 

 

 

9. Once the PBI Semantic model gets refreshed , the error disappears and visuals comeback .

 

 

 If there are any other easier methods, please feel free to comment on this post.