In this article I would like to talk about the "Role playing" dimension in SSAS which is commonly used and it is very important to know what the Role playing dimension is.
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 the purchasing data, we can have multiple dates in this scenario like Order Date, Ship Date and 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 is to just create a one Date database dimension while designing the cube and others can use the same with different name. For example we created a database dimension called as "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 (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 database date dimension and other date dimension should be created as a cube dimension. This means in the cube we will have many different date dimensions but behind the scene we are only using one database dimension. Creating one database and others as cube dimensions will also save some memory usage as the cube database date dimension will be processed once and other dimensions will use the same date dimension.
I hope this helps. If you have any questions/ comments please do leave the comment and I will get back to you as soon as possible.
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 the purchasing data, we can have multiple dates in this scenario like Order Date, Ship Date and 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 is to just create a one Date database dimension while designing the cube and others can use the same with different name. For example we created a database dimension called as "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 (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 database date dimension and other date dimension should be created as a cube dimension. This means in the cube we will have many different date dimensions but behind the scene we are only using one database dimension. Creating one database and others as cube dimensions will also save some memory usage as the cube database date dimension will be processed once and other dimensions will use the same date dimension.
I hope this helps. If you have any questions/ comments please do leave the comment and I will get back to you as soon as possible.
