R Packages in Power BI
Power BI service supporting many R packages as of now and it will increase in future. There is a misunderstanding that we can use these packages in Power BI Desktop R visual.
We can’t use all the packages in the Power BI site because it includes the packages which can be used for R custom visuals development.
Check the list of packages that Power BI Service supports, here.
Let us take an example,
There is a package called “timevis” which is part of Power BI supported packages.
We can use this package to build a visual to display a time range information. I used this package in R studio and got the below visual.
You can use the below R script and try it by yourself. This package requires many dependent packages which you can identify while installing the R scripts. If any dependent packages are missing then you should install those missing packages and finally run the R script.
library(timevis)
input<- data.frame(
id = 1:4,
content = c(“Apple”, “Orange”, “Mango”, “Banana”),
start = c(“2017-01-10”, “2017-01-11”, “2017-01-20”, “2017-02-14 15:00:00”),
end = c(“2017-02-13”, NA, “2017-02-04”, NA)
)
timevis(input)
It is easy to identify which packages are supported in Power BI from the list. Prepare your R script from any of the packages and make sure to check the supported package list and run the script in R studio.
If your package producing a visual and make sure that visual is displayed under plot section of R studio. If you see the visuals in plot section then those packages can be used in Power BI.
In our example, the visual is displayed under viewer section which means it is using HTML viewer which is not yet supported by R visual in Power BI.
Sankey Chart Visual in Power BI VS R
Check out my previous post to see the streamgraph chart comparison.
In this post, I have taken Sankey chart custom visual. The power bi custom visual has less functionality compare than R visuals
Power BI Desktop
We have a Sankey chart custom visual available in Power BI. Download and import into Power BI Desktop.
https://store.office.com/powerbiaddininstallpage.aspx?rs=en-US&assetid=WA104380777
Here I used a dataset called Titanic. This is one of the datasets in R which I used for this post.
R Studio
To get the sankey visual, we need to install the alluvial package.
Run the below script in R Studio or R console application.
install.packages(‘alluvial’)
tit <- as.data.frame(Titanic)
library(alluvial)
alluvial( tit[,1:4], freq=tit$Freq, border=NA,
hide = tit$Freq < quantile(tit$Freq, .50),
col=ifelse( tit$Survived == “No”, “red”, “yellow”) )
| Power BI Desktop | R Studio |
| Import Sankey Chart visual | Install alluvial and related packages |
| Less Functionality | More Functionality |
| GUI Type | R Script Type |
| Only 2 levels of visual by default for the simple dataset. To see multi-level visuals then need to prepare a dataset with multi-level of source and destinations | Multi-Levels of visual – Just add the number of fields. |
Statistical Functions in R
R language has many built in functions, in which statistical functions are mostly used on numeric vectors.
sum(x) – The sum of vector x
min(x) – The Minimum value of vector x
max(x) – The maximum value of vector x
mean(x) – The arithmetic mean of vector x
median(x) – The median of numeric vector x. 50% of data should be less than median and balance 50% data should be greater than median.
sd(x) – The standard deviation of vector x
var(x) – The Variance of numeric vector x
quantile(x,p) – The Pth sample quantile of numeric vector x. for example, quantile(x,.3) will tell us the value at which 30% of cases are less than value x.
summary(x) – It shows several statistics of vector x, including the above.
Practical Space,
X<-c(2,4,6,8,9,10,14,56,70)
sum(X)
min(X)
max(X)
mean(X)
median(X)
sd(X)
var(X)
quantile(X,0.5)
summary(X)
For Loop in R
In a real time, we will be in a position to handle the logic using loops. Loop concept is available in all programming language and also in R.
For example, if we want to generate first 200 prime numbers, we have to use loop to get the result.
In R, “for loop” is used to iterate over a vector.
Syntax
for (val in sequence) {
statement
}
In the above syntax, sequence is a vector which we can define and assign into an object and can use the object or we can directly place the vector with the expression. “val” will take the value of sequence and process in a loop. The statement will be triggered until the length of the sequence is completed.
Example,
x <- c(1,2,3,4,5,6,7,8,9,10)
count <- 0
for (val in x) {
if(val %% 2 == 0) count = count+1
}
print(count)
Using Next
We can use the word next inside the for loop and it will change the flow of the process. Check the below example,
for (i in 1:10) {
if (!i %% 2){
}
print(i)
}
If you run above script, I will get the result as 1,2,3,4,5,6,7,8,9,10 (All the values from the 1:10 sequence).
If you want to display only the odd number for the same sequence input then I can just add a “next” keyword inside if loop.
for (i in 1:10) {
if (!i %% 2){
next
}
print(i)
}
First “i” value 1 will go to if condition once the value reaches the keyword “next”. It will redirect to for loop again and it will come again for the second number i.e. 2 and it will continue up to 10.
If and Else function in R
I have discussed that how to use if and else statement in R in my previous article.
In a real time coding, we will be expecting a short form of code which should give the same expected result.
Vectors are basic building block of R Programming and will be used as input.
To apply the same if and else logic for the vector input, we can use if and else function which will give the same result but as a output vector.
Syntax:
Ifelse(test_expression,x,y)
The output of the function could be a vector. In the above syntax, test_expression is a condition which we can apply into the input vector and x is nothing but a custom value or expression, in logical it is called TRUE. If the condition is satisfied then x (TRUE) will display, you can replace x with any value. Here y is FALSE, if the test_expression is not satisfied then y will display.
Example,
number = c(3,5,7,10)
ifelse(number %% 2 == 0,”even”,”odd”)
Logically the result would be FALSE, FALSE, FALSE, TRUE.
If and Else statement in R
As we know, decision making is an important part for every programming. Almost all the projects make use of this decision making logic and sometimes this is very mandatory as well. Considering this importance, R language has if..else statement.
Probably we are in situation to run the set of codes when the condition is match. In that case, we can use if statement. It is very easy to use in R as like other programming language.
The General flow of “IF” statement,
Syntax:
if (test_expression) {
statement
}
Examples:
Example 1
x <- 10
if(x > 0){
print(“Positive number”)
}
Example 2
X<-weekdays(Sys.Date())
if(X == “Monday”)
{ print(“Today is Monday”)}
But in a real time scenario, sometimes we need to run the set of codes when the condition is not match. If that is the case then we need to use if and else statement.
Syntax:
if (test_expression) {
statement1
} else {
statement2
}
Examples:
Example 1
x <- -1
if(x > 0){
print(“Positive Number”)
} else {
print(“Negative number”)
}
Example 2
X<-weekdays(Sys.Date())
if(X == “Monday”)
{ print(“Today is Monday”)} else
{ print (“Today is not Monday”)}
We can use Nested if and else statement,
Syntax:
if ( test_expression1) {
statement1
} else if ( test_expression2) {
statement2
} else if ( test_expression3) {
statement3
} else
statement4
Examples:
Example 1
x <- 0
if (x < 0) {
print(“Negative number”)
} else if (x > 0) {
print(“Positive number”)
} else
print(“Zero”)
Example 2
X<-weekdays(Sys.Date())
if(X == “Monday”)
{ print(“Today is Monday”)} else if (X==”Tuesday”)
{ print (“Today is Tuesday”)} else if (X==”Wednesday”)
{ print (“Today is Wednesay”)} else if (X==”Thursday”)
{ print (“Today is Thursday”)} else if (X==”Friday”)
{ print (“Today is Friday”)} else if (X==”Saturday”)
{ print (“Today is Saturday”)} else
{print (“Today is Sunday”)}
R Plots in SQL Server 2016
As we know, Microsoft related the latest version of SQL Server 2016 RC3, download it here. From RC2, we no need to install R components separately. Actually, R database and standalone server both are installed as part of SQL Server 2016 installation itself.
It makes the R integration with SQL Server 2016 is easy.
As part of the integration, we just need to enable the external scripts and start using the R scripts inside SQL Server 2016.
Creating a plot in R console is easy, just write a code for plot and the plot will be displayed in the new window. In SQL Server 2016, we need to store the R script result in VARBINARY and then need to convert to an image file in reporting or using BCP command line tool.
I have provided the steps to get the image (plot) from R scripts in SQL Server 2016.
Solution 1:
Step 1: Create a sample database and table. Insert some values into the table.
Step 2: Do the modification on the table name and column name and run it.
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet;
image_file = tempfile()
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Values);
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));
Step 3: Make sure that you are getting an varbinary result as a output.
Step 4: Wrap the above code within stored procedure, like below and check the result again.
CREATE PROCEDURE Sp_Test1
As
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet;
image_file = tempfile()
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Values); #column name
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));
Step 5: Use below bcp command and convert the Varbinay into jpeg format.
bcp “exec Sp_Test1” queryout “Test.jpg” -S <server name> -d <database name> -U <user name> -P <password>
Step 6: Verify the plot in a above image.
Solution 2:
Step 1: You can create a table and insert this varbinary result and can use in SSRS report with image option.
Step 2: Verify the plot.
Solution 3:
We can export the result as an image file without using bcp tool itself.
Step 1: Do the following changes in the script. Replace the tempfile() with the exact path where you want to store the plot image.
Step 2: Run the script and check the above specified location. Image file will be available with plot.
SQL Server 2016 RC2–Enhancements in R Integration
Microsoft released the latest version of SQL Server 2016 RC2 which has many enhancements. This article exploring the enhancements related R integration in SQL Server.
Before RC2, we need to install SQL Server 2016 prior versions and then R related components separately. This scenario has been changed, We can install R services or standalone R server through SQL Server 2016 RC2 installation itself.
Look at the below changes in the installation center.
New feature to create a standalone R Server.
Confirmation to install Microsoft R Open.
