Tag Archives: R

ByHariharan Rajendran

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.

ByHariharan Rajendran

Sankey Chart Visual in Power BI VS R

Check out my previous post to see the streamgraph chart comparison.

StreamGraph Visual in Power BI VS R

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.
ByHariharan Rajendran

StreamGraph Visual in Power BI VS R

This post just shows the comparison of streamgraph in Power BI Custom Visual and R Script Visual.

Power BI Desktop

We have a streamgraph custom visual available in Power BI. Download and import into Power BI Desktop.

https://store.office.com/en-us/app.aspx?assetid=WA104380772&sourcecorrid=373810e3-72d4-4afd-96e7-6859505efb87&searchapppos=5&ui=en-US&rs=en-US&ad=US&appredirect=false

Here I used a very simple dataset to explore the visual.

Pass the above dataset values to chart.

R Studio  

To get the streamgraph visuals, we need to install the streamgraph package and other supporting packages .

Run the below script in R Studio or R console application.
install.packages(‘devtools’)
install.packages(‘Rcpp’)
install.packages(‘zoo’)
install.packages(‘DBI’)
install.packages(‘assertthat’)
install.packages(‘tibble’)
install.packages(‘yaml’)
devtools::install_github(“hrbrmstr/streamgraph”)
library(streamgraph)
# Create data:
year=rep(seq(2000,2005) , each=1)
name=c(letters[1:6])
value=c(1000,200,600,500,1250, 300)
data=data.frame(year, name, value)
streamgraph(data, key=”name”, value=”value”, date=”year”)

Power BI Desktop R Studio
Import Streamgraph visual Install Streamgraph and related packages
Visual values are keep

amending on the initial value

Identifies the maximum value and accommodate all the values within the maximum range
It is not interactive It is interactive
GUI Type R Script Type
Can’t export as HTML Can export as HTML
ByHariharan Rajendran

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)

 

image

ByHariharan Rajendran

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,

image

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.

image

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,

image

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”)}

ByHariharan Rajendran

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.

image

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.

image

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.

image

Step 2: Run the script and check the above specified location. Image file will be available with plot.

image

ByHariharan Rajendran

Issues in R Package in SQL Server 2016

I have got a below error when I try to use one of the R package “ggplot2”.

R

It says that there is no package called ggplot2 even though I installed the ggplot2 package in R.

Message from R when I install ggplot2 package,

R1

The above message clearly explaining that the package is not installed on defined path, it has been downloaded on temporary directory. It means there was no proper installation happened in R. This was the reason for error in SQL Server.

Follow the below steps to sort out the issue,

Use “.libPaths” to define the library path before install a package in R.

Command is,

> .libPaths (.libPaths()[2:3])

> install.packages(“ggplot2”)

Package will be installed like below,

R2

Now run the same SQL Script with R, this time it will be executed without an issue.

ByHariharan Rajendran

First R Script in SQL Server 2016

Follow my previous article to integrate R in SQL Server 2016, Once done then check the R script in SQL Server to make sure integration is done successfully.

Run the below script in SQL Server

CREATE TABLE Sample ([Value] int not null) ON [PRIMARY]

 INSERT INTO Sample   Values (101);

 INSERT INTO Sample   Values (102);

 INSERT INTO Sample   Values (103) ;

GO

execute sp_execute_external_script

  @language = N’R’

, @script = N’ OutputDataSet <- InputDataSet;’

, @input_data_1 = N’ SELECT *  FROM Sample;’

WITH RESULT SETS (([Value_R] int NOT NULL));

GO

When I run the script, I got below error.

“Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime. STDERR message(s) from external script:  Fatal error: cannot create ‘R_TempDir’”

To avoid this type of error, check the working directory on Rlauncher.config file

In my case, I have installed in E drive “E:\Local_Install_Applications\SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”

The blue highlighted path will be different if you have installed SQL Server on a different path.

Open the Rlauncher.config file,

image

make sure your working directory is correct without any extra space and also check the permission for “ExtensibilityData” directory.

image

Provide similar permission level as above.

Now try to run the same script in SSMS,

image

12