Category Archives: Tools

QVPR Analysis Tool Adds Task Visualization

Ralf Becher has created a nice Icon Graph extension and used it to visualize Reload task structure within my QVPR Analysis tool. I’ve integrated Ralf’s script into V2 of QVPR Analysis and all you need to do is download and install the Icon Graph extension from https://github.com/ralfbecher/QlikView_Extension_IconGraph.  Links are also provided in the QVPR Analysis qvw.  Select some Tasks or Documents and you’ll get a visualization like the following.

Ralf Becher has done some really interesting work with Graphs and Neo4J. Ralf is presenting a session (T56) at Qonnections on Wednesday afternoon if you want to learn more about using Graph models and visualizations in Qlik.

-Rob

 

Share

Major Update to Document Analyzer

I’ve been publishing the free Qlikview “Document Analyzer” tool since 2009. I’ve just released a major update to DocAnalyzer. You can download Document Analyzer V2 from the Qlikview Cookbook website

DocAnalyzer is a tool to help tune the performance — response time and resource requirements — of a Qlikview QVW. DocAnalyzer can identify unused Fields and pinpoint slow charts.

How do you use DocAnalyzer? After identifying the QVW to be analyzed. press the “Extract Metadata” button to begin the analysis process. When the extract is complete, you’ll receive a popup message. The extract can take anywhere from a few seconds to several minutes for documents with many thousands of objects.

 

 

When the extract is complete, you must run a Reload of Document Analyzer. A button is provided for this or you can do Ctrl-R.

What can you discover in the analysis? Here are a few items.

– The Fields sheet will give you an overview of what fields are used and unused and where they are used — Dimensions, Expressions, Variables, etc.

A Listbox is provided with suggested DROP FIELD script statements for the unused fields.

Select $date and $timestamp in “Field Tag” listbox. Examine the “Symbols Size” column in the Field References chart. Dates and Timestamps can be stored in 8 bytes, but frequently get converted to longer string representations during loading. The impact is only significant if there are a large amount of Distinct Values.

– The Memory sheet provides an overview and detail of RAM requirements and estimates how much memory could be saved by dropping unused fields.

 

 

 

 

– The Object sheet lists all Objects along with their Calc Time and RAM Size.

 

 

 

 

Selecting a single object will enable a “Goto Object” that open the target document and Activate that object so you can examine it’s properties.

There are many other areas you can examine such as the use and performance of Calculated Dimensions, Groups, Expression contents and what properties use those expressions.

Document Analyzer is one of the Tools that Oleg Troyansky will present in his “Performance Tuning” session at the upcoming Masters Summit for Qlikview in Amsterdam Oct 1. I hope you can join us there for more performance tips and a chance to learn advanced Qlikview development skills.

-Rob

Share

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