Quick - Qlear - Qool

DTAP deployment with common script

Bas — Thu, 19 Jan 2012 07:56:13 +0000

When I develop a QlikView app I use datasources that are local on my PC. For instance my local SQL Server instance has a shrunken copy of the production database. When I deploy the QlikView app to Test I want it to use the datasources that are on the Test machine. When I deploy to Acceptance I want to use the acceptance datasources and of course in Production the real datasources have to be used.

This article describes how I cope with the DTAP (Development, Test, Acceptance, Production) environments in QlikView. It will show how you can move your app from one environment to the next without changing anything to the app and without being dependent on the file structure of the server you are deploying to. It can also help you if you are working on a single QlikView app with multiple developers.

I achieve this by creating a registry entry that holds the path of a common script. This common script can contain anything, but I use it for my connections and common procedures. The registry entry is read in the QlikView app, where the common script is included. The figure below shows a graphical representation of the steps.

Figure 1

So why do I use a registry setting to point to the file and don’t include a direct link to the file in the QlikView app? For me it seems a realistic possibility that someday you might decide to change the location of your common script. By not referring to the path of the common script directly from your QlikView app you will always have the opportunity to do so.

Let’s start off by creating our common script. Create a text file with the following content and save it as C:\Common.qvs.

// Make a connection to the datasource
SUB ConnectToDatasource(datasource)

IF datasource = ‘First’ THEN
LOAD MsgBox(‘Connected to datasource “First”‘, ‘SUB ConnectToDatasource’, ‘OK’, ‘ICONHAND’) AutoGenerate 1;
// ODBC CONNECT TO DS1 (XUserId is HEXKBHwKBaQYUCZJ, XPassword is UUTSWXUOBeYRWQdJ);
EXIT SUB
ENDIF

IF datasource = ‘Second’ THEN
LOAD MsgBox(‘Connected to datasource “First”‘, ‘SUB ConnectToDatasource’, ‘OK’, ‘ICONHAND’) AutoGenerate 1;
// OLEDB CONNECT TO [Provider=SQLOLEDB.1;Initial Catalog=Second;Data Source=DEVELOPMENT];
EXIT SUB
ENDIF

// This line should only be reached if the datasource that was provided cannot be found. This means
// that it is not possible to make a connection.
LOAD MsgBox(‘Failed to locate the correct datasource: $(datasource)’, ‘SUB ConnectToDatasource’, ‘OK’, ‘ICONHAND’) AutoGenerate 1;
ENDSUB

The common script file contains a single procedure that can connect to two datasources: First and Second. Because of this demo I have replace the actual connection strings with messageboxes. So when we are using this in our QlikView app, connecting to a datasource will show us a messagebox that informs us to which one we have connected.

The second step is to create a registry entry. Start the Registry Editor by going to Start – Run… and type in regedit (figure 2).

Figure 2: Run regedit

In the Registry Editor go to the key where you would like to create the reference to the common script. I have done it in “HKEY_LOCAL_MACHINE\SOFTWARE\MyCompany”. Create a new string value here, name it “QlikView Common Script” and set its value to C:\Common.qvs (figure 3).

Figure 3: Create new string value

This is all the work you have to do outside QlikView. We have created our common script file and made a reference to it in the Windows registry. Now we have to refer to it in our QlikView script.

Add the following lines to your QlikView script to reference the common script:

CommonScript = GetRegistryString(‘HKEY_LOCAL_MACHINE\SOFTWARE\MyCompany’, ‘QlikView Common Script’);
$(Include=$(CommonScript));
LET CommonScript = NULL();

The first line gets the value of the registry entry and puts it into the variable CommonScript. CommonScript now contains C:\Common.qvs. The second line includes the common script and the third line cleans up the variable.

You will have to include these three lines in every QlikView app you create. But once included you can use the procedures you have created in the common script anywhere in your QlikView script.

So let’s put our common procedure in practice. Let’s say we want to load something from datasource First. The only thing we have to do prior to the LOAD statement is to use the procedure of our common script:

CALL ConnectToDatasource(‘First’);

This will make a connection to the First datasource, but does so from a central place. When the connection to this datasource changes you will only have to change it in one place. And when you want to move your common script to another file location, you will also only have to do it in one place.

