Hi Guys
I would like discuss an old topic about data model design: Linked table or concatenate?
I read some articles talking about that if the two fact tables are basically quite similar, such as sharing most of the dimensions except for one or two, they should be concatenate.
In the real projects, I also see that there is some implementations that concatenate several almost quite different fact tables into one single fact table based on forcing concatenation mode.
So what is the boundary or criteria to help us to decide when to use concatenate, when to use linked table?
I came from traditional data warehouse world, in my past experience, I follow the rule that you cannot merge two different kind of data into one place. And in the other BI software, it is supported easily. For exmaple, in Cognos, you can easily to seperate different star schema based on different fact tables, you don't need to concatenate them together.
I know in Qlikview, due to the performance consideration, we are recommended to concatenate the tables in some situation.
Personally, I am thinking to concatenate the conformed dimension attributes and measures and use that table to link to the other fact tables with remaining fact specific attributes. In this case we can enjoy the performance advantage of the concatenated tables and also store the fact specfic info efficiently in the proper place.
what you think?
