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. 

 

R Dataframe in the eyes of SQL developer.

R has gained lot of momentum in the last few years  for Data Science. At first , for a SQL professional , this may be bit daunting ; however there are lot of similarities  between the RDBMS concepts and R concepts , that will make the learning curve tad easier. One of the similarity is the data frame. 


Data frame is one of the important component in R to capture the data from the external data sources ( aka importing from CSV , loading from RDMS  , and so on ) . 

It is conceptually same as the a table in a RDBMS system.

In the following , I have created a data frame with  3 elements and 6 rows. 
In RDBMS , this is the same as creating a table 'emp' and inserting 6 records. 

emp <- data.frame="" span="">
  name=c("Zahir","Farook","Hameed","Basheer","Aslam","Suhaib"),
  deptno=c(10,20,30,30,20,20),
 city=c("Monroe","Trichy","Kilakarai","Kilakarai","Chennai","Chennai"))  
















When  the data frame is referenced at the prompt , it returns the entire data set. This is similar to "SELECT * FROM EMP",




The function "rbind" is used to insert a record into the existing dataset. 
This is similar to "INSERT INTO EMP values ('Karady' , 100 , 'Colombo') "

emp <- arady="" data.frame="" deptno="c(100),city=c(" emp="" name="c(" olombo="" rbind="" span="">

 

The function "nrow" is used to get the record count of the dataset.
This is similar to " SELECT count(*) from EMP". 


The function "ncol" is used to get the record count of the columns.
This is similar to " SELECT count(*) from information_schema.columns where table_name =EMP'" .


 With the following example , we are filtering the records that have deptno = 30 . This is similar to
"SELECT *  FROM EMP WHERE DEPTNO= 30'.



 We can add , additional filter with the pipe function . Pipe is used for 'OR' condition. 
This is similar to "SELECT *  FROM EMP WHERE DEPTNO= 30 ORCITY ='Chennai'.





As we can see , there  are lot of similarites in with the concept of Table (tuple) and the dataframe. 
This could be a starting point to get familiar with R  for a SQL professional . 

I understand , I have just scratched the surface on the data frame and its functions.

As of now , Oracle and MS SQL Server has incoprated 'R' into their offerings.

Comments Welcome.