Let's say I have a simple data warehouse with the following fact tables:
- FactPurchase: one row per property purchased, with purchase amount, value, etc.
- FactMonthlyStatus: one row per property, per month; with rent paid, total maintenance expenses, etc.
I have some dimensions that apply to FactPurchase (such as property, property type, location) and some dimensions that apply to FactMonthlyStatus (such as occupancy status, condition, and also the month).
I'd like to be able to have a cube set up where I can see facts from both of these tables regardless of the dimensions I select. For example, if I am looking at total rent paid this month (by putting rent paid as a measure in the browser grid and month in the filter), I'd like to be able to see property type as a dimension as well. That is, so I can see rent paid by property type for month X.
In SQL, this would be easy. I join FactPurchase to FactMonthlyStatus on the property dimension key, and then I put month in the where clause, and I group by property type and sum up rent paid. However, I'm not sure how to properly model this in SSAS. It seems that I need to relate the property type dimension to both facts (as measure groups), but that dimension only directly maps to FactPurchase. There is no property type dimension on FactMonthlyStatus. I've tried many to many in dimension usage, and that appears to work at first, but if I had a dimension to my browser (e.g., condition) from the FactMonthlyStatus table, I end up getting, as expected, a many-to-many situation, with all of the used combinations of property type and condition, and not the combinations valid as of the month specified. Referenced also doesn't work because there's no intermediate dimension between my condition dimension and property type dimension. There's an intermediate fact, though.
Best Answer
If you want to see measures from two different fact tables side by side but different granularity, it's possible but not perfect. For example, the measure for purchase amount in FactPurchase has no monthly equivalent.
To do this, it helps to have as many related dimensions between both fact tables. So if you have a dimension such as property type that is related to FactPurchase, I would make that a dimension on FactMonthlyStatus as well. This can be done by adding these to the actual FactMonthlyStatus table and figuring them out in your ETL process or by creating a view that sits on top of FactMonthlyStatus that does a join to lookup these dimensions as they are loaded into your cube. However, you are still going to have dimensions such as month that are at a lower level of granularity than FactPurchase.
The main thing to figure out is how your measures in both fact tables relate to each other. Are you attempting to keep a running total for rent paid and see how it relates to what you paid for the property? If that's the case, it might make sense to create a cube based off a single view that combined the FactPurchase with an aggregate of FactMonthlyStatus to see how they compare.