Integration of Qlikview with R
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 [email protected] 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 [email protected] 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. [email protected] 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 [email protected] Page 3 Installation of R Step 1: Install the R 3.1.1 windows edition. Kindly select the following as Yes (customized startup) [email protected] Page 4 Then Select SDI (separate windows) Select Internet2 if your using a proxy network (usually at work locations) [email protected] Page 5 Step 3: Install the StatConnDCOM connector (Kindly download the latest version of statconn) [email protected] Page 6 [email protected] Page 7 Select the checkboxes as shown below [email protected] Page 8 ‘Finish’ the setup. [email protected] 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.) [email protected] 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. [email protected] 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. [email protected] 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. [email protected] 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. [email protected] 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. [email protected] 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 [email protected] 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. [email protected] 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 [email protected] Page 18 Document Properties to be set in qvd. These settings will allow the update, when the document gets reloaded. [email protected] 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. [email protected] 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 [email protected] 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 [email protected] Page 22 5. Plot generated by R for Independent variables. 6. Other images plotted by R by same Linear regression algorithm [email protected] 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. [email protected] 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) [email protected] Page 25
© Copyright 2024