Category Archives: Tools

CubeTester

When working on Qlik Sense performance issues I frequently find I want to measure the performance of specific expressions. I might want to know how variations of an expression may perform against each other.  In a slow chart with many measures I want calculation time individually for each measure to focus my efforts.  Or perhaps I’m just satisfying a general curiosity or trying to settle a bet.

You can measure the performance of expression variations by modifying the chart and measuring the overall chart response time with something like Chrome Add Sense or QS Document Analyzer.  That approach can get kind of clunky especially when you are focused on a subset of measures in the chart.

I prefer a more structured approach to testing expressions. The tool I reach for is CubeTester.

CubeTester is an open source Nodejs command line tool for testing the performance of Qlik HyperCubes (Dimensions and Measures).  The test specification is written in a json file as either a HyperCubeDef or the “simplified” Dimension/Measure syntax.

Here’s a sample test written in simplified syntax that tests three variations of a cube (chart) containing one Dimension and three Measures.

I’ll run  CubeTester specifying the file that holds this test:

node index.js test tests/columns.json

And receive this output:

There is no significant difference in performance between the variations. Importantly, I can also see that all three return identical  total values as well.

CubeTester supports two commands:

  • test : Run tests.
  • extract: Extract app charts into a test file.

There are a number of options that can be specified on the command line or in the test definition. See the readme for more information on available options.

in addition to testing variations or trying out a theory, here are some other cases where I’ve used CubeTester.

  • When working with a mashup where my HyperCube exists only in code, there is no chart to test.
  • In a slow rendering chart I can test individual measures, combinations of measures and non-data expressions (like color expressions) to find the culprit.

Using CubeTester I can quickly try out ideas and document my progress as I work through an issue. I’ve made some interesting discoveries!

Some notes:

  • Testing against a server uses certificates for authentication.  (Pull request welcome if you want more auth options).
  • Make sure you specify “wss” when using a server endpoint eg
    wss://your.server:4747
  • You’ll need to test with enough data to get calculation times of sufficient magnitude.  Two results of 5 milliseconds vs 7 milliseconds are not precise enough to draw conclusions from.
  • Calculation time is affected by the capacity of the target machine and what else is running.  I recommend to repeat tests until you see a stable pattern.  Use the –repeat option and take the lowest result from each repeat.

CubeTester is free to use. Have fun!

-Rob

 

Share

Data Browser Tricks

Summary: I demonstrate my latest “Data Browser” sheet for use in Qlik data modeling. Download here.

Today I want to share the latest version of my “Data Browser” sheet.

What’s a “Data Browser”? It’s my name for something you may already have. The ubiquitous “System Sheet” or sometimes “Profiler” where you may have listboxes or charts utilizing the system fields “$Field”, “$Table”, “$Rows”.  These fields are part of the shadow data model that hold useful metadata about the data loaded in your app.

Some very useful example profile sheets have been shared in the community over the years.

You can download the latest QlikView and Qlik Sense versions of my Data Browser here.

For QV, I just copy the objects into my new app in one go. For QS,  I either copy the charts one-by-one or if I’m lucky they are already in my template (who’s going to build the sheet copy extension?) .

I’ll do this walk through with the QV version, the QS version is similar.

When I make selections in $Field I can see values and frequency counts in a listbox. After selecting for example the “Sales” field, the values and frequency counts are shown in a listbox. I also have a  histogram and some descriptive statistics about the field values.

I can select field values and drill into data using  this sheet or my application sheets. For example, I might want to select the outlier high Sales value, then select Product in $Field to find out what Product is associated with this Sale, clear the Sales field to see what other Sales look like for this product and so on.

There are several properties such as Null Count (Information Density), field alpha/num content ($tags) that I can get from the built in Table Model Viewer.  The Viewer requires me to examine one field at a time. In the chart below I get an overview of those properties of interest.  Because it’s a chart, I can use sorting and selecting to focus.

Something I don’t get from the Table Viewer is the difference between numeric count and text count for a field.  I like to surface this problem (highlighted in yellow) early in my modeling.

One of my favorite features is the “Value Association” chart below. It’s likely a favorite because it took me a long time to work out the expression!

In  Table Viewer we use “Subset Ratio” to understand where we have connected and un-connected data in our model.  Subset Ratio is limited to reporting the relationship between key fields. It can’t tell me how data field  values in this table associate to data field values in other tables.  Subset ratio, like other stats in the Viewer, does not respect selections. For example, if I select a specific Customer, how many SalesReps are linked to the Customer?

The chart below (highlights added) covers all these use cases and can also surface problems in the model.

I’ll start by selecting a field central to my model, in this case “OrderID”.   I then sort by the “Pct” column.  This column represents the percentage of field values associated to the selected field,
“OrderID”.

In the green highlight, I’ve called out a Table “Sales” that has zero association with Orders. Something to look into.

In the orange callout, I can see that only 19% of my Employees are associated with Orders.  That might be a candidate for trimming the Employees table when loading.

In the red callout, I see something puzzling. Only 47% of my OrderDates are linked to Orders. That may be ok, I would need to review the data.  What looks not ok is that WeekDay Pct is also 47%. I would have expected something like 5/7 or 6/7. No fraction of weekdays would equal “47%”.  And there are 1092 values for WeekDay…something is off.

I’ll select WeekDay from the chart and examine the values in the listbox. Aha, the Weekday values were created incorrectly.  They were created with a Date() function instead of the correct WeekDay() function.

I used to hide the sheet before production but now I generally leave it in as I and others find it useful in production as well.

Besides the Table Viewer, another Qlik modeling tool I really like is Catwalk. If you haven’t used Catwalk I encourage you to check it out.  I won’t go in to explaining Catwalk as it has an excellent tutorial built in.

Do you have any favorite profiling or modeling tools to use with Qlik?

-Rob

Share

Form Input and Commenting in Qlik

A common desire from Qlik customers over the years has been the ability to interactively edit data on a QV sheet and persist the changes in Qlik or maybe write back to another system. As Martin Mahler of VizLib describes it “turning a one-way data street into a two-way street”.

Sometimes the term “writeback” is used to describe the idea of inputting data in a Qlik app and persisting that data — writing back  the data — to some other system.  The new data is saved and shared with other users of the app. Importantly,  in a form/writeback application, the added data is associated with some row of the data model. For example, in a warranty claims app we may have a dropdown that allows the user to categorize each claim.  The assigned category is used in further analysis.

I think of “commenting” as making annotations on a chart, or chart data point, and a given set of selections.  Ideally, comments may turn into a discussion and use some sort of notification mechanism.

These are not pure terms. There are overlaps to be sure.

I’ve seen some interesting bespoke implementations by partners.  There is also a growing list of off the shelf products that enable writeback and or commentary within Qlik Sense or QlikView.

Some products are Qlik Sense only, some work with both QlikView and Qlik Sense.

Most products allow you to create table sheet objects that mix Qlik DImensions and Expressions with additional input fields such as freeform text, dropdowns or checkboxes.  They all persist the data to some type of backend store such as a database.

When evaluating a product for your requirements, here are some items to consider:

  • Do you have requirements for read-only and update users?
  • Are you looking to add additional data in a single chart or do you need to reference the new data from multiple charts?
  • Are you looking to add one to one new data or build complex workflow apps?
  • Does your business objective require structured form data,  free-form commentary or both?

There are an interesting range of products and capabilities out there.  Klikins and Emark Forms for example let you add new fields to a straight table.  One of the more interesting approaches is K4 Analytics, which embeds Excel into Qlik. This provides the full range of Excel formula and formatting functions. You can build some pretty powerful aps this way.

There are products that focus on finance reports. TrueChart creates a set of functional and beautiful IBCS compliant reports along with a nice navigation interface that can be reused throughout the Qlik app.

Both TrueChart and Climber Finance Report support the type of commenting and annotations user require in finance reporting.  I’m excited that the Climber commenting is being expanded and released as a generic commenting & collaboration product by VizLib. Qommentary is another global commenting solution.

Here in no particular order are some Writeback / Commenting products I’m aware of. The headings are links to the vendor site.

I’m sure I’ve missed some products.  Feel free to leave me a comment if you have something to share.  It would also be good to hear about use cases where you have found value in implementing an input/writeback solution.

Inform Write 

QlikView and Qlik Sense

K4 Analytics

QlikView and Qlik Sense

TrueChart

Qlik Sense

Emark Forms

Qlik Sense

Klikins

QlikView and Qlik Sense

Pomerol Writeback

Qlik Sense

Qommentary

Qlik Sense

VizLib 

Qlik Sense

Komment

Qlik Sense

Share

Qlik Sense Document Analyzer V1.5

Version 1.5 of the Qlik Sense Document Analyzer (QSDA) tool is now available for download here.

If you are not familiar with QSDA, it’s a free application profiling tool for Qlik Sense that can help you identify items such as unused fields, poorly performing expressions and data model problems.

Here are the significant changes in V1.5:

The Installer allows editing of all install paths and installation will create a log file in the User’s temp directory.

The  Summary ribbon at top of each sheet provides for sheet navigation by clicking a cell.

 

Improved error handling in the connector. Certain types of errors, such as a chart calculation timeout, will not terminate the script. Error count is reported on Summary sheet and detailed error messages can be viewed on the “Extract Log” sheet.

Items that should be considered incomplete due to analysis errors will highlighted in yellow.  This highlighting is a work-in-progress as I discover new possible errors.

Field widths (Symbol width) are no longer estimated! Field sizes are obtained directly from the engine.  Due to this change, this version will report different values for field sizes than previous releases, generally +/- 10%.

The connector edit dialog has been improved.  Error messages now appear properly in their own window and a progress bar displays when the list of applications is fetching.

If you have general usage questions on QSDA, please use the comments section here or Qlik Community.

If you have a bug to report, use the issue tracker.

The only installation supported by the installer is QS Desktop. You can use QS Desktop to analyze applications on a server.  It is possible to manually install on a server by uploading the application (qvf), the qsda-ribbon extension and the connector directory if you have the need and want to give it a shot.  I do plan to create a supported server installer in the future.

Have fun!

-Rob

Share

Qlik Sense Document Analyzer v1.4

Version 1.4 of the Qlik Sense Document Analyzer (QSDA) tool is now available for download here.

If you are not familiar with QSDA, it’s a free application profiling tool for Qlik Sense that can help you identify items such as unused fields, poorly performing expressions and data model problems.

I’m pleased that Mike Steedle of Axis Group has joined me as co-developer of QSDA.   Mike contributes his many years of experience in profiling and tuning Qlik applications.

 

Here’s what’s new in version 1.4:

  1. Installer improvements. The correct directory for Connectors and Apps is automatically detected.
  2. New attribute “ObjectIsExtension”. Possible values are:
    • False – not an extension.
    • True – object is an extension but not a widget.
    • Widget – object is a widget.
    • Missing – object is an extension, but extension is not present on server.
  3. Unused Master Visualizations, and the Dimensions and Expressions therein, are now extracted and processed.
  4. DimensionLabel field added to “Dimensions” table.
  5. New table “Bookmarks”. Bookmarks are now extracted and linked to Fields.
  6. MB Constant changed from 1000*1000 to 1024*1024. This means these numbers now scaled in MB will have slightly smaller values than previous versions.
  7. Calc Time is now displayed in seconds instead of milliseconds.
  8. Dimensions and Expressions summary added to Objects sheet.
  9. Some analysis of the data model is performed, and the results and recommendations expressed as flag fields. New sheet “Flag Summary” will display an overview of found conditions.
  10. Color highlighting of detected problems.
  11. Reorganization of sheets and sheet layouts.
  12. Addition of a “Glossary” sheet provides descriptions for flags.

The installer currently only supports installation on Qlik Sense Desktop, although you can  analyze applications on Enterprise Server.

