I have a confession to make. As someone who comes from a background of visual ETL tools (such as SAP BO Data Integrator and Informatica PowerCenter) loading data into QlikView via scripts has always struck me as somewhat old-fashioned.

Don’t get me wrong. I can very much appreciate a good QlikView script. It is just that whenever I hear someone say that QlikView does not need a visual way to load data and that script is fine it reminds me of the “veteran” DBA’s I’ve met during my work as a BI Consultant. If you have worked in BI/data warehousing for any length of time you probably know the type, lots of facial hair, heavy smoker, followed his last training in 1986 and, most importantly, he is absolutely adamant that PL/SQL is the only right way to do ETL. Graphical tools are for kids and amateurs!

I do not agree with that statement, so you can imagine that I was pleasantly surprised when I read the announcement for QlikView Script Generator (QsGen) this week. QsGen promises, amongst other things:

• Visually design complex scripts to load your data
• No more need of scripting skills
• 500% faster design

Interested to see if I really would not need any scripting skills to build fantastic “marble tracks” of data I decided to download the trial version and test it with QlikView 10 SR1.

The test

As I needed an objective while testing the software, I decided that I was going to try to accomplish the following:

• Load data from the Northwind Access database to build a small star schema;
• Load some additional data from Excel;
• Create a time dimension using a complex script.

For those that are not familiar with the Northwind database, this is an example database that comes with Access and SQL Server. It contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. The database contains information about customers, suppliers, products, orders and employees (as well as a few other bits of miscellaneous information).

Creating a new project

When you create a new project you are presented with an empty workspace. All actions are accessible via the ribbon at the top. Data sources are listed on the right. There is also a “mappings library” on the right. At the moment this library only contains simple wizards for data sources, but I assume/hope that more complex, predefined transformations will be added in the future. For example, a time dimension wizard.

The center of the screen is where all the action happens, this is where the data model is built.

Setting up the database connection

Setting up a database connection to the Northwind Access database was nice and easy, though unfortunately only the 32 bit drivers were shown (I tested this on a 64 bit system with the 64 bit version of Office). At the moment QsGen only supports OLEDB connections, but I have been told that there will be a new release next week which also includes ODBC connections.

Loading data from the database

This part I liked. The SQL Editor lets you build a (sub-)query by dragging and dropping tables, drawing relationships between tables and selecting output fields. Simple and straightforward and a lot better than the SQL editor that is built into QlikView.

There is an option to export and test the resulting query in QlikView, but I was less impressed with that feature since it did not work correctly. QsGen was probably developed with QlikView 9 in mind, as the code it generates to connect to a database is not compatible with version 10 and had to be corrected manually (click for screenshot). I suspect that it should be easy to add a routine to QsGen that detects (or just asks) which version of QlikView is installed so it can generate the correct code.

After I had loaded all the tables from the database I had the following data model in QsGen:

Something that I could not easily achieve was limiting dimension values based on the fact table. For example, loading only data for customers who had actually placed an order. I normally do this with the KEEP prefix or the Exists function but ended up doing this in the SQL statement. It worked, but of course this solution will not work when you are loading data from multiple sources.

Loading additional data from Excel

For the Excel data I created a file with some additional product data that I wanted to merge with the existing product dimension. To do this I had to create a “mapping”, which is shown below. The purple track on the left represents the data loaded from the database, while the green track on the right represents the Excel data.

The code that this mapping generates is shown below.

It seems a bit nonsensical to me to first implicitly concatenate all the data into a single temporary table and then load that entire temporary table into a new table. It works, but juggling two tables in memory might get problematic when you are loading huge amounts of data.

Creating a time dimension

For the time dimension I used a “QlikView Mapping” from the mapping library. This object lets you “Code your own QlikView mapping”.

While the QsGen training video made creating a time dimension seem very simple, it turned out to be a bit more difficult. When I create a time dimension, I usually determine the range of dates dynamically. In this example I wanted to generate a calendar for the lowest to the highest order date. For this I use a piece of code that looks like this:

etc.

When you have entered your script, QsGen validates it by running it through QlikView and retrieving information about the table that was created. However, when QlikView ran my time dimension script, I got the following error:

This error can be easily explained; QsGen only sends the script for my time dimension to QlikView, it cannot find the Orders table that this script is referencing because it does not exist. I am sure there is a way to properly work around this, but I did not want to spend too much time on this so I just created a static min and max date.

Generating the script

After adding the time dimension my finished data model looks like this:

The last step is to generate the script and export it to QlikView, this is done via the Script Designer dialog.

In this screen you can select which part of your model you want to export, giving you the option to build multiple applications around a single shared model. This idea quite appeals to me.

Unfortunately, exporting the script was not entirely problem-free either. When I looked at the QlikView application it still had “artifacts” of things I had deleted or changed earlier. For example, relationships that I had deleted still appeared in the script. Also, as I said before, the CONNECT statement was not in the correct format. After manually correcting these issues I was left with the following data model in QlikView:

Conclusion

So, can you use QlikView Script Generator to visually design complex scripts to load your data, without any need of scripting skills and in 20% of the time it would take you to write the script in QlikView?

At this moment, I am not convinced. For simple scripts, maybe. For anything that is a little more complex, you quickly run into the need to use a “QlikView mapping”, which means you have to write script anyway.

However, this is still an early release. Despite limited features and abundant glitches, I do think that this tool has a lot of future potential. I am putting it on my watch-list.

Download

Download QlikView Script Generator

 

Update 2014/02/03: QlikView Script Generator is now known as QVScriptor. You can find the new website here.