Category Archives: Tools

Analyzing Your QVPR

The Qlikview Management Console (QMC) is used to define Reload and Distribution tasks for your Qlikview documents. These definitions are stored in a set of XML files — the Qikview Repository (QVPR) — located in C:ProgramDataQlikTechManagementServiceQVPR on your server.

The QMC provides a good overview of scheduled tasks, but a better analysis is sometimes need to answer questions like:

  • What documents are being scheduled on a Monthly schedule? Weekly?
  • What documents are using Dynamic Distribution (reduction) and what field controls the distribution?
  • Which administrator modified this task and when?
  • What documents are being distributed to group Accounting?
  • How many documents are being distributed as PDF?
  • Do I have logical errors in my QVPR?
  • When was my server upgraded?

I’ve produced a “QVPR Analysis” tool that loads the QVPR XML files and makes them available for Qlikview analysis to answer the above questions and more. You can download the QVPR Analysis tool  from the “Tools” menu of QlikviewCookbook.com.

The download link above contains some screenshots from the tool (names are scrambled in the images).

You can load directly from the server QVPR folder or an offline local copy obtained from the server. The offline capability makes the tool especially useful for remote consultants who can analyze a customer’s configuration without having QMC access.

This is the same tool provided to Masters Summit for Qlikview attendees last year. If you’ve already received a copy, no need to download again. I plan to continue enhancing the tool and will release further updates on QlikviewCookbook.com.

The analysis is pretty much text and tables which has suited my needs just fine. I’ve used the tool several times to solve some tricky customer problems or get someone out of a jam.

If you have Qlikview Publisher, you’ll get detailed information on Distribution tasks. If you don’t have Publisher, you won’t have distribution information but you’ll still get useful information on reload schedules and QVPR structure.

If you build some new analysis or find the tool useful, drop me a comment.

-Rob

Share

Using Alerts to “Send the Numbers”

The Qlikview Document Alert feature is generally thought of as a way to notify users or administrators of exceptional conditions in the loaded data. But it may also be used for regular emailing of non-exception conditions, like daily sales figures. This can be very useful to inform mobile or travelling user of the status of Key Performance Indicators.

The Document Alert feature is part of basic Qlikview. No additional software or license is required. Please note that that Alerts can only send out text email. They cannot send graphical charts or attachments. If you want that  functionality, look to third party addons such as NPrinting.

Alerts are configured in a QVW using the menu item: Tools, Alerts. The Alert Message may contain dynamic (calculated) content in the form of an expression like:

=’Sales YTD:  ‘ & money(sum({<Year={$(=year(max(OrderDate)))}>}OrderAmount))

I won’t go deep into the configuration details here, because I’ve provided a downloadable annotated example at this link:
Qlikview Cookbook: Using Alerts To Send Numbers Via Email 

The example will produce an email after reload that looks like this:

Alerts are a low cost and simple way to expand the reach and value of your QV applications. Users receiving emails do not require a Qlikview license.

-Rob

Related posts on Alerts:
http://qlikviewnotes.blogspot.com/2010/09/monitoring-reload-schedule.html
http://qlikviewnotes.blogspot.com/2010/12/alert-questions-answers.html

Share

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