If you create a similar structure with the common script file and the registry entry on each server you are deploying to. Or if each developer that is working on the app has a similar structure on their development machine. You will have no problems with deployment to through the DTAP environment and collaboration on a single app will work smoothly.

And as an extra you have a central place to define other commonly used procedures or variables. I have all my system variables defined in the common script for example.

Footnote
I still use QlikView 10 and have not tested this in QlikView 11.

OnApplyBookmark document event trigger

Bas — Wed, 28 Sep 2011 05:32:24 +0000

QlikView offers different document event triggers: OnAnySelect, OnOpen, OnPostReduceData and OnPostReloadData. They all enable you to respond to certain events generated by QlikView. This can be useful in many cases. For instance, I often use the OnOpen trigger to have the current date selected when the user opens the document.

The other day I was developing a QlikView app where I wanted to execute a macro action when the users applied a bookmark. In this case I needed to refresh a chart with different measures that were selected by the user. QlikView does not have an OnApplyBookmark trigger. So how do we persuade QlikView to help us achieve this goal?

My first hunch in solving this was to simply use a field event trigger. But when you setup an OnSelect trigger on a field, it only fires when the user makes a manual selection in the field. The trigger will not fire when the user changes the field selection by applying a bookmark.

Fortunately it possible to mimic the OnApplyBookmark trigger behavior by using a variable event trigger. To make this happen I use the option to include the value of the variable in a bookmark together with a variable event trigger. The following steps describe how this works.

Start off by adding a variable to the Variable Overview (CTRL-ALT-V) with the name OnApplyBookmark and set its value to any piece of text. In this example I have used the text true (see the image below).

Open the document properties (CTRL-ALT-D) and go to the tab Variables. Select the just created OnApplyBookmark variable and check the Include in Bookmarks checkbox (see red rectangle 1 in the image below). This ensures that whenever the user creates a bookmark the value of our variable is included in this bookmark.

Select the Triggers tab in the document properties and add an OnInput action to the OnApplyBookmark variable. (In this situation the OnChange does not work in the IE plugin so therefore it is best to use the OnInput.) Blue rectangle 2 in the image below illustrates how to add an OnInput action to the variable.

From here on out you can select any action to be executed when a bookmark is applied. For this example I will execute a macro function. Therefore click on the add button to add an action and select External – Run Macro. Type OnApplyBookmarkFired in the Macro Name field (see image below).

Click on the Edit Module… button to add the code below to the macro editor.

Sub OnApplyBookmarkFired
MsgBox(“A bookmark was applied”)
End Sub

Now confirm all the dialogs with OK and start testing. If all is well you should get a messagebox each time you apply a bookmark that was created after implementing this change.

Why does this work? We have setup our newly introduced variable OnApplyBookmark in such a way that its value gets included in bookmarks. This means that when a user creates a bookmark the value of OnApplyBookmark is stored within that bookmark. When the user applies the bookmark, the value of OnApplyBookmark is written back into the actual variable. Because we have set a Run Macro action on the OnInput event of the variable, our code gets fired the moment the value is written to the variable.

This example show how you can execute macro code when a bookmark has been applied. But, as mentioned before, you can also use this to trigger any available action.

Quick post: Qlik Here for My View of QlikView 10

Gilles — Thu, 21 Oct 2010 19:07:27 +0000

There’s a nice positive review of qlikview 10 and it’s new features by Ventana research. You can read it here: Qlik Here for My View of QlikView 10.

Funny: the writer does see value in the mekko chart. I think it’s confusing and might even consider a pie chart.

Furthermore remarkable: the writer (David Menninger) says it’s not such a major release. And I think he might have a point here!

Anyway read it yourself!

Quick post: Qlikview’s SWOT analysis from Gartner and comments by Qlikview

Gilles — Wed, 20 Oct 2010 10:47:23 +0000

I came across this post on Qlikpowers blog: Qlikview SWOT. It’s interesting to read because it adresses some threads and weaknesses wich are commented on by Qlikview.

Quick post: Qlikview 10 is generally available

Gilles — Tue, 12 Oct 2010 12:30:36 +0000

The press release can be read here: pressrelease

The Qlikview 10 introduction site can be found here: QV10

A soon to be released post about the most important new features and functionality is coming up!

I forget: what’s in memory?

Gilles — Thu, 02 Sep 2010 13:21:30 +0000

