Using EDX to boost your Qlikview Scheduling

Over the course of multiple SAP reporting implementations our team have found that the native Qlikview Management Console (QMC) is often not fit for purpose.

As with many systems but especially SAP, a large comprehensive reporting suite can have upwards of 150 source tables and importantly a single table is often used in multiple data flows and applications. The native QMC enables cascading of tasks, however keeping track of the data flow between raw SAP tables and the end analytical SAP applications can become rather complex.

The example above is overly simplified, however it is equally representative of many complex scenarios.

In this case a number of SAP tables feed three different data models yet there is additional complexity as two of the data models are also used as inputs for the Plant Scorecard Datamodel.

It is difficult to schedule a reload time for the MD4C, Warehouse Inventory Optimisation and Plant Score Card Applications as the exact timing of the sources table might vary.

Sometimes the ETL processing from SAP will take slightly longer than normal due to the processing demands on the SAP client.

In effect, there is a many to many scenario with many source tables being used in many data models. With the native QMC cascading of tasks, the above complexity is not catered for.

Fortunately, there is a solution which uses a Qlikview application to work off an Excel based master schedule in order to create EDX triggers.

The QMC can handle EDX triggers but a small executable file is required for the Qlikview application to interface with in order to fire off the EDX trigger.

1. QMSEDX_CommandLine executable file. This is a simple executable file that is programmed to send a trigger to the Qlikview Management Console.
2. Excel based scheduler. This is an excel file that maps out all source data inputs for each data model or application. The scheduler will also contain the task id and full file path for each application.
3. Qlikview Trigger Application. This application reads the Excel scheduler and checks the reload dates and file sizes of each dependent data source. Based upon the status of the dependants a task will be triggered for reload or else nothing will happen.

In the below screen shot, a simplified presentation of the Excel scheduler is displayed. The same file is also replicated in Qlikview and the dependencies are represented in Red or Green, depending on the status. A reload will only happen when all of the dependencies are CORRECT and it is easy to see which data sources are holding up the process.

Changes to the QMC involve changing the reload type to “external event” (EDX). In the screen shot below, the password field has been captured. It is important to note that EDX triggers will never work in the absence of passwords (the field can’t be blank). As part of the framework, all of the tasks need to make use of the same password.

1. Obtaining the Task ID from the Qlikview Management Consule

The Excel scheduler needs to have the Task ID for each task to be triggered. Finding the task is not straight forward.

The Task ID can be found by looking at the Trigger History section on the console.

2. The Qlikview trigger requests to the QMC

The following is a generic trigger request that can be placed into the Qlikview load script. Note that the executable file has been set up to take the task name as a parameter and this is passed into the execution statement with a variable.

Using an EDX framework removes the complex cascading chains that are formed using native QMC functionality. Scheduled times become more elastic and delaying in source file processing wont result in data errors. Through the use of Qlikview to visualise the source file status will make for a more user friendly and easier analysis experience

Deployment for the above EDX can be achieved in under a day, however prework needs to be done to map out all the dataflow dependencies. During this exercise it is also advisable to recheck the code stack to see if any optimisations can happen.