Role Playing Dimensions Materialized and Aggregated? Yes.

Ok. We have an official word and it is good news.  Role playing dimension are materialized and aggregated and there was just an error in the Project REAL docs. You can read about it in the comments to this post and also on Chris Webb’s blog.

I have to admit that I probably should have thought twice about this statement and had a little more doubt.  Not being able to aggregate on a role-playing dimension would be a big blow to the product.



Role playing dimensions are dimensions that are defined once and then used multiple times in a cube.  One example might be a date dimenion.  You create one date dimension on top of one relational table and add that to your dimensions folder in your project.  In your cube you have two roles for this dimension to play, sales date and ship date.  You add the dimension to your cube and for each one, you select the key that it will join to in the fact table.

There is one thing that should be noted when doing this.  If you create your dimensions this way, they are not materialized.  This means that no aggregations will be stored for that dimension.  Since the dimension plays two roles, we can’t store two sets of aggregates for it.  This does save space, but at a performance cost.

One way to materialze your dimensions is to create a named query in the data source view.  You can select * from your table and then rename the table and the key column so that it matches your fact table.  This will also create the primary key on the named query.  Then go through and re-create your dimensions but create one dimension for each new named query.



4 Responses to Role Playing Dimensions Materialized and Aggregated? Yes.

  1. John says:

    Hi Mark,

    That is disappointing. We designed and built a warehouse with a big cube on top of it (about 15 measure groups). The performance seems to be fine. Performance easily overrides cube processing time in terms of priority for us. However, if role-playing dimensions are not aggregated – and we’d known about it before – we would have done things differently! A couple of the dimensions play over 15 roles. Shame Microsoft didn’t make this clearer as this is the first time we have heard of it!


  2. Hi Mark,

    I have to question this… I don’t think your statement about no aggregations for role-playing dimensions is correct. When looking at the aggregation design for some of my cubes, attributes from role-playing dimensions are indeed part of some of the designed aggregations.


  3. Deepak Puri says:

    Hi Mark,

    I would second what Michael says – looks like the standard Adventure Works cube has aggregations for the different cube roles of the Date dimension (based on my limited knowledge of the .partitions XML file format).

  4. Akshai Mirchandani says:

    Hi Mark,

    AS uses “cube dimensions” for building aggregations — so role-playing dimensions aren’t a problem for aggregations.

    The Project Real documentation is talking specifically about Referenced Dimensions (“Implement five reference (or role-playing) dimensions with a single Vendor dimension. “). If you define a dimension as an “indirect” or “unmaterialized” reference dimension then it cannot participate in aggregations.

    Approach #5 should really read “Implement approach #4 but make the referenced dimensions materialized.”

    Hopefully this clarifies.


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

%d bloggers like this: