Greetings, Qlik community! Let’s talk about a question which is probably almost as old as Qlik itself- what is more efficient for managing multiple fact tables – Link Tables or Concatenated Facts? Several blog articles and Qlik Community posts were written on the subject, and yet a recent question in Qlik Community sparked my interest once again.

To summarize, user Qlik_William, the author of the question, generated a test dataset with three Fact tables, with 20M rows in each, and up to three common key fields, and he compared the app sizes between the Link Table and the Concatenated Fact approaches. To his surprise, Link Table model appeared to be more efficient than the Concatenated Fact, resulting in a smaller footprint. This is contrary to the popular belief (which I’m guilty of promoting with my Data Modeling and Performance Optimization sessions at the Masters Summit for Qlik and at my own Qlik Expert Classes) that Concatenated Facts are more efficient than Link Tables for large data sets. Here, we have an example of a fairly large data model (60M rows in total), and yet Link Table appears to be more efficient – at least from the memory footprint standpoint – than a comparable Concatenated Fact. Let’s try to understand why.

Link Tables? Concatenated Facts? English, please!

I know that most of you know this terminology very well, and yet I should explain these two data modeling approaches to the rest of the readers that may not be familiar. This is the most important part of Qlik Data Modeling, which I teach in great detail at my advanced classes – did I mention Masters Summit for Qlik or my own Qlik Expert Classes? 😉

Let’s just review these briefly. When we load multiple Fact tables with several shared key fields into Qlik, we typically end up with Circular References and Synthetic Keys. Qlik doesn’t like those very much. To avoid these issues, we can use one of two common approaches.

In the Link Table model, we combine all common keys into longer composite keys, and we create a new table (aptly called “The Link Table”) that contains all the links to the multiple Fact tables and to all the Dimensions. In this model, all Facts remain separate and all tables have only one key field that links them to the Link Table. Link Table models are intuitive, easy-to-build, and easy-to-use. We could stop here, if not for one problem. As our data volumes climbed up, Link Tables earned a bad reputation of being too heavy and too slow for large data sets. That created the need for the alternative approach – Concatenated Facts.

The alternative is to combine all facts into a single Concatenated Fact table. Here, the common fields are populated throughout the whole table, and missing values are padded with NULLs. There is no need for long combo keys. There is no additional table for storing data links. The structure looks like a commonly known Star Schema, and in most cases, it performs “like a star”. These data models can get a bit more complicated, because different slices of the concatenated fact are no longer associated or linked between them. For example, if the Fact table contains Sales and Inventory data, it’s not easy to associate Inventory levels for individual Products with the corresponding Customers that purchase those Products. Or, some important non-key attributes could be missing in some of the data slices. Those issues are called “Broken Links” and “Missing Attributes”. In my Data Modeling lectures, I teach the solutions to these common problems in great detail. Did I mention … ? Just kidding! Moving along.

The Cardinality Factor

Now is the time to mention the fundamental conversation about “the birds and the bees” that every Qlik developer should hear at some point – aka “How Qlik stores data”. I always include this conversation in my Data Modeling and Performance Optimization sessions, because this knowledge is fundamental to understanding what’s “good, bad, or ugly” in Qlik data models.

In order to get the full “spiel”, find a way to my lectures, or read my book QlikView Your Business. In short, let’s just mention that Qlik automatically de-duplicates the data by generating lists of distinct values (“Symbols” in the technical lingo) and by replacing original data tables with Index tables that store tightly-packed pointers to the Symbols. The size of the pointer is the direct function of Cardinality (the number of distinct values in each field). High cardinality hits memory in two ways – the Symbols list gets longer, and the pointer size gets bigger. Since the Index tables contain the same (presumably huge) numbers of rows as the original Fact tables, the size of the pointer becomes extremely important for large data models.

So, how is Cardinality related to the comparison of Link Tables and Concatenated Facts? Actually, it works for both teams, like a rogue intelligence agent. Depending on the situation, it can make one data model smaller than the other one, and vice versa. Let’s see how it works.

Cardinality of Key Fields

The reason Link Tables got their bad reputation is the issue of key fields’ cardinality. For example, you may have a data set with 10,000 Customers, 1,000 Products, and 1,000 Dates. Separately, these three key fields don’t cause any performance concerns. However, once they get combined into a single composite key for the Link Table, the Cardinality skyrockets. In theory, if every customer bought every product every day (every businessman’s dream!), the number of possible combinations would be 10,000 X 1,000 X 1,000 = 10 billions distinct values. Realistically you may only expect a few millions of valid combinations, and that is enough to cause a substantial performance hit with a large dataset.

