… and points beyond

One of the most useful tricks shared at the QlikView conference was from Nik Boman on improving the data extraction from databases.

ODBC is a slow protocol, running orders of magnitude slower than the database or a typical Ethernet connection. Very pricey ETL tools for data warehousing get around this by extracting through multiple connections to the database, and there’s no reason that a QlikView infrastructure can’t take advantage of it.

For example, run two copies of QlikView at the same time and extract approximately half of the data set with each. First, make a copy of the QV.exe file and give it a unique name. You can open QV.exe and your unique copy at the same time. You can run three or more copies of QlikView with this method.

Next, decide how to divide your data set; it could be based on date, country, state, or half the alphabet, for example. What you want is to divide the data set into roughly equal segments, one for each copy of QlikView.

How does each copy of QlikView know which segment to load? One way to do this dynamically is to use the command-line to set a variable in the script. Reference this variable in the SQL SELECT statement in the script: WHERE YearField=$(vYearVariable). See the reference manual for command-line options.

Your mileage will vary. Some databases don’t do much better with simultaneous ODBC reads. Oracle does quite well.