QVD this and QVD that

    During the course of working with customers questions always come up on QVD's. For some it is already an inherent part of their lives. They wake up in the morning, enjoy their caffeine, head to work and bang out some QVD's or read from some QVD's.

     

    In Qlik Dork terms a QVD stands for a "Qlik VERY compressed Data file". Notice my focus on the word VERY. Because to me that it is one of the most resounding things that new Qlikkies need to begin understanding. There is something magical that happens when you take only the unique values and you use bit pointers to map to those fields. You can take massive amounts of data and you end up with a VERY compressed set of values. That means a lot less memory needed than you think and a lot less space on disk than you think.

     

    I know, I know ... you need to see it to believe it. Ok in this first of 3 videos that are part of this post I take 500,000 patient demographic records and show you the size before saving to a QVD and after.

     

    COMPRESSION

     

    From 173 MB to just 6 MB. That's an insane compression amount. Hence why I like to think of the V as "very compressed".

     

    Are you ready to try it for yourself and create a QVD? Hope so because that's what this post is about. In this second video I have some data in a SQL Server database that I want to read into Qlik Sense and start the process of building QVD's. In this video we are going to simply create what is usually referred to a Layer 1 set of QVD files. Meaning "raw data from our source system." No ups. No extras. Nothing fancy. Just read the data and store it. Not sure how to create a library where they can be stored? That's Ok, I walk you through that process as well in the video.

     

    QVD STORE

     

    We are making progress but let's face it Healthcare isn't quite as simple as loading raw data from a single source. Rats. We almost certainly need to take some transformative steps to support interoperability. Whether we are trying to navigate data from multiple hospitals if we are a health system, we are trying to navigate data from multiple EHR's or we are trying to navigate data from our EHR and some external set of data. Transformation. Masking. Merging. Whatever the term you use in your environment it's MANDATORY. Don't worry that's where Qlik really excels. For raw data I used the term Layer 1. In this next video I demonstrate loading data from 2 different Patient Demographic sources and bring the data together. In the example the filenames are "Hospital 1" and "Hospital 2" but the names are simply placeholders. Consider it whatever you wish for your environment. It could be "Old data from our previous EHR" and "Data from our new EHR." The concept of the video is that we need to make a modification to the format of the Patient ID field and we need to change some values of fields.

     

    In my simple example I use a field like "Is this person a smoker." One system has "Yes and No" while the other has "True and False." We don't want our users to have deal with both. In fact the question I ask in he video is "why do I want my end users to deal with programming terms like "true or false" at all. Since Qlik has such tremendous compression by storing only unique values why not transform the field to something that is more user friendly like "Smoker and Non Smoker."

     

    MERGING QVD's

     

    Hopefully these 3 videos have helped you understand the basic concepts surrounding "QVD" files. The goal was to get you started. While also starting a conversation. Yes a conversation. Undoubtedly your situation requires something a bit more complex than "smoker status." Perhaps you are trying to merge values from different sets of lab data. Both sets of data have a name for the lab a value and a status like "Normal or Way above normal." What if both systems use different terms? How would you handle that? What if ... oh no ... both systems use different values based on region or whatever to determine the words that they use. For one Above normal is .52 and the other is .50?  Can you retain the raw values and the original text and then report a "Hospital Status" field where you apply your definition of what is above normal across all your EHR's, Hospitals, Sourcces?" Sure you can. You just have to come up with the naming convention that would make it clear down the road which was the "external" definition and which is your internal definition.

     

    Now here is where the conversation comes in play ...

    1. What situations have you had to use Qlik's ETL capability to resolve and how did you do it?

    2. How do you handle your QVD layering?

    3. Do you break out your QVD's into different libraries using some scheme?