Category Archives: Tools

Quick Tip for Exploring Your Data Model

I picked up a great tip from QlikCommunity to view field values in your data model:
http://community.qlikview.com/thread/89396

I refined it a bit and am finding it very useful.

1. Create a listbox for field “$Field”.
2. Create a Straight Table Chart with:
  a) Calculated Dimension:  =$(='[‘ & only([$Field]) & ‘]’)
  b) Expression:  =sum({1}1)

Select a $Field in the listbox and the field values and frequencies will appear in the chart. Very handy for exploring data values in your model.

-Rob

Share

QViewer – The Time Saving QVD Viewer

The QViewer utilility has quickly become one of my favorite can’t-live-without Qlikview development tools. QViewer is a fast viewer for QVD files. Written by Dmitry Gudkov, it’s available for download from http://easyqlik.com/. A free version, limited to 10,000 rows, and an unlimited paid version are available.

In addition to browsing QVD data, information on field values, simple queries, and metadata such as QVD source(s) are available. Double click a QVD and it opens in a second!

One of the interesting uses of QViewer is to examine the contents of a table mid-script, as blogged by Dmitry here . I’ve added a routine in the latest version (6.1) of Qlikview Components (QVC) to make this idea even simpler.

  CALL Qvc.InspectTable(‘mytable’)
will save “mytable” to a temp QVD and open it in QViewer.

I’ll be showcasing a number of ways that QVC can facilitate script development and debugging at the Masters Summit for Qlikview April 16-18.

-Rob

Share

First Look at Governance Dashboard

The Qlikview Governance Dashboard (“QGD”) is now available for download in QlikMarket. It’s marked as “beta” but it looks to be pretty close to complete.

QGD is a Qlikview app that scans files from your Qlikview deployment — QVW, QVD, log files — and builds a dashboard of metrics that can be utilized to understand and manage your deployment. There is a  FAQ available here.

QGD replaces the MetaScanner sample that was available on the Community. QGD is a QT supported product, whereas MetaScanner was provided as a sample app.

QGD uses a Connector, an Expressor dataflow, and a hidden script to do the scanning. The dataflow cannot be modified by the customer. Not a problem as this is now supported by QT. The scanner tolerates older (pre V10) qvws without error.

The data model produced by QGD is pretty comprehensive, and in my limited review, accurate and useful. You can use the dashboard objects as delivered or binary load the model to create your own analysis.

The scanner reports on some attributes that were previously difficult to scan, such as the document “Generate Logfile” property. The dashboard presents some interesting displays. I like the idea of counting expression variations against a label. For example, we can see that something labeled “Proceeds” is associated with twelve different definitions  Those variations may indicate a business difference or perhaps just different set modifiers — you have to look to see — but it can point to something worth reviewing.

For complete lineage results, you must run the scanner on the same machine where the reloads took place, using the same path mapping.

There are some improvements I’d like to see. The current notion of a database “source” is whatever appears after the FROM keyword in the SELECT. That works for a simple select but yields less useful and sometimes incorrect results for more complex SQL. In my QVD Dictionary app, I parse the DB table  names out of the SQL statements. I’m sure a connector could do this much easier by leveraging a SQL parser.

I would also like to see the one QVW Field to many QVW Table relationship. Currently, a Field is reported as being in only one table.  The complete association would allow us to analyze table associations, synthetic keys and even draw table models of a QVW.

All in all, a great move forward. I’m happy this is now available and will be testing it on different customer deployments.

Share

Identifying Unused Fields With DocumentAnalyzer

An first step to improving  the performance of a QV Document is to remove unused fields from the data model. A tool that can assist with this process is DocumentAnalyzer, freely available from my downloads page at http://robwunderlich.com/downloads/

I first blogged about DocAnalyzer in 2009. It’s since undergone a number of improvements and there is a whole new audience of QV Developers out there. I thought it was worth revisiting.

DocumentAnalyzer is a QVW that analyzes another QVW and identifies Fields that are unused by the UI. That is, Fields that are not used in expressions, dimensions or keys. These are fields that can probably be safely removed without affecting the user experience. I say “probably”, because DocAnalyzer is a guide; you must apply your own common sense and knowledge of the application before following it’s recommendations.

DocAnalyzer uses a macro that requires system access. Allow system access when prompted. The main sheet provides an input field for specifying the document (target document) to be analyzed:

 

 

 

 

In the input field, enter the path to the target document. You may alternatively use the “Choose File” button to open a file chooser dialog. The usability of the chooser dialog is dependent on the windows version.

After entering a target name, press the “Process Doc” button. DocAnalyzer will open the target document and extract the info required for analysis. When the analysis is complete, you will see the popup

 

 

 



Press OK to continue and review the results.


