Packt+ | Advance your knowledge in tech

You're reading from Qlikview Unlocked

Product type Book

Published in Nov 2015

Publisher Packt

ISBN-13 9781785285127

Pages 196 pages

Edition 1st Edition

Languages

Concepts

Data Analysis

Table of Contents (16) Chapters

QlikView Unlocked

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Behind Every Successful Project Is a Plan

Building the Correct Environment

Are You Sitting Comfortably? Be More Productive

It's All About Understanding the Data

The Right Data Model Pays Dividends

Make It Easy on Yourself – Some QlikView Tips and Tricks

Improving Chart Performance and Usability

To Deployment and Beyond

Hidden Image List

Index

Chapter 5. The Right Data Model Pays Dividends

In this chapter, we will cover the following key topics:

Synthetic keys and why they're sometimes bad news
Link tables
Avoiding loops in the data model
Simplify, simplify, simplify – never have subtables that you don't need
Data islands, single calendars, and set analysis
Avoiding problems with JOIN

Synthetic keys and why they're sometimes bad news

Whenever you create two or more tables that have two or more fields with the same name, QlikView creates a synthetic key table to join them.

Background

Old school logic has stated for a long time that Synthetic keys in the data model design are bad. This is not always the case, however, and in some cases, it is unavoidable.

How to do it

The best way to avoid Synthetic keys is to create a single field with all the fields concatenated together. This needs to be done in all linking tables. The result would be a cleaner and potentially more efficient design.

The problem with this approach is the amount of memory it can consume. The uniqueness of this new combined field means that the amount of memory used to store it is increased and not just in one table but all of them, as in the following simple example.

Current fields:

Link tables

There are times when it is necessary to have two or more fact tables that have some common dimensions, but they are too big or complex to merge or join in a simple way.

Background

Let's take a typical example of a daily transaction table and a monthly budget/forecast table. They could have common fields that need to be shared as dimensions, such as the Period, Product Type, or Region, but having them in two separate tables would not work.

How to do it

Taking this simple example, we want to be able to connect these tables together so that we can not only select Periods, Product Type and/or Region (Product Type and Region are held in both tables) but also be able to find the forecast figure for the transactions.

Using the following script to read these two tables, you will notice that both alias the Product Type and Region fields so that they don't join, and the script also adds a unique ID field based on the record number (the Date field name is already different in both tables, so...

Avoiding loops in the data model

Loops—or as Qlik calls them, circular references—are to be avoided at all costs as far as QlikView is concerned.

Background

When creating a data model, it can be very easy to create circular references as you add in all your tables. As QlikView cannot handle these in its selection engine, they must be avoided. However, should your data model have them, QlikView "loosely couples" some of the offending tables. This means that the loosely coupled tables will not be restricted in any selection.

How to do it

Sometimes, having performed a reload of your data, you would get the following message:

This indicates that QlikView has detected a circular reference and has loosely coupled some tables in the data model.

If you view your table structure (Ctrl+T), you will notice that there are some dashed lines connecting at least one table; this is where the circular reference has been detected and needs your attention.

In many cases, including the preceding one, circular references...

Simplify, simplify, simplify – never have subtables that you don't need

Does good database design give the best results in QlikView?

Background

QlikView's data model design philosophy doesn't follow that of good database design; in fact, to get the best out of QlikView it is nearly the exact opposite.

For anyone who has been involved with database design, for many years the philosophy has been to have a fully normalized data structure. For those who don't know what this means, in simplistic terms, don't store anything twice. So, if you have, for example, a Manufacturer Name in each Product record, you should create a separate table to hold all the Manufacturer Names and a Key and then just store the appropriate Key in each of the Product records.

The Manufacturer Name will only be stored once in the database, taking up less space.

How to do it

QlikView quite happily works with a fully normalized database. However, from a QlikView perspective, the most efficient data model is one flat file with...

Data islands, single calendars, and set analysis

Quite often within QlikView applications, it is necessary to use the same selection criteria over a number of fields. For example, you may want to have a date selection, which selects Date Ordered on one chart and Date Received on another chart.

Background

Previously, we mentioned circular references. One way in which these are created is when there is a Date selection in multiple date fields. The multiple dates exist so that we can produce charts, for example, using Date Ordered on one chart and Date Received on another.

We could avoid the circular reference by having two selection boxes, but we could then have the issue that only selected records fit with both selections.

The alterative to this is to use the Data Island approach.

How to do it

The way around this is to have a Data Island, where a table is created with no connections to any other table. Have a look at the following script:

Here, we created a MasterCalendar table using the earliest...

Avoiding problems with JOIN

When using the JOIN function within QlikView, there are a few things to remember; otherwise, things can go disastrously wrong. However, it is the best way of cutting down the number of branches in a star data model.

Background

QlikView has four joining types; they are the same as SQL but there are slight differences in the syntax. QlikView uses Inner, Outer, Left, and Right, whereas SQL uses Inner, Full, Left, and Right. Using the right one with the right keys is essential.

How to do it

The easiest way to flatten the data model is to join two tables together into one. For a table where there is a key field and a description, it is very easy to add the description into the main table using a LEFT join (refer to the following section for an example.)

Data is joined using all the matching fields in the Load statement, so care must be taken to ensure that all fields needed are actually in the Load statement and that any field that is not needed as a link is aliased.

A further...