Improving the server analysis capabilities and possibly a server install will be a focus of the next update.

If you have general usage questions on QSDA, please use the comments section here or Qlik Community.

If you have a bug to report, use the issue tracker.

I hope you find QSDA useful.  I’m excited to see it maturting and pleased to have the help from Axis Group.

-Rob

 

 

Share

Theming the Qlik Sense Script Editor

Summary: In this post I show how to modify the style of the QS Script Editor window.

An astute reader — Johan Roelofsen — of my blog on bookmarklets noticed I had a bookmarklet named “QS Blackboard” and asked if this was used to change the window background color.  Excellent guess, spot on, but there’s more to it.

Changing only the background color can yield a poor result because the text begins to blend into the background.  I’ll want to change the text colors and possibly size as well to yield something like this:

Fortunately QS uses the CodeMirror (CM) editor which provides a theming capability.  Theming the CodeMirror window is done by loading a style sheet and then making an API call to use that style.

The instructions that follow will show how to implement your own custom theme in QS Desktop (I assume it will work in Enterprise as well, just haven’t tried it).

Note that we will not be modifying any of the installed Qlik software files!

  1. In the folder :
    C:\Users\userid\AppData\Local\Programs\Qlik\Sense\Client

    Create a new subfolder (below Client) named “cmtheme”.  This is the folder that will contain your CM style sheets.

  2. Download or build a theme-name.css file in the cmtheme directory.  You can try out various existing themes on this demo page here and download the corresponding css file from the CM repo here.

“Projector1” available here is a theme I’ve built for projecting. If you read the doc or look at the examples, you’ll see the pattern whereby you must define .cm-s-themename selectors in your CSS.

If you start with one of the CM themes, you’ll want to add selectors for QS script specific elements like function, table and field.

.cm-s-projector1   .cm-function {color: #c678dd;}
.cm-s-projector1   .cm-table {color: #d19a66;}
.cm-s-projector1   .cm-field {color: #d19a66;}

All that’s remaining is to  invoke your theme while in the editor.  I use this bookmarklet that first loads the css file and then calls SetOption on the CodeMirror editor instance:

javascript: (function () { 
 var theme = "Projector1";
 $('head').append('<link rel="stylesheet"  href="../resources/cmtheme/' + theme + '.css" type="text/css"/>');
 $('.CodeMirror')[0].CodeMirror.setOption("theme", theme);
 }());

It’s the same bookmarklet code for any theme, I just change the hard-coded theme variable.

To “unapply” your theme and return to QS native, just press F5 to refresh the browser.

Have fun.  And remember when you tinker with something like this the best approach is to not update the vendor (Qlik) software files and instead seek a non-intrusive approach.

-Rob

Hey Rob, I’m a Qlik Dev. This CSS + Javascript stuff looks like a foreign language to me.  How I can upskill to be able to leverage the Qlik Sense APIs including creating Mashups and Extensions?

Easy answer.  Take the  “Web Dev for Qlik Devs” course from Websy.  You can self-study online through the Websy Academy,  take an on-line instructor led course or have one of the Websy team — including Rob — deliver a course at your site to your entire team. 

 

 

Share

Running QS Regression Test in Batch

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

QS Document Analyzer 1.3 Supports Server Apps

I just released the V1.3 update to Qlik Sense Document Analyzer.  Two significant features in this release:

  • The ability to analyze applications on the Enterprise Server.  You still install and execute QS Document analyzer on the Desktop,  but you don’t need to move the application to the Desktop.
  • The option to specify a Bookmark that should be applied before calculating objects.  This is particularly useful when an object has a calculation condition.

Another interesting update is that we are now validating all expressions and will flag any expression that has a syntax error.

You can download the latest version from the tools section of this site.

Thanks to Axis Group for their input on this release.  I’m looking forward to an ongoing collaboration.

You can report any issues with QS Document Analyzer in the issue tracker here.

Share

Exploring Data Lineage with NodeGraph

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

QViewer Acquisition

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