I have 5 fact tables at the time of data modeling i need to concatenate all the fact tables but some of the keys are not common in all the tables so in that case what should i do please give your suggestions on the same.
this is a fairly common situation, and shouldn't really be a problem.
If you can derive missing keys, for example by using a MAPPING table, then this is a good idea.
Otherwise just leave the missing keys as null values. This is valid also as some facts may not have any logical relationship to some of your dimensions. For example if you have Order and Invoice fact types, and Order and Invoice dimension tables it makes sense that Order facts will have a key to the Order dimension but not the Invoice dimension, and vice versa.
In that case you have to force concatenate the tables using Concatenate statement. And it would be helpful if you create an additional field something like Flag to track the source of table. This flag field could be used in the set analysis in the front-end. Like:
If this is UN-Comman fields not effecting your model then you go for concatenate this fact tables. Other way is create the joins between the Fact tables.
Concatenating 5 fact tables will not be an issue as long as you create a flag for each of these fact tables during concatenation, ie. `Fact1` as transactionType, `Fact2` as transactiontype and so on.
As long as these fact tables have common keys to link to their respective dimension tables, this solution should work fine.
