O'Reilly logo
live online training icon Live Online training

Data Modelling with Qlik Sense

BI made easy with the most powerful Data Analytics tool in the market.

Umberto De Domenico

This course is the foundation for developing optimized Data Models in Qlik Sense. Data model optimization is critical when data sets are large or complex. Qlik Sense is not only a powerful BI Visualization tool, but it is also an effective ETL (Extract Transform and Load) software and it allows us connect data from any kind of different sources, link them together, create metrics and optimize data structure. This are key points for fast data surfing and analysis and they are considered the winning point of a data analytics tool.

Qlik Sense, conceived to be a Self-Service Analytics Tool, of course provides users with a fantastic data manager, able to link data and create data models in matter of minutes. Here no scripting is necessary. We will look at this great feature and create our first Data Model, linking data from a mix of sources and create some basics metrics.

Next step will be to explore a different approach, the manual one, when programming becomes necessary and it is aimed at reaching a schema as close as possible to a star one. Programming is necessary when data sets are complex and data manager is unable to manipulate tables to the extent needed. We will look at the different techniques used to associate tables, create metrics, moves fields from one table to another, joins and concatenations… we will also spend some time looking at different prefix tables which can be used to manipulate tables and we will finalize our course learning what QVD files are and how they can be used to create incremental loads.

What you'll learn-and how you can apply it

By the end of this live, online course, you’ll understand:

  • Understand Data Profiling, this knowledge will allow you to create Data Models without the need of scripting
  • Resolve synthetic keys and circular reference to ensure the associative model we create is reliable and reflects best practices
  • Work with Resident loads, Mapping Tables, lookups, Joins and Concatenations. Techniques used to develop Data Models in Qlik Sense
  • Prefix Tables to manipulate original data e.g. create hierarchies or classifications
  • Create QVD tables creation with an overview of how to use them to speed up application loads

And you’ll be able to:

  • You will be operational and able to create your own data model as any data architect.

This training course is for you because...

  • You're a user with minimal knowledge of any programming language, even better if SQL oriented. Target audience is whoever interested in gaining practical experience with Qlik Sense Data Modelling logic.
  • You've already done the Create Visualization course, this course will complete your Vision of Qlik Sense and allow you to master both the design and development layer.

Prerequisites

  • In order to make the most of it you should be already familiar with graphs creation in Qlik Sense and have a minimal understanding of any scripting language, better if SQL oriented.

Recommended Preparation:

Materials, downloads, or Supplemental Content needed in advance:

The main preparation for you is to gain access to Qlik Cloud by creating an account and install Qlik Sense Desktop on the PC (64 bits) you will be using during the training.

About your instructor

  • Certified as a Qlik Sense Business Analyst, Data Architect and System Architect, I am an education Consultant with a professional career of over 20 years in highly recognized multinational companies. Deep knowledge of Qlik Technology applied to Business Intelligence. Highly experienced in training multicultural, objective-oriented work teams. I hold a degree in electronic engineering and a master in strategic communication.

    I spend my spare time delivering Mindfulness courses (I am a qualified and passionate Mindfulness Teacher) and entertaining kids with the magic art of clowning at the local Hospital.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

DAY 1

Section 1: Working with Qlik Sense Data Manager (40 mins + Lab 10 mins)

  • Learn how to load data in a freshly create application and how to associate tables by using Data Manager.
  • Understand the core of the Associative Model and explore your data by using Data Viewer.
  • Lab 1: Create a new application, load Data from Database, create a KPI and know how to edit tables and replace, remove and trim data as needed
  • Break: 10 mins

Section 2: Working with Data Load Editor (40 mins + Lab 10 mins)

  • We work round the limitation of Data Manager to build up complex Data Models by loading data manually
  • Lab 2: Create a new application, this time we access the data load editor and in there we start loading tables and work on the associations between tables resolving Synthetic Keys and Circular References
  • Break: 10 mins

Section 3: Setting up metrics in Data Model: (35 mins + Lab 15 mins)

  • Understand the advantage of setting up metrics in the Data Model rather than at design layer
  • Lab 3: After our first resident load, we create a couple of metrics and explore the benefit of having them already in place rather than asking the data engine to calculate them on the fly. We will also load the Master Calendar which already has some flags and it will be conveniently used for KPI creations, again reducing engine overload.
  • Break: 10 mins

Section 4: Loading data into Qlik Sense (35 mins + Lab 10 mins) In this session we finalize loading tables and in fact complete the first phase of the modelling. Lab 4:Load data from xml tables, excel, csv and txt formatted tables. In this section we will also load an external script as an example of centralized scripting.

Wrap up Q&A 15 mins

DAY 2

Section 5: Data Model Optimization through Mapping (40 mins + Lab 10 mins) Continuing from previous section we refine our model and justify the next steps to come, the goal is to create a star schema Lab 5: We will unify our tables by using residents and Mapping load techniques, a popular way to join tables and move fields around. Break: 10 mins

Section 6: Working on granularity of metrics (20 mins + Lab 10 mins) Before dealing with bigger tables in our schema, we will set up one more KPIs and explore an alternative way to move fields from table to table. Lab 6: Create Margins and explore the granularity of our metrics. Break: 05 mins

Section 7: Working with Joins in Qlik Sense (40 mins + Lab 10 mins)

  • Lecture: Last tables left to join will need a different approach, therefore standard joins will be used, we will look at pros and cons of joins and what should be carefully checked before and after joining two tables.
  • Lab 7:Explore the different Joins available in Qlik Sense and when to use them.
  • Break: 10 mins

Section 8: How Concatenations work in Qlik Sense (40 mins + Lab 15 mins)

  • Lecture: Understand how concatenations work in Qlik Sense and why they are so popular for incremental loads.
  • Lab 8: We will upload a new table and observe the automatic concatenation triggered by Qlik Sense, we will then analyse code of an incremental load to appreciate the power of automatic concatenations.This section is completed by creating QVD tables of our final model

Wrap-up: Summary, Discussions (30 min)