I was recently asked by a SSAS developer about how to set up a role-playing dimension so I did a brief demo to show how to set it up. In this post, I am document the steps to help you get started:
What is role-playing dimension?
What if you want to see sales data by Order Date as well as Shipped Date? You can one date dimension that is used more than once for seeing sales data by order date and shipped date. In this case, date dimension becomes a Role Playing Dimension.
1. Open SQL Server Data Tools and SSAS project that you are working on.
2. Go to Data Source Views from the solution explorer.
3. Make sure that the relationships are specified between dimension (e.g. date dimension) and Fact Table (e.g. Fact Sales). Example: If you’ve OrderDateSK and ShippedDateSK in your fact table, then establish a relationship between the both columns with the Primary Key of the Date dimension.
Note: If you do not have the relationships specified in the data source views, there’s an alternative to go to dimension usage for a cube and setup role playing dimensions. since this is targeted to get you started, I am not covering the steps for that.
4. Now go to the Dimensions section in the solution explorer and add a Dimension that uses the existing data source view from step #3.
5. switch to Cubes.
Now, if you don’t have a cube created yet then you can use the cube wizard if you want and as long as you have the relationships specified at DSV’s, it should create a role-playing dimension.
Alternatively, you can also create a new dimension. In the Design pane, lower left section, you’ll see dimensions.
select your dimension that you created in step #4 that’s a role-playing dimension from the list and click on OK. And it creates a role-playing dimension for you! So all you had to do was to make sure the relationships are created between the fact and (role-playing) dimension table and SSAS does the rest of the work for you
Note: for the demo I didin’t rename the dimension attributes but you do all that good stuff too when you are building the cube for users!
In this post you got started on how to add a role-playing dimension in a SSAS cube.