SSAS: Consider Cube Browsing when Building Role Playing Dimensions

A Role Playing Dimensions reuses its definition to represent same concept in different contexts. Common example of Role Playing Dimension is date dimension. For instance, date dimension can be used to represent order date, delivery date and shipment date. Currency, staff, organisation hierarchy are other dimensions that are usually role played. While Role Playing Dimensions promote dimension reuse and share dimension data security, the effect on cube browsing must also be considered.

If let’s say a role played date dimension is used as filter in cube browser, you would see all possible date members that the dimension can possibly hold irrespective of whether there are facts for that date (Screen Capture 1). Because of their generic nature, dimensions that are role played must cover all possible ranges of data. As you can see, the lengthy filter list where the user is unsure which dates have facts is bad for cube browsing and could potentially turn-off users. With Role Playing Dimensions also consider Dimension Data Security which is slightly different from regular dimensions.

Screen Capture 1 – Role Played Date Dimension

Now compare this with a Non-role Playing Dimension that is customized to display only the date members that have facts associated with it (Screen Capture 2). Isn’t this a better cube browsing experience? You could achieve this by using a named query as data source for the dimension. If the query is to be used in different projects use a materialized database view instead.

Screen Capture 2 – Non-Role Playing Dimension

However take note if not careful, this would lead to creating more dimensions than what is required. You could for instance take a hybrid approach and customize only the commonly used slicers to non-Role Playing dimension and can leave the rest as Role Playing Dimensions. The key point to remember is whether the dimension is role playing or not, as long as it is derived from same source it must be a Conformed Dimension. This means the name and number of the attributes, key column, name column & value column, attribute relationships, sort order and hierarchies must be the same between the role playing and non-playing dimensions derived from same data source. The only difference is the number of members in a non-Role Playing dimension is fewer than the Role Playing Dimension.

Benny Austin

8 thoughts on “SSAS: Consider Cube Browsing when Building Role Playing Dimensions

  1. Do you happen to know if the following Scenario has a solution:
    Say i Have 100 Customers that get into a Dimension, and i filter for the current Month, and now only 12 of them have Values, currently when i try to filter out specific Customers, i get 100 as oposed to the 12 that could really concern me.

    Thanks again

    1. You should preferably build your dimension from a database view which gives you the flexibility to exclude unused dimension members. Refer to Option 1: Use a View on Dimension Table of this post By using this method you can eliminate customers that are never associated with your fact table. If the customer is referred by your fact table even once, it will appear in the filter irrespective of the month you are browsing.

  2. Hi there, could you please Show some Explanation as to how this filtering of Dimension members that have no Measure Values is done in SSAS
    Thanks a lot! your help is much appreciated

  3. Benny – Just a thought. If showing too many members (including members not associated to any facts) is a problem, it is not limited only for the role playing dimensions, but also all dimensions in general. Any attribute would always show the members regardless of whether there is a fact for it or not. Isn’t it?

    1. Thanks Pavan for your comments on this blog. Yeah you are right – by default dimension attributes displays all members irrespective of whether they are associated with fact or not. However by using views you could eliminate unused dimension records and improve user experience. This is possible if the dimension is non-role playing. Although this is possible for role-playing dimension, its not as effective.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s