March 17, 2013

Better Lineage in QViewer and some other updates

Here is a brief overview of features recently added to EasyQlik QViewer -- my favorite QVD viewer :) If you're subscribed to QViewer newsletter then you've heard about some of them except the most recent.

Better Lineage

Since QlikView 11 all QVD files generated by version 11 apps contain SQL statements from loading script. This is quite handy when you need to recall what fields from database were used as source for a QVD.  It doesn't contain full snippets of script so it's not possible to see entire transformation logic, however this is handy when you only need to remind yourself what it was. Also QVDs contain connection strings, so that you can double-check if source database and schema are correct. Previously, Metadata window in QViewer showed only 1 line per Discriminator/Statement and it wasn't convenient for viewing SQL statements. Therefore starting from version 1.4.4 there is new additional multi-line window for viewing Discriminator/Statement records from QVD headers. See screenshot below (clickable).

Side note: while having SQL statements and connection strings is sometimes very useful for tracking lineage, it raises questions about security. QVD files themselves are not encrypted and password protected and thus insecure. But now they carry even more risks for security because now they may have connection strings in plain text which may contain unscrambled passwords to databases. And you don't even need QViewer to see them -- since they are stored in XML header of a QVD any text editor would work just fine.
UPDATE -- this bug has been fixed (see Credentials removed from QVD headers. Thanks to +Ralf Becher for pointing this out.
Notice in the screenshot the path and file name of QlikView application that created the QVD. Now, if it is located on the same computer at the specified path it will be shown as a hyperlink. If you click it it will open Windows Explorer with the application selected.

Better duplicates detection

My goal is to make QViewer useful for quick sanity checks of a QVD data. Checking key fields for duplicates is one of the most frequent tasks which is usually a bit cumbersome in QlikView -- you have to create a throwaway straight chart with counts every time when you need to find duplicates.
To make this task simpler, QViewer now allows sorting in listboxes. Therefore finding a duplicate requires only two clicks: first click to open a listbox, second click to sort counts (they're intentionally sorted in descending order first to show higher values on top). Duplicate values, if any, will appear on top of the list. Two clicks -- easy as that!

Wait a second, you can find duplicates even in one click. Press F5 to open Fields window and check Uniqueness of a field. If it's less than 100% -- there are duplicates in it. Special care is taken for the case when uniqueness is very close to 100% but not equal to 100%. In previous versions of QViewer it might show up as rounded to 100.00% which could be misleading. Now if even only one duplicate exists for a billion rows, uniqueness will still be shown as "<100.00%" (notice "less than" symbol). So you will never miss a duplicate.

Other news and updates

  • Added Generate Script button (can be seen on screenshot above) -- it will generate and put in clipboard LOAD statement for selected (checked) fields
  • New unlimited licenses available -- Team and Enterprise. Team License can be a good fit for QlikView partners and small departments, while Enterprise License allows the use for completely unlimited number of users in an organization
  • A routine for inspecting tables with a QVD viewer has been added to QlikView Components -- a popular QlikView scripting toolkit developed by Rob Wunderlich. One more reason to check it out.