SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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:

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Posted by Anonymous on 3 December 2011

Pingback from  Role-playing Dimensions | SQL Server | Syngu

Leave a Comment

Please register or log in to leave a comment.