Simple proof of concept Qlik Sense Extension to connect a Qlik Sense hypercube to an Open CPU R web service.
The extension creates a regression formula based on the Qlik Sense hypercube, and passes the formula and the contents of the hypercube to the R function lm() on an Open CPU web service.
As of now, the visualisation of the results is somewhat bare-bones (and now I'm being generous).
Download and unpack, then follow these steps.
#Using the module
Select the dimensions you would like to use for the regression. Since the hypercube aggregates on the dimensions, it is possible to indicate that one dimension is a grouping dimension. In that case it will not be included in the regression model.
| Explanatory variables | Grouping parameter (ignored dimension) |
|---|---|
 |
 |
Select the measure on which you want to run the regression. Since Sense wants an aggregation function on measures, there's a bit of name scrubbing in the background to avoid confusing R (all non-alphanumeric characters are replaced with underscore _).
Given the parameters chosen in the screenshots above, the extension will generate the following regression formula to pass to R:
Sum_performance ~ mkt_rf + smb + hml
Choose an Open CPU server. For testing purposes, I suppose you could use the public server on https://public.opencpu.org, but you really, really, really should set up your own server should you decide to use this for anything more than playing with the interface. The screenshot below shows the extension pointing to a local server running the Open CPU service.
If the stars are aligned, and your parameters are correctly set up, you should get something looking a bit like this:
Dead sexy, ya? Since we're talking to the hypercube, you can select dimensions in the Sense model, and a new R call with the reduced data set will be sent to the web service for re-execution.
#Limitations
The data extraction from the hypercube isn't very robust at the moment. If you send off millions of rows, you're unlikely to get the result you expect. See todo list.
#Demo
There's a simple [.qvf file in the demo folder](demo/R demo.qvf) in the repository, which includes a small dataset and a setup that runs a three factor regression.
#To do
- There's room for making the presentation a bit nicer. To put it mildly.
- It might be interesting to send the factors back as parameters to the Sense model, so they could be used elsewhere.
- Jeroen Ooms for Open CPU and opencpu.js.
- Stefan Walther for qliksense-extension-tutorial.
- Jeff Koch, who presented session T60 Integrating Qlik with R (link required Qlik partner login) at Qonnections 2015, which showed that this was possible and provided pointers to Open CPU.
- Speros Kokenes for senseUtils.js.
Unsupported and provided as is. Feedback is appreciated.