I’ve came across an excellent post by Boris Evelson on his blog: http://blogs.forrester.com/boris_evelson/10-03-31-i_forget_whats_in_memory (thanks to @vizubi) it’s an old one, but it makes perfectly clear what are the differences between the different in memory options. Evelson makes distinction between 5 types of in memory solutions, divides the solution providers over these 5 types and gives pro’s and cons for every option. Nice read!

In the same category: compare in-memory solutions is this post by Boris Evelson as well: http://blogs.forrester.com/boris_evelson/10-09-01-not_all_in_memory_analytics_tools_are_created_equal where Boris gives advice on how to compare in-memory solutions. Nice read as well, and nice comments from two Qlikview adepts

New Qlikview video, about the in-memory advantage

Gilles — Tue, 29 Jun 2010 12:39:52 +0000

There’s a nice new marketing video on qlikview.com. It’s called “The Qlikview in-memory advantage“.

There’s just one thing they always seem to forget at Qlikview: to have such a nice way of ” analysing and visualising all your data” you DO NEED a solidly build and well architectured datawarehouse!

QlikView Security

matthew — Tue, 15 Jun 2010 07:57:40 +0000

Last week I encountered a few problems with a QlikView app where I had to implement a section access component. The section access needed to fetch the users and roles from an Oracle Database.

Reading through the QlikView Docs I found the chapter about security, explaining an example how to implement it but unfortunately the QlikView Docs aren’t so clear and I had to search through the QlikView forum to find people with similar issues.

Issues I encountered :

1. Fetching users from the database within the section access does not work. After a succesfull reload I closed down the QlikView App and every user I tried to login with was denied access. So the QlikView apps did become useless, lucky me I made a backup.

2. Adding an inline table with admin users and concatenating a load statement with the users from the database did not work either (at least not the way I always write a concatenate statement). As mentioned in the the QlikView Docs all column names and values need to be in upper case if not they will be converted. But as experienced this is not the case, you need to explicitly make every column upper case with the function Upper().

Why I first insert an inline table is mainly because of fixed users that never change and secondly because of this post of William (link to post ).

Finally this is the code that works:

DbUsers_login:
LOAD
ID
,	LOGIN
,	TYPE
,	NAME;
SQL SELECT *
FROM "SEC"."USERS_LOGIN";

Section Access;
Star is *;

Security:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, ROLE
ADMIN, ADMIN, 123, *
];

CONCATENATE (Security)
LOAD
'ADMIN' as ACCESS,
upper(LOGIN) as USERID,
'123' as PASSWORD,
'*' as ROLE
Resident DbUsers_login;

Section Application;

drop table DbUsers_login;

The underlying technology of QlikView

Gilles — Tue, 15 Jun 2010 07:31:43 +0000

There’s an excellent post by Curt Monash on the underlying technology of Qlikview. Nothing really new, but a very nice summary of how it works, with comments from (among others) TDWI’s Wayne Eckerson and Håkan Wolgé (the author of qlikview 1.0 code and still chief architect at Qliktech).

One thing really new to me was the following part and I quote:

The main ingredient of the performance secret sauce in QlikView is that selections are compiled straight into machine code. (QlikTech gave me the impression that this post is the first time that will be publicly revealed.) Notes on that include:

In the old days, QlikTech thought compilation gave a 10X performance benefit vs. interpreted code. However, 5X might be a more up-to-date figure.

It’s not just code; part of the compilation is to create temporary lookup tables.

A single calculation can use multiple cores. QlikTech thinks it’s done a very solid job of engineering efficient multicore parallelism. (Note: So far as I could tell, Hakan was using “calculation” to refer both to queries and, well, calculations.)

There’s a good reason QlikView runs only on Intel-compatible processors. A port would be painful.

So read the full story here: The underlying technology of QlikView

Qliktech and Human Inference work together for better dataquality

Gilles — Tue, 04 May 2010 06:58:27 +0000

There’s a press release on EarthPublisher.com about a new technology partner for Qliktech. They start working together with Human Inference. Human Inference is a Dutch company and is the European market leader in data quality solutions. They focus on high quality solutions developed for large databases and critical systems. The solutions are based on natural language processing. You can read their corporate factsheet here. You can read the press release here (in Dutch).

I consider this as a little bit strange partnership. I thought Human Inference works on your source systems, like your main CRM system and would love to hear how this works in conjunction with qlikview. Any thoughts?