Data model with one principal fact table and fact tables extensions

[sdavinblanc]

Hi,

I would like to know if that kind of modelisation is ok with Qlik (see pictures attached) ?

Some details : the TBL_FACT has 236 columns today and she's automatically load with 'LOAD *' in all our applications and it's not possible for me to change that. (all the application has been developped before my start in that company)

I have to add 70 more columns necessary for only one application i have to update with all that measures.

My idea i to create a secondary fact table with all that new measures linked with my principal on [id_ligne] (in 1:1 relation) instead of adding 70 more columns in the principal TBL_FACT. I'm afraid it will slow down all our application where the TBL_FACT is used because of the LOAD *. if I add the new columns.

First question : is it a correct idea to do that ?

Other question : in the futur if i have to add another third fact table link to the principal do I have to keep the same key (id_ligne) on the three fact table (like in my pictures).

Thanks for your help, hope my explanations are clear !

[WeLoveQlik · Accepted solution]

Hello,

You may want to consider joining those tables together with a Left Join if the relationship is truly 1:1.

This should help with performance of the app.

The end result should be 1 wide table. Just make sure you are not losing or doubling records along the way

[sdavinblanc]

Hi, It was my first idea to join them but is it not a problem in term of performance to have a fact table with 306 columns loaded completely in all our application ? 

[WeLoveQlik]

No, in my experience this should actually help performance!

[sdavinblanc]

Ok, thank you for your answers

[marcus_sommer]

I suggest not to discard a change of the fact-loads too easily. Yes, of course it will need some efforts but maybe less as it look like. For example by displaying them in a UI table with $Table and $Field, exporting it to Excel and there grouping the fields into logically and/or application related field-clusters which are there string-concatenated and copy & paste to Qlik and assigned to n variables.

The pure technically part of it is not very difficult - the checks which field has which information for what reason will probably need more invest. Also the checks which fields are really in usage is no big magic and runs with the debugger an few records will quickly show if some were missing and/or the document analyzer might be used: Solved: Qliksense Document Analyzer - Qlik Community - 1986299 

Such variables keep the load-statements readable and not each application must be cluttered with all fields - which also save significantly resources in the load-times and the UI performance.

Beside this 236 fields within a fact-table and the mentioned need to add n further fact-tables with a rather huge amount of fields hints for a rather unsuitable data-model - probably with several crosstable-parts. If so it should be considered to transform them per The Crosstable Load - Qlik Community - 1468083 into a stream-structure.

In regard to your origin question - you shouldn't try to link these fact-tables (it would cause a lot of trouble to connect them with the dimension-tables by avoiding circular references and synthetic keys) else they should be joined like hinted by @WeLoveQlik .