Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

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
Highlighted
marcus_malinow
Valued Contributor III

Re: Link table v Concatenate for fact tables

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

jonbrough
Valued Contributor

Re: Link table v Concatenate for fact tables

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

mov
Esteemed Contributor III

Re: Link table v Concatenate for fact tables

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

Partner
Partner

Re: Link table v Concatenate for fact tables

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

mov
Esteemed Contributor III

Re: Link table v Concatenate for fact tables

No problem Charlotte, I have enough points already 🙂

Partner
Partner

Re: Link table v Concatenate for fact tables

ha!

Re: Link table v Concatenate for fact tables

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

HIC

paulyeo11
Valued Contributor III

Re: Link table v Concatenate for fact tables

nice post

alis2063
Contributor II

Re: Link table v Concatenate for fact tables

What is disadvantage of Concatenate ??