If someone told you to learn to drive a car and you had never seen a car before where would you begin ?

In this case we'll get familiar with Qlik Sense by creating your first dashboard app to analyse ~2 years of health care data.

We'll use a requirements specification to emulate a real world scenario.

When you have completed this section you will be comfortable navigating/driving the Hub and understand the features of Qlik Sense ready to dive into the Data Architect (QSDA) and Business Analyst (QSBA) sections of the course.

We will always start the project dialog with the user(s) by asking ... " What questions do you want answered ? "

Then what ?

Well this is easier to answer when a methodology is applied !

We'll work with a sample methodology (abridged) to setup your thinking and approach to Qlik Sense solution development.

So now we have the questions that are being asked , it is time to analyse the "What & Where is the data" in order to prepare the analyst for loading data to the Qlik Sense solution.

The data will likely be disparate yet the analyst has to work it all into a consistent data-set that is easily visualised no matter what visual tool is being used, for us it is Qlik Sense, but what if it was some other tool such as Tableau ?

Hence how we tackle the data side of things could be applied across any platform you will work with in your enterprise.

In this final part we explore the dimensions and other final requirements for our app.

Now is a great time to understand why the associative data modelling methodology is used.

For now this is a primer to get you familiar, we will cover modelling in more detail in the Data Architect lectures.

Continuing on from Part 1 lets look at schemas

In this lecture you will create the new data visualisation solution (app) and learn to load the patient (obfuscated) fact data using the data manager and make changes to the data set prior to loading into the final model.

We can affect the data at import time by adding value via calculated fields.

We'll play with date arithmetic in this lecture.

Based on what you were shown in the fact data load; a new calculated field is required based on dates

Step through the solution for you to check your results

Grouping data is common for analysing broader demographics, this is know as Discretisation.

How can we Discretise at data load time ? 

There's a catch here, so you need to apply some lateral thinking to solve the riddle!

Not just a solution walk through here, you will be introduced to the internal testing process that is expected of you in the certification exams.

When dealing with data at load time we are working closely with the user(s) and we will have questions about data anomalies and what action should be taken to rectify them.

Here's one for you to practice with

Solution walk through and a preview to another mechanism to verify our change

Data may contain null values but the null might be a hard null, how to we switch to a real null ?

Let's check the outcome once this has been done

To add value to our data model we'll start loading up the dimension data from our sample extract files.

Time for you to add a dimension and set the association

What happens when association is not correct ?

Now your turn to try a manual association, a small catch for this one !

Cool association intelligence don't you think ?

So ; you just were given data that is a single column with a key and description ! What next ?

Ok; time for you to have a try at another data issue presented by the data management team!

Did you observe the weird association that Qlik Sense recommended ? Why did this happen ?

Following this section and practising your coding will greatly assist you with the work we will do in the QSDA and QSBA sections of this course.

Sometimes we may like to change some attributes about the app e.g. App thumb nail image

Lets create a sheet and add the first KPI object

You get to add a KPI as per the requirements with another thrown in for good measure (forgive the pun !)

Sometimes a formula includes aggregatable and non aggregatable values.

What are non aggregatable values ?

We'll calculate the revenue for this healthcare provider and consider the non aggregatable values in the expression

Use what you learned in the previous lecture to apply to a new KPI

We'll add the age group filter pane per the user specification

Just for practice you can add another filter requested by the user.

The Green, White, Grey paradigm when selecting values to filter on.

Some of our charts require drill down capability.

You will learn how to do this, there is a catch however that we must be aware of.

In this exercise you will create a drill down that will replace the filter you created in the previous exercise

This exercise is a challenge and exploration is required to solve the requirement !

Here we will add a line chart and dimension it with the calendar master item drill down.

And we'll add a measure to the master items for re-use, it is a great idea for you to code along as you

will need this master item in an exercise.

Based on what you learned about the line chart you can add a bar chart to the CEO overview.

