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
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.
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.
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.