Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Using EDX in QlikView 11

cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using EDX in QlikView 11

Attachments

A QlikView Technology White Paper

Published: November, 2011

www.qlik.com

Introduction

Event Driven Execution (EDX) allows the running of QlikView batch jobs based on external events. The primary usage of EDX is to have an external scheduler or Extract,Transform, and Load (ETL) tool (for example, Informatica) run QlikView batch jobs as part of a larger batch process. There are also other use cases, forexample, triggering a QlikView batch when a file arrives and user-initiated batches. This document describes the requirements for each of these three use cases.

The example code (attached below as QMSEDX-v1.exe) and executable (attached below as QMSEDX_CommandLine_v1.exe) that is delivered together with this document uses the new EDX API, which means this document and the example code are useful both when using the EDX functionality for the first time and when migrating from previous versionsof the EDX API.

Use Cases

Using an External Scheduler or ETL Tool

Many large organizations have other batch requirements than just QlikView. Such organizations often use a standard tool to run all batch processes, so that the IT training costs are minimized and common operations (for example, logging and re-launching) are handled in one central place. In addition, QlikView batch processing is almost always reliant on new data being available and in many cases this data is created by other batch processes. An external scheduler or ETL tool can be used to prepare the data and then run the QlikView batch to load it.

Most scheduler and ETL tools are designed to run external batch programs via a command line interface. The reasons for this include:

  • Batch processes are long-running, so starting aseparate process to handle them provides a simple way for the scheduler tomonitor the batch processes.
  • The scheduler can be configured to run the batchusing a particular operating system account that provides the required securitycontext.
  • If the batch process fails, the standard operatingsystem error handler can be used to pass this information back to the scheduleror ETL tool.

The main alternative to using a command line tool is using some sort of web services. Unfortunately,web calls cannot be used for long-running processes, since a timeout eventually occurs. A common solution to this problem is to start the process with a web request and then follow it up regularly with subsequent requests to find out if the original request has finished successfully or not.

File Arrival Detection

In many IT departments, the inter-system communication is not orchestrated by ETL or even Enterprise Application Integration (EAI) tools. Instead, files are pushed. For example, a non‑QlikView batch job that extracts data from a production database is typically owned by the IT team running the database, which means the team is responsible for creating and configuring the batch job and ensuring that it runs correctly.On the QlikView side, a program that checks if a file has arrived must be running, so that the file can be used in a subsequent QlikView batch.

In this scenario, simply starting the QlikView batch process should be enough. There is no need to follow its execution. The program is typically a Windows service as it runs all the time.

User-initiated Action

TheQlikView Automation API provides a number of functions that run QlikView batch scripts, for example, Document.Reload(). However, it is normally not a good idea to let users run batch jobs, since a QlikView batch job typically replaces the entire data set of an application used by many users.This means a single user can interfere with the work of all the others unless the QlikView batch is carefully configured. Because of this, the batch functionality is disabled in a QlikView Server deployment.

  • The marketing department at a pharmaceutica lcompany has an organizational division between the analysts and the product managers. The analysts are in charge of loading a dozen external data sources of varying quality. Once the data relating to a particular product is ready, the running of a QlikView batch loads the data and publishes the application to the relevant product managers.
  • A software company with thousands of employees wants each employee to have access to their sales data up to the last 30 minutes. The computer resources needed to support such data usage are massive. Since the salespeople only use the system once a week on average, it is decided to allow each user to run a QlikView batch to generate a QVW (for the user)whenever needed.

EDX in QlikView 11

In QlikView 11, EDX runs through the QlikView Management Service (QMS) API. This is a major change from QlikView 10, where EDX is realized by calls directly to a QlikView Distribution Service (QDS). The QMS API is a web service API that uses theSimple Object Access Protocol (SOAP). Client applications make HTTP (web) requests to QMS on port 4799. The system is secured by NT LAN Manager (NTLM) as well as special protective measures to avoid certain types of hijacking attacks known as “time limited service key”. This combination of security means the client application must be written in .NET and therefore the provided example code is .NET projects/solutions developed in Microsoft Visual Studio 2010.

The client application uses NTLM to authenticate a Windows account to QMS. QMS then checks which Windows groups the Windows account for the client application is member of to determine the function calls the user is allowed to make. Most of the QMS API requires membership in a local group called “QlikView Management API”, but to run EDX, a separate group, “QlikView EDX”, should be used. Both groups are local Windows groups on the server where QMS runs.