As always you'll need to explore to provide a change to the appearance.

We will introduce a constraint into the expression code for this to work as designed

Just for practice and thinking outside of the box.

Thinking outside of the box is a time saver here !

Sometimes users love to view data the old fashioned way in a tabular form.

You can finish off what we started.

You're an expert by now.

Let's checkout some other straight table features that enable a user to arrange items.

How can we influence the appearance of a chart object easily ?

You can add a colour to the KPI !

Let's create a new sheet and work with Geo Analytics to visualise the stats in the data

First understand the data requirements, layers and dimensioning

Now to add the data points with the metric expression

Time for you to add the second map in our solution requirements.

Walk through the solution and discover how to remove artefact white space.

We'll add our 3rd map as per the requirements but the user has requested more information that was not included in the original requirements.

This is how we adapt to last minute change(s) during development.

The final map is for you to create and accommodate another last minute change from the user.

You've got this !

Up till now we have used the default mechanisms to build aggregations resulting in measures for visualisation.

Of course we cannot do this for everything we want to measure!

In this lecture we get a quick look into writing Set Expressions to get your appetite set.

Of course we go into greater detail later in the course to get you highly proficient in coding Set Analysis.

And the user has requested changes to requirements , fairly normal during development so we'll look at these in this lecture.

Continuing with the decomposition of the expression and the governing rules of syntax

Time to code what we learned in Part 1 & 2

And we'll use a different chart type to display a KPI value

This one had a catch based on logic, did you figure it out when you tested your expression result and selections made ?

A scatter plot is used to observe and show relationships between two numeric variables

Some lateral thinking required here !

If you thought outside of the box here then that's really great as this is what analysts do !

A summary of what you will learn in the Data Architect course modules.

In this lecture I'll summarise the content covered in the section.

The business objectives will be defined in order to provide scope for the data architecture.

The business is our customer , we will be striving to support their representatives and SMEs

Introducing DAR to the dialog.

Forgotten anything ?

We'll discuss the missing objective

As we established the business objectives it is time to identify the broad data requirements and where to source the data.

It appears the data is held in different systems and file & database types which means we are dealing with a semantic model type.

Visualise our data via a semantic model diagram.

What is a tiered architecture and can you decide which one to use for our solution ?

We'll explore the first 2 Tiers in this lecture.

And what of .QVD files ?

We'll cover Tier 3 and 4 here , these are a little more involved than T1 & T2

We'll recap on the Tiers and then you try decide the appropriate Tier Architecture for our project

We'll discuss the choice of Tier for our project solution

What are some of the Qlik Sense features that provide powerful functionality to our analytics solution.

Here we will provide a preview of what is coming.

The advantages of the Qlik Sense Associative Model over others

This is one of many methods to connect with Big Data in Qlik Sense

The magic and power of Qlik Sense variables cannot be over looked !

No not Quantum States but it sometimes seems that way !

Getting tough on unauthorised access!

Managing security via the QMC !

Wrapping it up into data reduction.

Do you think we can meet the customer needs for our project ?

Identify possible users and their roles for security definition and implementation

So now to put a proposal together that will provide the roadmap towards developing & delivering the complete solution for our customer.

In this lecture I'll summarise the content covered in the section.

A introduction to terminologies so we're on the same page when Terms & Keywords are used during design & development.

Qlik Sense enterprise architecture for windows - An Overview

We'll explore the meaning and application of Qlik Sense Multi-Node

Also known as horizontal scaling!

What makes Qlik Sense so scalable ?

Wrap up the scalability subject.

Models (although associative) will take on a visual and functional appearance, here we discuss the Star Schema

A chance for you to assemble a Star Schema using the tables provided.

How'd your puzzle look ?

Models (although associative) will take on a visual and functional appearance, here we discuss the Snowflake Schema !

A chance for you to assemble a Snow Flake Schema using the tables provided.

