This is an old slide we used to use for QlikView 11. And it mostly applies for QlikView 12.20 as it stands today.
In QlikView 12, the biggest change has been to introduce the newer QIX engine (the one Qlik Sense used since the beginning) which stores the values in a more columnar way than the associative engine used in QlikView 11 utilized. This affects to resource consumption overall, both storing/writing and retrieving/reading.
My main statement prevails: whatever makes sense to your data. I have applications running every 8 minutes, I have other apps running once every day. Obviously, I cannot apply, even if all best practices in the world say it, the same script for the former than for the latter. In the scripts to run every 8 minutes, I want them to be fast, even if it means doing less transformation in the script and having to do so in the front-end expressions.
However, for the task that runs daily, I can afford to do some more transformations and avoid workload for the front-end.
In addition, experience will tell you when using ApplyMap() instead of JOIN, even if JOIN seems the straightforward technical feature.
With regards to RAM consumption, that seems to be one of your concerns, do the maths following The Importance Of Being Distinct.
Data is stored into QlikView's internal database (and very similar to how QVDs work) in several tables, but oversimplifying, let's say 2 tables: data and symbol tables.
Symbol is each unique value for a field. If field Product has 3 distinct values 'QlikView', 'Qlik Sense' and 'NPrinting', the (internal) symbol table will contain one column named "Product" with those 3 values. Regardless how many times those values are in the source data, those will be stored only once.
Let's say you are reading from a 20 million line server log which stores Qlik product usage. Although there are 20 million lines, those values are stored only once, so again, the internal symbols table will not contain 20 million lines, but only 3.
To each distinct value, a bit stuffed pointer is assigned. Following with the example of the field Product and its values, the symbol table will store 3 lines with values 1, 10 and 11 (base 2). These pointers are also stored into the symbols table.
For the sake of the example then, our symbol table for "Product" looks like
Product, Value
QlikView, 1
Qlik Sense, 10
NPrinting, 11
Definitely, far smaller than whatever the size might be for those 20 million lines.
Then there is the data table, which stores each column with the pointers to its distinct values. We could say the first 10 lines of the logs, speaking from how QlikView stores values internally, could be
Product
1
10
10
10
1
11
10
1
1
1
Now do the equivalent with the rest of the source data feeding the QlikView app. Yet for the 20 million data table, the size is considerably smaller than its source counterpart.
How all this affects which schema should your data model look like and why that consumption "bad" in the slide is plainly wrong, given all the above?
Apply the example to that table, and see how could affect to have Product in a separate table (dimension) or in the fact table (logs). You will still have pointers to the symbols in the data table. Why then not doing it in the first place instead of separating "Products" as a different table in the script instead of keeping it on the fact table?
Because of how your data looks like. Coming from the relational world, making two tables looks like a no brainer. Easier to maintain, cleaner code, faster performance.
However, in the associative world of QlikView, the easiness to maintain is something you, as a developer, will have to decide. If you are performing 20 consecutive JOINs in your script, it will definitely take a harsh time for anyone else to understand where each field comes from, how it looked like in the first iteration and how it looks like in the resulting table.
Also as mentioned, in QlikView some operations are more resource intensive than others, and JOIN is definitely one of the most expensive (utilizes a lot of CPU to calculate the resulting internal data table and memory to the intermediate steps until the final table is calculated). How big are the tables you want to join?
In any small server, joining 20x20 takes probably exactly the same time to compute than 20000x20000. But 2000000x200000000 will take much, much more longer in time, CPU and RAM (during the load! not in the front-end).
If you can code it clean, commenting your script and the server resources permit, I don't see the disadvantage of not JOINing those tables, rather, in fact, the opposite, if you value most the user experience over loading times.
Last but not least, let's consider again the uniqueness of your data. Data from a transactional system with dozens of columns with 4 or more decimal positions will have a much higher uniqueness (meaning, will use more memory and CPU both loading and in the UI) than data from another transactional system with even more columns but integer values. How often the amount of "EUR 10.00" can appear in your data model and how often the amount of "EUR 112.7896"?
While dimensional tables may seem useful to keep separate (because our DBA minds tend to think so), understand your data first and your environment second to decide which model you want to use and why the closer you get to a single "big" table the better it will be for both
- your developers to write cleaner and simpler expressions
- and to your users to consume the data in a performing way.
And then test, test, test.
EDIT: some formatting added.
.png "Miguel_Angel_Baeyens"){kind=avatar}
