Qlik circular references and synthetic keys

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about Qlik circular references and synthetic keys.

This question proved to be a little easier than last week’s question, as most of the respondents got the right answer.

The correct answer is C

Adding the PRODUCTMATERIALS table using the provided script will result in both a synthetic key as well as a circular reference.

You may wonder: is this a bad thing? In the case of a circular reference, in 99.999% of cases, yes. In the case of a synthetic key, probably. Let’s look at each in some more detail.

Qlik circular references

A circular reference occurs when there is more than one path of association between two tables. This is illustrated in the figure below, where a circular reference exists between the ORDERS, PRODUCTMATERIALS and CUSTOMERS tables.

There are now multiple paths between the fields in the tables. For example, the path from the field Country to Function can go directly from the ORDERS to the CUSTOMERS table, but an alternative route via PRODUCTMATERIALS is also possible. This creates an ambiguous situation, depending on the route followed the results may be different.

Qlik handles this ambiguity by creating loosely coupled tables. In this case the ORDERS table is loosely coupled. You can recognize this by the dotted lines used for association.

What are the practical implications of a circular reference?

Selections made on fields in tables that are loosely coupled will not cascade to the rest of the data model, and vice versa. In the example above, a selection on the Country field in the ORDERS table will not filter any data in the CUSTOMERS and PRODUCTMATERIALS tables. Neither will selections made in the CUSTOMERS and PRODUCTMATERIALS tables filter any of the data in the ORDERS table.

Circular references are not just confusing for users, as they are typically also caused by incorrect data modelling it’s best to remove them from your data models.

How to resolve a circular reference

A circular reference is resolved by ‘breaking the chain’, which means that at least one association in the circle needs to be removed. In the example above, we have 3 candidates:

1. ORDERS to CUSTOMERS, based on the %CustomerID field
2. ORDERS to PRODUCTMATERIALS, based on the %ProductID field
3. CUSTOMERS to PRODUCTMATERIALS, based on the Name field

When we carefully consider each of the associations, we notice that the third association, based on the Name field, is incorrect. In the CUSTOMERS table, it’s the name of the customer, while in the PRODUCTMATERIALS table, it refers to the name of the product material. These are two very different things. They shouldn’t have the same name and certainly shouldn’t be used to link two tables.

We can correct this issue by giving both fields a different (unique) alias that more accurately describes the contents of the field:

Once reloaded, the circular reference is resolved.

Next, let’s take a peek() at the synthetic key!

Qlik synthetic keys

When two or more tables have two or more fields in common, a synthetic key is created between the tables. A synthetic key is an anonymous field containing all possible combinations of the composite key, the combination of the fields common in the tables.

In the example shown below, both the PRODUCT and PRODUCTMATERIALS tables contain the fields %ProductID and Price. This causes Qlik to create a synthetic key, identified by the $Syn 1 Table name.

How to resolve synthetic keys in Qlik

Synthetic keys are often caused by data modelling errors. It’s highly recommended to remove them from your data model. There are multiple ways to resolve a synthetic key. The flow chart below, from the book QlikView for Developers, provides an easy process for diagnosing a synthetic key and selecting the right solution.

If we apply this flow chart to the example above, we get:

1. Should the two tables be linked? Yes, we do want to link the PRODUCT and PRODUCTMATERIALS tables.
2. Decide which field(s) form a unique key between both tables. The %ProductID field tells us which PRODUCT a PRODUCTMATERIAL belongs to. Upon close inspection, the Price field means something different in both tables. One details the price of the product material, the other the price of the product. We do not need this second field.
3. How many field form the unique key? Only one
4. For the field(s) that should be left out of the key, do we need them in our app? Yes, we want to see prices for individual product materials, as well as for complete products.
5. Rename those fields to prevent associations. And that’s what we’ll do!

After renaming the Price field in both tables, the synthetic key is resolved:

At the top of this article, we said that you’ll probably want to resolve your synthetic keys. Does this mean that you don’t always have to fix them?

No. Synthetic keys don’t always need to be resolved.

Sometimes, a combination of two fields or more provides exactly the association you want, for example when using a link table. John Witherspoon’s classic Should We Stop Worrying and Love the Synthetic Key? post on the QlikCommunity argues convincingly that sometimes, at least performance-wise, it is unnecessary to remove synthetic keys.

However…

At Bitmetric we’re of the opinion that synthetic keys should always be removed to make it easier to understand, read and maintain the data model. Whenever we see a data model that contains one or more synthetic keys, it always forces us to think:

Keep it simple, remove your synthetic keys!

How to deal with these types of questions on your Qlik certification exam

It can sometimes be hard to visualize the result of these types of questions in your mind’s eye. If you’re unsure of the answer, it may help to use the sketch pad and draw lines between the tables.

That’s it for this week, hope to see you again next week!

More from the Bitmetric team