How'd your puzzle look ?

It is time to expand the semantic model to understand the data we will work with e.g. Extract, Transform and Load.

At this stage it is reasonable to model the data as we identify it.

Examine the remainder of our stand alone associative models

This is a thought experiment ! Designed to trick your mind into a false model !

We'll reveal the answer in the next lecture.

We'll assemble the model parts to understand if it will work for a solution implementation !

Why is the Master Calendar an important part of a data model and visualisation?

In this lecture I'll summarise the content covered in the section.

Introduction to the Data Load editor (IDE)

Jump in the deep end and load/store the next dimension to start the model evolution.

Walk through the process and check we are on the same page and discuss another point about our loads

We need SQL Server installed on our PC to use the SQL data in this part of the course.

This lecture outlines …

1: Download

2: Installation

Walking you through the steps to restore the eCommerce database

Connect to SQL Server and extract a dimension

Connect to SQL Server to extract a Fact table

Walk through the exercise solution and pick up some extra tips along the way.

As part of our semantic model we'll load data from a legacy system maintained in MS Access.

However the user misinformed the model keys, in this scenario we explore an easy fix

No need to just load tables 1 at a time !

Let's load multiple tables in one go.

Your turn for some coding fun!

Let's check our results.

Wrapping up the Data Extract layer prior to starting to develop the Rich QVD  layer !

You can finish off the remaining data load here.

Let's compare notes.

And did you observe something strange in your model ?

Up to now you have been coding with these and now it is time to reveal them.

We use variables extensively in Qlik Sense , now is a great time to learn how they work and how to code them.

You will definitely need to know this when we start coding incremental data loads.

And we'll get a first glimpse of the Script Debug window

Variables in action …

Using LET and $expansion to display your variable

There is a catch with this one and if you were watching closely in the lectures for variables you'll get it!

Did you get the catch ?

What are these and how do we recognise them ?

E.g. Join is script prefix

Why use a join and how does it differ to the Concatenate statement ?

Let's understand the Inner Join mechanism.

The LEFT Join

The Right Join is useful when answering a customer enquiry!

We explore adding ease of use for the user when producing the results from our Right Join

The Outer Join is useful when answering a customer enquiry!

We explore adding ease of use for the user when producing the results from our Outer Join

Just for practice here is a join for you to code.

A slight catch in the spec that you will surely observe in your model results !

Exploring the effects of the Inner Join for this solution

Control statements are typically used for controlling the flow of the script execution.

In this lecture we'll summarise these to get a feel of what is available to developers when scripting.

We will need to use some of these in our code for incremental data loads.

There is new data available from the source system, how can we load this incrementally ?

We'll need to implement script control statements to assist with this.

There is a lot of coding in the Incremental Load script lectures and you will gain substantial experience!

Start coding the content our first sub routine and introducing the TRACE statement.

Commence coding the baseline load and get ready for the next sub routine

The next subroutine will introduce you to a resident load and take a peek into the data as well as dynamic parameter assignment.

Yes another sub routine and this one will use concatenate as part of it's processing

How to observe a script set using the debugger

Now to load the new data the source system has accumulated

Finalise the load and test it.

This challenge is not easy ! if it was then where is the challenge ?

It will challenge your lateral thinking; a skill the Data Architect will draw upon in their design work.

As this can be considered a mini coding project it will require thought and attention to detail.

We'll walk through the solution and reveal the catch

Using debug we'll process the full script to observe behaviour .

We'll also leverage the loader log for audit review.

Often a client/customer will send you data like this and ask for some analysis built on it.

As part of the process you will be introduced to an inter-record script function as well as explore ideas to make the model work as designed.

Starting with a staged data table for the preceding load.

Identifying a candidate key to support the model

Create a key for the model, we could leave it here but we shall introduce a frequently used inter-record function in the next lecture

Adding that last key using a inter-record function to finesse the model

This exercise will keep you on your toes!

