I have a model where in my Project dimension, I have an attribute (AgreedReturn), which is a fixed number.
I included it in my model, all good.
My problem now, is that I have a requirement where users want a SUM of that attribute.
This is very confusing to me... For me, it clearly belongs to the dimension, but, it messes it up that they want ‘math’ applied on it.
What is the right modeling approach to this? Am I forced to move this attribute to the fact somehow?
Numbers in dimensions are not problematic. Including them in calculations is not problematic. For example a Product may have a weight, which you could multiply by a quantity from a fact table to produce a shipment weight.
So your problem is just one of understanding what the users are trying to measure, and figuring out how to calculate it.
And it may well be that it's more convenient to push this attribute to the fact table during ETL, or with a calculated column. But that's a secondary issue.
