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: 
charlotte_qvw
Partner - Creator
Partner - Creator

Link table v Concatenate for fact tables

Hi, I'm trying to decide which model to use and would appreciate any advice -

1. Concatenated multifact table with different fields forced to concatenate and different measures

or

2. Multiple different fact tables with a link table

So far I've been using a concatenated multifact table as was recommended this as best practice. But am interested to know if this is best, what other people do and what are the pros and cons that others have come across.

Thank you

Charlotte

9 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Charlotte,

I think you might find this blog post and document useful.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

This question comes up every time you build a new data model so it's good to have an understanding of how each approach can work for you.

Personally I generally favour a link table. You keep your fact tables isolated from each other, your tables are more densely populated, and I think the data model is easier to navigate. However the number of tables in your data model is increased.

Marcus

Anonymous
Not applicable

Along with providing a better table view, one of my main reasons for using a Link Table is to handle different levels of granualirty. This blog post presents a solution to that within one Fact table though. http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

Comment on performance considerations are included in the comments to this blog post: http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join#comment-5658

Jonathan

Anonymous
Not applicable

Unless "Link" design is necessary, I use plain "Concatenate".


Given that I have to work mostly with CRM data which is often complex, most application are "Link".  It gives more felxibility dealing with the complex data relations.  But it makes application "heavier", and adds more hops between the tables.  I have to tell that even with the "Link" structure, I concatenate Facts into one table anyway.  So, the question is "to Link or not to Link", because "concatenate" is always there.


Concatenate without Link is "lighter", hence better performance.  I try to use it if data relations allow this.

Reagrds,
Michael

charlotte_qvw
Partner - Creator
Partner - Creator
Author

Hi Michael, Thanks very much. I can't mark as helpful as I used 2 already! But I agree with your thinking...

Anonymous
Not applicable

No problem Charlotte, I have enough points already 🙂

charlotte_qvw
Partner - Creator
Partner - Creator
Author

ha!

hic
Former Employee
Former Employee

I agree with Michael. In my experience, a concatenated fact table has better performance.

HIC

paulyeo11
Master
Master

nice post

alis2063
Creator III
Creator III

What is disadvantage of Concatenate ??