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 different name. For example, we created a database dimension called ”DimOrderDate” and other date dimensions can be created by using the same database dimension with different names like “DimShipDate”, “DimOrderReceivedDate”, etc. And these remaining date dimensions should be the cube dimensions (In SSAS, under the Cube Structure Tab–>In the Dimension Section–>Right Click on the Cube–>Add Cube Dimension–>Select existing “DimOrderDate”–>Give another name for example “DimShipDate”). 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. Creating one database dimension and others as cube dimensions will also save some memory usage as the cube database date dimension will be processed only once and other dimensions will use the same date dimension.
In this illustrated example, the DimDate table serves as three different roles, the order date, due date and the ship date: