Design Relationship screen
Select to store the attribute member in the intermediate dimension that links the attribute in the reference dimension to the fact table in the MOLAP structure. Materializing the relationship is the default behavior to maximize query performance, but at the expense of an increase in processing time and storage space.
Based off this definition, I assumed that deciding whether to use materialize was either choosing to (a) increase query perfomance at a cost of processing time, or (b) decreased processing time at a cost of query performance. I decide to materialize the dimension and clicked the checkbox.
This dimension (Business Group) is based off an Account Manager to Promotion hierarchy. An Account Manager has Accounts, and an Account has Promotions. An Account Manager belongs to a Business Group. Business Group is self referencing which is why it’s a separate dimension. From time to time an Account can move to another Account Manager who may possibly reside under another Business Group. When this happens, the revenue is supposed to "follow" to the new group from that time on and also historically (this is by design and we have other cubes that don’t reflect this behavior). It’s supposed to behave like a changing dimension.
After a few weeks an Account Manager notified me than the revenue wasn’t being transfered in the Business Group dimension when an Account moved to another Account Manager. It was working correctly in the other dimension, just not in the parent child (Business Group) reference dimension.
The first thing I did was process update the Business Group dimension just to make sure it wasn’t a problem of the dimension not getting updated successfully. The result was the same. I also checked to make sure the relationship attributes were correctly defined; not an issue either.
My last ditch effort was to change the dimension from materialized to non-materialized. This worked and the data followed to the proper Business Group.
Frankly this suprised me. Everything I’ve read implied that materializing a dimension only had performance implications, not data. Maybe it was a problem because the reference dimension was a parent child. I don’t know.
I’m not sure this is an error or by design. Either way, I wish MS will be more specific about the effects of setting it.