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. Mark