Let’s try to compute the “cost” of generating such a composite key in a large dataset. For simplicity, let’s assume that our largest Fact table stores 100M rows, and that all possible combinations of the three key fields produced 10M distinct values. The size of the pointer that can cover 10M distinct values, has to be 24 bits, or exactly 3 bytes. Stored 100 million times in the Index table, this field alone will require 300 MB of Index space. Add to it a (smaller) index for the Link Table and potentially for other Fact tables, and you can see how this data model can become way too heavy. In my practice with large data sets, the composite key could use up to 70-80% of the total memory footprint of the app, while it’s added analytical value is zero – this is simply a technical way of linking tables.

Cardinality of Non-key Fields

With non-key fields (attributes and metrics – I’ll call all of them “attributes” for brevity), high cardinality can cause an opposite effect, making Concatenated Facts more wasteful.

With the Link Tables approach, each attribute is only stored once in its own Fact table, and its Index only needs to be stored N times, where N is the size of the corresponding Fact table. This is the most efficient storage solution for these attributes.

With Concatenated Facts, when multiple fact tables get combined into one large fact table, the length of the Index table grows accordingly. With low-cardinality attributes, the impact is minimal, however with high cardinality, the size of the pointer is higher, and it needs to be repeated over a longer Index table, resulting in a larger memory footprint. As an example, concatenating 3 equally sized tables with all non-repeating attributes into one large table will result in tripling the memory footprint of each attribute.

What About Those NULL values? I heard they are harmless…

In the early QlikView days, when computers were running on 32 bit, and the largest fact table could not exceed 2M rows (cute, right?), NULL values were considered harmless. After all, they are not stored in Symbols, and while they still take space in the Index table, who cares about the Index size with only 2M rows?

Today, with fact tables getting as long as billions of rows, NULL values cannot be ignored anymore. Yes, they are still not using any space in Symbols, but their size in a very long Index table can get substantial. And again, high cardinality plays a critical role. Fields with high cardinality require a larger pointer size, hence the waste of storing useless pointers for NULL values gets higher.

Concatenated Tables naturally contain many more NULL values, compared to Link Tables. Having sparce fields, combined with their high cardinality, may cost you more in a Concatenated Fact data model.

Does AUTONUMBER Help With High Cardinality of Combo Keys?

Well, yes and no. Applying AutoNumber to an alphabetical key field replaces long key values with shorter integer numbers. Better yet, when the integer values are consecutive, they are not being stored in Symbols. This ingenious technique (kudos to Håkan Wolgé) saves quite a bit of memory for high cardinality fields. However, these fields are still indexed, and the same cardinality concerns still apply. So, even with the use of AutoNumber, combining several key fields into one high-cardinality combo key is still quite “expensive” for the memory footprint.

So, what about William’s question?

In William’s example, he used 3 key fields that he generated randomly. One key had a single value, which makes it irrelevant to our discussion. The other two keys were allowed to have up to 10 distinct values. Combined together, they produced about 100 distinct combinations – clearly not enough to cause any performance concerns.

On the other hand, a few attribute fields were allowed to have up to 100 distinct values – not too bad either. However, their pointers, repeated over 20M rows in each of the three fact tables, took up some Index space. When the three tables got concatenated into a single 60M fact table, that index space got tripled for each of the fields.

The difference between very low cardinality of the keys, and a larger number of attributes, tipped the scale in favor of the Link Table approach.

I tested William’s randomized dataset in my Qlik Sense Cloud tenant, and I got similar results. With two low-cardinality key fields, Link Table approach appears to be more efficient. However, this is not a realistic scenario. With 60M of facts, you are likely to have much more than 10 distinct values in your key fields.

In my effort to get closer to a real-life scenario, I increased the possible number of distinct values for both Key fields to 1,000. With up to 1M distinct values in the composite key, I was expecting to see a different picture. To my surprise, even then the Link Table model appeared to be smaller than the Concatenated Fact model.

Only when I increased the number of key values to 10,000, I saw the expected outcome – the Link Table required 3 times more memory than the Concatenated Fact.

