Does Data Sort Order Impact Chart Calc Time?

Lately I’ve been digging into an old Qlik performance question.  How much impact, if any, does the order of Qlik data tables have on chart calc time?  My experience is that for a chart or aggr() cube with a lot of dimension values,  ordering of rows by dimension values can have a significant and measurable effect.

Mike Steedle of Axis Group blogged about the issue  a couple of years ago.  Mike’s post includes a useful subroutine to organize any table by a specific field.

I’ve added my own study and sample files on the topic in this QlikCommunity post.

Mike and I are are working together on the next update to Qlik Sense Document Analyzer.  Mike is keen on analyzing the data model and making useful recommendations.  One of the optimization questions we are studying is whether it is possible to make a solid recommendation on data table organization.

I’m curious to hear what others have discovered on the topic.  Do you have any rules you follow in ordering table rows?   Any thresholds or object/expression scenarios where you find it’s worth the trouble to manage the ordering?

-Rob

 

Share

Web Dev for Qlik Devs in Atlanta

Here’s an opportunity to fast track your Qlik team in using Qlik Sense APIs to create extensions, mashups, portal integrations and custom content pages that leverage data and visualizations from Qlik Sense.

In this four-day hands-on course you will learn:

  • The fundamentals of HTML/Javascript/CSS as they apply to QS Development and how to get started with popular frameworks and libraries including bootstrap, enigma.js and picasso.js.
  • Creating Visualization Extensions.
  • The differences and use cases for the various QS APIs e.g. Capability, Visualization, Engine.
  • Key QS API concepts such as the generic object model.
  • Connecting to the QIX engine to retrieve existing content or generate aggregations (hypercubes) on the fly.
  • Visualizing data using third party libraries.
  • Using Qlik javascript libraries picasso.js and enigma.js.

Students will come away with example code and completed exercises giving them the confidence to move ahead on their own.

No prior experience with web programming is required as the course will provide an intro to web dev technologies and how they are used in Qlik Sense Web Development.

Even if you don’t have a specific project in mind, I recommend taking this course to understand the power and potential of the QS APIs.  You’ll be surprised and inspired!

The instructor for this course is Rob Wunderlich, a well-known and respected Qlik Consultant and Trainer, Qlik Luminary and publisher of QlikCookbook.com. For more information on the course please contact rob@robwunderlich.com

Cost for the four days is $2600 and includes all course materials and lunch each day. Register at http://qlikviewcookbook.com/registerAtlanta/

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

Masters Summit Philadelphia

The 15th edition of the Masters Summit for Qlik will take place October 3-5 2018 at the Bellevue Hotel in Philadelphia PA .  Our goal in this three day hands on education event is to “Take your Qlik skills to the next level”  to make you more productive and increase the business value of your QlikView or Qlik Sense applications.

Through lecture,  hands on activities and takeaway code samples,  the Summit will expand your knowledge with advanced techniques and a deeper understanding of the core skills required in all Qlik application development:

  • Data Modeling
  • Advanced Scripting
  • Advanced Aggregation & Set Analysis
  • Visualization.

In addition to core topics, we’ll have 1/2 day workshops on performance tuning and an introduction to creating Qlik Sense mashups using APIs.  See the complete agenda here.

Our evening guest speakers, networking events and optional lunchtime lectures fill out the program with additional content and lively discussion.

Our panel of five presenters are well known as authors, educators,  Qlik experts and members of the Qlik Luminary and MVP programs.

In addition to our regular presenters, we’ve got some exciting evening guest speakers in Speros Kokenes and Paul Van Siclen.

Have you taken basic Qlik training and/or worked with the product for a while?  Do you find yourself struggling with data modeling questions such as slowly changing dimensions and rolling time analysis?  Syntax for aggregation questions like “what products do top salesreps in each region sell?”  When do I need “$()” and when do I not?  More self assessment to determine if the summit is right for you can be found here.

