Implementing three-tier architecture in Qlik Sense

Regardless of how much we prepare the data for Qlik consumption in the data warehouse layer, there are still a number of transformations that must happen within Qlik before the data gets displayed on the dashboard. Things like creating a master calendar, renaming and formatting fields, creating flags, joining etc.

As the data volume grows, optimising the script becomes even more important for faster performance and quicker reloads. This optimisation can happen on two fronts.

First, optimising Qlik layers through the use of a N-tier Architecture model and second, optimising the expressions and transformations within the layers.

N-tier Layer Architecture

Qlik should include at least 3 layers of apps:

Layer 1 – RAW: Extract the data as-is from the source databases and save it in a QVD file.

Layer 2 – TRANSFORM: Transform the data as needed and save it in a QVD file.

Layer 3 – DASHBOARD: Load final QVD files as is into the dashboard app and build needed visuals.

How To Implement

Raw Layer

This layer is responsible for creating a copy of all needed tables for the dashboard from the source systems and storing these tables into a native QVD format that is easy for Qlik to consume. This is done for the following reasons:

• To decrease the workload on the source databases (DBs) by hitting it only once to create copies of the needed tables and then store them in QVDs, thus directing other apps to fetch data from the QVD files instead of the database(-s).
• To increase the load speed of other apps since loading data from QVDs is much faster than loading data from any other connection.
• To enable incremental load for transactional data by loading only the newest data from the source database(-s) and combining with the older data from locally stored QVDs.
  Note: Tables that require incremental load should be loaded separately in an individual app per table.
• To decrease the dependency on the DB connection performance by always having a copy of all data in QVDs.

What Is Happening?

Input:

db.TableName

Steps for each table:

• SELECT * FROM TableName and store the result of the query into QVDs/RAW/TableName.qvd

Done for each table needed for the final dashboard.

Output:

RAW/TableName.QVD

Transform Layer

This layer contains all transformations necessary to optimize the data for a final dashboard. This QVD Generator is a separate app in Qlik. Having 2 layers of QVD Generators (RAW and TRANSFORMED):

• Allows us to work on these Generators in parallel, independently of each other and thus to continue development in one QVD Generator regardless of any bottlenecks in the other.
• By applying the transformation logic once, we make sure all business definitions are universal in all apps that feed from this QVD Generator.

Note: Both Raw Layer and Transform Layer are QVD Generators, these apps contain only load script and no data, since its sole purpose is to generate and store the QVD file.

What Is Happening?

Input: 

QVDs/RAW/TableName.QVD

Steps:

The script loads all needed QVDs created by the RAW QVD Generator from QVDs/RAW/TableName.QVD, performs the transformations (examples of transformations are listed below) and stores final tables into QVDs/TRANSFORMED/TableName.QVD

Example of transformations happening here:

• Selecting only required fields, renaming fields, converting dates into suitable format
• Cleaning the data, if required
• Transforming hashed IDs into integers for faster performance
• Generating basic flags in this script to avoid complex set analysis in the front-end application (Dashboard layer)
• Generating master calendar table to enable Time Intelligence

Output: 

TRANSFORMED/TableName.QVD

Dashboard Layer

This is a final layer with all visualisation objects. In this layer, the complete data model is created by loading and automatically joining all tables on fields with the same names. Having a separate layer (app) for the dashboard allows us to:

• Separate development work from design work, thus enabling both of these activities to run in parallel and solving some of the security concerns around giving users access to data connections.
• Re-use the same data model and business logic in multiple dashboard apps, thus maintaining the same business logic defined in the QVDs.

What Is Happening?

Input: 

TRANSFORMED/TableName.QVD

Steps: 

For each table needed for a final dashboard, we SELECT * FROM QVDs/TRANSFORMED/TableName.QVD . No other transformations are done in the script in this layer and the rest of the work is done in the front-end.

Output:

Visualisation objects