Tuesday, May 21, 2024

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”

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

 


 


 

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

 

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

 



 

  1. Delete all the queries associated with the report.

          In our example , delete the “zipcodes” query

 

 



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

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

 



 

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

 

 



 

 

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

 



 

 

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

 

No comments:

Post a Comment