I guess a lot of us actually use many tools to accomplish various things in their everyday life. There is the (not that uncommon) case where you have to build something that others will use in their everyday business life to get insights, information and/or take decisions.
The basic implementation scenario here would be to build an excel workbook where you will feed the data and have a overview sheet, named Dashboard…If things are on your side you could set-up a connection to a database (an existing one or one you will create for the data in discussion) and pull data from there. You can build powerful and visually elegant things using this approach. A cool resource to generate tears of joy among colleagues is Chandoo.org.
OK, we all love R. You can make so much things with R that you could set-up a nice view on the issue that is addressed. There are a lot and excellent of graphics initiatives for R (ggplot2 the most appealing to me) to make great dashboards. This question has been asked in many occurrences (example : Information Dashboards in R with ggplot2 via stackoverflow)
But what about interactive results? This is the main thing that you should validate as the number one target in a dashboard. A static picture is well suited for a report to top level management but mid-level management would expect something that would at least give them the option to explore things. This is the region that BI tools come into game. Now, when your company will acquire such a tool I could bet that they would expect that you spend a looooot of time with this. There are many tools to use and I suggest that you spend time with all of them before turning in a suggestion for buying a licence. You can take a pretty compact list in the The essential collection of visualisation resources via Visualizing Data blog. I use QlikView extensively. It has a great community and trust me if you managed to learn R then culprits of QlikView won’t burden you (they will manage to iritate you, however).
Unfortunately you will soon realize that building a highly interactive dashboard has limited added value for complex questions, like the ones that predictive analytics would bomb at your inbox. You would not expect I guess to solve a classification problem with clicks I guess. In that case…Call R! Now, have in mind that the interactive nature of BI tools would enable you to make data management (aka rapidly creating a segment of your data to analyse) and then feed R for analysis. Then the results from R will feedback QlikView and continue with enriched analysis. This is pretty cool, trust me! The following is the approach I am using.
Integrating QV with R example kit.zipVERSION 1Created on: Apr 25, 2012 11:13 PM by Elif Tutuk – Last Modified: Apr 25, 2012 11:17 PM by Elif Tutuk
This paper is designed to provide information explaining how QlikView can be integrated with R. R is an open source programming language and software environment for statistical computing and graphics. The R language is widely used among statisticians for statistical software development and data analysis (https://www.r-project.org/).
There are a couple of ways to integrate R with other software such as QlikView. This paper focuses on using COM interface and explains the details on the integration. Please note that the integration requires some code development in Visual Basic and a basic understanding of R language. This solution is tested with small data sets and could be suitable for the cases where the business users make selections on the QlikView application and uses the selected data sets for advanced statistics by using R. The goal of this document is to provide some guideline to technical resources on how QlikView R integration can be achieved.
Now, the above example has a setup of sales per zip code. The data are constructed in an order level. The objective of the analysis is to segment the selected zip codes by order frequency, average order size and customer count metrics using R’s clustering algorithm (K-cluster) . This is accomplished in one click and then the data are available in QlikView for further investigation. As you may have suspected from the quoted description above you will need the statconn package.
Technically you insert in the QlikView Module the R code like this. You will notice that “R.” indicates an operation of the R connection. There are a few more R.* that are straightforward to interpret, like R.EvaluateNoReturn, R.GetSymbol, R.Close. Let’s get a quick walk-through the R module script as far as R is concerned.
1 2 3
'create a COM object representing R Set R = CreateObject("StatConnectorSrv.StatConnector") R.Init "R"
R. Init indicates the start of a R connection
'Pass data to R from clipboard R.EvaluateNoReturn "QVData
Calculations that aren’t returned to the QlikView interface but are instead written to a table for further use
1 2 3 4 5
'Write the data to text file for AUDIT purposes R.EvaluateNoReturn "setwd('C:\\R\\')" R.EvaluateNoReturn "write.table(QVData,'text.txt',col.names = NA)" 'Get R results RResult=R.GetSymbol("DisplayResult")
Show results and close the connection.
'Close R connection R.close