Let’s examine the details using QSDA Pro (my favorite performance optimization tool, developed by my friend and Masters Summit colleague Rob Wunderlich).

Here is the analysis of the memory footprint for the Link Table version. The two dimensions Dim1 and Dim2 have 10K distinct values, and they occupy about 58MB each. Their combinations produced about 33M distinct values in the two composite keys, each using 850MB (!) in memory.

On the other hand, the three Value fields with 100 distinct values, take up 17MB each.

The total memory footprint of this dataset is 2GB. We can easily see how most of this volume is occupied by the two composite keys.

In comparison, the Concatenated Fact model looks much better, even though the key fields Dim1 and Dim2 take up twice as much memory (105MB each), and all attribute fields take up three times more memory than before. Without the two heavy composite keys, the total footprint is only 630MB.

But wait, there is more!

Then, I applied AutoNumber to all key fields in both models. Concatenated Fact didn’t show much of a benefit – because there was no substantial waste with key fields to begin with. The total savings amounted to 160 KB, which makes total sense – we saved the Symbols from two 8-byte numeric fields with 10K values each.

The Link Table app got reduced in size substantially – down to 1/3 of its original size, almost comparable to the size of the alternative. Each composite key’s footprint got reduced from 850MB down to 199MB! While it looked surprising to me, it also makes sense – with the average width of about 20 bytes and 33M distinct values, the Symbols size of each field should be roughly 650MB. With the use of AutoNumber, that memory can be saved! The total memory footprint of 742MB is still higher than the Concatenated Fact’s 630MB, but the difference is not as substantial as it was before using AutoNumber.

Summary

If you are still reading this, I bet you want to know the answer – which data model is more efficient? And, after reviewing all the pros and cons, you’ll have to agree with me the answer is still “IT DEPENDS”. Let me summarize the findings.

• Both models suffer from inherent waste that should be minimized as much as possible. Link Tables waste memory on large composite keys, while Concatenated Facts waste memory on populating high-cardinality attributes over a longer fact table, and from creating sparce data fields with many NULL values. Null values are not stored in Symbols, but they are indexed, and the index space is wasted.
• Link Tables can be preferable when the data is relatively small and the composite keys produce a low number of distinct values. They might sometimes be preferable for larger data sets when the cardinality of attribute fields can be high, and when the waste caused by NULL values outweighs the waste caused by composite keys. Link Table models are also easier to work with, so generally they should be preferred when they don’t cause performance concerns.
• Inversely, Concatenated Facts perform better when composite keys can be so large, that their waste outweighs the waste of repeated attributes and sparce fields. Concatenated Facts may require additional data modeling efforts, to solve the issues with broken associations and missing attributes, which makes them a bit more advanced for beginners.
• Applying AutoNumber on large-size key fields can help substantially reduce the footprint by eliminating the Symbols space.
• With Concatenated Facts, it’s possible to reduce the waste and the sparsity of fields by combining similar fields into one. For example, Sales data may contain a field called “Sales Amount”, and inventory data may contain a field called “Inventory Amount”. We can combine the two fields into a single field “Amount” and differentiate between the two using the “Fact Type” field that is routinely added to Concatenated Facts. This way, the waste of index space can be reduced for both original fields. Similarly, multiple quantity and date fields could be combined into single fields, further reducing waste.

Then, there are many other aspects that may cause you to favor one model of the other, but that is a subject of another blog post. With that, Happy Qliking, folks, and I’d love to see you at my Data Modeling and Performance Optimization sessions. Two excellent opportunities are coming up in September:

The next session of the Masters Summit for Qlik, in its completely new Cloud Edition, is going to take place in Hamburg, Germany, on September 29. You will learn everything there is to know about the cloud, moving to the cloud, managing the cloud, and also you will hear me talk about “the birds and the bees” – what’s good and what’s bad in Qlik Data Modeling and Qlik Application Performance.

In addition to that, I will teach my own Qlik Expert Class in Vienna, Austria on September 22nd, where I will be focusing solely on “the birds and the bees” – the fundamentals of advanced Qlik application development – Data Modeling, Scripting, Set Analysis and AGGR, and Performance.

And, if you bring your copy of QlikView Your Business, I’ll be happy to sign it for you.

That’s it, boys and girls. Share your experiences, thoughts, and examples in the comments. I’m happy to continue this dialog!

Cheers,

Oleg Troyansky