Data Model: 2 or more fact tables

[#1]

Hi all,

I'm not that used to the star data model yet and kindly ask for your advice on the following matter:

• I have two (later more) fact tables A and B.
• The two fact tables can be linked by concatenating Document ID & Company Code
• USER ID is a common dimension but can have different values in each of the tables.

• Merging the two fact tables into one is not an option because granularity of one of them could become different at a later stage. An additional reason is that we will likely deal with up to 10 fact tables later which would make the one fact table very crowded and hard to maintain.
• Giving the tables unique column names is not an option either because the selection of one of the dimensions (e.g. Company Code) should matter for the second table as well.

Right now I think a link table would be the best option. But how would such a table look like exactly? And what would I do with the User ID dimension?

Thank you for your help!

David

[Anonymous]

Hope this screen is enough. The question is how to change Syn 1 Table to Link table. Create Link table with fields DocumentNo, CustomerKey, Currency Code, Salesperson Code- to link to tables (Detailed_CLE and SalesInvoiceHeader) and then link to SalesInvoiceLine? Or create link table connecting all three tables by DocumentNo field?