A good place to start is the “Memory” sheet. The Memory Reference chart will show how many bytes are represented by unused (Unreferenced Data)  fields, and therefore how much memory could be saved by removing those fields. In this example 2.2GB of data is unused!

 

 

 

Next go to the “Fields” sheet. Select “N” in the FieldIsUsed listbox. Sort the Bytes in the “Field References” chart. This will identify the amount of memory occupied by each unused field.

 

 

It makes sense to consider removing the “OrderTime” field. It uses about 90MB. The other fields are small. You may want to remove them for neatness but the impact on performance will be insignificant.

A field may be required as part of the script processing or it may have been loaded by a “LOAD *”. A simple way to remove it from the model is to add a
DROP FIELD OrderTime;
at the end of the script.

In addition to identifying unused Fields, DocAnalyzer has picked up a couple of useful features for understanding target documents.

– The Groups sheet show the composition of Groups and their use.

– On the Object sheet, select to a single objectId. Clicking the “Goto Object” button will take you to that object in the target document.

– A customer requested the ability to analyze consistent font usage in his application. While not really the mission of DocAnalyzer, I had the framework there I so added the Font sheet.

If you have problems or questions on DocAnalyzer, please follow the reporting instructions on the About sheet. If you are reporting a possible bug, please include the contents of the Logtext on the Main sheet in your report.

-Rob

Share

Script Diagnostics using Qlikview Components

Qlikview Components (Qvc)  is a script library that simplifies and improves the quality of Qlikview scripting. You can read an introduction to Qvc here.


Today I’ll talk about a couple of debugging and diagnostic routines in Qvc. Qvc has a Log routine that records messages in a Log table. The Log may optionally be written to an external file. 


A call to the log routine is simply:
  CALL Qvc.Log(‘message’);


Log adds ‘message’  to the Table Qvc.LogTable as  fieldname Qvc.LogMessage. A sequence number and timestamp are added to the message. The Qvc.LogMessage field may be displayed in a chart or listbox. 







Options such as writing to an external file are controlled by configuration variables. See the Qvc.Log doc for a complete list of options. 



The Log routine is used by several other Qvc routines. Let’s look at one example.


We sometimes want to know in script how a table’s row count is affected by an operation such as Join. The Qvc.TableStats routine displays a list of tablenames, row and field counts. The basic call is:
  CALL Qvc.TableStats;



The output is written using Qvc.Log. If the optional parameter is provided, the message parameter will be included in the log lines to identify this call. For example:
  CALL Qvc.TableStats (‘After Customer Join’);











If you are using Qvc V1.1 (the latest as of this post), in etcQvcSheetObjects.qvw you’ll find an example chart that colors the log messages based on level.







It’s can be useful to record progress and status information during a script run. Qvc can make this a snap. 

Share

Incremental Load using Qlikview Components

Qlikview Components (Qvc)  is a script library that simplifies and improves the quality of Qlikview scripting. You can read an introduction to Qvc here.

Today I’ll show you how to do Incremental Load (IL) using Qvc. Incremental Load means extracting only the latest changes from a database table and merging those changes with a master QVD file. Writing your own IL script can be rather tedious and frequently involves copy/paste operations followed by forgetful edit errors. Qvc can make it much easier.

Incremental Load is not necessary for every table. We typically use IL for large source tables. IL can reduce the elapsed reload time and impact on the database system by loading only new or updated rows.

To utilize Incremental Load a source table must have both of the following attributes:

  • A unique identifier — a Primary Key — for each row.
  • A “Modification”  column that identifies when a row was added or updated. The column type may be a Date, Datetime or ascending Revision number.
The classic IL logic is this:
  1. Determine the “Last reload Time”. The most robust technique is to extract the max value for the “Modification” column from the Master QVD.
  2. Select rows from the database table where “Modification” is greater than “Last Reload Time”.
  3. Add and update rows in the Master QVD, based on primary key.
Here is the complete Incremental Load script using Qvc.

// Include Qvc code
$(Include=..qvc_runtimeqvc.qvs)


// Calling parameters are QVTablename, UpdateColumn, PrimaryKey.
CALL Qvc.IncrementalSetup (‘Rates’, ‘LastUpdate’, ‘RateId’);  


// v.Tablename is set by IncrementalSetup
[$(Qvc.Loader.v.Tablename)]: 
// Whatever LOAD and SELECT goes here
SQL SELECT * FROM dbo.RateTable
// v.IncrementalExpression is set by IncrementalSetup
WHERE $(Qvc.Loader.v.IncrementalExpression);


// Update the QVD with the changed rows
CALL Qvc.IncrementalStore

That’s it.

You’ll also get useful log messages telling you what was done.

