Hide a specific attribute hierarchy in role playing dimension – SSAS multidimensional

Role playing dimension is a dimension which plays different roles in a fact table. In SSAS multidimensional, a role playing dimension can be used across different cubes and measure groups. Date dimension is the most common example. A date might be appear in various forms such as SaleDate, ReceivedDate, OrderDate, ShipDate etc. Creating a separate dimension for each of these would not be a good approach. Instead, a single date dimension is created and is attached to various measure groups using different keys. In fact, if you have defined a relationship between multiple fact table columns and same dimension key, SSAS multidimensional will add the role playing dimensions automatically as shown below.

AdventureWorksRolePlayingDimension

AdventureWorksRolePlayingDimension

In certain situations, however, not all the attributes of a role playing dimension are relevant to all measure groups or cubes. Let us take an example of Organisation dimension. This dimension has four attribute hierarchies namely OrgnisationKey, Organisation Name, Customer Organisation Reference, and Provider Organisation Reference. Let us also assume a fictional fact table with columns CustomerOrgnisationKey , ProviderOrganisationKey and TransactionAmount. This fact table references Organisation dimension twice i.e. CustomerOrgnisationKey –>  OrgnisationKey and ProviderOrganisationKey –> OrgnisationKey. This means Organisation dimension is a role playing dimension. The table relationship and role playing in SSAS is shown below.

Tabler Relationships and Role Playing

Tabler Relationships and Role Playing

Now, when browsing the Customer Organisation dimension, the hierarchy Provider Organisation Reference doesn’t make much sense. At the same time, when browsing Provider Organisation dimension Provider Organisation Reference doesn’t convey any meaning. There is no apparent way of hiding the non-relevant hierarchy in each dimension. If you hide a hierarchy in the parent Organisation dimension, it would hide it in both the role playing dimensions. This is where I was stuck. As mentioned in one of posts, there are two places where ‘AttributeHierarchyVisible’ property can be set. For role playing dimension, you can do that for each individual role playing dimension from Cube structure tab. So go to Cube Structure tab and you should see Dimensions panel below Measures panel. This will show each dimension and it’s attributes separately. Now you can hide or even enable and disable attribute hierarchies for each role playing dimension. A picture is worth a thousand words, so here’s a screenshot.

Hiding Attribute Hierarchy in Role Playing Dimension

Hiding Attribute Hierarchy in Role Playing Dimension

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s