[box type=”note” align=”” class=”” width=””]This article is an excerpt from a book written by Ahmed Sherif titled Practical Business Intelligence. This book is a complete guide for implementing Business intelligence with the help of powerful tools like D3.js, R, Tableau, Qlikview and Python that are available on the market. It starts off by preparing you for data analytics and then moves on to teach you a range of techniques to fetch important information from various databases.[/box]
Today you will explore how to use R Markdown, which is a format that allows reproducible reports with embedded R code that can be published into slideshows, Word documents, PDF files, and HTML web pages.
Getting started with R Markdown
R Markdown documents have the .RMD extension and are created by selecting R Markdown from the menu bar of RStudio, as seen here:
If this is the first time you are creating an R Markdown report, you may be prompted to install some additional packages for R Markdown to work, as seen in the following screenshot:
Once the packages are installed, we can define a title, author, and default output format, as follows:
For our purposes we will use HTML output. The default output of the R Markdown document will appear like this:
R Markdown features and components
We can go ahead and delete everything below line 7 in the previous screenshot as we will create our own template with our embedded code and formatting.
Header levels can be generated using # in front of a title. The largest font size will have a single # and each subsequent # added will decrease the header level font. Whenever we wish to embed actual R code into the report, we can include it inside of a code chunk by clicking on the icon shown here:
Once that icon is selected, a shaded region is created between two “` characters where R code can be generated identical to that used in RStudio. The first header generated will be for the results, and then the subsequent header will indicate the libraries used to generate the report. This can be generated using the following script:
# Results
###### Libraries used are RODBC, plotly, and forecastExecuting R code inside of R Markdown
The next step is to run the actual R code inside of the chunk snippet that calls the required libraries needed to generate the report. This can be generated using the following script:
```{r}
# We will not see the actual libraries loaded
# as it is not necessary for the end user library('RODBC')
library('plotly') library('forecast')
```We can then click on the Knit HTML icon on the menu bar to generate a preview of our code results in R Markdown. Unfortunately, this output of library information is not useful to the end user.
Exporting tips for R Markdown
The report output includes all the messages and potential warnings that are the result of calling a package. This is not information that is useful to the report consumer. Fortunately for R developers, these types of messages can be concealed by tweaking the R chunk snippets to include the following logic in their script:
```{r echo = FALSE, results = 'hide', message = FALSE}
```We can continue embedding R code into our report to run queries against the SQL Server database and produce summary data of the dataframe as well as the three main plots for the time series plot, observed versus fitted smoothing, and Holt-Winters forecasting:
###### Connectivity to Data Source is through ODBC
```{r echo = FALSE, results = 'hide', message = FALSE} connection_SQLBI<-odbcConnect("SQLBI")
#Get Connection Details connection_SQLBI
##query fetching begin##
SQL_Query_1<-sqlQuery(connection_SQLBI, 'SELECT [WeekInYear]
,[DiscountCode]
FROM [AdventureWorks2014].[dbo].[DiscountCodebyWeek]' )
##query fetching end##
#begin table manipulation colnames(SQL_Query_1)<- c("Week", "Discount")
SQL_Query_1$Weeks <- as.numeric(SQL_Query_1$Week) SQL_Query_1<-SQL_Query_1[,-1] #removes first column SQL_Query_1<-SQL_Query_1[c(2,1)] #reverses columns 1 and 2
#end table manipulation
```
### Preview of First 6 rows of data
```{r echo = FALSE, message= FALSE} head(SQL_Query_1)
```
### Summary of Table Observations
```{r echo = FALSE, message= FALSE} str(SQL_Query_1)
```
### Time Series and Forecast Plots
```{r echo = FALSE, message= FALSE} Query1_TS<-ts(SQL_Query_1$Discount)
par(mfrow=c(3,1))
plot.ts(Query1_TS, xlab = 'Week (1-52)', ylab = 'Discount', main = 'Time Series of Discount Code by Week')
discountforecasts <- HoltWinters(Query1_TS, beta=FALSE, gamma=FALSE) plot(discountforecasts)
discountforecasts_8periods <- forecast.HoltWinters(discountforecasts, h=8) plot.forecast(discountforecasts_8periods, ylab='Discount', xlab = 'Weeks (1-60)', main = 'Forecasting 8 periods')
```The final output
Before publishing the output with the results, R Markdown offers the developer opportunities to prettify the end product. One effect I like to add to a report is a logo of some kind. This can be done by applying the following code to any line in R Markdown:
 # image is on a website
 # image is locally on your machineThe first option adds an image from a website, and the second option adds an image locally. For my purposes, I will add a PacktPub logo right above the Results section in the R Markdown, as seen in the following screenshot:
To learn more about customizing an R Markdown document, visit the following website:
http://rmarkdown.rstudio.com/authoring_basics.html.
Once we are ready to preview the results of the R Markdown output, we can once again select the Knit to HTML button on the menu. The new report can be seen in this screenshot:
As can be seen in the final output, even if the R code is embedded within the R Markdown document, we can suppress the unnecessary technical output and reveal the relevant tables, fields, and charts that will provide the most benefit to end users and report consumers.
If you have enjoyed reading this article and want to develop the ability to think along the right lines and use more than one tool to perform analysis depending on the needs of your business, do check out Practical Business Intelligence.
