1. No category

This Whitepaper describes how to integrate
the Qlikview with R analytics software on the
web
Integrate
Qlikview with
R on the web
Swapneel Golapkar
Qlikview Developer
R&D associate
swapneelgolapkar@gmail.com
Contents
Introduction............................................................................................................................. 2
The Connector ......................................................................................................................... 3
Installation of R ....................................................................................................................... 4
Setting the environment for web integration ................................................................................ 13
Building the Qlikview document for integration .......................................................................... 14
Basics of Integration / Qlikview ....................................................................................... 14
The Approach .................................................................................................................. 14
Tweaking the code in Qvd: .............................................................................................. 15
Using the Integration Live. .................................................................................................. 21
References .......................................................................................................................... 25
SwapneelGolapkar@gmail.com
Page 1
Introduction
Vision:
Developing an Analytical Dashboard in Qlikview, which will initially present the raw data,
and allow the user to make desired selection and choose a statistical model.
The selections will be passed to R, where the statistical computations be performed and the
results generated by R (graphs / tables) shall be retrieved back to Qlikview in either
image/tabular format or in form of some Qlikview chart.
In other terms to integrate the strengths of the two applications:
1. QlikView’s visualization & in-memory analytics power.
2. R’s analytical power.
Leveraging visualization power of QlikView, with the analytical power of R.
SwapneelGolapkar@gmail.com
Page 2
Qlikview & R Connector
Qlikview

Qlikview 11.2 has been used for this integration
R Connector




StatconnDCOM is the middleware component you can use to integrate R into your
application on Microsoft Windows.
StatconnDCOM provides a DCOM component for R. The out of process (EXE) server
has been designed with both performance and stability in mind. Very low latency
makes it well suited for integrating R into an interactive application.
It comes in two versions, a licensed version which is paid, and other being Home &
Student license which is Non-commercial and comes free, but with a copyright &
watermark from the owner.
The connector can be downloaded by clicking here
SwapneelGolapkar@gmail.com
Page 3
Installation of R
Step 1: Install the R 3.1.1 windows edition.
Kindly select the following as Yes (customized startup)
SwapneelGolapkar@gmail.com
Page 4
Then Select SDI (separate windows)
Select Internet2 if your using a proxy network (usually at work locations)
SwapneelGolapkar@gmail.com
Page 5
Step 3: Install the StatConnDCOM connector (Kindly download the latest version of statconn)
SwapneelGolapkar@gmail.com
Page 6
SwapneelGolapkar@gmail.com
Page 7
Select the checkboxes as shown below
SwapneelGolapkar@gmail.com
Page 8
‘Finish’ the setup.
SwapneelGolapkar@gmail.com
Page 9
Step 4: Let’s test the connection between R & StatConn to check if the connector is working
fine.
Go to Start -> Type in ‘Basic Test’ the search box, and open the Basic Test application (See
below image for details.)
SwapneelGolapkar@gmail.com
Page 10
Click on the R32 button
Statconn image will appear as it’s a non-commercial version. This can be removed by
purchasing a paid version of statconn. Plz refer statconn site for the pricing of the same.
SwapneelGolapkar@gmail.com
Page 11
Once the test is successful the following will be displayed. It means that the connector is
working fine!
We can proceed to Qlikview now.
SwapneelGolapkar@gmail.com
Page 12
Setting the environment for web integration
Step 1: Enabling macro execution on Qlikview Server.
 Login to the Qlikview Server & open Qlik Management Console.(QMC)
 Navigate: QMC -> System -> Setup -> Qlikview Servers -> (Select Qvs) -> In the right
pane select Security tab.
 In the Security tab, make the selections under Miscellaneous tabs activating
 Allow macro execution on server &
 Allow unsafe macro execution on server.
This will allow the macros to be executed on the server via the access point document.
SwapneelGolapkar@gmail.com
Page 13
Building the Qlikview document for integration
Now the technical environment for the Qlikview & R integration is all set!
The integration works the same way as it works on the local environment, but the code
requires some small tweaking.
Basics of Integration / Qlikview
It’s imperative to understand how an application works, so that we can handle it the right
way. Below are some basics of Qlikview that will help us identify the hindrances as well as
the strengths of Qlikview, which can be used to facilitate the integration process.
Qlikview strengths:




It has powerful visualization capability.
In-memory analytics.
Allows macro execution.
There exists a connector to R.
Qlikview weaknesses:
 Cannot easily execute statistical algorithms like R.
 Doesn’t allow reload on the access point.
 Doesn’t have dedicated a statistical engine.
Accounting the strengths and weaknesses of Qlikview we have devised the following
approach of integrating Qlikview with R.
The Approach
1. The Qlikview document is to be set up on access point (Qlikview Server (Qvs)).
2. There needs a directory on the server which should not be altered by any other user
/ application.
3. The macro needs to be written considering the Qvs as the local machine and the
appropriate paths to be associated with the above mentioned directory, so that all
the images / tables are stored / picked to / from the above the directory.
SwapneelGolapkar@gmail.com
Page 14
4. Whenever the macro is executed to store the image or execute R script or load image
/ table, it should address that specific mentioned directory and pull data from same
directory.
5. This makes macro calls to be executed on the Qvs, considering it as local machine.
6. The newly generated data now needs to be reloaded, the Qlikview built in
functionality of reload doesn’t work directly on the access point, and for this we need
another macro.
7. The reloading task is done by calling a macro which executes a batch file to reload
the same Qlikview document open in the access point. The batch performs a reload
of Qvd through command line.
8. Once the reload is complete the new data is visible in the document open on the
browser.
Tweaking the code in Qvd:
Qlikview Application Welcome screen.
SwapneelGolapkar@gmail.com
Page 15
Main sheet
In this example we have coded the Import Variables button to trigger export the selected
table name & pass it as a parameter to R script & then the R script that will fetch the values &
display results in Qlikview
SwapneelGolapkar@gmail.com
Page 16
Macro code to export the selection.
**The above image has ServerSideExportEX underlined; this is explicitly required when
we need to perform export via the web browser.
Making a call to the R application & executing the R script.
SwapneelGolapkar@gmail.com
Page 17
Reload & Partial Reload Macro
Batch files definition: Kindly ensure the location of batch files being stored, should be
same as mentioned in macro.
Partial Reload.bat
Reload.bat
SwapneelGolapkar@gmail.com
Page 18
Document Properties to be set in qvd.
These settings will allow the update, when the document gets reloaded.
SwapneelGolapkar@gmail.com
Page 19
Setting Variables properties: In order to set the layout on the access point according to the
variables, we need to include all the variables used in the application into Bookmarks,
kindly do this for all the variables.
That’s it; we are done with all the settings!
We can now go ahead and test & use the application over the browser.
SwapneelGolapkar@gmail.com
Page 20
Using the Integration Live.
1. Open access point on the browser & open the Qvd
2. Navigate to the Main Sheet of the Application
SwapneelGolapkar@gmail.com
Page 21
3. After Clicking Import Variable, the table name is exported & R script is executed,
which sends back the List of dependent & independent variables.
4. Selecting variables & running Linear Regression, R script by passing selected
variables to it. The part highlighted in yellow is output generated by R
SwapneelGolapkar@gmail.com
Page 22
5. Plot generated by R for Independent variables.
6. Other images plotted by R by same Linear regression algorithm
SwapneelGolapkar@gmail.com
Page 23
In this way the Qlikview application can be integrated with R, one can also feed the tabular
data generated by R to Qlikview as an input & generate Qlikview visuals.
SwapneelGolapkar@gmail.com
Page 24
References
1. QlikView and R Integration for Predictive Analytics Example – Brian
Bauer
http://community.qlik.com/docs/DOC-3981
2. R application (http://www.r-project.org)
SwapneelGolapkar@gmail.com
Page 25