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.