SSAS Security : 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.