Role-playing Dimensions
Dimensions are often recycled for multiple purposes within the same database. For instance, a “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a “role-playing dimension”.
Basically, if the same dimension is used more than once with different names in the cube then it is called the role- playing dimension. For example, suppose we are designing a cube which captures purchasing data, we can have multiple dates in this scenario like Order Date, Ship Date, Order Received Date, etc. In these kinds of situations we need to have different date keys stored in the fact tables (like OrderDateKey, ShipDateKey etc…) to get the different date information while browsing the cube.
To handle this situation we do not need to create the “n” number of database dimensions for dates in the cube. What we can do instead is to just create one date database dimension when designing the cube and others can use the same dimension but with a different name. For example, we can create a database dimension called “DimDate” and other date dimensions can be created by using the same database dimension but with different names like “DimShipDate”, “DimOrderReceivedDate”, etc. And these remaining date dimensions are created as cube dimensions (In SSAS, under the Cube Structure Tab–>In the Dimension Section–>Right Click on the Cube–>Add Cube Dimension–>Select existing “DimDate”–>Give another name, for example “DimShipDate”, then go to the “Dimension Usage” tab and define the relationship).
The key thing here is to keep in mind we should have only one date database dimension and other date dimensions should be created as cube dimensions. This means in the cube we will have many different date dimensions but behind the scenes we are only using one database dimension, which prevents duplication of data. This also improves cube processing time as you are only processing one database dimension instead of multiple database dimensions.
In this illustrated example, the DimDate table serves as three different roles, the order date, due date and the ship date:
More info:
Add Cube Dimension Dialog Box (Analysis Services – Multidimensional Data)
Setting role security in SSAS for a role-playing dimension
SSAS: Consider Cube Browsing when Building Role Playing Dimensions
Neatly explained. thanks!!!
I do not understand the physical implementation of role playing dimensions, but cannot imagine that you would safe much on processing time. As per your example each role playing dimensions’ members would still create its own tuple where data needs to be aggregated. (i.e. the group by processing concept would have to run for each one in any case).
I have always understood that by creating role playing dimensions you are simplifying the project layout and the need to create each role playing dimension and its structure (hierarchies, etc.) separately. However, behind the scenes SSAS should create individual structures for each role playing dimension in order to aggregate properly.