Hands-on with QlikView Script Generator

By Barry Harmsen

A marble track of data!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

Creating a new, empty 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 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

SQL Editor

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:

Data loaded from database

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.

Map & merge

The code that this mapping generates is shown below.

Mapping code

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

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:

Calendar based on date range

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:

Table not found!

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:

Finished data model

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

Script designer

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:

QlikView data model

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.

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

19 Comments

  • 1
    February 5, 2011 - 01:47 | Permalink

    Excellent article. Very detailed and saved me the effort!

  • 2
    February 5, 2011 - 10:10 | Permalink

    Thanks Barry for your excellent work, I will try to answer your some questions:
    -Yes you still need to have some scripting but you are not limited as you can get QlikView code mappings. The goal is to centralize your code and generate from that place. After we will add a publish to Qv Server that will update all the modified Qv stuff.
    – The connect problem, 32 and 64 bit connections has been fixed in the new release already in production. The ODBC connections are also implemented yet.
    – If you have any sample of scripts you use to KEEP or exist we will implement it it’s already planned to have a load prefix. You can also modify the scripts and keep your mods using the “Do not regenerate script option”.
    – You can test your time dimension script creating a script that includes the your related table

    QsGen a young product and is destined to evolve, and this will be done with the help of people like you who bring ideas and constructive criticism.

    We are not QlikView experts so if QlikView experts can help us make a nice product that will be all profit for everyone.
    John

  • 3
    March 30, 2011 - 10:09 | Permalink

    QlikView Visual Script Generator “Personal Edition” has been released

    This version is free and limited to a single user.

    To use “QlikView Personal Edition” with QsGen we developed a free GsGen Edition that allows a single user to develop scripts quickly without programming.

    Free QsGen Personal Edition is available on the website http://www.qlikview-script-generator.com with demonstration videos. Just register and you’ll get instant access.

    QsGen Team

  • 4
    Rob
    May 31, 2011 - 19:59 | Permalink

    Thank You – most helpful

  • 5
    GC
    October 5, 2011 - 11:58 | Permalink

    Y we r using diffrent tables to attach a particular table…
    Instead of dis we cn join whole data , and make single table…. May b dat works faster …..

    bcoz after loading whole data in memory , the whole data is our and do wat ever we want….

    Wat would u say?????

    • 6
      October 5, 2011 - 12:04 | Permalink

      I would say “Please work on your English”. I’m not usually one to point out spelling or grammar errors, but it’s very hard to understand what you mean.

  • 7
    GC
    October 5, 2011 - 12:41 | Permalink

    I apologize for using the shortcuts… 🙂
    I mean ,why not we work on a single table after fetching whole data in a memory??
    Actually ,I am the fresher and interested to build my career in Qlikview. That’s need some help.

  • 8
    April 16, 2012 - 13:29 | Permalink

    Hi Barry,
    QsGen and QlikView Visual Script Generator have been re branded to QVScriptor.
    The 2012 Version have a lot of enhancements in comparison with the first version that you tested.
    We encourage you to have a look to the new version that have drastically changed with the new ETL Mappings, a Drag & Drop script generator. Also some basic reverse engineering is available and a nice feature called snippets, that help you to insert and share some scripts parts.
    Have a nice day, John

  • 9
    May 31, 2012 - 17:50 | Permalink

    We are pleased to announce you QVScriptor 2012

    This new release contains a lot of new features like process designer, QVD Incremental Load,
    Reverse engineering etc.

    A 14 days full evaluation is available to download at http://www.qvscriptor.com

    We are also looking for partners in some remaining countries so do not hesitate to apply for.

    QvScriptor Team

    • 10
      Adolfo
      February 1, 2014 - 19:38 | Permalink

      That’s BS too. No download available anywhere.

  • 11
    Adolfo
    February 1, 2014 - 19:36 | Permalink

    Your download link is broken. Maybe you forgot to pay your hosting bill. Bummer!

    • 12
      February 6, 2014 - 18:16 | Permalink

      Hi Adolfo,

      You can now find the new re-branded QsGen (QVScriptor) at http://www.qvscriptor.com
      If you got any questions, please do not hesitate to drop us an email.

      Regards,
      Jason

  • 13
    beideohren
    April 2, 2014 - 17:25 | Permalink

    Your opinion about ETL with SQL,T-SQL, PL/SQL is ridiculous. In fact you have 10 times longer development times with so called ETL tools instead of getting your PL/SQL code generated out of the database. I need 3 seconds for getting an ETL job done with a Code generator and every job looks like the other, no typos, no problems with naming conventions, just other tables and columns. How fast are you with OWB, ODI, BODI, DataStage, Informatica and other crap? Have fun “drawing” your code!

    Cheers!

    • 14
      April 2, 2014 - 23:20 | Permalink

      Wow, did my DBA description hit a little too close to home for you? 🙂

      Anyway, when I am referring to PL/SQL etc., I am talking about the handwritten kind, not the generated kind. It is an analogy to the (very often) handwritten scripts in QlikView.

      In fact, I can very much appreciate DWH automation and strife to generate/automate things as much as possible. Especially using modeling methods which lend themselves well to being automated such as Data Vault.

      Barry

  • 15
    beideohren
    April 3, 2014 - 09:11 | Permalink

    No, cause I am not a DBA and for sure I don´t look like you described them. It´s just about ETL-tools. Why do people think those tools are helpful or better than writing/generating code? I will tell you: Because there code looks terrible. And there datamodels too.

    But I agree: You can script your application to death and you can do the same using tools. A fool with a tool is just a fool. And a fool with a programming language is just a fool as well.

    There are so many disadvantages of ETL tools but people like you never see them:
    – They are expensive, you need additional hardware/servers, installations, partner companies, “consultants” not even aware of simple SQL.
    – ETL tools actually never can´t be faster than the database, because in the end you will always need the database for writing your data into it. The opposite is true: Tools like Datastage using row by row dml like in cursors really suck. OWB generates thousands of lines of PL/SQL code, so why should one use it, when you can do the same with 100 lines.
    – You can´t generate jobs, like I can do with code generators. So you have to “draw” every job. Get your jobs done by 5 consultants and you will have 100 different types of jobs, unable to maintain.
    – Every transformation being more complicated than upper/lower becomes an obstacle, no use of analytic sql (window functions), slow lookups instead of left joins, too less functionality, transformations are not visible in one place, but they are shown in hundred places (like Transformers, boxes, windows) instead of using a database view and SQL to make your transformations fit on one screen. Have fun searching for an error!
    – Due to that: very long development times and cost, unstable and slow ETL.

    Summary: It´s more expensive, has less functionality than the database, takes more time to develop, is slower, is unable to maintain, because your developers never follow the rules, no generation, but manual development. I´ve used a lot of them: OWB, ODI, Datastage, SSIS, Pentaho, it´s all the same crap. Use it if you want to, but I will tell you: Generated PL/SQL is the only right way to do ETL!

    Cheers!

  • 16
    Sree
    June 26, 2015 - 18:05 | Permalink

    Hi Steve,

    The Download QlikView Script Generator is broken.

    Thanks
    Sree

  • 17
    Sree
    June 26, 2015 - 18:06 | Permalink

    Hi Steve,

    The LINK to Download QlikView Script Generator is broken.

    Thanks
    Sree

    • 18
      June 27, 2015 - 07:16 | Permalink

      Hi Sree,

      You can now find the new re-branded QsGen (QVScriptor) at http://www.qvscriptor.com
      If you got any questions, please do not hesitate to drop us an email.

      Regards,
      Jason

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    How to get started with QlikView

    Lately I have been getting a lot of inquiries by people asking me how to get started with the Personal...

    Close