Sunday, August 28, 2016

How do you use R Script in PowerBI?

PowerBI is a powerful tool and Microsoft has updated it regularly and it can connect to very many data sources.

Launch PowerBI after you have installed it on your desktop. In the Visualizations pane you will see the icon for R.



Click on it and you get the message shown above. Click Enable. The Report area and the RScript editor area gets displayed.
..

One needs to drag fields from the Fields pane to this area.

Right now I have no fields. Let me get something to put here.

I connect to one of my old connections (which PowerBI has remembered after several updates although I have not used it very much). I use the GetData to connect to my previous connection to AdventureWorks on my SQL Server 2012. I am unable to connect.


Perhaps the Server is not ready. I wake it up from Services in the Control Panel. I retry connecting, I succeed. Voila! I have my data.


Just for demo, nothing planned yet. I just choose one table as shown in the above and click Load.

The UI gets loaded with fields from the chosen table as shown.


I use the DirectQuery option which connects Live to the table.


Now you can see the fields that are available for me to use (yellow rectangle).

Now you need to click and drag them on to the RScript plane. Actually I did not even drag and as if the program knew that I am going to drag, as soon as I clicked they entered the script area ass shown.


Well the script created a data frame and used the fields selected into the dataset as shown above.

Now I have not filtered the columns etc. but I can export the result as shown.


Add a line type dataset after the last line above and click the Right pointing arrow in the R script editor window which gets enabled.

It comes up with the next display showing that there was no Visual Created (bad choice of data to start with).


The Export produces a .CSV file that you can save. It appears that the default save folder is on the OneDrive. You can change it to a location of your choice.

In my next post I will describe connecting to a table where we can see some Visuals created after processed by the RScript editor.

Here is an example of Visuals created using  R Script  in Power BI. The 'demo' database used is on a SAP SQL Anywhere 17 Server.
 











No comments: