Category Archives: Productivity

Productivity, Qlik Sense, QlikView

Understanding and Using Subset Ratio

8 Comments

Summary: If you are familiar with subset ratios in Qlik, you may not find much new in this post. But if you are new to Qlik or are unfamiliar with subset ratios in your data model, please read on. 

When loading data into Qlik and building a new data model, inspecting the Subset Ratio of key fields is an important exercise to ensure data quality.

Subset Ratio is displayed in the Preview Pane (Qlik Sense Data model viewer) or Table Viewer (QlikView) when a key field (field linking two or more tables) is selected.

After clicking a key field, in the Preview pane you will see three important numbers:

Total distinct values:  The count of all distinct values for this field (CustomerId) from all tables (Orders” and “Customers”) in the model.

Present distinct values:  The count of distinct values for this field (CustomerId) in the currently selected table (Customers).

Subset ratio: “Present distinct values” divided by  “Total Distinct Values”.  What percentage of total field values are represented in this table?  In this case, 100% of all CustomerId values in the data model are represented in the Customers table.  This is good. We will typically expect to see 100% Subset ratio in a dimension table.

Let’s take a look at the Subset ratio for the same field in our fact table — the Orders table,

It’s less than 100%.  Our Customer table is our “customer master” and represents all of our potential customers.  Our Orders table represents a limited time period, perhaps 12 months.  Only 44 distinct customers, or 22%,  are represented in the set of Orders we have loaded.

Less than %100 Subset ratio is a normal condition for a Fact table.  If we don’t want to include Customer data for those customers who have no orders, we can filter the Customer load with a “Where Exists(CustomerId)” clause.

So far we’ve seen “normal” subset ratios.  Let’s look at some exceptions.  What does it mean when the dimension table (Customers) has less than 100% subset ratio?

It means we have an order(s) that has no link to a Customer row.  That’s a data quality problem. In the example above we can see that we have 1 missing CustomerId (201 – 200).

Why do we have a missing Customer?  We would have to dig into the data to find out why.  It could be that we have loaded historical orders and “inactive” customers are archived from the Customers table.  It could be that we have some bad data due to a bug.  We have to analyze the data and decide on the best path to remediate.

By the way, what is the specific value(s) of the missing CustomerId?  A simple way to make this determination is to create a table chart with two columns — The key field and a field that has 100% density (every row has a value) from the Customers table.  I’ll use the CustomerName field. Sort the table by CustomerName and the key value in question will show at the bottom of the table with a null value for CustomerName.

What does it mean when the sum of the subset ratios for two tables equals 100%? It means there are no matching values between the two tables.   This can happen for instance when the keys come from two different systems that use slightly different nomenclature.  Perhaps in your ERP all ProductId values start with “P” but in the spreadsheet that someone provided for additional part info the “P” is excluded because none of the humans use the “P” when identifying parts.

Examining Subset ratio as you build up your data model is an important quality step.  Validating the quality of your data model will make the process of creating visualizations go much smoother.

-Rob

 

Share
Productivity, Qlik Sense, Tools

Running QS Regression Test in Batch

2 Comments

If you’ve ever seen my presentations on automated testing of Qlik apps, you know I’m a big fan of the free Qlik Scalability Tools  — both QV and QS.

I use the Regression Test feature to automatically validate Qlik app changes before promoting to production.   Regression testing allows us to validate that charts continue to produce correct results after updates are made to the application or platform software.

This graphic provides a brief overview of the regression testing concept.

The Qlik Sense Scalability Tool (QSST) provides a GUI workbench to run and analyze regression tests.  The workbench is a great tool for a developer or QA Analyst to validate an app on an adhoc basis.

What if I want to run the analysis unattended on a schedule?  For example, after every reload to validate that my app is still working correctly and my expected data was loaded.

In this post I’ll demonstrate using a system monitor to run the regression test as an ongoing validation and send an alert if the validation fails.

I won’t go into the details of setting up the Scalability Tool.  You can get that from the product documentation.  If you would like me to do a presentation on automated testing for your team contact me here.

We will need a system monitor that is capable of running commands on a schedule, monitoring the command output and sending alerts or status via something like email or Slack.  You may already have a capable system monitor installed at your site.

NodeGraph is my current favorite Qlik-centric testing tool for ongoing quality monitoring (I’m a partner).  NodeGraph’s Test module allows for testing expression values,  presence of fields and update times.  At the moment NodeGraph does not support running QSST Regression tests, but I expect it will be part of the product by 2018Q4.

For the time being, I’ll demonstrate batch regression testing using Woodstone’s Servers Alive, a low cost but powerful system monitor I’ve used in the past.  You can try Servers Alive for free.

Let’s assume we have used the QSST workbench to author the regression test scenario and have executed the scenario to create a  baseline log.  When using the workbench, we would re-execute the scenario at a later time to create a compare log and then analyze the differences between the logs using the workbench Analysis tool.

QSST provides command line versions of both the scenario executor (“SDKExerciserConsole.exe”) and the regression log analysis (“RegressionAnalyzerConsole.exe”) .  We will need to bit of script to stitch these two operations together and process the output.

I’ve posted a powershell script here that wraps all the necessary operations and exits with an ERRORLEVEL of “1” if validation fails (differences found between compare and baseline logs), or “0” if no differences are found.

  1. Download the RunRegressionTest.ps1 script and place it in a directory named “Regression”.
  2. Create a subdirectory under Regression named “baseline”.
  3. Copy your baseline log file for each app of interest to the baseline directory.   Do not rename the file.
  4. Following the comments in the file, update the first three variables in  RunRegressionTest.ps1 to identify the location of your QSST install and your scenario json file(s).

We can check that the script runs correctly from a powershell command prompt in the “Regression” directory.

.\RunRegressionTest.ps1 "ABC Sales Demo"

where “ABC Sales Demo” is the name of a json file in our scenario directory.  If all goes well, we should see output messages like:

PS C:\QlikSense-Projects\Regression> .\RunRegressionTest.ps1 "ABC Sales Demo"
 Comparing C:\QlikSense-Projects\Regression\temp\results\ABC Sales Demo_localhost_[1-0-1--1]_18072311263304_regression.log
 to baseline: C:\QlikSense-Projects\Regression\baseline\ABC Sales Demo_localhost_[1-0-1--1]_18072216105188_regression.log
 Differences found

Now to implement this in Servers Alive.  In Servers Alive we define things to test as “checks”.  In this case I’ll use the External(errorlevel) check type and provide the command string to run my powershell script.

The full command string is: (no line wrap)

powershell -NoProfile -ExecutionPolicy bypass -File "C:\QlikSense-Projects\Regression\RunRegressionTest.ps1"  "ABC Sales Demo"  "p2"

where “ABC Sales Demo” is the name of my scenario json file.  “p2” is a dummy parameter that works around a windows bug.  Without that extra parm, a blank space is added to the previous parm causing a failure.

On  the Alert tab I’ll specify to send a message to Slack on failure. On the schedule tab I’ll specify when to run this check.

All set up!  When run either manually or on schedule, a failed check — that is, the regression test found unexpected differences — the check will display red on the SA console and I’ll  also receive a Slack message.

 

 

 

 

I’ve just introduced a number of moving parts.  If this is all new to you what I hope you’ll take away is that formal, structured and automated monitoring of your Qlik Applications is possible with relatively low cost and effort.

Want some assistance with planning your testing strategy and implementation?  I offer consulting in planning and implementation of automated testing and monitoring for your Qlik environment. Contact me if you want to chat about your needs and how I can help.

I also typically do an optional lunchtime session on testing at the Masters Summit for Qlik.  Come along to Philadelphia or Johannesburg to talk testing and many many more Qlik topics.

-Rob

 

 

 

Share
Productivity, Qlik Sense, Scripting

Expanding the Qlik Sense Editor Pane

18 Comments

Summary: In this post I present a non-intrusive bookmarklet to hide the Sections pane in the Qlik Sense Script Editor to provide more real estate for typing script. 

Edit:  Note in the comments below that hiding the pane can be done out of the box using keyboard shortcuts such as Alt-F11.   That reminds me to read the doc! Nevertheless the concept of bookmarklets is useful and this post is bringing in some interesting contributions. 

I sometimes wish for a larger window in the Qlik Sense Script Editor where we type  statements.  This is especially true when I am projecting and I’ve zoomed my browser to make the text legible to the audience, or I’m saddled with a very low resolution.

