Still I haven't decided on Data Model. I am in initial stage of Data Model, so would like to get suggestion on how to handle multiple fact table ? Which one is better Concatenate or Linked Table when we have around 10M records per table..
I would rather try to reduce the number of fact-tables by joining, mapping and/or concatenate but it will depend on the data within your tables which method could be applied for which part of your tables. I assume you will need all of the above mentioned methods and if it's not possible/practicable to reduce it to one single table you will need a link-table between them - this meant a link-table between two large fact-tables is better then a or several link-table(s) between multiple fact-tables.
If most of the fields are same then it is better to go with Concatenate with an additional flag to identify the datasource, if a less number of columns are same then go for Link table.
.png "Luminary Alumni"){kind=icon}