1) Have A Design Plan
Before you write a single line of script or create your document's first visual object, decide whether your buttons will be text objects or button objects, whether they'll have solid colors, one-color gradients or two-color gradients. Whether they'll have rounded or square corners; shadows or none. Whether your objects will have borders or not, and if so, how thick and what color. Get all of this seemingly mundane minutia decided early and avoid tedious, time-consuming re-work later.
2) Avoid creating unnecessary variables.
It's easy to create a variable for just about any reason, but from what I've been able to discern, QlikView doesn't do the most efficient job of tracking what it is your variables refer to. As a result, every time something happens that causes a variable to update, they all get updated. This can impact performance.
That said, there are times when using a variable improves efficiency. For example, if you have an expression that determines a maximum value over your dataset and you use that expression everywhere, then consider assigning that expression to a variable and then replacing the individual expressions with the variable. For example, if you want to display the dates of the oldest and newest records in your dataset, don't create text objects with expressions like:
=min(rec_date)&' - '&max(rec_date)Sure, it'll work but it will waste time iterating over your dataset again and again. Instead, in your load script have:
Let vDateRange = min(rec_date)&' - '&max(rec_date)
and then display vDateRange in your text objects. Golden rule of QlikView (and software) development: IF YOU CAN DO SOMETHING ONCE AND USE IT MULTIPLE PLACES, THEN DO IT ONCE!
2a) Avoid unnecessary system service calls.
System service calls (functions that access the underlying operating system; eg, OSUser(), Now(), Today()) incur lots of overhead and, as I have observed, are prone to causing system exceptions which can lead to script failures. Minimize the need to call such functions, when possible, by calling them once (possibly as a Document "OnOpen" trigger) assigning their values to variables and then referencing the variables instead.
2b) Avoid date/time functions that poll.
Functions like Now(1) will set up a polling cycle within your document and refresh every second whether you want them to or not. If you're assigning their values to a variable, this can result in the variable being updated every second and that can cause a ripple effect throughout your document. Not a pretty sight. Avoid these functions if at all possible.
3) Delete unused expressions.
Ever create an expression to control the visibility of an object and later decide that you want the object to always be visible, so you go into the preferences and click on the Always display option, but don't bother to delete the (now disabled) expression? Truth be told, it doesn't hurt to keep those disabled expressions around, but if you use the Expression viewer regularly to track down variable references and such, those disabled expressions will continue to show up in the viewer, and you'll waste your time looking at them.
The same goes for the practice of "commenting out" bits of chart expressions. Once you've verified that your charts are performing as expected, remove the commented out junk.
4) Define Widely Used Custom Colors in Variables.
You create a document with a hundred buttons using default colors. Your client decides they'd rather have the buttons drawn in pastel shades of their logo's colors instead. So you go through the document and set the colors of each and every button. Unfortunately, the client's marketing department just released a new logo that employs 60's retro-psychedelic colors this year.
If you create color variables in your script and reference those variables in you object color definitions, then all you have to do is change the variable value. A great time saver, especially if you have buttons that use two-tone and/or state-changing color schemes.
5) Create a User Preferences Sheet.
This is a nice, neat way to organize user-specified preferences. I also use the Preferences sheet to display version and other static information about the document.
5a) Create a Debug Info Sheet.
Even if you design it so that only developers can see it, it will helpful in resolving deployment issues. I use one to display database connection parameters, report component versions, last reload time, first/last record dates, etc. I also use this sheet to perform and report on some rudimentary analysis of the state of the document; e.g. "Only 1 out of 3 data centers provided data during last refresh", "Last refresh took longer to complete than expected", etc. This sheet is the first place I go when someone calls me up asking if there's a problem with the report. More about this later.
6) Give Sheets Meaningful IDs.
Sheet IDs show up in the Expression Viewer and other places. It's much easier to relate an expression or variable back to the sheet in which it is used if the Sheet ID (configurable thru the Properties dialog) matches the name of the Sheet. NOTE: BLANKS IN SHEET IDS CAUSE SHEETS TO DISAPPEAR IN CHROME AND SAFARI!
7) Use Meaningful Captions Even When Not Displaying Them.
I use a lot of text objects in my QlikView documents, usually configured as custom buttons for things like dynamically selecting the trending period of a report. Even though I never display the Caption of these text object/buttons, looking these buttons up in a sheet's Objects list is much easier if you specify meaningful caption text.
8) Change Tab Color When Active.
It's a simple but nice effect to change a sheet's tab color to give a visual clue that it's been selected. Here's how.
9) Organize dimensional tables into high frequency and low frequency reloads.
Some dimensional tables have values that almost never change. I call these low frequency tables. If your documents refresh at a rather high rate; say every 10 minutes (yes, it can happen), there's no need to refresh these low frequency dimensional tables at this high rate. In this case, it's more efficient to either 1) create a separate document to load the low frequency tables and run that document's refresh task less often, or 2) have one document to reload all dimensional tables, but incorporate an external variable in that document to enable/disable reloading the low frequency stuff or 3) use QlikView's partial reload capabilities to control what gets reloaded and when.
10) If possible, summarize data during document refresh, instead of in your graphs.
Sometimes you encounter a dimensional table that contains data that only has value when summarized; ie the individual records have no meaning but a summary of their values does. It's easy to do this summation within a chart, however, it's a waste of CPU cycles to do it that way. Instead, reduce this table when you load it. If necessary, reduce it into a second table and then discard the original. Then your charts can reference already reduced data and save those CPU cycles.
11) Enable the XML-object generating.
The XML-object-generating feature exists so that you can insert XML representations of your document's objects/scripts into a repository. But even if you don't use a repository, this is a handy debugging feature as these XML objects can be opened with any text editor and comparing them to previous reversions is a great way to track down changes in behavior.
You can enable XML-object generation by creating a sub-directory under the directory in which your qvw exists, and give it the same name as your qvw plus a "-prj" suffix. For example, if your document is Foo.qvw, then create /Foo-prj. The existence of this sub-directory will cause QlikView to generate XML objects every time you hit the SAVE menu option.
12) Create a History tab inside your script.
Every script I create has a History tab where I record change/revision information. If nothing else, it provides a quick way to tell someone what you've been working on since day X. But the real value is to be able to quickly track a change (or changes) to a specific date and revision. I also keep revision information for each qvw in an external table and then load that table into my main document. This is a great way to quickly tell if all of the document's components are at the correct revision(s). Also, with a little more logic, you can force document reloads to abort should any component be at the wrong revision. More on this later.
13) Compartmentalize your document architecture.
Create separate scripts/qvw's to collect, transform/model and display your data. This will help you to isolate and solve problems more quickly.
14) Use linked objects instead of separate instances.
If you have the same object(s) on every sheet, create on object and link all the rest. This is a big time saver when modifying your UI because changing properties of one instance will affect all instances.
15) Organize and display your control objects by function.
Let's say your document provides the ability to choose dates, select filters, edit selections, etc.. on every sheet. You should create (or simulate) a container object to display all the date selection buttons together on one panel, all the filter selectors on another and so on. In short, don't display all of your control objects at once; this can lead to confusion and waste UI real estate.
16) Version and version-check your components.
My QlikView documents generally consist of multiple qvw's; one that aggregates historical data, one that models data, one that provides the UI, etc. In order to mitigate issues that can arise when modifying a qvw (and especially when updating a table schema), I have designed all of my qvw's to contain a version string that it stores in a common table in a "version.qvd" file. Any qvw that targets other qvws/qvds for import during reload, must first access the common table to check the versions of the target components and if the versions aren't compatible, the reload is aborted.
This helps avoid situations where you have a lot of data organized by one schema, and you import some new data with the same schema plus one new field and QlikView goes off into never never land trying to reconcile the two schema. It also help avoid deployment issues in production where someone (cough..Tech Ops..cough) missed updating a component.
17) Create debug objects.
I add objects to all of my documents that help me debug problems when they arise. For example, I have a "Master Reset" button on my dashboards that reset all variables to their initial state.
To keep these objects from interfering with my client's enjoyment of the document, I set their layout parameters so that they only appear if I'm the person viewing the document.
You can even have an entire sheet dedicated to displaying things like last refresh time, min/max record age, etc and configure that sheet to only appear for you. I have such a sheet on each of my documents and it's always the first thing I check when someone reports a problem. In addition to refresh time and record age, my debug sheets display messages in big red letters for conditions such as: "Not all data centers are reporting", "Document not reloading as often as it should", "Incompatible component versions".
No comments:
Post a Comment