Scripting for the long haul


Scripting for the long haul

Mike Steedle
September 22, 2016
Data Model, Load Script, Qlik Sense, QlikView
1

You know how Qlik recommends creating custom properties to ease administration of security rules in Qlik Sense? In a conceptually similar way, I am realizing the wisdom of creating flags to encompass business rules in the transform layer, whereas I might have otherwise done it differently before.

For instance, at one customer, there are several business rules governing who should be included in HR Headcount — employee status, employee type, etc. We also have several versions of Headcount stored in QVDs aggregated at different levels. Instead of rewriting that business rule every time we re-crunch the Employee data, I added a binary “Include in Headcount” flag one step upstream that could easily be used in each load when we created the different Headcount aggregations. I’m sure the downstream reloads are faster with simpler WHERE clauses, too, ex. WHERE ExampleFlag = 1 instead of several arguments.

Conversely, a while ago, the same customer asked us to exclude a few Items from one UI application, which seemed like a one-off business rule. As time went on, they asked for those same Items to be excluded from other applications, too, and in each case we rewrote the business rule independently. Oops. Now, if that list of Items changes, we will have to change a lot of applications to absorb it.

We could have used an include file or created a Item list in a QVD to specify the excluded Items in each application, but in retrospect I think the best and cleanest approach, again, would have been to just create a binary “exclude” flag in the Item dimension table, to be referenced in each application where it is relevant. The flag could still be defined using an externalized Item list, of course, but with only one place to change it in the script, that additional layer of complexity may not even be worth the trouble.

Live and learn. Each project can be of higher quality than the previous. Consider the long-term implications of what you are doing, even if it seems like a bit of extra work, up front.

NB: Add a field comment explaining the exact business rule encapsulated in your flags, ex. COMMENT FIELD ExampleFlag WITH ‘A pretty sweet comment…’; That way any time you are loading from the QVD you can always see what is baked into that field using the QVD’s XML header without having to look back at the script where it was generated.

One comment on “Scripting for the long haul

  1. I think you are absolutely correct here, especially in that you don’t always know that the one-off business rule will be applied elsewhere.

    It is always best to build in the architecture as much as possible up-front. Not only does it lay the groundwork for these situations, but it represents an optimistic approach in that you believe Qlik will expand throughout the organization.

    There is always pain in getting this done early in project, but down the road, you look like a rock star because you saw the evolution.

    Good post.

Leave a Reply

Your email address will not be published. Required fields are marked *