{"id":1714,"date":"2013-10-21T00:00:00","date_gmt":"2013-10-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/creating-a-business-intelligence-dashboard-with-r-and-asp-net-mvc-part-3\/"},"modified":"2021-05-17T18:34:54","modified_gmt":"2021-05-17T18:34:54","slug":"creating-a-business-intelligence-dashboard-with-r-and-asp-net-mvc-part-3","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-3\/","title":{"rendered":"Creating a Business Intelligence Dashboard with R and ASP.NET MVC: Part 3"},"content":{"rendered":"
\n

\tIn the first two parts of this series of articles, I set the groundwork for a solution that used ASP.NET MVC to create a simple site for a fictitious company called NorthWind Traders with a SQL Server database and which used R for the analytics and visualisation. Now, in the third part of the series, I’ll build on this by introducing two new R libraries that widen the analytical power of the application that we began developing in part 1<\/a> and 2<\/a>. These libraries are called lattice and googleVis<\/a>. <\/p>\n

\tThe name of the latter one leaves few doubts about its nature, whereas the former name sounds a bit peculiar (at least for some people whose first language is not a Germanic one). The word lattice has many meanings that can be found in Wikipedia<\/a> but it is better to give you a quote from the book ‘<\/strong>Lattice: Multivariate Data Visualisation with R‘<\/strong> by Deepayan Sarkar. Here it is “The lattice package is software that extends the R language and environment for statistical computing by providing a coherent set of tools to produce statistical graphics with an emphasis on multivariate data<\/em>“. <\/p>\n

\tOnly a relatively small number of people in history could abstract a complex physical phenomenon such as that falling apple into a mathematical model expressed by a simple algebraic equation. “Simplicity is the ultimate sophistication.” (da Vinci). In our context it is a unique ability or\/and talent to cut unimportant variables to make a simple equation. The less fortunate of us have to deal with multiple variables, build statistical model and use computer simulation. Because I am going to focus on graphics of multivariate data in this article, the lattice package is a logical choice to unravel those relationships. <\/p>\n

\tAn animated, interactive, visualisation can provide an additional insight into relationship between variables in a data set. This approach was effectively popularised by Professor Hans Rosling in his TED Talks<\/a> in 2007 when he presented Trendanalyser – a software package that converts data into animated, interactive charts. It was developed by the Gapminder Foundation that he co-founded earlier. <\/p>\n

\tOne year later Google released Motion Chart (currently known as Google Charts) – a flash based charting library to plot animated charts. The googleVis package provides an interface between R and the Google Charts as well as makes use of the internal R HTTP server to display the output locally. We will try to integrate an output from the R HTTP server into our application. <\/p>\n

Implementing the Category Performance Report<\/h2>\n

\tLet me remind you that the Parts 1 and 2 of the series demonstrated a process of building the first page (see Figure 1) of a web based business intelligence application for a fictitious wholesale company Northwind Traders that sells exotic food. <\/p>\n

\t\"1888-1-8d120598-fe46-4fd6-90d6-344081c90\"<\/p>\n

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

\tIn its current state the application uses four R libraries: RODBC, R2HTML, PLYR, and GGPLOT2. All R routines were collated into a batch file which could accept parameters and the results of its scheduled execution (two HTML fragments and two images) are hosted by the ASP.NET MVC 4 page above which is styled using Twitter Bootstrap (bootstrap). The navigation toolbar and page layout were also developed with bootstrap. <\/p>\n

\tThe next page we will add to the dashboard is a Products page and the first report on this page –<\/strong> the Category Performance report. This report is displayed on the Figure 2 below. It shows sales volume and profit as a percentage of their total values for 1998. You might have noticed that the height of the magenta portion of the volume bar is nearly twice that of its profit counterpart. This might indicate that the seafood profit is inadequate to the cost of sales in comparison to other categories and that the performance of the seafood category requires some improvement. <\/p>\n

\tLet me be more specific on this. Every product requires some effort to sell it. Let’s assume we have two products x and y that belong to the same category. The product x contribution into the overall volume is 20% and product y – 10%. It means that the effort to sell product x is about two times more than the effort to sell the product y. On the other hand the product y contribution into the overall profit is 20%, and the product x – 10%. This is why we call the product x – a poorly performing one. This conclusion also applies to the Confections category. <\/p>\n

\tThe Listing 1 shows the R procedure that was used to create the Category Performance report below. <\/p>\n

\t\"1888-1-f70d9f24-a8e8-4836-a347-1d82f9e4f\"<\/p>\n

\tFigure 2. Product categories performance in 1998. <\/p>\n

library(RODBC)\r\nlibrary(R2HTML)\r\nlibrary(ggplot2)\r\ncn<-odbcConnect('NW')\r\nctgGP <- sqlFetch(cn,'Ch2_Category_PmfGP_vw')\r\nctgQty <- sqlFetch(cn,'Ch2_Category_PmfQty_vw')\r\nodbcClose(cn)\r\nctgGP$MeasureValue <- 100.0*ctgGP$MeasureValue\/sum(ctgGP$MeasureValue)\r\nctgQty$MeasureValue <- 100.0*ctgQty$MeasureValue\/sum(ctgQty$MeasureValue)\r\nctgPmf <- rbind(ctgQty,ctgGP)\r\nq <- ggplot(ctgPmf,aes(x=MeasureName,y=MeasureValue,fill=Category))\r\nq <- q + geom_bar(stat=\"identity\") + theme(axis.title.x=element_blank())+ylab(\"%\")\r\nwd <- getwd()\r\nimagedir <- \"C:\\\\DoBIwithR\\\\Code\\\\R-2-ASP\\\\Reporting\\\\Content\\\\\"\r\nsetwd(imagedir)\r\nfilename <- 'ctgPmf.png'\r\nif(file.exists(filename)) \r\nfile.remove(filename)\r\nggsave(q,file=filename,width=5,height=4)\r\nsetwd(wd)<\/pre>\n
 \tListing 1. plotCategoryPerformance.r procedure. <\/p>\n
 \tMost of the code in the Listing 1 is similar to the analogous procedures from parts 1<\/a> and 2<\/a> of the series with just a couple of exceptions. This 100 * ctgGP$MeasureValue \/ sum(ctgGP$MeasureValue )<\/em> statement converts the absolute value into the percentage of total for each category and rbind(ctgQty, ctgGP)<\/em> combines (like T-SQL UNION operator) two data frames, ctgQty and ctgGP by rows to produce a new data frame ctgPmf showed below. <\/p>\n
 \t\"1888-d80d1f09-c460-4b5e-8e62-0726f5ed3f4\"<\/p>\n
 \tFigure 3. ctgPmf data frame. <\/p>\n
 \tThe following T-SQL view was used by sqlFetch() function from the RODBC library to populate the ctgGP data frame <\/p>\n
SELECT dbo.Categories.CategoryName AS Category,\r\n       'Profit' AS MeasureName,\r\n       SUM(((1.0 - dbo.[Order Details].Discount)\r\n       * dbo.[Order Details].UnitPrice - dbo.[Order Details].UnitCost)\r\n       * dbo.[Order Details].Quantity)AS MeasureValue\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\r\nWHERE (YEAR(dbo.Orders.OrderDate)= 1998)\r\nGROUP BY dbo.Categories.CategoryName\r\n<\/pre>\n
 \tListing 2. T-SQL view Ch2_Category_PmfGP_vw <\/p>\n
 \tIt won’t be difficult to figure out what changes to the above view are required to make it deliver Quantity instead of the Profit figures and then use the updated view to populate the ctgQty data frame. <\/p>\n
 \tNow that we know that some categories have inadequate performance, we will create the next report that should shed some light on whether there is a chance of improving their performance. <\/p>\n
Implementing the Product Category Demand Report<\/h2>\n
 \tWhat is a demand graph? According to Wikipedia “… the demand curve is the graph depicting the relationship between the price of a certain commodity and the amount of it that consumers are willing and able to purchase at that given price.<\/em>” And “Demand curves are used to estimate behaviours in competitive markets<\/em>… “. <\/p>\n
 \tIf we look at the graph of Seafood demand in 1998 (Fig. 5) we notice that Northwind customers bought much more at lower prices than they did when prices were higher. The relationship between price and quantity that is depicted in Figure 4 shows the responsiveness, or elasticity, of the quantity of seafood purchased as its price changed. <\/p>\n
 \tUsing statistical modelling, we could infer a mathematical relationship (simply speaking – a formula) between profit, price, cost, and quantity. This formula would show whether the volume increase due to the price decrease can compensate for the losses (or even often uplift profit) due to the price reduction. This concept of nonlinear relationship between various factors is not uncommon in science or\/and business and is illustrated by the graph below (Fig. 4), where Po – optimal price, Pcur – current price. <\/p>\n
 \t\"1888-aa69515b-c660-4e82-92f9-b7f7b0c1442\"<\/p>\n
 \tFigure 4. Relationship between price, volume, and profit for a price elastic product.\"1888-demand-81d42869-65bc-400d-a345-c6b8\"<\/p>\n
 \tFigure 5. Confections and Seafood products demand. <\/p>\n
 \tThe R procedure that was used to create the above report is shown below. <\/p>\n
library(RODBC)\r\nlibrary(lattice)\r\ncn <- odbcConnect('NW')\u00a0#no user id and password as we use trusted connection\r\nctgDmd <- sqlFetch(cn,'Ch2_Category_Demand_vw')\r\nodbcClose(cn)\r\nplotn <- xyplot(Qty ~ Price | Category + Year, data = ctgDmd,type = c(\"g\", \"p\", \"smooth\"))\r\nwd <- getwd()\r\nimagedir <- \"C:\\\\DoBIwithR\\\\Code\\\\R-2-ASP\\\\Reporting\\\\Content\\\\\"\r\nsetwd(imagedir)\r\nfilename <- 'demand.png'\r\nif(file.exists(filename)) \r\nfile.remove(filename)\r\ntrellis.device(device=\"png\", \r\nfilename=filename)\r\nprint(plot)\r\ndev.off()\r\nsetwd(wd)<\/pre>\n
 \tListing 3. R routine plotCtgDemand.r <\/em> <\/p>\n
 \tWe can use the RODBC package just as we did in Parts 1 and 2 of the series in order to connect to the database, retrieve the data and save it in the data frame ctgDmd. A few first and last rows of the data frame shown below. <\/p>\n
 \t\"1888-1-58496176-8394-4c68-a764-d57008f3e\"<\/p>\n
 \t………………………………………………………… <\/p>\n
 \t\"1888-1-78303f54-4406-4fc6-ae6f-685ec8bed\"<\/p>\n
 \tFigure 6. ctgDmd data frame from Listing 3. <\/p>\n
 \tThe lattice function xyplot(), that charts the data frame ctgDmd, has a signature common with other lattice functions, i.e. function_name ( y ~ x | A + B, data = , … )<\/strong>. It says ‘display <\/em>the <\/em>relationship between variables y and x for every combination of factors A and B<\/em>‘. In our case of xyplo (Qty ~ Price | Category + Year, data = ctgDmd type = c(“g”, “p”, “smooth”))<\/strong> x and y are Qty<\/strong> and Price<\/strong>, and A and B are factors Category<\/strong> and Year<\/strong> respectively. The second argument specifies the data frame that holds those variables and factors. In the formula y ~ x | A + B you can interchangeably use symbol * instead of the +. <\/p>\n
 \tAn ability to see relationship between two variables simultaneously in the context of available business dimensions (e.g. Category and Calendar as we’ve just seen) is a very valuable characteristic of a visualisation package. <\/p>\n
 \tFinally, here is the T-SQL view that populates the ctgDmd data frame. <\/p>\n
SELECT TOP (100) PERCENT '-' + DATENAME(yy, dbo.Orders.OrderDate)+ '-' AS Year,\r\n       dbo.Categories.CategoryName AS Category,\r\n       CAST((1 - dbo.[Order Details].Discount)* dbo.[Order Details].UnitPrice + 0.5 ASint)AS Price,\r\n       AVG(dbo.[Order Details].Quantity)AS Qty\r\nFROM   dbo.Products INNER JOIN\r\n       dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN\r\n       dbo.[Order Details]ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN\r\n       dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID\r\nGROUP BY '-' + DATENAME(yy, dbo.Orders.OrderDate)+ '-',\r\n       dbo.Categories.CategoryName,\r\n       CAST((1 - dbo.[Order Details].Discount)* dbo.[Order Details].UnitPrice + 0.5 AS int)\r\nHAVING (dbo.Categories.CategoryName IN ('Confections', 'Seafood'))\r\nORDER BYYear, Category, Price, Qty\r\n<\/pre>\n
 \tListing 4. Ch2_Category_Demand_vw T-SQL view.   <\/p>\n
 \tThe transformation CAST((1 – dbo.[Order Details].Discount) * dbo.[Order Details].UnitPrice + 0.5 AS int)<\/strong> was used to merge price points within the same dollar into one point and then to convert the price value to the integer. It was done to reduce the visual clutter when scatter points have close prices. <\/p>\n
Finding Price Elastic Products<\/h2>\n
 \tNow as we know product categories that need some sales performance adjustment, let’s drill down into individual products and find those which are sensitive (‘price-elastic’) to price changes. An R routine that identifies such products is shown below <\/p>\n
library(plyr)\r\nlibrary(RODBC)\r\nlibrary(lattice)\r\ncn <- odbcConnect('NW')\r\nsfProd <- sqlFetch(cn,'Ch2_Seafood_Products_vw')\r\nodbcClose(cn)\r\nsfProd <- sfProd[order(sfProd$Product,sfProd$Price),]\r\nsfPQCor <-ddply(sfProd,\"Product\",function(x) cor(x$Price,x$Qty))\r\nnames(sfPQCor) <- c(\"Product\",\"Elasticity\")\r\nsfPQCor <- subset(sfPQCor,Elasticity <= -0.6) \r\nsfEP <- subset(sfProd , sfProd$Product %in% sfPQCor$Product)\r\nep.plot <- xyplot(Qty ~ Price | Product, data = sfEP,type =\r\nc(\"p\",\"g\",\"r\"),xlim=c(0,70),ylim=c(0,50))\r\nwd <- getwd()\r\nimagedir <- \"C:\\\\DoBIwithR\\\\Code\\\\R-2-ASP\\\\Reporting\\\\Content\\\\\"\r\nsetwd(imagedir)\r\nfilename <- 'elc_products.png'\r\nif(file.exists(filename)) \r\nfile.remove(filename)\r\ntrellis.device(device=\"png\", \r\nfilename=filename)\r\nprint(ep.plot)\r\ndev.off()\r\nsetwd(wd)\r\n<\/pre>\n
 \tListing 5. findElasticSeafoodProducts.r procedure. <\/p>\n
 \tAs we have used similar constructs in other procedures, most of the code above is probably understandable, but I’d like to draw your attention to the 7th line. The ddply<\/strong> function applies the user-defined function function(x) { cor(x$Price, x$Qty) }<\/strong> to its x argument (data frame sfProd<\/strong>) that returns the correlation (this is our elasticity) between Price<\/strong> and Qty<\/strong> for each product in the category. The result of this calculation is displayed in the table below <\/p>\n
 \t\"1888-1-fd3b4326-0114-4b55-872b-5815274d7\"<\/p>\n
 \tTable 1. Elasticity of the Seafood Products. <\/p>\n
 \tThe purpose of the following two lines after the 7th line is to change the column names and extract only products that have high negative correlation between their price and quantity (–<\/strong>0.6 or less). In other words we take only highly elastic products and then use their names to filter the original data frame sfProd<\/strong> and draw Price ~ Qty<\/strong> scatter plot for each product (see below) using the xyplot()<\/strong> lattice function. <\/p>\n
 \t\"1888-elc_products-7a1df8ac-b521-4f23-9c0\"<\/p>\n
 \tFigure 7. Highly elastic seafood products. <\/p>\n
 \tPlease bear in mind that we are dealing with a demo database and do not have enough data to fully justify word ‘highly’, but this concept can be helpful in real life. <\/p>\n
 \tThe T-SQL view that feeds the findElasticSeafoodProducts.r<\/strong> procedure follows. <\/p>\n
SELECT ProductName AS Product, Price, AVG(Quantity)AS Qty\r\nFROM (SELECT dbo.Products.ProductName,\r\n         ROUND((1.0 - dbo.[Order Details].Discount)* dbo.[Order Details].UnitPrice, 1) AS Price,\r\n         dbo.[Order Details].Quantity\r\n      FROM dbo.Products INNER JOIN\r\n         dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN\r\n         dbo.[Order Details]ON dbo.Products.ProductID = dbo.[Order Details].ProductID\r\n      WHERE(dbo.Categories.CategoryName = 'Seafood'))AS p\r\nGROUP BY ProductName, Price\r\n<\/pre>\n
 \tListing 6. Ch2_Seafood_Products_vw T-SQL view.<\/em> <\/p>\n
 \tThis ROUND( (1.0 – dbo.[Order Details].Discount) * dbo.[Order Details].UnitPrice, 1)<\/strong> is done to make price points such as 14.72 and 14.70 indistinguishable and to reduce the noise. <\/p>\n
 \tSo, what is the Carnarvon Tiger optimal price? Let’s assume that our goal is to get the maxim profit, which is <\/p>\n
 Profit = Quantity * (Price – Cost) <\/p>\n
 \tThe linear regression line for the Carnarvon Tigers can be written as <\/p>\n
 Quantity = a * Price + b <\/p>\n
 \tAfter the substitution into Profit formula we have <\/p>\n
 Profit = a * Price2 + (b – a * Cost) * Price – b * Cost <\/p>\n
 \tIf we use the liner regression model represented by the blue line on Figure 7, then the optimal price is ~ $60. If the Northwind Trades were found a supplier that providers the same product for $19.99 then the optimal price would be ~ $50. <\/p>\n
 \t\"1888-5ba77d40-3ed4-409a-b758-b10c78f4724\"<\/p>\n
 \tFigure 8. Carnarvon Tigers Profit vs. Price when Cost = $19.99 <\/p>\n
 \tIf our goal was to get rid of the excessive inventory (it’s a perishable product with a ‘best before date’ after all), then we would calculate the optimal price differently. It will be covered in one the following parts of the series. <\/p>\n
 \tI leave it for you as an exercise to find the equation of the blue regression line on Figure 8. Hint<\/strong>: use the subset()<\/strong> and lm()<\/strong> functions. <\/p>\n
 \tAssembling all the pieces together including the report that we are going to build in the next section gives us the Product page shown on Figure 9. <\/p>\n
Implementing the ‘Profit by Country’ Report Using googleVis Library<\/h2>\n
 \tThe report we are going to build is displayed in the top right corner on Figure 9. The R procedure that generates this report is as follows <\/p>\n
library(RODBC)\r\nlibrary(googleVis)\r\nsuppressPackageStartupMessages(library(googleVis)) \r\ncn <- odbcConnect('NW')\r\nctgGP <- sqlFetch(cn,'Ch2_Profit_by_Country_vw')\r\nodbcClose(cn)\r\ngvPlot <- \r\ngvisMotionChart(ctgGP,idvar='Country',timevar='Year',xvar='AvgPrice',yvar='Qty',options=list(width=460, \r\nheight=400))\r\nwd <- getwd()\r\nhtmldir <- \"C:\\\\DoBIwithR\\\\Code\\\\R-2-ASP\\\\Reporting\\\\Content\\\\html\\\\\"\r\nsetwd(htmldir)\r\nfilename <- 'googleVis.html'\r\nif(file.exists(filename)) \r\nfile.remove(filename)\r\nprint(gvPlot,\"chart\",file=\"googleVis.html\")setwd(wd)\r\n<\/pre>\n
 \tListing 7. Ch2_plotCategoriesProfitByCountry.r procedure <\/p>\n
 \tThis procedure produces the html fragment displayed below with some lines (comments and data) removed to shorten the Listing 7. Although this code fragment is rather long to be included in the article, it explains clearly how the gvisMotionChart<\/strong> (see Listing 6) function works. <\/p>\n
<script type=\"text\/javascript\">\r\nfunction gvisDataMotionChartID23047e685bae (){\r\n   var data = new google.visualization.DataTable();\r\n   var datajson =[[\"Norway\",1997,33.33333333,21,249.5999889],\r\n..............removed data elements.............\r\n[\"USA\",1996,30.96551739,1539,16499.50122]];\r\ndata.addColumn('string','Country');\r\ndata.addColumn('number','Year');\r\ndata.addColumn('number','AvgPrice');\r\ndata.addColumn('number','Qty');\r\ndata.addColumn('number','Profit');\r\ndata.addRows(datajson);\r\nreturn(data);\r\n}\r\nfunction drawChartMotionChartID23047e685bae(){\r\n   var data = gvisDataMotionChartID23047e685bae();\r\n   var options ={};\r\noptions[\"width\"]= 460;\r\noptions[\"height\"]= 400;\r\n\r\n      var chart =new google.visualization.MotionChart(\r\ndocument.getElementById('MotionChartID23047e685bae')\r\n      );\r\n      chart.draw(data,options);\r\n\r\n}\r\n (function(){\r\n   var pkgs = window.__gvisPackages = window.__gvisPackages ||[];\r\n   var callbacks = window.__gvisCallbacks= window.__gvisCallbacks ||[];\r\n   var chartid = \"motionchart\";\r\n   \r\n   var i, newPackage =true;\r\n   for(i = 0; newPackage && i < pkgs.length; i++){\r\n      if(pkgs[i]=== chartid)\r\n         newPackage =false;\r\n   }\r\n   if(newPackage)\r\n      pkgs.push(chartid);\r\n\r\n   callbacks.push(drawChartMotionChartID23047e685bae);\r\n})();\r\nfunction displayChartMotionChartID23047e685bae(){\r\n   var pkgs = window.__gvisPackages = window.__gvisPackages ||[];\r\n   var callbacks = window.__gvisCallbacks = window.__gvisCallbacks ||[];\r\n   window.clearTimeout(window.__gvisLoad);\r\n   window.__gvisLoad = setTimeout(function(){\r\n      var pkgCount = pkgs.length;\r\n      google.load(\"visualization\", \"1\",{ packages:pkgs, callback:function(){\r\n         if(pkgCount != pkgs.length){\r\n            return;\r\n         }\r\n         while(callbacks.length > 0)\r\n            callbacks.shift()();\r\n      }});\r\n   }, 100);\r\n}\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"https:\/\/www.google.com\/jsapi?callback=displayChartMotionChartID23047e685bae\">\r\n<\/script>\r\n<div id=\"strong\">\"MotionChartID23047e685bae\" style=\"width: 460px; height: 400px;\">\r\n<\/div>\r\n<\/pre>\n
 \tListing 8. The html fragment made by the gvisMotionChart() function <\/p>\n
 \tIt converts the R data frame ctgGP<\/strong> that is returned by the sqlFetch()<\/strong> function into a JavaScript array and then into a googleVis DataTable, which is passed to the MotionChart()<\/strong> that plots an animated interactive chart. <\/p>\n
  <\/div>\n
 \t\"1888-6c807ead-2c68-4844-a765-567159d0651\"<\/p>\n
 \tFigure 9. Product page <\/p>\n
 \tHere is the T-SQL Ch2_Profit_by_Country_vw view that is used to extract data for the googleVis chart <\/p>\n
SELECT dbo.Customers.Country,\r\n       YEAR(dbo.Orders.OrderDate)ASYear,\r\n       SUM(dbo.[Order Details].Quantity)AS Qty,\r\n       AVG((1 - dbo.[Order Details].Discount)*dbo.[Order Details].UnitPrice)AS AvgPrice,\r\n       SUM(dbo.[Order Details].Quantity *((1 - dbo.[Order Details].Discount)*dbo.[Order Details].UnitPrice - dbo.[Order Details].UnitCost)) AS GP\r\nFROM dbo.Orders INNER JOIN\r\n           dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID INNER JOIN\r\n           dbo.[Order Details]ON dbo.Orders.OrderID = dbo.[Order Details].OrderID\r\nGROUP BY dbo.Customers.Country, YEAR(dbo.Orders.OrderDate)\r\n<\/pre>\n
Summary<\/h2>\n
 \tThis part adds some analytical elements and an improved user experience (animated, interactive chart) to what was previously, in parts 1 and 2, just BI reporting. It shows how to detect poorly-performing product categories and estimate their demand over time. Once we know the poor-performing categories and the demand, we can then drill down to the product level and find those products which have a sales volume that is sensitive to price changes. This information can be used to improve the category’s performance or to reduce the inventory levels. <\/p>\n
 \tIn the following parts I will demonstrate how the inferred relationship between product prices and sales volume for price elastic products can be integrated into the sales forecast. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
By introducing R libraries into an ASP.NET MVC application, it is possible to broaden its analytical and visualization power immensely. To illustrate this, Sergei shows an example of how to use the R libraries Lattice and GoogleVis to detect poorly-performing product categories, the quantity sold and the profit from them, and estimate their demand over time.…<\/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,5877,4151],"coauthors":[],"class_list":["post-1714","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-asp","tag-asp-net","tag-bi","tag-mvc","tag-r-statistical-package","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1714","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=1714"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1714\/revisions"}],"predecessor-version":[{"id":91058,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1714\/revisions\/91058"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1714"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1714"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1714"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1714"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}