If you think coding complex Set Analysis expressions and writing repetitive script is an efficient use of your QV development time, then this post is not for you. Go back to memorizing pi or whatever it was you were working on. For the rest of us, read on.
Qlikview Components is a set of Qlikview scripting subroutines. I started this project to address two problems.
As a Consultant, I’ve written and left behind QV script for many different customers. I frequently use script subroutines for common tasks to simplify implementation and maintenance. As Qlikview (and me) grows and matures, better scripting approaches and product features become available. What’s missing is an effective way to feed those improvements back in to the various versions of code I’ve spawned in the world.
Another problem is that the flexibility of scripting is also a curse. There are many ways to do the same thing. What’s the “best” way? Do we all need to learn that variable loop and load thing? Many script tasks are like plumbing in a building. They are not that interesting (my apologies to Plumbers) but it is essential that they work correctly. We want to get them done quickly and move on to the interesting bits.
How to address these problems? Enter “Qlikview Components” (Qvc), an open source (free) project. Here’s the mission statement from the project home page:
- Implement scripting best practices encapsulated by a simple and stable API.
- Improve the speed and quality of script development.
- Create common ground between script developers.
- The broadest possible input for design, validation and improvement of code. The project is hosted on google-code and allows for multiple developers around the world to contribute to the project.
- A stable API. We’re committed to continuous improvement and compatibility.
- A unit testing regime that ensures quality.
- All the supporting pieces one would expect in a serious project — documentation, examples, localization, issue tracking, discussion groups, formal testing and build systems.
I received several questions regarding my post Monitoring the Reload Schedule. Surprisingly the questions were not about the monitoring solution, but rather using Alerts. I’ll summarize the questions and answers here.
Can I loop through field values in an Alert?
Not directly. But you can create multiple alerts that use either bookmarks or set analysis in the Condition to handle a known set of field values. For example create one alert for Region=US and a second for Region=Europe and each sends an email to the associated Regional Manager.
Can I use an alert to always send an email?
Yes. Just set the Condition to “-1” (without quotes). This is always true and can be used to send text “mini-reports” to your email recipients. Your email text might be something simple like:
=’Currently open tickets: ‘ & sum(OpenTicketCounter)
Or something more complex like:
=’YTD Sales are ‘
Quantity * Price
YTD Orders are ‘
This will generate an email body that looks like:
YTD Sales are $12,014,788
YTD Orders are 167,580
You can build up complex expressions in a Text Object to get them correct and then paste to the Alert.
Can I include a chart image in an alert email?
No. If you know of a method, please leave a comment.
It may be helpful to include a url in the email that opens the Qlikview document for further analysis.
If you have Alert questions or tips, please leave a comment.
Monitoring successful operation is an important aspect of any IT system. What kind of monitoring is useful for Qlikview? I’ve found the following monitoring important in a Qlikview Server installation:
- Notification of individual reload failures. This capability, sending an email on reload failure, is included in all editions of Qlikview Server.
- Status of Qlikview services. Most shops have a network monitor such as Servers Alive to monitor and send alerts about status of Windows services.
- A particularly effective style of monitoring is “goal” monitoring. That is, instead of monitoring the resources required to achieve a goal, monitor the goal itself. In Qlikview terms, this means confirming that a set of Qlikview documents has been updated as scheduled.
In this post I’ll look at using a Qlikview document to monitor the filetimes of Qlikview Server documents. An “old” filetime is an indication that a reload or distribution has been missed. An email notification will be sent when we are off schedule.The mail body will look something like this:
4 documents overdue: Expenses_Joe.qvw, Expenses_Rob.qvw, Expenses_Sally.qvw, fieldIndex.qvw
The code used in this post can be downloaded at File Age Monitor. Download and extract the three files:
FileAgeMonitor.qvw — monitoring document.
FileAgeMonitor_Rules.txt — Filename masks and maximum expected age.
FileAgeMonitor_Email.txt — Address(s) to send alert about overdue documents.
To use FileAgeMonitor in your shop you’ll need to make the following changes:
- On the “Configuration” script tab, specify the directories you want to scan for qvw files.
- Modify FileAgeMonitor_Rules.txt to specify rules meaningful to your installation, Instructions are in the file.
- Modify FileAgeMonitor_Email.txt for your email address. Instructions are in the file.
FileAgeMonitor_Rules.txt consists of two fields:
– a filename mask (Key)
– a maximum allowable age in hours (MaxAge).
Lines beginning with “#” are comments. Example:
# Rules file for FileAgeMonitor.qvw. First match wins.
# “Filename Mask”, “Max allowable age in hours”
# All of the Expenses* docs should be no more than 25 hours old
#Films can be 90 days old
# Everything else – catch all default – 7 days
The first field, “Key” is a filename mask that may use the standard Qlikview wild card characters of “*” to match any number of characters and “?” to match a single character.
The second field, “MaxAge”, is the threshold age at which a file is considered “overdue”. MaxAge may contain any expression that evaluates to a numeric value. The value is hours.
The last entry in the Rules files, “*” will match all files.
The script builds a list of qvw files and matches each filename against the entries in the Rules file. The first match wins. The age of each file is tested against it’s matching rule and the flag field “Is Overdue?” is set to Y or N. The flag field is defined as a dual:
if(FileAge > MaxAge, dual(‘Y’,1), dual(‘N’,0) )
as “Is Overdue?”.
Y has the dual value 1, N the value 0. This allows the flag to be summed.
So now we have a chart that displays what’s overdue, but how about automatic notifications? For that we’ll use a Document Alert. Alerts are created from the Tools menu. Here’s the alert defined used to send the email. Refer to the notes that follow the picture.
1. Before the condition is evaluated, apply the bookmark that selects “Is Overdue?”=Y.
2. The alert condition is specified as:
=sum([Is Overdue?]) > 0
Recall that “Is Overdue?” was defined as a dual so it may be summed. If there are any overdue documents, the condition will be true and the alert will “fire”.
3. Both the Mail subject and mail body contain the count of overdue documents. The body contains the document names as well. The body expression is:
=sum([Is Overdue?]) & ‘ documents overdue: ‘ & concat(FileName, ‘, ‘)
4. The email will be sent to the addresses that were loaded into field “AlertTo”. The script loaded this field from the file FileAgeMonitor_Email.txt.
5. Batch Mode limits this alert to server based reload only. If Interactive were checked, the alert may fire when we are reloading during Development.
6. The Alert will be tested at the end of each reload.
7. The trigger level is set to “Message Changes”. This means a new email will be sent only when the count of overdue documents changes. So we will not get an hourly email telling us that “10 docs are overdue”, but will receive a new email if the next reload produces an overdue count of 8 or 12 — something different than 10.
FileAgeMonitor itself needs to be scheduled to reload periodically. I schedule it to run towards the end of interval reload cycles. For example, if there are hourly reloads at the top of the hour, I schedule FileAgeMonitor at 45 minutes after the hour.
If FileAgeMonitor relies on Server scheduling, and the entire scheduling process fails, how will FileAgeMonitor be able to tell us that reloads are not running? This is the “monitoring the monitor” problem that inevitably occurs with system monitoring. I address this issue by monitoring the Filetime of FileAgeMonitor.qvw using an external monitor like Servers Alive.
Qlikview reloading can get “off schedule” for any number of reasons; Database errors, Administrator errors. bugs in the scheduling software. It’s important for the Administrator to know of exceptions and their scope as quickly as possible.
Wouldn’t it be nice to know what fields are “unreferenced” in your document? By unreferenced, I mean fields that are not used in Expressions, Dimensions or Keys. These are fields that if removed from your document, would have no impact on the visible elements of the application.
Removing unused fields is sometimes important in addressing performance issues and generally makes your application easier to maintain and understand.
The Expression Overview dialog is great for finding where a field is used, but what about the unused fields? I don’t know of any QV supplied tool that can identify unused fields, so I created one myself. My tool is a QVW named “DocumentAnalyzer” and it’s available for download from:
First off, let me make it clear that this tool is imperfect. It’s difficult to do a precise field usage analysis from “outside” of the product. I hope that this work of mine will encourage (goad?) Qliktech into writing a field usage analysis tool within the QV product. I’ll be happy if my work becomes obsolete.
I’ll explain the usage and limitations of DocumentAnalyzer as well identify some interesting code tidbits for anyone who may want to enhance or borrow from this app.
The app code itself consists of two pieces. 1) A Macro Module that extracts meta information from the document to be analyzed (the “target” document) 2) A load script that processes the extracted meta data.
The Macro does a lot of filesystem access and requires System Access module security. If you have not allowed System Access, the macro will warn you and provide instructions for setting it.
Using DocumentAnalyzer is a two step process, driven by buttons in the Main sheet. The first step is to choose a target document. Pressing the button will bring up a standard windows file chooser dialog. If a file chooser dialog cannot be created, the user is instructed to type a filename directly in the input box.
Once a target document is selected, press the “Process Document” button. The Macro module will be invoked and extract the metatdata to a series of files in your temp directory. After extraction the load script will read the extracted files. At the end of the load script, the metadata files will be deleted.
The tool will open the target document and navigate through the screens. Keep your hands off the keyboard while this process runs. When the load script is complete, you’ll receive a msgbox like this. Press OK.
The first chart of interest can be found on the “Fields” sheet. The Field References chart lists each FieldName and indicates whether the FieldName was used as a Key, Dimension, In an Expression, Macro or Variable. FieldNames not referenced anywhere are highlighted in yellow. These are fields you might consider dropping from the document.
FieldNames that contain special characters are flagged in red. The usage of these fields cannot be accurately determined due to limitations of the parsing method I’m using.
The Exception sheet displays you may consider warnings about the data presented. Of particular interest is the “Unmatched Fields” listbox. Check out the Help in this listbox to understand it’s contents.
The Parsing Algorithm
The identification of field rerefences is performed by the load script. The parsing is rather primitive so I’ll provide some explanation here so you can understand it’s limitations. You can also find this information on the “About” sheet of DocumentAnalyzer.
Field names are discovered in expressions by replacing “special” characters with a delimiter and then parsing into words using the subfield() function. For example, the expression:
“Avg(Price * Quantity) * 1.05”is delimited to:
“AvgPrice Quantity 1.05”which is then parsed into four words — Avg, Price, Qty, 1.05 . The words are then matched against fieldnames. You will get a false match if a function name such as “Avg” is also used as a field name. If you just want use DocumentAnalyzer, no need to read further. If you want to learn something about the code, read on.
The Macro writes the metadata to a series of files. The files are normally deleted at the end of the script.
If you want to keep the metadata files, comment out this line in the script (on the Cleanup tab):
LET x = DeleteFolder(‘$(f)’); // Delete the data files
The Macro Module is of a fair size — about 500 lines. If you examine the code, you’ll find some conditional code (IsInternal()) devoted to allowing the macro to run internally in a qvw or externally from cscript.exe. The entire macro module can be copied to an external file and run with cscript. I coded for “dual” execution environments because I get a better editor and debugger in the external environment. I do the development running externally and then paste the script into the QVW for final testing.
Meta information (Field names, Dimensions, Expressions, etc) are extracted from the target document using the QV API. Getting Dimension values was fairly easy, they are only a few API paths for the various object types.
Finding all the expressions was the most challenging part and took the most time to solve. There are many different properties where expressions may be used in sheet objects. They may also differ by release. I could not see discovering and writing all the API calls to extract every possible expression. I experimented with a number of approaches, including generating code from the API doc — never got this to work correctly. The most promising approach was using the file export from the Expression Overview dialog. This gave me a complete list of expressions, but the exported file was not consistently usable. The export file is a tab delimited file. If an expression uses tabs or newlines it can make file impossible to navigate.
My eventual solution was to export the objects into XML using the
WriteXmlPropertiesFile outputFile API method and then extract the expressions from the XML files. I first tried to get the expressions using load script, but found this too cumbersome. I settled on using XPath to extract the data I needed from the XML files. XPath is a sort of “query language” for XML. Where SQL returns a set of rows, XPath returns a set of XML elements. This required only a few XPath expressions to cover all the possible expressions.
Once I perfected the XPath method, I switched to doing the Dimension extraction this way as well.
In QV 8.5, Sheets do not have an XML representation. So Sheet expressions (background color, conditional show, etc) are extracted indivdually.
The Document Variables. Macro Module and Script are written to files as well. The Script is not currently processed by the load.
Contact me if you have any problems or questions on using the app. Contact information is on the “About” sheet.
I’ve fielded several questions lately about loading Group membership information from Active Directory. The Active Directory sample in the Qlikview Cookbook uses AdsDSO and can load “single-valued” fields such as Name and Mail. AdsDSO will not load “multi-valued” fields such as “memberOf” or “member” — fields that define group members.
To read multi-valued fields, you’ll have to install some sort of tool to extract the data into a format QV can load. There are a number of free and commercial utilities available that will extract AD information into a text file.
My favorite tool for AD extracts is the free Microsoft Logparser. Google for it, you’ll find lots of information as well as the download link. There is also a Logparser book and forum available.
Logparser can read data from many different inputs — Active Directory, IIS logs, Windows Event logs, Registry — to name a few. Logparser can write to several different output formats, CSV being the most useful for QV.
Logparser uses a SQL syntax for it’s queries. Here’s an example:
logparser -objClass:Group “select cn, member into tmpAdGroups.csv from LDAP://mydomainController”
This Logparser query will create the output file “tmpAdGroups.csv”. The file will contain one row for each group (cn). The members of the group will be returned as a single field with the members separated by the pipe “|” character. The members are easily separated in the QV load using the QV subfield() function:
subfield(member, ‘|’) as member
Other uses I’ve found for using Logparser with Qlikview:
- Extracting data from Windows Event logs.
- Preprocessing IIS log files. The fields contained in a IIS log can vary between sites and may also change dynamically within the same physical file. Logparser can neutralize these differerences and produce a common input for QV load.
Logparser is a favorite tool of mine. I use it frequently for non-Qlikview tasks as well.
Update 12/12/2008 I’ve published a complete example of using logParser to extract Group and User data from AD for loading into QV. The example is in version 9 of the Qlikview Cookbook available at http://robwunderlich.com/Download.html