Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wardzynski
Creator
Creator

What are your comments in terms of performance for this type of data model?

Will renaming the main Key in each table actually improve the performance?

8 Replies
marcus_sommer

I suggest to develop the datamodel in the direction of a star-scheme. It's recommended as the best compromize between handling and performance. Many more could you find here:

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

More advanced topics of qlik datamodels

- Marcus

swuehl
MVP
MVP

Why should renaming a key field in each table change anything?

wardzynski
Creator
Creator
Author

Marcus,

I have the same question as Stefan. In what way would changing the key field name for each table improve the performance? I understand the best practice of data modelling in Qlik, but would like to understand the performance advantage when making this change. I mean, if they all use the very same key, then maybe they could all be joined instead, table by table, which would results in an un-normalized table which would improve the performance, even if the size would be bigger. Am I missing something?

Would a linking table help with the performance?

marcus_sommer

Improving the performance respectively optimizing an application could have quite different directions - load runtimes, RAM and storage space, opening times, UI response times - and you couldn't optimize it in all ways at the same time. Your biggest bottleneck by your system-resources and/or related to your requirements will determine which optimizing approach will have the biggest effect.

If you have no performance issues you might just keep this datamodel probably directly pulled from a database without bigger changes. But if there are performance issues you should reduce the number of tables by joining, mapping and/or concatenating the tables together and to de-normalize the datamodel which in general better worked in qlik than full normalized sql-models. Renaming keys/fields is just one working step to reach the above mentioned transforming and not alone the solution.

To your last point about a link-table datamodel - from a performance point of view it's most often the worst possible approach because the link-tables could become quite large and often larger as the fact-tables itself - this meant I wouldn't recommend such an approach.

- Marcus

wardzynski
Creator
Creator
Author

The bottleneck that I am currently dealing with is the slow performance of the UI, which I want to improve be removing a lot of unused fields (when the new doc analyser for Qlik Sense is out), but also improving the data-model. And I was a bit surprised when I saw the data model that I pasted, which actually is not a data model but merely a bunch of tables sharing the same primary key. I was also on the thinking path where I wanted to join all these tables one way or another and forming a large de-normalised table instead.

I have only used link tables when connecting actuals with budgets, due to the different dates involved, but I was not sure if using a link-table in between all these tables would improve the performance. Is it because the linking table would need to have a pair of the renamed key for each table, which would create a very big amount of rows? Feel free to clarify this for me if you have time.

Thanks Marcus!

marcus_sommer

I think you could remove a lot of fields (at first by commenting them out) even before the document analyzer for qlik sense is released by just leaving those one which you noticed within ui. Are there really some missing then you could comment them in again.

Normally a loading-process should start with: load F1, F2, F3 ... with explicitly specified fields and not with a load/select * from ... especially if the data comes from databases which usually contain a lot of fields which have only a meaning on the database-side respectively to the processes which are stored with it and which have no added value from a analysis point of view, for examples various flags to control anything on the input screen or any table-id's and quite probably a lot of old and since years unused stuff.

Beside this it's important to differ the tables into fact-tables with measures and dimension-tables with just describing informations. The dimension-tables are often not very important from a performance point of view and could be handled later when there are resources to fine-tune the application. Important are the fact-tables and they should be if possible merged by joining/mapping and often much easier with a concatenate (or union in sql). In qlik you could even concatenate tables which are quite different to eachother and they perform usually quite well.

This meant you could concatenate the actual- and the budget-data together. A different kind of granularity could be often solved with quite simple methods. For example it's quite usual to have actual data on a daily-level on budget-data on a monthly-level which could be with makedate() and similar functions converted into a real date.

Your mentioned link-table should be the last approach when all other ideas are out especially if there are any performance-issues.

- Marcus

wardzynski
Creator
Creator
Author

Marcus,

Thank you for another extensive reply from your side.

Yes, I have done the manual drop of fields, even if it can be very time consuming, but it is indeed a good approach, before the Doc Analyser existed, or even the Governance Application.

I do understand the basic principles of a Kimball data-modelling approach that Qlik is based on.
I was just trying to understand from a technical point of view why a linking table would be a less efficient solution from a performance perspective.From my understanding you would create a very big table with all the matching key pairs for each particular table. But shouldn't this allow fewer fields to be engaged in the live QIX association instead of all tables being engaged if you have this same generic key for all tables?

Daniel

marcus_sommer

A link-table needs to include the distinct key-values form all connected tables and this often meant that the link-table itself will become quite big and often even bigger than the largest fact-tables - bigger meant here usually longer in the sense of the amount of distinct field-values what not only meant that there are many values to store else that the pointer between the system-tables and the data-tables will be rather huge.

More to them how qlik handled the data internally could you find here: Symbol Tables and Bit-Stuffed Pointers - a deeper look behind the scenes whereby the general effect is more easier explained here: The Importance Of Being Distinct.

To them comes how the calculation engine worked mostly by generating virtual tables between the included fields and their possible values and when those fields belong to several rather large tables which are connected over multiple loops the needed virtual tables will become quite large. That's a lot better explained here: Logical Inference and Aggregations‌.

- Marcus