Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 106

Role-playing dimensions
It's also possible to add the same dimension to a cube more than once, and give
each instance a different relationship to the same measure group. For example, in
our Sales fact table, we might have several different foreign key columns that join
to our Time dimension table: one which holds the date an order was placed on, one
which holds the date it was shipped from the warehouse, and one which holds the
date the order should arrive with the customer. In Analysis Services, we can create
a single physical Time dimension in our database, which is referred to as a database
dimension , and then add it three times to the cube to create three cube dimensions,
renaming each cube dimension to something like Order Date , Ship Date , and Due
Date . These three cube dimensions are referred to as role-playing dimensions; the
same dimension is playing three different roles in the same cube.

Role-playing dimensions are a very useful feature. They reduce maintenance
overheads because you only need to edit one dimension, and unlike linked
dimensions, any changes made to the underlying database dimension are
propagated to all of the cube dimensions that are based on it. They also reduce
processing time because you only need to process the database dimension once.
However, there is one frustrating limitation with role-playing dimensions and it
is that while you can override certain properties of the database dimension on a
per-cube dimension basis, you can't change the name of any of the attributes or
hierarchies of a cube dimension. So, if you have a user hierarchy called Calendar
on your database dimension, all of your cube dimensions will also have a user
hierarchy called Calendar , and your users might find it difficult to tell which
hierarchy is which in certain client tools (Excel 2003 is particularly bad in this
respect) or in reports. Unfortunately, we have seen numerous cases where this
problem alone meant role-playing dimensions couldn't be used.

Dimension/measure group relationships
So far we've seen dimensions either having no relationship with a measure group or
having a regular relationship, but that's not the whole story: there are many different
types of relationships that a dimension can have with a measure group. Here's the
complete list:

• No relationship

• Regular relationship

• Fact relationship

• Referenced relationship

• Many-to-many relationship

• Data mining relationship