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.

  • Is property type not an attribute of property?
    – brian
    Commented Feb 25, 2014 at 16:17
  • @brian: let's substitute another dimension that would be relevant only to FactPurchase, but not FactMonthly status, for example, purchase channel or seller.
    – siride
    Commented Feb 25, 2014 at 20:30
  • Is there a reason you don't change your model to accommodate the expected use cases? It sounds like you are perhaps missing a reference dimension somewhere?
    – mmarie
    Commented Mar 3, 2014 at 17:55
  • @mmarie: the thing is, I'm not entirely sure what the reference dimension should be. There are shared dimensions -- at the very least, the property dimension applies to both facts -- but that doesn't seem to allow me to use the Referenced dimension usage for other dimensions.
    – siride
    Commented Mar 4, 2014 at 13:48
  • When you say "that doesn't seem to allow me to use the Referenced dimension usage for other dimensions," what do you mean? What steps have you tried?
    – dev_etter
    Commented Mar 6, 2014 at 16:24

1 Answer 1


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.

  • I'm going to try having as many dimensions in common as I can. This will help, but not fix the problem. As for your last paragraph, I don't see why I should need to have an aggregate. If I select a given month from the date dimension that identifies months in FactMonthlyStatus, then I should be able to compare current rent to purchase price. I'm not sure why that requires creating an aggregate table.
    – siride
    Commented Mar 17, 2014 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.