00002 1/17/2012 1:23:46 PM; QVDRates.qvd exists, rows=31
00003 1/17/2012 1:23:46 PM; Loading rows where LastUpdate >=”01/14/2011”
00004 1/17/2012 1:23:46 PM; Rates loaded, rows=1
00005 1/17/2012 1:23:46 PM; QVDRates.qvd updated, rows=31

Review the Qvc Documentation for explanation of parameters and configuration variables. The documentation also contains a working example.

Share

Easy Period Analysis using Qlikview Components

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.

 
With Qlikview Components (Qvc) you can quickly build all the parts needed for many point in time and period over period analyses. Let’s walk through an example. 
 
Download the latest distribution (this article require V0.7+) of Qvc from the download link at
http://qlikviewcomponents.org. 
Unzip the distribution file anywhere on your disk. If you are new to Qvc, start by reading the ReadMe file.
 
Let’s assume our QVW contains a fact table of Orders with a field named “OrderDate” that we will use as the date for period reporting. 
 
Add this include at the beginning of the script to bring in the Qvc routines. 
 
  $(Include=..qvc_runtimeqvc.qvs);
 
After loading the fact table, generate a Master Calendar with this call:
 
CALL Qvc.CalendarFromField(‘OrderDate’);
 
The Qvc.CalendarFromField routine will build a calendar with dimension fields named  OrderDate, Day, Month, Year, Year-Month and Quarter. You can optionally include a language file to use your local language for the fieldnames — in this example Swedish.
 
$(Include=..qvc_runtimelanguageqvc_language_SE.qvs);
 
Now that we have a calendar, we can use any of the calendar fields in listboxes or chart dimensions. And… Qvc.CalendarFromField() also creates a collection of set analysis expressions stored in variables. The variables follow the naming convention vSetPeriod. For example:
 
vSetYTD
vSetPreviousYearYTD
 
The documentation for Qvc.Calendar provides a list of all generated vSet* variables. 
 
These variables in a chart expression like:
=Sum(Sum($(vSetPreviousYearYTD) OrderQuantity) 
which will sum up OrderQuantity for YTD of the previous year. The actual set analysis expression is rather lengthy but the variable is compact. 
 
I’d like to credit Mike over at iQlik for first teaching me the set analysis variable idea at http://iqlik.wordpress.com/2011/01/01/point-in-time-reporting-out-of-the-box/
 
Now we can quickly whip up a chart like this without coding any of the complex stuff:
The documentation supplied with Qvc provides examples for all Qvc routines. On the Documentation.qvw “Details” sheet select Qvc.CalendarFromField. You’ll be able to open a sample qvw where you can explore the vSet variables and learn how they interact with selections.
 
If you have comments or questions about Qvc, use the User Forum found on the project page.
Share

Introducing Qlikview Components

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:

The mission of Qlikview Components is:
  • 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.
QVC aims to serve the needs of the “big middle” of script tasks. Not the simple tasks, not the very complex tasks, but the garden variety common needs found in many scripts.
 
As an open source (OS) project, we have the opportunity to capture and redistribute the broad knowledge of the Qlikview Community. What do I see as the strengths of this project?
  1. 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. 
  2. A stable API. We’re committed to continuous improvement and compatibility.
  3. A unit testing regime that ensures quality.
  4. All the supporting pieces one would expect in a serious project — documentation, examples, localization, issue tracking, discussion groups, formal testing and build systems. 
Qlikview Components is currently in a “Preview” stage with a target for first production release on Feb 1, 2012. You can find the project at 
 
 
That address will redirect to the hosting site on google-code where you’ll find downloads and links to discussions. 
 
I’ll be blogging more on Qvc in the coming weeks. In the meantime, here’s a taste of what it takes to build a typical master calendar using Qvc. 
 
$(Include=..qvc_runtimeqvc.qvs);
// Load fact tables, etc…..

CALL Qvc.CalendarFromField(‘ClosingDate’);
 
I hope you’ll find Qvc useful and look forward to hearing your ideas for improvement in the discussion groups!
 
 
 
 
 
 
 
Share

Alert Questions & Answers

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 ‘
& money(
sum({1
<[Invoice Year]={$(=year(today(2)))}>
}
Quantity * Price
)
, ‘$#,##0;($#,##0)’
)
& ‘
YTD Orders are ‘
& num(
sum({1
<[Order Year]={$(=year(today(2)))}>
}
Quantity
)
, ‘#,##0’
)

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.

-Rob

Share

Monitoring the Reload Schedule

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:

  1. On the “Configuration” script tab, specify the directories you want to scan for qvw files.
  2. Modify FileAgeMonitor_Rules.txt to specify rules meaningful to your installation, Instructions are in the file.
  3. 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:

Key, MaxAge
# 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
Expenses*.qvw, 25

#Films can be 90 days old
Films.qvw, 90*24

# Everything else – catch all default – 7 days
*, 7*24

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.

-Rob

Share