Architecture is a great way to structure frameworks for designing systems, offering benefits such as clarity, scalability, interoperability, ease of maintenance, and security.
Similarly, the Three-tier architecture in Qlik Sense allows you to build robust, scalable, and secure data analytics applications!
This article will cover all the basics you need to know about the Three Tier Architecture.
As the name suggests, the data extraction phase of the analytics begins in this tier. Client data is extracted from one of the several data sources supported by Qlik Sense (80+ data sources). Data Sources also include Local Storage, Google Drive, etc.
Coming to the data format, Qlik Sense supports the following data types:
Below is the image of a generic data extraction from an Excel file:
Once the data has been extracted, it needs to be converted into a QVD file and subsequently stored. Below is the code to convert and store as QVD.
The advantage of using QVDs is that they are extremely efficient to load (almost 100 times more efficient). This is possible because QVDs compress the data while retaining all filed values!
Once all the files have been extracted and converted to QVDs, they must be dropped. Below is the code to achieve this.
Overall, in the 1st tier i.e. Extract Layer, the data is extracted from the source, converted to QVDs for performance, and finally dropped.
The entire Second tier can be categorized into 3 parts, as represented below:
Let's delve into each category.
Data Modeling in Qlik Sense
The data model is made using either the Star or Snowflake schema. A Star Schema contains one or more fact tables (tables that undergo frequent transactions, ex: Orders Table) in the center and multiple-dimension tables (tables that undergo infrequent transactions, ex: Employee Table). Below is the representation for both.
The Star schema is modeled by joining relevant tables. Once this is done, all the tables are joined to create a single table. The below table will be obtained when all the tables are joined!
The reason all fields are joined into a single table (known as denormalization) is because it drastically boosts read performance, which is a key requirement for Analytics Applications (known as OLAP).
The Master Calendar is a dimension table that is linked to fact time via a common date field. The Master Calendar contains all dates between the minimum and maximum of the date in the fact table. Confused?😄
Suppose you have an Orders Table with a date field order_date. Intuitively, the first-ever order to be made will have the minimum date (say it was 1/1/2014) while the last order will have the maximum date (say yesterday).
Thus, the Master Calendar will have all dates starting from 1/1/2014 to yesterday!
Usually, clients desire the dates to adhere to the Financial Calendar, which generally begins on the 1st of April and ends on the 31st of March.
Here is a list of useful links to help you create a Master Calendar:
Data Transformation in Qlik Sense
This phase includes performing calculations on the fields, data type conversion, bucketing, currency conversions, etc.
Calculations that can be performed on fields are:
Aggregation using Group By.
Arithmetic Operations (ex: Multiplying product cost with quantity to calculate total sales).
Sorting the data using Order By.
Data type Conversion:
Most date fields are either in string or numeric format, thus they need to be converted to a date format.
Once you've transformed your data, created a Master Calendar, and created an optimal data model, without dropping any of the tables, it's time to move on to the next stage/ tier.
The final stage is categorized into 3 phases:
Binary Load in Qlik Sense
Once you’ve completed the 2nd tier/ stage, you load that application’s data model along with its data into the 3rd tier/ stage. This is accomplished using Binary Load! This means that there is no need to load data from the source!
There are a few important rules regarding Binary Load implementation:
It has to be written on the first line of the first section.
Only one Binary Load per application is allowed.
Only .qvf files can be loaded.
Section Access in Qlik Sense
Usually, clients have this requirement that a particular group/ hierarchy of employees should get to see a particular subset of data. Suppose you have a Sales table containing sales of several regions. You may want employees from the West to see sales pertaining to the West and so on.
This can be easily implemented using Section Access. The formal definition of Section Access by Qlik itself is:
Once all of this stage is implemented, you can start creating your visualization and let your creativity go wild.
In conclusion, the first tier of the analytics process involves efficient data extraction using Qlik Sense, converting it to QVD files for optimal performance, and subsequent file management.
Moving to the second tier, data transformation encompasses schema modeling, the creation of a Master Calendar, and various data manipulations.
Finally, the third tier focuses on presentation, featuring Binary Load for seamless data transfer, Section Access for security control, and the freedom to unleash creative visualizations.
