Recently I was working with QVD files in QlikSense and found that information online was available yet scattered around, so I’ve decided to put together a few blogs to bring some of the wealth of information together…
So, to start off at the beginning, what is a QVD and what’s the purpose?
QVD stands for QlikView Data, however these files may be utilized in both QlikView and QlikSense, and are compatible between platforms with some modifications to the connection / LIB strings. Compatibility between platforms is great for those who are looking to step up into QlikSense!
A QVD file is a buffering of tabular data, from one of the variety of sources Qlik can connect to. Principally speaking QVD files are where all the ETL happens, like semantic layers found in other reporting platforms. QVD’s may be generated from other QVD files, so a potential data architecture could involve sourcing the various tables into a QVD for each table, with subsequent model/s based on underlying QVD’s (see image below). This channel of thinking leads any developer to the path of building SQL views in the data warehouse and simply bringing the data into a single QVD in one hit, not a good idea and I will cover some of those limitations in a future blog.
By utilizing QVD files required production source system data can be brought across the network at off-peak times, and the great news is because the data then resides on the QlikSense server subsequent retrieval isn’t limited by the network. Equally having a replication of data on the reporting server ensures that queries are not hitting production systems during operating hours!
Obviously when replicating data over to another location size becomes a consideration, questions like: “why would be duplicate our data warehouse of 1TB to another server?” get thrown out there. Fortunately, Qlik have delivered an excellent compression ratio of up to 10:1, so it’s not all bad!
Within Qlik scripts we can implement logic to handle various ETL situations, like incremental loads, upserts, deletes, truncates and so forth. There’s definitely a lot of performance benefits available when utilizing the QVD strategy.
Keep an eye out for my future blogs on QVD’s and QlikSense architecture considerations.
Thomas – MacGyver of code
Thomas blogs about reporting platforms, data warehousing and the systems behind them.