13 Fact Tables: Concat, join, link . . . or resign?! ;-)
I spent many sleepless nights building a data model to accommodate 13 fact tables. During unit test it became clear that the associations needed were not working with the link table I drafted..So, back to the drawing board. Anyone have ideas about how to manage the data in the attached mocked up tables?
It would be require to dive very deeply into your tables to be able to give you concrete advices - therefore it's more generic with the main-goal to reduce the number of fact-tables and if possible to avoid any link-tables and rather to create table-associations.
For this I would concatenate such fact-tables like sales and planning - you could harmonize this by creating a table-source field like 'sales' and 'planning' and the values are within a field value - but even without them and leaving an asymmetrical table with NULL's will work fine. Maybe the coupon-table and the prescription-table could be (after a crosstable-transforming) be concatenated, too.
Other fact- and maybe also dimension-tables would I try to join or to map to these fact-table. I'm not sure if it's possible and also useful to try to get everything in one big fact-table. I mean with them that I have the impression that your datamodel might be in reality two or more datamodels and it could be sufficient to leave them as loosely coupled datamodels (maybe associated with only a date and one further key) to get your required views between them (and be cautious to match not the wrong dimensions within your charts).
Thank you for your response. I am making progress! It looks like I am going to need a calendar bridge, too. Can I have a link table and a calendar bridge in the same model? Or, should I include the date field dimension in my link table?