Friday Qlik Test Prep – Week 3 – Circular references and Synthetic Keys

By Bitmetric Admin

Qlik certification test question 3 - 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:

Qlik certification test question 3 - 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.

Circular reference

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 ORDERSPRODUCTMATERIALS and CUSTOMERS tables.

qlik circular reference

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 circular reference

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 areloosely 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:

resolving qlik circular reference

Once reloaded, the circular reference is resolved.

resolving qlik circular reference

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

Synthetic key

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.

qlik synthetic key

How to resolve synthetic keys

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.

How to resolve synthetic keys, a flow chart

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:

qlik resolved synthetic key

Do synthetic keys always need to be 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:

Was the person who created this data model a genius, or an idiot?

People who keep synthetic keys in their Qlik data model

Keep it simple, remove your synthetic keys!

How to deal with these types of questions on your 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.

Qlik certification questions about data modeling, how to handle

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

Previous posts

Week 1: Optimized Load

Week 2: Section Access

About The Author

Bitmetric Admin

Blog posts from the Bitmetric team. At Bitmetric, Qlik is what we do. We've been doing it for years, and we can do it for you! ;)

Leave a Reply

Your email address will not be published. Required fields are marked *

Read previous post:
Free eBook: Get your Data Literacy lights on in 2022!

Data is the new language of business. And company-wide fluency is vital to ensure everybody is making data-driven decisions. Learn...

Close