The UI allows me to hide the Data Connections pane which provides  more space, but I’m still left with the Sections pane consuming 250 pixels on the left which may not serve me at the moment.

Wouldn’t it be great if I could hide/show the Sections pane on demand, like I can do with  Data Connections? Here’s a simple non-intrusive hack that will allow you to do just that.

Paste the following code in a bookmarklet. If you’re not familiar with bookmarklets, they are small bits of javascript code that can be executed from the bookmarks menu.  If you are using Chrome as I do, it’s as simple as pasting the javascript code in the URL property of a bookmark.  Google to learn more about bookmarklets.  If you are using a browser other than Chrome google to see how to create (if possible) bookmarklets in your browser.   Here’s the javascript:

javascript: (function () {
 var size = ($('.scripteditor-left').css("width") == "0px") ? "250px": "0px";
 $('.scripteditor-left').css("width", size);
 $('.scripteditor-stage').css("left", size);
 }());

Apply the bookmark and here is what the editor looks like now:

Apply the bookmark again and the Sections pane reappears.

 

This is an unsupported hack of Qlik Sense.  If something else does not work in the editor, you should refresh the browser — which will completely remove any effects of the bookmark — before suspecting or reporting that Qlik Sense has a defect.

This is  a very clean technique to add behavior because we have not modified any Qlik Sense files.

I file my QS bookmarklets in a Bookmarks Bar folder named “QS” which gives me easy access in a dropdown to mods I’ve created for Sense.

Here’s another bookmarklet I find useful.  This one opens the Qlik Sense script log folder for QS Desktop.  My userid in the path is hardcoded, you would of course update to the correct folder name for your machine.

file:///C:/Users/rob/Documents/Qlik/Sense/Log

Please share if you develop additional useful bookmarklets for Qlik Sense.

-Rob

 

Share
Productivity, Tools

Exploring Data Lineage with NodeGraph

6 Comments

In the Qlik world, we are frequently faced with questions like:

  • Where did this field come from?
  • What applications (if any) use this QVD?
  • If I change this database table, what applications will be affected?
  • Am I creating QVDs that are not being consumed?

I’ve maintained a QlikView lineage application for my customers over the years.  I was never completely happy with it as Qlik lineage metadata is inconsistent and the critically important field level lineage was never available.

All that changed when I discovered NodeGraph.  Now I’m a NodeGraph fan and partner.

NodeGraph is an add-on for your QlikView and Qlik Sense solutions that allows you to explore, visualize and trace where your data comes from, whether it’s up-to-date, how it’s calculated, and much more.

NodeGraph scans both QlikView and Qlik Sense files and produces a graph of applications and data in a beautiful easy to navigate interface.  Select any node, for example a dashboard QVW, and lines trace the data lineage through transformed QVDs all the way back to the source database.

 

You can search, filter and drill in any direction;  from applications,  QVDs, source tables, fields, charts or SQL queries.

Where did this field come from?  Right-Click any field and select the Field Explorer to get a report of the complete lineage for this field, including all script transformations!

 

I’m looking at a chart that shows a count of Customers.  How was that count calculated? Where did the data come from?  In the Content view I can review the expression and see the lineage for fields used.

 

One of my favorite features is the one-click application documentation.  The tool generates a pretty printable/PDF document suitable for Auditors that describes all data sources, transformations, and expressions used in the selected application.

 

There are more uses and features in NodeGraph than I can cover in a blog post, including governance reports, application usage, GDPR compliance and testing.

