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.
[ I know you can create an incremental load for these files, but I prefer to keep them separated. Even though is not very efficient, I find it more flexible. ]
The routine is easy to use and consists of five steps:
As always, you can download the files in order to use them in your QV environment or you can check the interesting part of the code directly here:
After analyzing the issue in hand, we found out that all the tables took the same time regardless of the day or hour… all but one… We realized that we had a “temperamental” Oracle view that needed to be optimized. [ We ended up creating a new table, but at least we identified the problem. ]
If you have any comments or recommendations, please leave them in the section below!
PD. The program I used to show you the content of the QVD file without opening QlikView is called EasyQlik Viewer. It’s one of my favorites!