There are two different approaches to refresh Qlik Sense data – this will always depend on the data model complexity. In this article, we will present solutions that can be applied to different situations.
We will also explain how to schedule and create dependencies between data loads in order to better manage time and resources.
DIRECT ACCESS TO DATA
Data model over database
This approach is normally applied to:
- Simple Dashboards
- Smaller data volume
- There is no data sharing between several Dashboards
It is important to keep in mind that every time we refresh data, the database is accessed and, due to that, we need to keep in mind the time of day these refreshes have to occur in order to minimize slowdowns on other processes and our own.
Reading data from other data sources
Besides reading data from a database or QVD files, it is also possible to use other data sources, such as:
- Files (Excel, CSV, TXT, others)
- Web sources
Build a datamodel from QVD files
Creating data models from QVD files (Qlik View Data) is the most used approach and the one that is highly recommended when there is the need to build a complex data model in what concerns calculation logic or data volume. A QVD file is a proprietary file from Qlik that contains tabular information. These files can be read or written by both Qlik Sense and Qlik View.
The main advantages on using QVD’s are:
- Better data loading performance
- Charge load decreased over the data sources
- Data Consolidation from multiple sources
- Incremental Data Loading
The ideal implementation to achieve a data model built from QVD files is based on:
- Have one or more APPS with the exclusive purpose of refreshing data from a database into a QVD file.
- Have one or more APPS with the exclusive purpose to read from QVD files in order to load previously refreshed data. In these APPS, it is usual to also have the developed dashboards, in order to visualize the data.
Image 1 – Store data on a QVD file
It is also possible to model data between QVD files, using SQL functions such as join, concatenation, etc.
All the SQL functions applicable to strings or numerical data are also applicable to columns on QVD files.
Image 2 – Read and Transform data from QVD files
HOW TO SCHEDULE AN EXECUTION
Qlik Sense offers the possibility to set up the multiple executions on different schedules and to create dependencies between them.
Execution scheduling is created in Qlik Sense QMC (Qlik Management Console). The URL that is used to access QMC normally has the format QlikServerURL/qmc.
In Tasks section, the user should select Create New. An Edit window will appear where it is possible to fill several fields, such as, schedule name and the App name that will be the target of the execution.
Image 3 – Create an execution schedule
Here it is also possible to select the schedule execution periodicity. In order to achieve that it is only necessary to select, over Actions menu, the trigger for the scheduling:
- Create new once-only trigger
- Create new hourly trigger
- Create new daily trigger
- Create new weekly trigger
- Create new monthly trigger
- Create new scheduled trigger
- Create new task event trigger
Inside each of these options, will be set up the begin and end date of the execution.
Image 4 – Set up Trigger Execution
How to create dependencies between different schedule executions?
As previously mentioned in this article, it is possible to combine and set up dependencies between several schedule executions. It is quite simple to achieve this.
Picking the example of a data model based on QVD files, it is necessary to create a dependency between the execution that reads the data from the database and stores it into the QVD and the execution that reads the data from QVD files and refreshes the data within a previously published dashboard.
The first one can be scheduled to execute daily, using a daily trigger.
Every day, the data will be refreshed based on this trigger.
The Dashboards only have to be updated when this first execution finishes with success, to achieve this we create a second execution using a task trigger event, similar to what is presented on the below image:
Image 5 – Create dependencies between schedule executions
The user can select between Task Successful and Task Failed:
- Task Successful: the execution only starts after the execution mentioned on Task field has finished with success
- Task Failed: the execution only starts after the execution mentioned on Task field has finished with failure
When possible, developments in Qlik Sense should always be over QVDs. The key element is to create one or multiple APPS to manage data refreshes efficiently.
- A single refresh can contain information for multiple Dashboards
- One QVD can be used for multiple Dashboards
- One Dashboard can have information from multiple QVDs
- Increased performance relative to all other options
- Execution load will be inferior on the data sources
- QVDs can be incrementally refreshed if required