The client application makes calls to instruct QMS to start a task and in return receives an error code indicating success or failure, as well as an execution ID. The execution ID uniquely identifies the execution of the task as opposed to the task itself. The client application then periodically polls QMS to check the progress of the task execution. This poll request returns a data structure that contains, among other things, the execution status, start time, stop time (if already finished), and a list of new execution IDs. These subsequent execution IDs represent the execution of tasks that are triggered because the initial task has finished. The client application can then follow an entire set of inter-related tasks that together make up a full parallelized batch flow.

Lastly, the example code outputs log information and returns an error code to the operating system. The error code can be recovered in a standard way, for example, by using the %ERRORLEVEL% environment variable in a BAT file.

Changes Compared to Previous Versions

From a functionality perspective, the following has changed compared to using EDX in QlikView 10:

  • Because communication is with QMS instead of QDS, a QDS cluster can be used to load balance EDX calls. If high availability is required for the batch environment, QMS is a single point of failure. Consider either virtualization of QMS or an active/passive system.
  • The overall success or failure code of aQlikView batch can be returned. Previously, the status of the task could be fetched (that is, the definition of the batch job), but not the execution ofthe batch job.
  • The execution of subsequent tasks can be followed.

Notes on Using the QMS API

Finally,some notes on using the QMS API in QlikView 11:

  • The EDX password must not be null. Use an empty string, if no password is configured.
  • The QDS ID is required in the function call that starts the EDX batch job. However, higher-level privileges than membership in “QlikView EDX” are required to get a QDS ID. The simple solution is to pass an empty GUID for the QDS ID, which means QMS will search for the task in all configured QDS instances. Almost all projects today only have one logical QDS configured, so this can be a useful solution.
  • EDX can be used without a QlikView Publisher license. Simply pass the name of the QVW instead of a task name and the reloadschedule of the QVW will run.
Labels (1)
Comments
Not applicable

Arthur,

Thanks for the documentation, it is most useful.  However I didn't see any code examples, only a pdf and and exe.

Could you provide the example code? 

How is the .exe file used?

thanks

Anonymous
Not applicable

The .exe file expands into a sample code example.

Sent from my iPhone

Not applicable

Hi Arthur,

Can this code be used in Microsoft Visual Studio 2005/2008? Or it is just for Microsoft Visual Studio 2010?

Thanks,

Ruby

Anonymous
Not applicable

Hi Ruby,

Sorry but the code was complied as a 2010 project so I don't think it would be backwards compatible without some hacks.  Do a search on google and you can see some people were able to hack a 2010 project to work with 2008.

Arthur

Not applicable

Arthur,

In Qlikview 10 was it possible to explicitly set the the login in the script. QV 11, on the other hand,  uses NTLM approach. We have clients which sit outside the QMS domain and therefore cannot execute the EDX trigger.

Is it possible to use Impersonation authentication or SSL certificate to reach this end? or something else?

Thanks,

Dror

Not applicable

I guess,great number of potential users of EDX need this mostly for onereason: set up a possibility to initiate documentreload on server with a button in the QV document which isopened in Access point or in QV client.

If someonecould give step-by-step instruction (for dummies) how to set up such button, that would be great help. Otherwise, each of us has to invent the bicycle again and again individually (= unreasonable waste of time).

Arthur, please give such step-by-step instruction.

Anonymous
Not applicable

Read this document for a custom way of doing it without using the commandline tool:

http://community.qlik.com/docs/DOC-3024

Not applicable

Is it possible to monitor my task for completion if I use this pre-built command-line exe? If yes, then how do I do it?

Not applicable

Hi,

I have managed to successfully use the EDX command line options detailed in this document and in the thread http://www.qlikcommunity.com/message/202619

(I have posted this there as well)

If I attempt to pass any variables and use them in the document, the reload fails.

The log file clearly indicates that the variable is not being passed in (is a blank)

