Audit Logging

This is something I’ve used time and time again in organizations I’ve worked for.

QlikView does automatically log information when using QMS, this is different…..

Within the script code is placed that will write to an ‘Audit Log’ qvd which can be read by a further dashboard. All types of information can be passed and what is up to you. Variable states, table sizes, durations, etc. Anything that will help you later debug an issue or monitor the performance.

Benefits of implementing this code become apparent when monitoring script performance or debugging script issues that arise outside office hours (or at any time).

Audit Logging Dashboard

Audit Logging Dashboard

The above file can be found on the QlikView Community

 

Below is a technical guide and I’m planning to upload a video demonstration soon.

 

Basic Principles

A QlikView subroutine is called at various touch points during script execution. This subroutine collates data regarding these touch points into a file which is stored into a QVD at the end of the script.

Saved QVDs are loaded into a central dashboard accessible by QlikView support staff.

Items passed to the audit file include:

  • Duration Point to Point within the script
  • Size (row and column count) of table loaded
  • Variable name and value

Standard information which is included by default:

  • Timestamps
  • Row number
  • Application Name / Group

 

 Audit Table Definitions

In order to ensure consistency please follow the definitions as shown below. Code examples can also be found later in this document.

Example Audit Log

Example Audit Log

Blue cells in the above example are passed to the subroutine at each touch point. It’s important to check new audit logs for consistency at the first possible opportunity as historic errors cannot be retrospectively changed.

 

Field
Type
Definition
AuditLog_CycleStartTime Single set up This timestamp remains constant through the load cycle and is set using a variable at the start of the scriptLET vCycleStart = TimeStamp(Now());
AuditLog_CycleRowID Automatic Row number which resets each time the script is loaded
AuditLog_ApplicationGroup Single set up Denotes the group which the QVW belongs.For example a QVD Builder and Application will belong to the same group and the value must be consistent.LET vAuditLog_ApplicationGroup = ‘jDiff’;
AuditLog_Application Single set up A descriptive name of the QVW being audited, this must be unique.LET vAuditLog_Application = ‘jDiff QVD Builder FACTS’;
AuditLog_AttributeType * Passed at each Point A predefined value to categorise the information passed(Please refer   to the following tables for examples)
AuditLog_AttributeName * Passed at each Point A predefined value to categorise the information passed or the name of a variable / table(Please refer to the following tables for examples)
AuditLog_AttributeValue * Passed at each Point The value of a variable(Please refer   to the following tables for examples)
AuditLog_Start Passed at each Point A timestamp used to calculate Duration. This is set before the code you wish to analyse (i.e. before a load statement)
AuditLog_End Automatic The timestamp the audit log row was recorded
AuditLog_Duration Automatic The difference in seconds between AuditLog_Start and AuditLog_End

* Type, name and value have predefined standards.

 

Predefined Standards

 

Type, Name and Value are used in conjunction with each other and the central audit log dashboard requires standardisation for some free text fields in order to monitor performance.

 

Attribute Type

Standard Entry
Name / Value Reference
Comments
Duration Duration Point to Point duration calculations
Variable Variable Variable value logging
DB FACT Row Count Size Number of rows from a fact table loaded from a database
DB FACT Field Count Size Number of fields from a fact table loaded from a database
DB DIM Row Count Size Number of rows from a dimension table loaded from a database
DB DIM Field Count Size Number of fields from a dimension table loaded from a database
QVD FACT Row Count Size Number of rows from a fact table loaded from a QVD
QVD FACT Field Count Size Number of fields from a fact table loaded from a QVD
QVD DIM Row Count Size Number of rows from a dimension table loaded from a QVD
QVD DIM Field Count Size Number of fields from a dimension table loaded from a QVD
Other FACT Row Count Size Number of rows from a fact table loaded from another source (csv, excel, etc.)
Other FACT Field Count Size Number of fields from a fact table loaded from another source (csv, excel, etc.)
Other DIM Row Count Size Number of rows from a dimension table loaded from another source (csv, excel, etc.)
Other DIM Field Count Size Number of fields from a dimension table loaded from another source (csv, excel, etc.)

 

 

Attribute Name / Value

Use the reference from the Attribute Type table to determine best practice for Name and Value

 

Name / Value Reference
Name Comments
Value Comments
Duration Tracking the time point to point the name would refer to the section of code of interest or the table name which is being loadedIf used in conjunction with ‘Size’ this value needs to be identical Value is not required and should be left empty
Variable The name of the variable being interrogated.Standard values are:RecoveryReload Type The value of the variable being interrogated.Standard values are:Recovery = True / FalseReload Type = Full / Limited
Size The name of the table being evaluated. If used in conjunction with ‘Duration’ this value needs to be identical The value of the attribute being interrogated.

 

 

Code Examples

Subroutine: Code to write a line to the Audit Log table

Load Table: Code to call the subroutine when loading data into the application

Conditional Statement Variable: Code to call the subroutine when saving the result of a conditional function or variable value

Store Audit Log:  Code to store the Audit Log table into a QVD file

 

 

4 responses to “Audit Logging

  1. I follow something similar to this in most of my environments as well. It is so helpful not only for debugging, but also for giving some lineage and sense of scale to your environment. Good stuff Richard.

    Like

  2. Pingback: QlikDevGroup meeting 24th March with Donald Farmer | qlikcentral·

  3. Pingback: Error Trap | qlikcentral·

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s