I am a QlikView Ninja!
QlikView Ninjas can quickly slice and dice QVDs with exacting precision. How do I do this with the skill of a Ninja?
Well, I use my Ninja Sword of course! In this case, my blade of choice is a finely crafted weapon forged from the hands of the developers at EasyQlik. This weapon is called QViewer. In today’s post, we will go through some great use-cases for QViewer.
What is QViewer?
QViewer is developed by Dmitry Gudkov, a friend of LivingQlik, a blogger in his own right over at BI Review, and a developer of tools like QViewer and EasyMorph. Dmitry has been in BI for over a dozen years. He knows what he is doing. Dmitry was also featured in our Expert Tips roundup.
QViewer is a utility that allows you to rapidly examine QVDs. With QViewer, you can quickly open your QVDs outside of any Qlik product, access valuable lineage and metadata, filter (select) and search row-level content, and with the help of the free version of EasyMorph, even transform the contents of your QVDs.
We all know that QVDs are the building blocks, the foundation of our QlikView deployments. The ability to transparently inspect QVDs outside of QlikView saves a load of time, and simply provides clarity on the fields and values and the changes that might need to be made to them.
I thought it would be a good idea to show you how I utilize the tool with some specific use cases.
Data Lineage – What App created this QVD?
I typically get into situations where there tons of QVDs laying around all with different levels of transformation and business purpose. Sometimes it is difficult to know which QlikView application created a specific QVD.
Getting this information is easy with QViewer. Double click on the QVD to open and then select Metadata –> File Metadata (F4) to find out what app created the QVD. You can see the path of the qvw and then also the key data sources and SQL statements if applicable that were involved in the creation.
This window also instantly gives you the file size, field count and row count.
Metadata – Optimization Opportunities
Call me crazy, but finding optimization opportunities is one of my favorite Qlik past times. QViewer gives you some good techniques to find these opportunities. Double click a QVD to open in QViewer and then click Metadata –> Table Metadata (F5).
This view provides table level meta data. Specifically, the number of unique values, the uniqueness percentage, density, size in bytes and average bytes. Sort this by the size in bytes to see the fattest fields and then focus on the fields with a high uniqueness percentage.
Then choose a field and select Values (F2) to see all the values. The url field below was taking up 13mb of data in the application, but upon examination, it seems that the majority of the url is a fixed path with only the final page reference being unique. So I used the Generate Load Statement and used QlikView to subfield the url into 2 fields, one with the fixed url and one with the number. These two new fields can be easily put together once in the front end. In the meantime, I reduced that field from 13mb to 3mb. That is a huge savings.
The Table metadata view will also show you QVD field comments. You can actually add field comments metadata to your QVDs before you store them using the Comment command:
Then you can see the comments in QViewer:
Validation and Inspection
I find that when creating my ETL layer QVDs, it is time consuming to confirm (or validate) the transformations as I code them. It seems that only a small part of my time is spent putting in the code that transforms my data and I spend the majority of my time figuring out how to spit out the QVD with the change and get it loaded onto my screen to validate the change worked.
QViewer DEFINITELY is helpful in this regard.
Once you have loaded your QVD, I no longer need to create a separate throw-away QVW to load my QVD or otherwise adjust my script to retain data that I would normally drop. I just open it in QViewer. It loads incredibly fast.
In the example below, the users wanted a Customer Group field to be manually populated based on the Customer Number. If the customer number was numeric, we wanted the Group to be “Adhesives”. If the customer number was alpha, we would use “Coatings”.
I used the isnum function to separate the Customer Groups
Then instead of using QlikView to load the QVD and check my work, I just opened the QVD again in QViewer.
Firstly, it loaded over one million records in .17 seconds. The response times of QViewer are amazing.
Secondly, I was able to actually make selections on the Customer Num field to check my results. Simply right click on the field and select Fields. A listbox pops up, allowing you to make selections just like you would in QlikView.
This is one of the most impressive features of QViewer because not only can you make selections like you can in a QlikView dashboard, but you can search, filter on number or text, view the metadata for the field and also see the frequency of each value. You can actually make multiple selections to inspect the QVD like you would any QlikView application.
I selected a few items that were numbers and a few that were alpha. It looks like my transformation was correctly applied.
Transformations in QViewer with Help of EasyMorph
One of the advertised use-cases for QViewer is to link it with the free version of EasyMorph. <add link>. EasyMorph is a great stand-alone ETL tool that enables you to make transformations in your data sources including MS SQL Server, SQLite and generic ODBC. One of the data sources that EasyMorph can transform and write back to is QVD, of course!
I would imagine EasyMorph is a more useful feature for Tableau since that tool does not have an ETL layer or really any scripting ability at all. And indeed Dmitry confirmed to me that this was the main reason he developed the tool. As we all know, QlikView and Qlik Sense have ETL covered for the most part.
So just to conduct a test, I went ahead and did the same transformation with EasyMorph using my Customer Group example from above.
I opened my source QVD in QViewer and then clicked MorphIt.
I won’t go too deep into the use of EasyMorph, but I was able to pretty easily create the same transformation and create a step to Export the changed table back to Qlik QVD format.
To get back to QViewer, you have to reload the QVD.
Inspecting a Resident QlikView Table
Now this is a neat use case. There are many times when I would like to see the contents of a resident table in QlikView. I generally wish to see the table at a specific point in the script as I am coding a transformation in the resident table.
Usually in QlikView, I have to insert an exit script and then possibly drop some of the other tables that still exist to avoid creating complex synthetic keys. This is all time consuming and error prone because when my test is complete, I usually forget to undue the various code pieces I used that allowed me to test the table.
Using a subroutine <add link>, I can call the script to export any resident table into a QVD as the script runs. This allows the script to continue and also neatly deposits QVD files maybe even multiple files to a directory where I can then view them in QViewer.
Here is the subroutine. It should be placed towards the beginning of your QlikView script.
Then you simply call the sub wherever you want to create a QVD file.
When you execute the script, the QlikView script will run uninterrupted, and QViewer will open up in another window with the resident table you requested. Behind the scenes, we have actually told QlikView to create a QVD and then asked QViewer to open it.
QViewer is Super Fast. It will save you TIME
QViewer appeared to be incredibly quick at opening the QVDs on my drive. I decided to conduct a small experiment to test if QViewer is faster at opening a QVD than QlikView would be initiating a 100 row preview from the script.
For me, this time savings paid for QViewer in the first day of use.
To set up the experiment, I created a 100 million row QVD with random data (using the Ctrl+Q+Q trick). Please be careful when doing this because scalability tests can greatly impact the resources of your machine. You might want to keep your task manager open so you can watch the RAM being used while the QVD is being built.
I first opened the QVD from my file system with QlikView. I don’t have a system verified time for how long it took to get the preview, but I used a stopwatch and it took about 3 seconds to open the preview. It was not actually usable at that point because the splash screen stays on your screen for 10 seconds (it seems to me like this time has actually been lengthened lately). I will digress for a second.
Expert Tip:You CAN get rid of the QlikView splash screen forever! Go to Help –> About QlikView and then right click on the QlikView logo in the bottom left of the popup. You will find a series of advanced settings you can manipulate. Scroll down to SuppressSplash and set the Value to 1.
A more test more suitable to being able to explore all the data in the QVD would be to time how long it takes to create a script to load the QVD into QlikView, load it and then create a table of the QVD. This takes some manual clicking, but working on a plane with my fat fingers, this took about 3 minutes. The biggest issue with using QlikView for this is that the table in the dashboard takes time to render for 100 million rows and consumes quite a bit of RAM.
Then I tried the same experiment with QViewer. It indexed the file in exactly .60 seconds.
I was able to freely scroll through the data in the main window and open the list box pane on a highly unique field and apply a selection with no discernible delay.
Dmitry cautions me that the measurements coming out of QViewer are only reporting the indexing of the QVD. And also the newest version of QViewer will be a tad slower as the low-level processes used to load the binary data are changing to better handle larger QVDs. But in real terms, I was gaining value from the QVD within seconds rather than minutes from QlikVew.
This experiment made clear to me the real advantage of QViewer. Much of my frustration with QlikView in general is the “waiting around” whether that is for reload scripts to execute, objects to render or splash screens to disappear. Using QViewer allows me to reduce some of that pain specifically for loading QVDs and displaying their contents for validation purposes. For me, this time savings paid for QViewer in the first day of use.
Where to Get QViewer
You can start with the free version of QViewer. The limitation is that it will only load 100,000 records. As you and I know, even though that sounds like a lot of records, it really is not in the land of QVDs.
Go to EasyQlik now and pick up this great tool.