DTAP deployment with common script

Posted by Bas on 19/01/2012 under How To | 4 Comments to Read

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.

Tags: Common script, Deployment, DTAP, include, qlikview 10, script

Brian S said,

We use a little simpler method. We have a common include script that all apps include. The include script detects the machine name it is running on and then we use a case statement that sets a standard set of variables to the appropriate connection strings for that machine. Each app can then instantiate any connection string variables it needs for its script. We try to avoid having any registry dependencies for portability sake.
Bas said,

That is also a very good option. There are multiple ways of solving the inclusion of a common script. I suspect that your include points to a static file path? That’s something I wanted to prevent. But of course in my method I point to a static registry location. Just a matter of preference I guess.

- Bas
Brian S said,

I see. Yeah we have a standard directory structure on all of our QV servers and developers machines. So then we just have a relative path ($(Include=..\..\shared includes\incDataSource.qvs)) to the include file. Many ways to “skin the cat” depending on how you need to do it. QlikView is nice that way.
Bas said,

You have pointed me to another way of doing it. It can also be achieved by placing your common script in a share. When you use a share you are also independent of the filestructure of Windows.

For instance when you create the folder “C:\MyScript” and create “Common.qvs” there. You can share the folder with the name “QlikView_Script”.

In your QlikView app you can include the script like this: $(Include=\\localhost\QlikView_Script\Common.qvs);

When you are on another machine you can use a different folder, but use the same share name. So the next machine can have the common file in “C:\QlikView\Scripts”. But still share this as “QlikView_Script”. This means that the same include will still work.