Enterprise Performance Management: How to with QlikView

Is well known that QlikView can read data from different sources and integrate them doing mashups. But it is also quite popular belief that QlikView can not write data at its base, which is a fairly misleading as there are not one but several ways to do it.

This ability to write data is often mentioned in BI tools as «write-back» feature and is closely related, almost as a «must», to use any BI tool in EPM (Enterprise Performance Management) where a good example is the corporate budget process where different budget holders are proposing target values ​​and adjusting them back and forth process. Other scenarios are also formulating the strategy, financial management process , supply chain, and so on.

This is a very common need for any company and interestingly is not really well known nor exploited in QlikView, possibly by lack the just recipe

To make it works we going use the instruction «INPUT FIELD» which is the way to declare a field of the model (don’t confuse with a variable) as an Input – Output.

There are many posts about this feature explaining their pros and cons and in general all the explanations have difficulties to implement it successfully. The main problem is that the inputs and / or modifications make by a single user remains in the «session» of that user (in QV server scenario), so there is no clear and simple way to share the data entered with the other members of the process, nor to persist information.

Now, think in a scenario (very simplified) where this EPM functionality could successfully used:

1 – Begin the process where the user is presented with historical data (eg previous year’s expenditures as the base target) and a current budget column.
2 – The user completes and confirms budget column.
3 – All other users see the data entered and they can change it with their comments.
4 – Repeat 1 to 3 until everyones reach an agreement.
5 – Confirm the changes and approves the budget.

Sounds good: in QlikView we solve the point 1)  declaring the field «Budget» as an INPUT FIELD type, but how do we get the remaining users with immediate visibility to the new data?

Luckily we have QlikView macros and the instruction «ServerSideExportEx» that allows us to save a pivot table, simple table or a table box  in different formats within QlikView Server. I emphasize this because in principle (and generally, though not always) the macros are run exclusively on client side.

When recording server-side the table that the user edit and complete, it  can be used as input to recharge back into the model (see my post on EDX and recharging on-demand) and thus after a few seconds that the user confirms the changes it will be available for other stakeholders in the process workflow through, which must be developed ad-hoc but not complex, just a good WF design is required.

Here’s the QV  script, a very simple example, based on an xml file that you can create using Excel:

inputfield Presupuesto;
..............
.....

CashFlow:
LOAD
    Type,
    Budget
FROM CashFlow.xml (XmlSimple, Table is [TableBox/_empty_]);

In our screen we will create at least one table and a button:

Due to the statement «InputField Budget» in the first line of our script, agter run it, we see that by passing the mouse pointer over the cells of the column «Budget» a icon appears as highlighted in the image. Clicking on it will see the cell in edit mode allowing enter data:

 

Then we associate to the button an Action type «external  macro execution» :

Finally clicking on the button «Module Editor» (or Tools /  Module Editor (CTRL + M) we put the following code for the macro VBS:

Sub Save_in_Server
	set tb = ActiveDocument.GetSheetObject("TB01")
	tb.ServerSideExportEx "C:\Blog\CashFlow.xml" , ";" , 3 '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF
end sub

Where to get the id of the table (TB01), we must go to the properties of the object:

After the user enters the data in the table and click the «Commit Changes» the table will be recorded in the QlikView server in the folder C:\Blog\CashFlow.xml (the path must exist on the server) and if you open the file (using eg. Excel) will see the  that the file was successfully modified.

Then should trigger through the same macro code (preferably another Sub) the code to manually reload a qvw so then the information modified will be accessible to other users.

Last  considerations:

1 – It only works when is open using «Open in Server» from the QlikView client or use the plugin (ActiveX) for IExplorer. Client using Ajax will not work.
2 – Apply the same considerations of security settings of the EDX (see post) (macro level security, Allow unsafe macros and CTRL + Shift + M).
3 – If after defining a field as an input field and execute the script does not edit icon appears on the table then some things should be simplified: a) the script and operations related to the field and/ or b) the relationships of the table in the model where is the field, even leaves unrelated.
4 – If the at script run time appears an error message indicating that there isn’t a unique identifier for rows you must create one using the instruction rown () as a field in the LOAD instruction.
5 – Any ideas, feedback, comments, improvement or return will be welcome!

Greetings!

PD: Any suggestion or comment will be appreciated, even if it refers to a my poor english in this post.
Txs!