Data Model with Multiple Facts?

[microwin88x]

Hello,

I have the following Data Model with a Fact Table for Incomes, a Calendar and other Dimension tables:

The thing is now I'm creating another APP with another Fact Table (Office), the Calendar (where I'm going to have the same periods, for ex, the last 3 years) and Dimensions (Region is the same than in APP 1 and Accounts is new):

In the future I'm going to have a 3rd APP with other common Dimensions (and new ones), another Fact Table (with different metric fields) and the Calendar too.

Is there any way to have all this information together? I mean, all the Fact Tables with the Dimensions and a general calendar?

My goal is to be able to select a Year and a Month and see the Metrics from every Fact Table ($ salesamount from Fact1, $ rateoffice from Fact2) and also being able to filter by the dimensions I have. Could that be possible? Or is it better to have a model for every app?

Thank you!!

[rwunderlich · Accepted solution]

If you want to analyze these facts together (and it sounds like you do) than it's best to load them all into the same app.  You'll have to put a bit of thought into how the fact tables should or should not link together.  With multiple fact tables the most common problem is circular references. An easy solution to this problem is to Concatenate all the facts into a single table.  This can present some limitations if facts fields are selected, they may filter out other facts you want to see. 

In your example I would start by concatenating the facts and see if I can get the result I want. 

-Rob