If you are coming to Qonnections,  see NodeGraph in action at the TechLab10 booth (#603) in the Discovery Expo.

Learn more about NodeGraph at nodegraph.se. If you have questions or would like to schedule a free demo,  reach out to the NodeGraph team on the website or info@nodegraph.se.  If you are in North America you can contact me directly for questions or to scheduling a demo.

-Rob

Share
General, Productivity, Tools

QViewer Acquisition

25 Comments

I’m pleased to announce that I’ve acquired the excellent QViewer tool from Dmitry Gudkov effective January 1.  I’ll be marketing, developing and supporting QViewer going forward.

QViewer has a stellar reputation as a must have tool for Qlik Developers.  I’m pleased to be taking over such a fine product.

This acquisition is one step in my plan to focus more on software development during 2018.  Stay tuned for other announcements during the year.

Existing licensed QViewer customers should email future support requests to support@panalyticsinc.com.

Happy New Year!

-Rob

Share
Productivity, QlikView, Tools

Mass Editing of QVW Script

8 Comments

Summary: In this post I describe a process using freely available tools to apply changes to all scripts in a set of QVW files.

So you have a lot of QVWs. And now you are asked to identify and make updates to all scripts to support changes such as:

  • A change in QVD or other file naming.
  • Changes in file paths due to a server move or directory restructuring.
  • Updating file paths to use variables.

If you are super cool, all those items are represented by variables, changes are handled by updating a single include file and you can relax and stop  reading this post. For the rest of us, read on.

You can scan and search all your script using my Script Repository tool.  That will allow to identify where changes are required, but do you have time to edit every QVW and make the changes?  Easy enough for a few, but what about when you have dozens?

QlikView Desktop has a useful facility we can leverage for mass changes; the “-prj” folder.  If a folder named qvwname-prj  (case sensitive) exists in the same folder as the QVW, when the QVW is saved, QV Desktop will write a set of text files representing the structure of the QVW to the -prj folder.  One of those files is “LoadScript.txt” which contains the load script.

When QV Desktop opens a file, it checks for the existence of a companion -prj.  If found, it populates the QVW with the contents of the files in the -prj.  If we change one of those files, for example “LoadScript.txt”,  that change will be inherited by the QVW.

Let’s walk thorough a scenario where we can utilize this feature to update the scripts of an entire set of QVWs.  I’ll utilize free tools that will make the process easier.

My sample problem is this: I have inconsistent QVD naming conventions. We’ve decided that “DimCustomer.qvd” shall henceforth be known simply as “Customer.qvd”. I’ll need to update the script that generates the QVD as well as all readers of the QVD.

I will accomplish this update in four steps:

  1. Create -prj folders for all QVWs.
  2. In the “LoadScript.txt” files replace “DimCustomer.qvd” with “Customer.qvd”.
  3. Rebuild the QVWs with the updated -prj.
  4. (Optional) Delete the -prj files.

The sample I’ll use for this post is relatively small to keep the demo simple.  But I’ve used this technique to process hundreds of QVWs at a time incorporating several different script edits.

I have a directory of QVWs that looks like this:

 

 

In the SubFolder “Loaders”, there are additional QVWs.

 

 

I’ll need a -prj folder for each QVW. I  can create the -prj manually, but this is where I can leverage the PrjTool to make life easier.  You can download the PrjTool from the Tools section of this site.  (Note: If you received a copy of PrjTool from the Masters Summit, please download this newer version as it contains important updates.)

PrjTool requires a Directory as input and the selection of one of  three functions:

  • BuildPrj: For all QVW files found in the specified Directory, create a -prj folder.  This includes opening and saving the QVW to populate the -prj.
  • CreateFromPrj: For all -prj folders found in the specified Directory, open and save the QVW to update the QVW with contents of the -prj.  If no QVW exists, a new one will be created.
  • DeletePrj: Delete all -prj folders found in the  specified Directory.

I’ll start by specifying the Directory that holds our QVWs and selecting the BuildPrj function.  Press the Execute button and the script will launch. The execution may take some time as each QVW has to be opened and saved. Good time to go for a coffee.

When the execution completes the log window will be filled with messages listing the -prj folders created by the tool.

 

If we examine the directory again we will see the new -prj folders.

 

Our next task is to edit the LoadScript.txt files. We can use any editor capable of searching and replacing across multiple files.  For this demo I will use the free NotePad++ editor.   From the NotePad++ menu, launch “Search” , “Find in File”.  In the search dialog I specify the Directory  and  the search and replace strings. I’ll also limit the search to the LoadScript.txt files.

 

After pressing “Find All”,  I’ll get a list of search results.  I can double click any of the results to open the file for further examination.

 

When I’m satisfied that I’m going to make the correct updates, I again launch “Find in Files” and press “Replace in Files”  to perform the update.

Now I’ll use the PrjTool again to update the QVWs with the updated -prj files.  I run the tool again, this time selecting the “CreateFromPrj” function.  Again, if you have a lot of large QVWs, be patient while the tool runs.  The resulting log messages will inform me of the updates.

We’re done!  All QVWs now contain the updated load script.  Optionally we can run the tool again with the “DeletePrj” function to delete the generated -prj folders.

You should always perform this kind of mass update activity on copies of QVWs and audit the results.  Also, never use -prj folders in production.  Server reloads do not recognize -prj folders.

-Rob

 

Share
Productivity, Qlik Sense, Visualization

Quick Sense App in a Page

14 Comments

Summary: Learn an easy method to deliver a Qlik Sense app as an embedded web page. Yes, the spelling of this post’s title is intentional 😉

I just came off a week of Qlik Sense API & Integration training with Nick Webster of Websy.  I’ve been nothing short of impressed with the integration and reuse options of Qlik Sense.

I was intrigued today by a question on Qlik Community asking if there was a way to disallow the application Edit mode in Qlik Sense Desktop.  The poster wanted his students to just “use” an app and not poke around in the design until a later time.  Kind of like the “distraction free” mode  in text editors.

The challenge I gave myself was how quickly could I knock out a solution to this problem?  I’ll detail my solution below and I’ll tell you it is taking me longer to write this blog post than it took to build the solution.

Rather than working at disabling or removing function I didn’t want, I approached the problem as including only what I wanted. My first thought was to create a mashup that represented the entire application.  Then I struck on an even easier approach — use the Qlik Sense “Single Integration API” with a bit of Bootstrap.   The Single Integration API does not require writing any code.  It’s just a URL that displays a single Visualization or complete Sheet.  You can create URLs and experiment with options in the Dev Hub Single Configuration Tool.

Bootstrap is a popular web design toolkit that makes it easier to create responsive and interactive web pages.  Thanks to Nick’s class I understood how Qlik utilizes bootstrap.

A basic URL to display a sheet looks like this:

http://server/single/?appid=myappid&sheet=sheetId&opt=options

Appid is a qvf filename on QS Desktop, or the GUID if using QS Server.  Options allow you to control  things like allowing selections.  A complete list is in the API doc.

With 50 lines of html I created a web page that delivers the full associative experience across seven sheets, including Current selections, Smart search and the Selections tool.

Here’s a screenshot showing my sheet navigation buttons across the top and the Sheet content, including Current selections, immediately below the buttons.

The html file is available for download here.  I used the “Sales Discovery” sample app but it is easy to adapt the file to any application.

There is a single iframe nested in a responsive Bootstrap container.  The buttons simply change the src attibute of the iframe to load a specific sheet .

If you want to try the file yourself you  only need to change the “var appid=” string to point to the app path on your machine.  No web server is required, just double click the html file to launch.  Your server or QS Desktop must be active.  Yes, this file works as is with QS Server.

Nick and I will be showcasing a variety of QS integrations at the Masters Summit for Qlik in Boston Oct 23-25.   Nick will also be presenting  a half day “Qlik Sense Integration” session at the summit that will teach you how to create a basic web page with bootstrap and explore more advanced QS integration options.

-Rob

 

Share
Productivity, Qlik Sense, Visualization

Vizlib: Innovation and Agility

13 Comments

Summary:  As a provider of Qlik Sense visualization extensions Vizlib promises to blend the innovation and agility of open source with the reliability of commercial software.. 

The pace of delivery for visualization in Qlik Sense has been disappointing to some.  Thanks to rich open APIs in QS, much of the slack has been taken up by the community in the form of open source visualizations as seen on branch.qlik.com.   There you can see some remarkable innovation and responsiveness to community requests.

Should you use open source visualizations?  An open source extension may provide just what you need in your project.  But you should consider the unsupported nature of open source and evaluate the risk and consequences of the visualization possibly failing at some point.

When I worked as  a Java developer  my team used the open source Eclipse IDE as our main tool.  We also used a dozen or more open source plugins.  As our plugin library grew, we found that testing and updating plugins between releases was taking an inordinate amount of time, sometimes making us afraid to upgrade the base Eclipse product.   We  turned to a vendor and purchased a bundled version of Eclipse with dozens of plugins tested, supported and verified. Problem solved.

Vizlib is a new company is promising to blend the innovation and agility of open source with the commercial support that many customers demand.  Vizlib is partnering with the best extension authors to produce a library of fully supported high function visualization extensions. Check out some of what they have published so far:

  • A Table object that delivers the functionality of  a QlikView table and much more.
    • Rich Formatting Options just like in Excel/QlikView
    • Conditional show & hide of columns
    • Dynamic Labels
    • Minicharts & Progress Bars

  • An Advanced Text Object that provides the full functionality of the classic QlikView text object  (including actions!) plus additional functionalities like HTML code and icons. 

To date Vizlib has released five visualizations with more on the way. Check out what’s available on the Vizlib download page.  A free trial license is offered that allows you to try any of the extensions in your own installation.  Take a look!

-Rob

Share
Productivity

Web Development for Qlik Developers

9 Comments

I just finished the four day “Web Dev for Qlik Dev” course with Nick Webster of Websy.io.  I rate the course Excellent!

The course focus is to equip  Qlik Developers with a basic understanding of how to use Web Technologies with the Qlik Sense APIs.  The week starts with an introduction to web technologies — HTML, CSS & Javascript.  And while I have some older experience as a web developer, I appreciated the brief review of current standards and practices.

We then moved on to using the Capability APIs for visualization in a mashup. We spent the last two days focusing on the lower level Engine API and the associated enabling technologies such as JSON and Enigma.js.

Lot’s of hands on work through well constructed exercises. Nick offered a lot of practical direction and tips.

I highly recommend the course to anyone who is considering or exploring integrating  Qlik Sense content into existing web apps or other mashup forms.

-Rob

Share
Productivity, Qlik Sense, QlikView, Scripting, Tools

Storing a Data Model in a Single QVD

18 Comments

Have you ever thought it might be interesting to store a  Qlik data model into a single QVD?  This can be useful in a number of cases such as:

  • Archiving (and retrieving) data models.
  • Overcoming the “single binary load” restriction.

QlikView Components (QVC) Version 11 introduced two new routines to do just that:

Qvc.ExportModel — Exports all tables of the current model into a single QVD.

Qvc.ImportModel — Import a data model created by Qvc.ExportModel.

Even if you don’t have QVC V11 installed, you can try Qvc.ExportModel right now using  http include.  Add these lines to any QlikView script (instructions for Qlik Sense further on down in this post).

$(Must_Include=https://github.com/RobWunderlich/Qlikview-Components/releases/download/v11.1/Qvc.qvs);
 CALL Qvc.ExportModel
 CALL Qvc.Cleanup

Mind the wrap. The Must_Include should be on one line. Using QVC requires the Qvc.qvs library be included (usually at the beginning of script), CALLing Qvc routines, and CALLing a Cleanup routine at the end of your script.

Assuming this script is included in “Sales Dash.qvw”,  the default exported model QVD will be named “Sales Dash.qvd” in the same directory.

 

Now, to import this QVD model into another qvw, replace the CALL to ExportModel in the above sample with:

CALL Qvc.ImportModel('Sales Dash.qvd')

The original model will be reconstructed as individual tables.

Qvc.ExportModel has three optional parameters:

CALL Qvc.ExportModel(['qvddir'],['qvdname'],['addTimestamp']);
Parameter Number Parameter Description
1 String. Optional. Relative or absolute directory where the model QVD will be stored. If relative, it follows the same rules as the STORE script statement for relative directory.
2 String, Optional. Name for the model QVD. If omitted, the name of the QVW will be used. For example, if QVW is “Sales.qvw”, then QVD will be “Sales.qvd”.
3 String, Optional. 1/0 True/False. If True, a timestamp of the form _YYYYMMDDhhmmss will be appended to the QVD name. Default if omitted is False.

 

Qlik Sense has no default path  so parameter #1, a lib:// for the QVD should be specified.  Alternatively, if a lib has been established with a DIRECTORY statement, parameter 1 can be omitted.

Qlik Sense will require a web file Connection for the http Must_Include.

webfile-connection

After defining the web connection and having an appropriate folder connection to store the QVD in,  Qlik Sense script would look like this:

$(Must_Include=lib://QvcWeb);
 Call Qvc.ExportModel('lib://QVData')
 CALL Qvc.Cleanup;

 

That’s all there is to it!  If you are already using QVC, I hope you’ll find these routines a welcome addition to the library.  If you are new to QC, explore more at QlikviewComponents.org.

-Rob

Thanks to Jörgen Peerik for raising the single-QVD export idea during a QVC class. 

 

Share