## James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

### 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 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:

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