I hope you can join us in Philadelphia to take your Qlik skills to the next level.  The early bird registration discount is available until August 17.  Event details and online registration.

See you there!

-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

Loading Variables From Another QVW

I just read a good post by Kamal Kumar Sanguri on QlikCommunity.  Kamal’s post reminded me that managing variables in QlikView has always presented some challenges and over the years various techniques and code snippets have been shared to address those challenges.

Most folks quickly find that maintaining variables in external files  loaded with a script loop is a good approach and resolves common concerns regarding shareability, dollar sign escaping and so on.

Sometimes you encounter a need for an adhoc import or export of variables. Kamal’s post offers some useful code snippets for that.  Several years ago my colleague Barry Harmsen  wrote a post on QlikFix.com that shows some useful macros to manipulate Variables.  Barry and I  subsequently collaborated on a desktop utility that handles both import and export from a menu.  While the download link on the blog is dead, I’ve reposted the utility on the QlikViewCookbook Tools section for download.

Kamal said that there is no way to directly load variables from one QlikView document to another.  That got me to thinking.  It is possible, but gee, no one has ever asked for it.

What’s the use case?  When I want to do an adhoc copy, I use the desktop utility referenced above.  I have seen a number of customers who generate complex calendars into QVDs followed  by  generation of variable for use with the calendar.  Calendar QVD consumers incorporate the variable generation logic with an include file. It works.

Would it be any better if calendar consumers loaded the variables directly from the calendar generating QVW?  I think not because there is possibility of a mismatch between the QVW source and the Calendar QVD.

All that said, maybe you have a use case for loading variables from a QVW?  No one asked, but for the record here is the script to load variables directly from another QVW.

VariableDescription:
LOAD 
 Name,
 RawValue
FROM [..\..\data\StudentFile.qvw] 
(XmlSimple, Table is [DocumentSummary/VariableDescription])
Where IsConfig = 'false' and IsReserved = 'false' // Exclude system vars
// Any addtional filtering here
;

FOR idx=0 to NoOfRows('VariableDescription')-1
 LET vVarname = Peek('Name',$(idx),'VariableDescription');
 LET [$(vVarname)] = Peek('RawValue',$(idx),'VariableDescription');
NEXT idx

SET idx=;
SET vVarname=;
DROP Table VariableDescription;

Happy Scripting!

-Rob

 

 

Share

Expanding the Qlik Sense Editor Pane

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. 

EditNote 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

Qonnections 2018 Reflections

I just wrapped up the week at Qonnections 2018.  I found it to be an inspiring conference and the most positive I’ve attended in years.

What was inspiring?  The cognitive insights demo was fantastic.  This is the future of BI.  We have well understood principles and algorithms recently formalized in the discipline of machine learning. Let’s incorporate them in the tool.   If the goal of data visualization is to amplify cognition of data, then I would say cognitive insights is to visualization as visualization is to data.

On the technical front, the previews and demos of elastic cloud and Qlik Core provided me the “aha” moment to understand how the Qlik Big Data Index will be possible.  I can see how many distributed indexing and aggregation engines can provide massively scalable access to a data lake.

The most exciting announcement to me wasn’t technical, but on the commercial side.  For a “small uptick in maintenance”,  QlikView  users will be entitled to use the same license for Qlik Sense.  To me this signals a new strategy for dealing with the two products going forward.  There was also a roundtable discussion on feature gap between the two products and the announcement that some level of pixel-perfect control will be available in Qlik Sense.

Taken together with main stage comments from CEO Mike Capone and CTO Mike Potter,  I’m seeing a needed correction to the “two product strategy” which so heavily favored Qlik Sense.  No one expects QlikView to live forever,  but loyal QlikView customers want to adopt a new product only when they see that it clearly benefits them.   Ideally we will see a converging product that blends the best features and lessons learned from both products.

-Rob

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

Recipes for Qlikview Success