{"id":1672,"date":"2013-07-23T00:00:00","date_gmt":"2013-07-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/creating-a-business-intelligence-dashboard-with-r-and-asp-net-mvc-part-2\/"},"modified":"2021-05-17T18:34:55","modified_gmt":"2021-05-17T18:34:55","slug":"creating-a-business-intelligence-dashboard-with-r-and-asp-net-mvc-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/creating-a-business-intelligence-dashboard-with-r-and-asp-net-mvc-part-2\/","title":{"rendered":"Creating a Business Intelligence Dashboard with R and ASP.NET MVC: Part 2"},"content":{"rendered":"
\n

In my previous article<\/a>, I began a demonstration of how to build a Business Intelligence (BI) application using R, Visual Studio 2012 and Twitter Bootstrap. The aim of this series is to leave you at the end of each article with a working system, adding functionality in each article as we go. In that first article, R was used only for data extraction and transformation supported by its two libraries, RODBC and R2HTML. With this article, part 2, we will add two more R libraries into the mix. These are GGPLOT2<\/a> and PLYR<\/a>. The first one is “…a plotting system for R, based on the grammar of graphics<\/code>“, whereas the second one is “… a set of tools that solves a common set of problems”. Those “common set of problems” include data transformation; and so PLYR significantly reduces not only the R coding effort, but also implicitly simplifies the development of T-SQL views or stored procedures.<\/p>\n

Part 1 of this article showed the step-by-step process of  developing the two reports in the top row of the dashboard, as you can see in the image below.<\/p>\n

\"1840-clip_image001-630x441.png\"<\/p>\n

Figure 1. The dashboard landing page.<\/p>\n

These reports contain a set of Key Performance Indicators (KPIs) evaluated for parallel periods in 1998 and 1997 as well as the period-to-period percentage growth.<\/p>\n

Usually KPIs are calculated by management information systems and they help to evaluate an organisation’s success by determining with some precision what is important to the organization, and then measuring it over time. It is important for any business to select and manage these KPIs to ensure that they measure performance. I came across a good example of the importance of KPI just a couple of months ago whilst watching a BBC documentary called ‘The Concorde Story’. <\/p>\n

Among many fascinating episodes that were shown in the documentary, there was one that is directly related to KPIs.  This episode describes how John King turned around the business of British Airways, that was losing £140m a year, into a company making £268 pre-tax profit. One of his most successful management decisions was to select  a proper KPI. Astonishingly it was a single KPI: the timely departure and arrival of BA aeroplanes. Find more here<\/a>.<\/p>\n

From my point of view, a selection of KPIs and their values should, ideally, indicate clearly which reports to then look at for explanations of the performance of the KPI. For example, the above dashboard tells us that all the Common Retail KPIs are in a very good shape, but despite the 109.3% growth in the number of transactions in 1998 compared to the same period in 1997, the TransValue<\/code> (average sales volume per transaction) growth dropped by 9.1%. This “strange” behaviour must be understood, its root causes identified, and some actions taken in order to reduce the potential negative impact of the reduction of the TransValue<\/code>  in the future.<\/p>\n

The Transaction Retail KPIs report gives us a few hints as to what to do next. The first one is to look at the TransValue<\/code> changes over time and across the available business dimensions, such as Product<\/code> and Customer<\/code><\/p>\n

Implementing the TransValue Trend and Heat Map Reports<\/h2>\n

The first report is displayed in the left-bottom corner of Figure 1 above. The R procedure below does almost all the job. All that is required on the SQL Server side is to create the Ch1_QtyWk_Trend_vw<\/code> view (see Listing 2).<\/p>\n

library(RODBC)\r\nlibrary(ggplot2)\r\nlibrary(plyr)\r\ncn  <- odbcConnect('NW',uid='******',pwd='******')\r\ntrend  <- sqlFetch(cn,'Ch1_QtyWk_Trend_vw')\r\nodbcClose(cn)\r\nvol  <-ddply(trend,c(\"Year\",\"Week\"),TransValue=mean(Qty))\r\nq  <- ggplot(vol, aes(x=Week, y=TransValue, colour=Year,fill=Year,shape=Year))\r\nq  <- q  + geom_point(size=3)+ geom_line(size=1)\r\nq  <- q  + scale_colour_brewer(palette=\"Set1\")\r\nq  <- q  + geom_smooth(colour=\"#0000c0\",stat=\"smooth\")\r\nq  <- q  + xlab(\"Week No\")  + ylab(\"TransValue\")\r\nwd<-getwd()\r\nimagedir  <- paste(Sys.getenv(\"HOME\"), \"\\\\R Analytics\\\\R-2-ASP\\\\Reporting\\\\Content\\\\\", sep=\"\")\r\nsetwd(imagedir)\r\nfilename  <- 'wktrend.png'\r\nif(file.exists(filename)) file.remove(filename)\r\nggsave(q,file=filename,width=9,height=4)\r\nsetwd(wd)\r\n<\/pre>\n
Listing1. QtyTrend.r procedure.<\/p>\n
SELECT CASE WHEN YEAR(dbo.Orders.OrderDate) = 1997 THEN 'LY' ELSE 'TY' END AS Year, DATEPART(ww, dbo.Orders.OrderDate) AS Week, \r\n       CAST(SUM(dbo.[Order Details].Quantity) AS numeric) \/ CAST(COUNT(DISTINCT dbo.Orders.OrderID) AS numeric) AS TransValue\r\nFROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID\r\nWHERE (dbo.Orders.OrderDate BETWEEN '19970101' AND '19970506') OR\r\n      (dbo.Orders.OrderDate BETWEEN '19980101' AND '19980506')\r\nGROUP BY CASE WHEN YEAR(dbo.Orders.OrderDate) = 1997 THEN 'LY' ELSE 'TY' END, DATEPART(ww, dbo.Orders.OrderDate)\r\n\t<\/pre>\n
Listing 2.  Ch1_QtyWk_Trend_vw T-SQL view.<\/code><\/p>\n
The sqlFetch()<\/code> function reads the  data that is required for the report and saves it in the data frame variable  trend<\/code>. A few of the initial and final lines of the data frame are shown below.<\/p>\n
  \"1840-clip_image003.png\"<\/p>\n
Figure  2.  The trend data frame as it is defines by the T-SQL view.<\/p>\n
After closing the ODBC connection, the  ddply()<\/code> function is used to apply the mean ()<\/code> function to each row in the  subset which is defined by the second input argument, c(\"Year\",\" Week\")<\/code>. The mean()<\/code> function does not do anything in this case as the data frame has only one value per row and it is  provided just to demonstrate what can be done. Instead of the mean(),<\/code> we  could use any other R or custom defined functions. I hope you are beginning to appreciate the power of R by now.<\/p>\n
This simple transformation is enough to make the new data frame vol<\/code>  compatible with the plotting  function ggplot()<\/code>. The remaining code creates the weekly trend lines for two  parallel periods, saves it as the wktrend.png<\/code> image in the Content directory  of the ASP.NET MVC project and resets the R environment to the initial state.<\/p>\n
Next we deal with the last report that shows TransValue<\/code> per Category and Country as a heat map<\/a>. The following R procedure (see Listing 4) is very similar  to the trendQty.r although it uses a new function that we have not used so far. This function is commandArgs()<\/code>.<\/p>\n
library(RODBC)\r\nlibrary(ggplot2)\r\nargs  <- commandArgs(TRUE)\r\ncn<-odbcConnect('NW',uid='******',pwd='******')\r\nview  <- ifelse(args[1]  == 1998,'Ch1_TranValue_by_Country_Categoty98_vw',   'Ch1_TranValue_by_Country_Categoty97_vw')\r\nqtyNatCat<-sqlFetch(cn,view)\r\nodbcClose(cn)\r\nq  <-ggplot(qtyNatCat, aes(x=Category, y=Country, fill=TransValue))\r\nq  <- q  + geom_tile()\r\nq  <- q  + geom_raster()\r\nq  <- q  + scale_fill_gradient(low=\"#c00000\",high=\"#00c000\",na.value=\"white\")\r\nq  <- q  + xlab('')  + ylab('')\r\nq  <- q  + theme(axis.text.x  = element_text(angle=10, hjust=1, vjust=1))\r\nq  <- q  + labs(fill=\"Qty\\nper\\nTran\")\r\nwd  <- getwd()\r\nimagedir  <- paste(Sys.getenv(\"HOME\"), \"\\\\R Analytics\\\\R-2-ASP\\\\Reporting\\\\Content\\\\\", sep=\"\")\r\nsetwd(imagedir)\r\nfilename  <- ifelse(args[1]  == 1998,'heatmap98.png', 'heatmap97.png')\r\nif(file.exists(filename)) file.remove(filename)\r\nggsave(q,file=filename,width=9,height=4)\r\nsetwd(wd)\r\n<\/pre>\n
 Listing 4. heatMap.r procedure.<\/p>\n
The commandArgs()<\/code> provides access to  a copy of the command line arguments when heatMap.r<\/code> is called from within the  batch file shown below (it contains only one line)<\/p>\n
>\"C:\\Program Files\\R\\R-3.0.1\\bin\\x64\\RScript.exe\" C:\\\\path-to-heatmap.r1998<\/pre>\n
The literal 1998 above is a parameter available to heatMap.r<\/code> as a first element of the vector args (see 3rd line in Listing 4).<\/p>\n
Apart from  allowing a  parameter to be passed to it, heatMap.r<\/code>  is similar to the qtyTrend.r<\/code> in  Listing  1 although the ggplot()<\/code> called from the heatMap.r <\/code>plots a heat map.<\/p>\n
The T-SQL view that supplies 1998 data to the heatMap.r<\/code> is displayed below.<\/p>\n
SELECT  dbo.Customers.Country, dbo.Categories.CategoryName  AS Category,  CAST(SUM(dbo.[Order Details].Quantity)  AS  numeric) \r\n         \/  CAST(COUNT(DISTINCT dbo.Orders.OrderID)  AS  numeric)  AS TransValue\r\nFROM    dbo.[Order Details]  INNER  JOIN\r\n        dbo.Orders  ON dbo.[Order Details].OrderID  = dbo.Orders.OrderID  INNER  JOIN\r\n        dbo.Products  ON dbo.[Order Details].ProductID  = dbo.Products.ProductID  INNER  JOIN\r\n        dbo.Categories  ON dbo.Products.CategoryID  = dbo.Categories.CategoryID  INNER  JOIN\r\n        dbo.Customers  ON dbo.Orders.CustomerID  = dbo.Customers.CustomerID\r\nWHERE   (dbo.Orders.OrderDate  BETWEEN '19980101'  AND '19980506')\r\nGROUP  BY dbo.Customers.Country, dbo.Categories.CategoryName\r\n<\/pre>\n
 Listing 5. Ch1_TranValue_by_Country_Categoty98_vw T-SQL view.<\/p>\n
The heatMap.r<\/code> creates a PNG image heatmap98.png<\/code> that is written into the Content folder of the ASP.NET MVC  project. Having an option of creating two heat map images for 1997 and 1998 allows for developing ‘drill up \/ down’ and  compare heat map reports for two parallel periods.<\/p>\n
Updating the Presentation Layer<\/h2>\n
Open the Notepad and create a DAL.bat file of the following content<\/p>\n
\"C:\\Program Files\\R\\R-3.0.0\\bin\\x64\\RScript.exe\" C:\\\\DoBIwithR\\\\Code\\\\RCode\\\\coreKPI.r\r\n\"C:\\Program Files\\R\\R-3.0.0\\bin\\x64\\RScript.exe\" C:\\\\DoBIwithR\\\\Code\\\\RCode\\\\transKPI.r\r\n\"C:\\Program Files\\R\\R-3.0.0\\bin\\x64\\RScript.exe\" C:\\\\DoBIwithR\\\\Code\\\\RCode\\\\qtyTrend.r\r\n\"C:\\Program Files\\R\\R-3.0.0\\bin\\x64\\RScript.exe\" C:\\\\DoBIwithR\\\\Code\\\\RCode\\\\heatMap.r 1997\r\n\"C:\\Program Files\\R\\R-3.0.0\\bin\\x64\\RScript.exe\" C:\\\\DoBIwithR\\\\Code\\\\RCode\\\\heatMap.r 1998\r\n<\/pre>\n
Listing 6. DAL.bat file.<\/p>\n
Note that the path  C:\\\\DoBIwithR\\\\Code\\\\RCode\\\\<\/code>  to the directory where you keep the R procedures as well as the location of the  RScript.exe<\/code>  will be different on your computer. So please edit the DAT.bat<\/i> according to your installation.<\/p>\n
The DAL.bat<\/code> generates content of all  four reports on the dashboard page (Figure 1) and the heatmap97.png<\/code> for 1997.<\/p>\n
Open the ASP.NET MVC project we created in the Part 1 and make changes to the  Index View shown highlighted in the Listing 7 below.<\/p>\n
@using Reporting.Helpers\r\n@{\r\n      ViewBag.Title = \"Index\";\r\n}\r\n<link href=\"~\/Content\/jquery.ui.all.css\" rel=\"stylesheet\" \/>\r\n<script src=\"~\/scripts\/jquery-1.9.1.js\"><\/script>\r\n<script src=\"~\/scripts\/jquery.ui.core.js\"><\/script>\r\n<script src=\"~\/scripts\/jquery.ui.widget.js\"><\/script>\r\n<script src=\"~\/scripts\/jquery.ui.position.js\"><\/script>\r\n<script src=\"~\/scripts\/jquery.ui.tooltip.js\"><\/script>\r\n \r\n<script>\r\n      $(function ()\r\n      {\r\n        $(document).tooltip();\r\n      });\r\n       function drillOnHeatMapClick()\r\n      {\r\n         var img = document.getElementById(\"heatMap\");\r\n         var title = document.getElementById(\"hmTitle\");\r\n         var imgUrl = img.src;\r\n         if (imgUrl.indexOf(\"97\") === -1)\r\n            {\r\n              imgUrl = imgUrl.replace('98', '97');\r\n              img.title = img.title.replace('97', '98');\r\n              title.innerText = title.innerText.replace(\"This\", \"Last\");\r\n            }\r\n             else\r\n            {\r\n              imgUrl = imgUrl.replace('97', '98');\r\n              img.title = img.title.replace('98', '97');\r\n              title.innerText = title.innerText.replace(\"Last\", \"This\");\r\n            }\r\n            img.src = imgUrl;\r\n      }\r\n<\/script>\r\n \r\n<div class=\"container-fluid\">\r\n      <div class=\"row-fluid\">\r\n            <div class=\"span6\">\r\n                  <p class=\"lead\">Common Retail<abbr title=\"key performance indicators\">KPIs<\/abbr><\/p>\r\n                  @Html.LoadHtml(\"~\/Content\/html\/coreKPI.html\")\r\n            <\/div>\r\n            <div class=\"span6\">\r\n                  <p class=\"lead\">Transactional Retail<abbr title=\"key performance indicators\">KPIs<\/abbr><\/p>\r\n                  @Html.LoadHtml(\"~\/Content\/html\/transKPI.html\")\r\n            <\/div>\r\n      <\/div>\r\n      <div class=\"row-fluid\">\r\n            <div class=\"span6\">\r\n                  <p class=\"lead\">Weekly<abbr title=\"Quantity Sold per Transaction\">TransValue<\/abbr>Trend for This and Last Year<\/p>\r\n                  <img class=\"img-polaroid\" src=\"~\/Content\/wktrend.png\" \/>\r\n            <\/div>\r\n            <div class=\"span6\">\r\n                  <p id=\"hmTitle\" class=\"lead\">This Year<abbr title=\"Quantity Sold per Transaction\">TransValue<\/abbr>by Country and Category<\/p>\r\n                  <img id=\"heatMap\" onclick='drillOnHeatMapClick()' class=\"img-polaroid\" title=\"Click on the image to see the 1997 report\" src=\"~\/Content\/heatmap98.png\" \/>\r\n            <\/div>\r\n      <\/div>\r\n      <div class=\"row-fluid\">\r\n            <div class=\"span12\">\r\n                  <br \/>\r\n                  <blockquote class=\"pull-right\">\r\n                        <p>Above all else show the data.<\/p>\r\n                        <small><cite title=\"Edward Tufte\">The Visual Display of Quantitative Information<\/cite><\/small>\r\n                  <\/blockquote>\r\n            <\/div>\r\n      <\/div>\r\n<\/div>\r\n<\/pre>\n
 Listing 7. Index View code.<\/p>\n
If you compare this code with the code we used for the  Index View in part 1 (Listing 12) , you would find that I’ve added three new  blocks of code highlighted in yellow. <\/p>\n
The first block contains references to jQuery<\/a>  and jQuery UI<\/a> libraries. If you like to follow along you have to download them and  copy the libraries into the same folders of the ASP.NET MVC project as in the references tags of the first highlighted  block.<\/p>\n
The second block includes two JavaScript functions. The first function is to  display tooltips when the user hovers mouse over the heat map image, and the second function is required  to allow the user to drill down or drill up on the heat map click, so as to view  and compare reports for the parallel periods. The second function also updates the report title and tooltip text in  order to coordinate them with the displayed report. The next two pictures illustrate this functionality<\/p>\n
  \"1840-clip_image005.png\"<\/p>\n
  \"1840-clip_image007.png\"<\/p>\n
Figure 2. The heat map report before  (above image) and after the drillOnHeatMapClick() function was executed.<\/p>\n
The new (compare to Part 1) bootstrap CSS class img-polaroid<\/a> is used to style  images in the last row of the grid (see Figure 1).<\/p>\n
Finally, we have to make some changes to the navigation bar. Open the _BootstapLayout.basic.cshtml<\/code>  layout view, comment out the current navigation bar mark-up and add the new one as follows.<\/p>\n
The updated toolbar contains links to four other pages that are not implemented  yet, so stay tuned, as there is more to come.<\/p>\n
@*<div class=\"navbar navbar-inverse navbar-fixed-top\">\r\n            <div class=\"navbar-inner\">\r\n                  <div class=\"container\">\r\n                  <a class=\"btn btn-navbar\" data-toggle=\"collapse\" data-target=\".nav-collapse\">\r\n                        <span class=\"icon-bar\"><\/span>\r\n                        <span class=\"icon-bar\"><\/span>\r\n                        <span class=\"icon-bar\"><\/span>\r\n                  <\/a>\r\n                  <a class=\"brand\" href=\"#\" title=\"change in _bootstrapLayout.basic.cshtml\">Application Name<\/a>\r\n                  <div class=\"nav-collapse collapse\">\r\n                        <ul class=\"nav\">\r\n                              @Html.Navigation()\r\n                        <\/ul>\r\n                  <\/div>\r\n            <\/div>\r\n      <\/div>\r\n<\/div>*@\r\n<nav class=\"navbar navbar-inverse navbar-fixed-top\">\r\n      <div class=\"navbar-inner\">\r\n            <a class=\"brand\" href=\"\/\">Northwind Retail Analytics<\/a>\r\n            <form class=\"navbar-search pull-right input-append\">\r\n                  <input class=\"span2\" id=\"appendedInputButtons\" type=\"search\" \/>\r\n                  <button class=\"btn\" type=\"button\">Search<\/button>\r\n            <\/form>\r\n            <ul class=\"nav pull-right\">\r\n                  <li class=\"active\"><a href=\"#\">KPIs<\/a><\/li>\r\n                  <li><a href=\"#\">Customer<\/a><\/li>\r\n                  <li><a href=\"#\">Products<\/a><\/li>\r\n                  <li><a href=\"#\">Time<\/a><\/li>\r\n                  <li><a href=\"#\">Raw Data<\/a><\/li>\r\n            <\/ul>\r\n      <\/div>\r\n<\/nav>\r\n<\/pre>\n
  Listing 8. Changes to the application’s navigation bar.<\/p>\n
This makes the navigation bar look like the one that is shown below.<\/p>\n
  \"1840-clip_image009-630x24.png\"<\/p>\n
Figure  3.  Updated navigation bar.<\/p>\n
Summary<\/h2>\n
We made a significant step towards making our application more practical.<\/p>\n
    \n
  1. As opposed to developing each reports separately (see the Part 1 of the article), all of  \t\tthem can now be generated simultaneously by executing the batch file DAL.bat that accepts parameters. Its  \t\texecution can either be scheduled to run immediately after an \t\tETL<\/a> updates the data source or it can be \t\t integrated into an SSIS package that manages updates using the SQL Server Engine change-tracking  \t\tfeature.<\/li>\n
  2. We introduced two new R libraries, PLYR and GGPLOT2. The PLYR streamlines data  \t\tpreparation for visualisation that was accomplished by the versatile GGPLOT2 library functions.<\/li>\n
  3. Lastly, jQuery and jQuery UI libraries were employed for developing the application’s  \t\tinteractivity (drill down, drill up) and keeping the user interface coordinated when the user drills down or  \t\tdrills up.<\/li>\n<\/ol>\n
    There is more to be done: passing parameters from R procedures to SQL server  procedures, multiple drill-down paths, etc. We’ll tackle all this in subsequent articles.<\/p>\n
    The R code for this article, for which you’ll have to provide  ODBC uid and password, and change the batch file extension from txt to bat, is provided in the ZIP file at the bottom of the article. The T-SQL code for parts 1 and 2 is already in the database image supplied with part 1. Please remember that this is sample code provided only to illustrate the points being made about the design of the application<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    Continuing the project to build a Business Intelligence (BI) application using R, Visual Studio 2012 and Twitter Bootstrap, Sergei introduces drill-down via jQuery and jQuery UI, simplifies the R coding with the PLYR and GGPLOT2 libraries, and shows how to generate reports simultaneously with a batch f…<\/p>\n","protected":false},"author":174820,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4156,4157,4178,5166],"coauthors":[6788],"class_list":["post-1672","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-asp","tag-asp-net","tag-bi","tag-mvc"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1672","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/174820"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1672"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1672\/revisions"}],"predecessor-version":[{"id":91059,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1672\/revisions\/91059"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1672"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1672"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1672"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1672"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}