I have had a good look on the forums and could find very little about passing variables (and everything I could find that went into any detail involved C# coding as opposed to the command line tool described in this thread)

The treads about C# coding indicates that the variable values have to input as a string – i.e. <string>values</string> -

 

I have tried doing so from the command line, and also introducing a trailing semicolon, as well as enclosing both the variable name and variable values in quotes and double quotes, but have not managed to get the command line to pass a variable to the Qlikview document so far.

 

I am using a command line similar to the following (changed server name and paths):

\\MyFileLoc\QMSEDX.exe -task="DevMount/test_EDX.qvw" -qms=http://MyQVServer:4799/QMS/Service -password=password -variablename=vDateTime -variablevalues=201211231000 -verbosity=3

My actual requirement was to pass more than one variable: one for date and two for time (hour and min) , but have read elsewhere that only one variable is supported – hence concatenated date and time.

 

What I attempted do within the reload script is to split up the variable into date and time

 

The QV script I used is:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//Let VDateTime =TRIM($(VDateTime));

LET vDate = left($(VDateTime),8);

LET vHour = mid($(VDateTime),9,2);

LET vMin = right($(VDateTime),2);

and the log file is:

23/11/2012 11:40:42:      Execution started.

23/11/2012 11:40:42:      QlikView Version:11.00.11414.0

23/11/2012 11:40:42:      CPU Target                    x64

23/11/2012 11:40:42:      Operating System              Windows Server 2008 R2 Enterprise Service Pack 1 (64 bit edition)

23/11/2012 11:40:42:      Wow64 mode                    Not using Wow64

23/11/2012 11:40:42:      MDAC Version                  6.1.7601.17514

23/11/2012 11:40:42:      MDAC Full Install Version     6.1.7601.17514

23/11/2012 11:40:42:      PreferredCompression          2

23/11/2012 11:40:42:      EnableParallelReload          1

23/11/2012 11:40:42:      ParallelizeQvdLoads           1

23/11/2012 11:40:42:      AutoSaveAfterReload           0

23/11/2012 11:40:42:      BackupBeforeReload            0

23/11/2012 11:40:42:      EnableFlushLog                0

23/11/2012 11:40:42:      SaveInfoWhenSavingFile        0

23/11/2012 11:40:42:      UserLogfileCharset            0

23/11/2012 11:40:42:      OdbcLoginTimeout              -1

23/11/2012 11:40:42:      OdbcConnectionTimeout         -1

23/11/2012 11:40:42:      ScriptWantsDbWrite            false

23/11/2012 11:40:42:      ScriptWantsExe                true

23/11/2012 11:40:42:      LogFile CodePage Used:        1252

23/11/2012 11:40:42: 0002  SET ThousandSep=','

23/11/2012 11:40:42: 0003  SET DecimalSep='.'

23/11/2012 11:40:42: 0004  SET MoneyThousandSep=','

23/11/2012 11:40:42: 0005  SET MoneyDecimalSep='.'

23/11/2012 11:40:42: 0006  SET MoneyFormat='£#,##0.00;-£#,##0.00'

23/11/2012 11:40:42: 0007  SET TimeFormat='hh:mm:ss'

23/11/2012 11:40:42: 0008  SET DateFormat='DD/MM/YYYY'

23/11/2012 11:40:42: 0009  SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'

23/11/2012 11:40:42: 0010  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

23/11/2012 11:40:42: 0011  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

23/11/2012 11:40:42: 0014  LET vDate = left(,8)

23/11/2012 11:40:42:       Error: Script line error:

23/11/2012 11:40:42:       General Script Error

23/11/2012 11:40:42:       Execution Failed

23/11/2012 11:40:42:      Execution finished.

 

Does anyone have an example command line entry that works with a parameter/variable being passed into a Qlikview document, or pointers on how to achieve it using the command line EDX tool?

 

Also, if there is a method that does work to pass variables, any suggestions on how to use the option to pass multiple semicolon separated values for the variable, to effectively pass multiple parameters would also be really useful (can do away with concatenating and then splitting out which I have had to do above).

 

Many Thanks,

 

Alex.

Not applicable

Hi Arthur,

My requirement is to execute a TASK automatically through a button in qvw. I have created a TASK and put the QMSEDX.exe on my server.

From the button that I am creating in qvw I am trying to execute the command line

"strCommand = "C:\ProgramData\QlikTech\QMSEDX\qmsedx.exe -task="&strTaskName&" -qms="&strServer&" -password="&strTaskPassword"

where I have already defined the variables and I access the qvw from Access Point.

That task executes fine when I am running the command line directly on the server, but I am not able to get it working through my qvw in Access Point on my machine.

Thanks

Nitin

Version history
Last update:
‎2011-11-23 04:15 PM
Updated by:
Anonymous