Materialize Option in a Reference Dimension

When setting up a dimension with a relationship type of Refence you have the option of "materializing" the dimension.

Design Relationship screen



According to Microsoft, this is the definition of Materialize:

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.

This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Materialize Option in a Reference Dimension

  1. Unknown says:

    I suggest you to post a bug on, they will track the issue without the need to open an incident with PSS.

  2. James says:

    I\’ve been experiencing the exact same issue wih a non parent-child hierarchy.
    I\’d say this is a bug

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s