THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Using R Visualizations

Last week I showed a simple line plot of a hypothetical college student’s GPA. The plot could have been done using R. Before showing you a visualization that requires the power of R, I’m starting with the simple line plot recreated in R. For additional information about using R with Power BI desktop, David Iseminger has done a great job writing Power BI documentation that can be found at Microsoft’s Power BI site. He also wrote about configuring Power BI desktop to use an external R IDE.

Begin by placing an R visualization on the canvas. Resize as needed.

image

Figure 1. Place an R visualization on the canvas.

Power BI desktop creates a data frame named dataset. You’re going to have to accept that and work with it. Power BI desktop also eliminates duplicate rows. The input Excel file I’m using doesn’t have any duplicate rows so it is unchanged by the call to R’s unique function. If you have duplicate rows and want to keep them, you’ll have to make your rows unique by adding a column to make them unique – this is similar to what you might do in SQL Server when you add an IDENTITY property to a column. David Iseminger covered this in the first link I provided.

image

Figure 2. Drag the X-axis variable into place. Power BI creates a data frame named dataset and eliminates duplicate rows.

image

Figure 3. Drag the Y-axis variable into place.

Notice that unlike the Line chart visualization, the R visualization does not automatically create the plot. You have to click the Run button. Your R code must create visual output or you’ll get an error message.

image

Figure 4. Click the Run button.

image

Figure 5. Completed plot using the R visualization. The data appears as individual points by default.

You can customize your R visualization. You need to read the R documentation for plot and par to find out how to do this. I used the following R code to modify my R visualization:

par(bg="lavender",cex.axis="1.6",cex.lab="1.5")
plot(dataset,main="GPA Over Time",type="l",col="magenta")

image

Figure 6. Using plot options and par to modify your R visualization.

The next post in this series covers more advanced R visualization.

Published Sunday, June 04, 2017 7:36 PM by John Paul Cook
Filed under: ,

Attachment(s): PowerBI20170604.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist who works in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at a clinic that treats low income and uninsured patients. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog

Syndication

Privacy Statement