Solution walkthrough and some new developer tips are discussed.

What happens to dimension data that changes slowly over time ?

If we do not account for this results will be skewed and misreported!

Apply the theory to coding

We have data changes in a key dimension, time for you to adapt the model and visualisations to consider these.

Lets compare notes.

If you cannot source a master calendar to load then we can create one from scratch using Qlik Sense date functions.

Pretty easy to code as well, don't worry I'll explain every step.

I cannot recall a site where there was no requirement for a Master Calendar;  It's a staple !

Roll up your sleeves and code along to make this table load!

To better explain the concepts of this topic we'll look at a segmented data extract request from the BA

Advanced scripting with Segmentation, Mapping Load & In Script Aggregation

In this exercise you will draw on previously taught topics such as If() constructs.

Walk through the code solution

Check our progress with the tier development

In this lecture I will walk through creating a model app read for self service consumption (via the binary load)

This type of model is also referred to as a Qlik Mart

Minimal spec to solve this one, emulating the vagaries of a customer request.

Code walk through

In this lecture I'll summarise the content covered in the section.

In order to stay on track and be consistent with validating data you should aim to establish a plan and a strategy.

As part of the Validation strategy we shall review Synthetic Keys and how to handle them

Continuing from Part 1 - What happens if synthetic keys cannot be easily removed ?

Load tables and decide/implement the strategy to fix the synth key

Did you find this one tricky ?

Another way to look at solving synthetic key challenges but with caution

Let's code the link table and follow a set of steps that are specific in purpose

Using the original baseline KPI's for testing is not going to work out ; hence we need to accommodate this

Based on the synth key solution you coded can you replace it with a link table ?

There are some catches to emulate a real business scenario to make this interesting , did you catch them in the coding.

Solution - Coding walk through

Solution : Testing walk through and integration to the original link table code

The manual says to avoid this type of data structure as much as possible, in my book we should just avoid it with effective model design!

Remedy the script code to remove the loop

Load some data and observe the outcome and remedy the loop

Solution walk though

Using the data model table statistics to understand data integrity

We observed a table statistic that did not look right hence we need to establish this and discuss with the customer

You have to think outside the box for this one

Explore the approach to demonstrating this

A simple yet no less important data validation requirement

Here we can validate our data using an aggregation expression

You can add 2 more KPI object to validate the data

Solution walk through

We can use straight tables to validate file based data

Here is an interesting validation task for you to practice with

Solution walk through

Can we repair/remedy data when issues have been found during ETL and Model validation ?

Coding a solution can become complex in many situations and environments, working with the customer is key

Finalise the steps to fix the data

Building on the previous lectures here is a chance for you to complete the load script and solve the challenge

Solution walk through

How the QSBA modules are structured

In this lecture I'll summarise the content covered in the section.

Qlik is more than Qlik Sense , there are so many products in their suite we should summarise these and their potential use case.

What is the Binary Load and how useful is it ?

Generate insights to get going with your data without any knowledge of it at all !

Exploring master items and their application

Create a drill down hierarchy as a master item, set segment colours , tags and more

What does this mean ?

A discussion around responsive design and how you should consider this in your app

A summary of the Qlik Sense deployment options

How to prepare questions to ask the customer/user when analysing their requirements

Before defining the tech requirements it is worth reviewing the business objectives to ensure they are still relevant

Translate the business objectives to technical requirements [D] Dashboard a component of D A R 

We will identify ...

# Dashboard objects

# Data Requirements & Model concept

# User types and security requirements

Translate the business objectives to technical requirements [A]nalysis a component of D A R 

# Review business objectives for customer specified changes

# Establish the technical requirements

# Document the business rules

Translate the business objectives to technical requirements [R]eporting a component of D A R 

We will identify ...

# Review business objectives for customer specified changes

# Establish the technical requirements

# Document the business rules

An informal requirement based on a request from the user for a basic summary of metrics