Perfomance :Linked Table vs Synthetic Keys

[#1]

Hi All,

i have multiple fact tables Call table F_A, F_B,F_C with three common columns as X,Y,Z

and multiple dimension tables.

so it formed a synthetic key in the data model, so in order to resolve synthetic key's i have created a linked table but,

The model with synthetic keys takes much lesser time to load compare to linked table approach and data is also fine in the synthetic key's model,

Can anybody tell me if it is to to have the synthetic keys, as i am getting the correct data with good performance.?

and i wanted to know if trading of performance is alight than having synthetic keys in the model..?

Regards,

Ankit Jain

[gautik92]

Have a look at this

Synthetic Keys

[hic]

If your data model is sound, then the synthetic key is OK. Performance wise, it should be slightly better (not a huge difference) compared to a link table. So if you see a big difference, you should suspect a problem somewhere.

However, I will point you in a different direction: I suggest a concatenated fact table instead. These are almost always faster than both synthetic keys and link tables. The basic structure is

Facts:

Load ... , 'F_A' as Source From F_A (...) ;

Concatenate (Facts)

Load ... , 'F_B' as Source From F_B (...) ;

Concatenate (Facts)

Load ... , 'F_C' as Source From F_C (...) ;

Just make sure that fields that are "compatible" are named the same thing. There could e.g. be a "SalesAmount" in one table and a "BudgetAmount" in another. These should both be named "Amount".

See more on

Concatenate vs Link Table‌

Fact Table with Mixed Granularity

HIC

[marcus_sommer]

Synthetic keys aren't per se wrong. They are an automatically creation of compound keys which you otherwise had to create himself - but it's strongly recommended to do those key-creation manually then only this makes sure that you know your data well enough and understand your datamodel. Further it kept the structures within table-viewer clear which is especially important if you have many tables and many synthetic keys then it will be quite difficult to search and explain unexpected results. Here you could dive deep into the topic: Should We Stop Worrying and Love the Synthetic Key?

Compared with a "normal" associative datamodel is a link-table datamodel more complex and complicated to create and it could be therefore slower in load-times and gui-performance. An easier approach is often to concatenate the fact-tables to one single fact-table to create a star-scheme datamodel - but per default no approach is better, performanter or more suitable as the others, it will be always depend on various things. I think this will be helpful, too: More advanced topics of qlik datamodels.

- Marcus

[#5]

Thank You Henric,

for giving this helpful information,

I had concatenated the  fact tables but it was taking more time to load the data,as compared to synthetic keys approach.

so in my case, i think i would retain synthetic keys approach as that is giving the best performance.

Regards,

Ankit Jain

[hic]

Well, "performance" can mean several things...

To me, the speed of the load process is not very interesting. But the performance when a user clicks in the UI is. And here the concatenated table is better.

HIC

[Shubham_Deshmukh]

Hi @hic ,

Can u suggest me something on this,

https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/Need-help-for-Data-Association-QlikSense/...

Regards,

Shubham