Qlik Sense and R

    The idea behind Qlik Sense is to spread the audience for data analysis: it is quite clear that not everybody can use R, so the need to automate it should be strong. Furthermore, a data scientist, could be interested in merging this two great tools in an elegant way.

    I've seen many threads about Qlik (View and Sense)  and R, and I'd like to share all the content that I have found, in an organic way: I'd like to share this informations, to improve this document and learn more, hoping to be helpful also to you. The focus is going to be on R and Qlik Sense Desktop.

     

    In this document:

     

     

    R: a brief introduction to this tool.

    BATCH WITH R: how to batch with it.

    USING R EXTENSIONS: extensions to use R.

    EXECUTE R IN THE QLIK SCRIPT: Another way to integrate R

    INTEGRATION OF R IN MSSMS: New feature from MSSMS, the integration with R (NEW!)

    QLIK SENSE FROM JUNE 2017 VERSION: A step ahead in Advanced Analytics Integrations (NEW!)

     

     

    R

    Link to R: https://cran.r-project.org/

    "R is ‘GNU S’, a freely available language and environment for statistical computing and graphics which provides a wide variety of statistical and graphical techniques".

    You can easily see how much this tool could be powerful, a great quantity of analysis could be done with it. You can imagine that the meeting of R with Qlik Sense could be a winning combination.

     

    Till now there are two way to merge R with Qlik Sense: someone from Qlik told me that in the next release there are going to be more elegant integrations but, in the present, here the solutions.

     

    Remember that some advanced statistical techniques are not simple to use (like ACP,MDS,FA etc.,), so be aware to make them automatic.

     

     

     

    It is clear that to use this tools, you should be able to use R and Qlik Sense, a bit of statistic/math skills and also a bit of Javascript could be helpful. Maybe the biggest cons of this ideas is a good knowledge of R and statistic/math skills.

     

     

    BATCH WITH R

    This is the most simple way to use R with Qlik: the idea is to elaborate your data with R, and send them to a folder that Qlik Sense is going to read when you'll load your data.

    First, you should download and install R, then, after you choose your analysis, you'll write down on its script where to save the results.

    After done that, you simply create a .exe file, to run everytime you'll want to refresh your analysis. The code should be this:

     

    "add path to R \R\add R VERSION\bin\R.exe"

    CMD BATCH

    --slave --vanilla

    "add path to script\script.R"

    "add path to put the report\report.txt"

     

    source: Quick-R: Batch Processing

     

    Then you should have your new files ready.

    On the load editor, you should only fetch your data: everytime you want to refresh the analysis, you should restart the .exe.

    It is quite clear that this way is very simple, but you should be aware that the analysis is not alive, so, be aware of your data structure, to see if there are any problem.

     

    PROS: easy, fast.

    CONS: not alive, be aware that you should know all the data model/mathematical implication of add an analyisis that is not alive.

     

     

    USING R EXTENSIONS

    Some clever Qlik Users have create great extensions to make the connection between R and Qlik "alive", so the analysis (and the technique) are affected by all the things that make us love Qlik Sense (filter etc.). The idea is to have data and R analysis in Qlik with an extension and, when you want a change, send a request to an external engine that is going to process it, and then the refreshed extension is going to have the data requested.

    To make this structure is very complex in my opinion, you should have a strong grip on R coding, Javascript coding, and Qlik.

    Luckily some developer have create something interesting: my favourite one uses Shiny, a R package as engine. Here the link.

     

    https://translate.google.co.jp/translate?hl=ja&sl=ja&tl=en&u=http%3A%2F%2Fqiita.com%2Fsasanquaneuf%2Fitems%2Fcd4aefdd466…

     

    This extension is fantastic, but not all the analysis are included, and sometime it crashes.

    If you do not like it, you can find other ideas here:

     

    Is R integration possible with Qlik Sense ?


    PROS: alive extension, for some analysis the R script is already done.

    CONS: sometime unstable, be aware of your data model, not all the analysis are already done.

     

     

    EXECUTE R IN THE QLIK SCRIPT

    You can also decide to execute R directly in the load editor of Qlik Sense. This is more or less similar to the batch, because the logic behind is to make R create a set of data that you are going to import directly. the idea is to import your data in Qlik, give the EXECUTE command, and load the result of the R analysis.

    It is do something like:

     

    Load*from...

    EXECUTE  "C:\Program Files\R\R-3.1.0\bin\R.exe" CMD BATCH "...directory\Rscript.r";

    Load*from...

    [..newdatadirectory\analyzeddata.csv]

     

     

    PROS: faster than the BATCH technique, without going out the Qlik environment when you execute R.

    CONS: as the BATCH technique, not alive, be aware that you should know all the data model/mathematical implication of add an analyisis that is not alive.

     

    source: Qlikview + R

    thanks to flanfranco

     

     

    INTEGRATION OF R IN MSSMS

    From the 2016 MSSMS (Microsoft Sql Server Management Studio), you can integrate one or more part of R code directly in queries, stored procedure and so on. Microsoft, with the sp_execute_external script, has given a quite elegant way to take data from database, give them to R to crush them, and maybe put the results in another table.

    Here some documentation:


    sp_execute_external_script (Transact-SQL) | Microsoft Docs

     

    I've tried several time this, from simple creation of contingency table with R (table()), to slightly more complex cluster analysis. However my use is quite border line in Qlik Sense way, because till now I've not tried to launch the sp_execute_external_script from the Qlik Sense script so, if you try it, tell me and I'll add the results and citations here.


    PROS: very elegant and official, with all the pros of MSSMS.

    CONS: as the previous techniques, not alive, be aware that you should know all the data model/mathematical implication of add an analyisis that is not alive.



    QLIK SENSE FROM JUNE 2017 VERSION

    Recently Qlik Sense has been updated to integrate the communications with AAI engines, like R, Python and so on.

    Everything is quite new to me, and I'm studying it to add something interesting to this post.

    Here some sources, hope it helps till I've managed to learn more.

     

    Installing R with Qlik Sense.pdf

    AAI With R - Learning Exercises.pdf

     

    PROS: alive and official, great use of R also in the graph part.

    CONS: probably this could not manage great quantity of data in reasonable time: for example, waiting for a refresh of the viz for more than 1 minute, could not be nice.

     

    There are many ways to use R in Qlik Sense: choose your wisely, because there are pros and cons everywhere, and share your result if you want!

     

    Now let's grind some data!

     

     

    -------

     

    This document has all what I have discovered about this. I hope you'll find interesting and help me to make my knowledge wider.

    I've not yet explored the Qlik Sense Enterprise with R, but I suppose it is similar: I'll add the progress if it is going to exist.