In this article, we will have a discussion and demo on how to integrate “R” scripts with “PowerBI” using PowerBI R Connector.
In order to utilize this article, you should have some basic understanding of R Programming.
Before getting into the demo let's discuss the concept using the following Solution Architecture Diagram:
Data Sources Layer shows a few of the available data sources that I have tested this solution with.
Data Crawlers & Connectors Layer shows a generic set of Data Connectors provided by Reporting Engines and Custom Crawlers.
CSV Raw Data Sources shows the set of output files that can be used as input files by R APIs.
R Programming Interface shows the R APIs and and packages that can be utilized to perform statistical operations on the incoming data and generate a meaningful output.
Computed/Analytical Data Output shows the output files generated by R APIs.
Reporting Engines shows a couple of famous reporting engines that can be utilized to develop reports based on the input received from R APIs.
In this demo, I am not covering the Crawl process since there is nothing new about it. I am considering a CSV Raw Data Source is available to be consumed by R APIs as shown below.
In this raw data source, we have some data related to the File Shares. It is showing a number of files that are available within each file share for a specific quarter.
Now, the scenario is to consume this data for a developing trend analysis report and the obvious problem is that it is not in the desired format to be consumed directly by the reporting engine.
That’s where we are utilizing R Programming Language which is quite in fashion these days and is well known for its statistical capabilities.
Though I am not displaying any hefty R Code here, rest assured you are all free to take this data transformation up to any level as per your requirements.
In the following R snippet, I am reading data from CSV Files and grouping it based on the Quarter Column to display quarterly files count.
For example, this could be helpful if we need to develop some trend analysis on sizing capabilities of a disk.
You can see the outcome of this script as highlighted below:
Column “Group.1” shows Quarters & Column “x” shows the total number of files per quarter:
Once the R script is ready and validated we can start designing the report using any of the report designers that support R connectors; or if they don’t, we can save the R output as CSV/Database file and can feed the report designer with this output.
For this demo, I am using PowerBI and the interesting thing about it is having inbuilt R connector for rendering R scripts within the report designer.
- Launch Power BI desktop application
- Click on Get Data Menu -> Click on More…
- Select “Other” category of available connectors
- Select R Script connector under “Other”
- Click Connect
- Paste the R Script in the script designer
- Click OK.
As soon as script designer is able to parse the script, it will show you all the possible outputs that were stored in R Variables inside the script:
- Select source as required
- Click Load to load the required data source in designer memory
In the report designer, we can see data source and its related fields listed under Fields section as highlighted.
Under Visualizations, we can see the possible Charts/Graphs available to design the dashboard.
Choose as per your requirement and design the report.
Here is demo dashboard that I have developed using this data source.
The important take away from this demo is to understand the right selection of technology and the possibility of technology fusion to get the desired output.
Hope you find this helpful.