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).

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.

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