QVDs are –without any doubts– one of the best parts in QlikView. They are small, super-fast and allow us to use data more efficiently. Most of the developers out there already have some sort of template for loading information from multiple sources and storing them in QVDs, but last week I found that metadata about each reload, table and data source could be useful. Here’s my story.
I have a customer that has 3 main data sources: Oracle, SQL Server and some XLS files. As usual, I created a layered model and everything was working perfectly. However, our extraction phase was not very consistent. It was executed 4 times per day, but the reload times were erratic (from 4 to 50 minutes).
Normally, we can assume that the reloads are slower in certain moments of the day depending on the network traffic, the workload on the transactional systems or database maintenance routines. But this was different, our reloads had great discrepancies even if you compared the same hours and days.
As you know, QlikView Server sometimes gives you valuable information like the duration of the tasks and you can always activate the QVW log to find out more data about the last reload. But we needed something more, so I created a small routine that stored the tables in QVD files but also kept track of the execution of each element.
When you open the QVD Generator, you can find useful information like the tables that were loaded, the numbers of fields / rows and the reload time for each one of them. Also, as the routine stores the metadata in QVD files, you can create new applications to analyze the average time for each table individually and compare different schedules in order to choose the most efficient one. Continue reading