• Scotchy

Data governance in QlikSense (Part 3)

My apologies for taking so long to add this next post , but my SQL course development work took up all of my time and I just did not even think about my blog, so remiss of me!

Anyway, time to rectify this ....

In this post , I will outline the repository architecture , note that this architecture is designed for single or multi tenancy applications, hence if you are using QlikSense in a portaled environment, i.e. you have 1 or more client connecting to your visualisations then this approach will apply!

The Business Dictionary can reside in a SQL database from any vendor provided QlikSense is able to connect to it via a built-in (or vendor provided) connector e.g. my last client site was considering DB2 as the repository!

For the SQL implementation there are 4 tables, these are described below…


Attributes that describe the Client, functionally the table defines the physical partitioning of clients from each other.

Application (If applied in QlikView then this a Document)

A client will have at least one application deployed for their interaction, an application is specific to the client at a data level and will very likely contain business rules specific to the client in addition to the standard global rules. Functionally; the table defines the applications that a client has access to.


The business definition is a high level description of a business component, for example 'Insurance Coverage' is a component of the client business domain.


The business expression table is the instantiation of the business definition. Functionally; the table is the repository of the expression calculations (qlik expression logic) for data visualisations.

Business expressions are scoped, the vast percentage of calculations e.g. ‘count of members’ will be the same for all visualisations ie. "the same subject domain" , hence this calculation scope is defined as ‘global’.

However, scoping can also be specified for a client, this is to say that where a client (domain) has a specific calculation to be performed across their own data set, then the scope will be defined as the client_id e.g. Client_1 and so on.

The below screenshot (scope_1) illustrates the implementation…

The below diagram (mod_1) illustrates the dictionary data model…

There are a few caveats governing the use of the Business Dictionary, these are summarised below…

Client table

All clients must be registered here; this information is maybe from your CRM etc

Application table (Documents in QlikView)

Applications that are to be surfaced to clients must be registered in this table and associated with the client (keyed off sid_client)

Business_Expression table

Applications will contain expressions for surfacing metrics and KPI’s , the expression to calculate these will be resident in this table (keyed off sid_app) , expressions are instantiations of the business definition, the relationship of expressions to definitions provides insight as to what calculations have been defined for a business definition (keyed off sid_definition) i.e. self defining documentation.

The scope value is mandatory; it can have only 2 values ‘global’ or the client_id, exercise care when specifying the client id value (Commercial confidentiality to be considered when clients are not internal to your own co. )

Business_Definition table

Business entities, components, subject domains are recorded in this table; the definition is the anchor point for describing the subject of what will be visualised in an app.

Naming conventions

The business dictionary specifies variables that are the containers for the calculation expression. To maintain organisation and readability variables will be named in a specific way, this method of naming is not arbitrary and can be changed later if desired by the QlikSense developer group, however this is the most common use of naming that has been implemented here.

Below is a list of variable name prefixes commonly used …

Ok, that's probably enough for this post, the next post (next week , I promise) will be about the implementation in QlikSense, and indeed this can also be used in QlikView with no requirement for modification!

Have a great week, see you then ....

Scotchy signing off ...

#Dictionaryrepository #Structure #Attributes #SQL #Namingconventions

© 2017 by awari.com.au

  • Black Twitter Icon
  • Black LinkedIn Icon