BISM – Dimension Hierarchies

As a consultant who specialises in SSAS, I didn’t think that the semantic model could stand up to the UDM dimensional structure. After all, SSAS has hierarchies, sorting and the ability to assign attributes, values and names. None of these options were present in the release of power pivot and I expected the semantic model to be enterprise power-pivot. How wrong I was!


Hierarchies are very easy to implement in denali. In this example, a dimension (if I can use that word) is created for adventure works product groups. As a refresher, the ProductSubCategories are related to ProductCategories which allow the RELATED function to return the product Category name to be shown in the ProductSubCategory table.

Once we have done this, the DimProductSubCategory can be hidden as it is no longer needed by right clicking on the table and selecting ‘Hide from Client Tools’. Once this is done, the DimProductCategory table is grey out

A hierarchy can then be created by selecting the table we want a hierarchy for and selecting ‘Create Hierarchy’ from the popup menu (right click on the DimProductSubcategory table).

If no fields were selected when the hierarchy was created, the hierarchy is created with no levels. Levels can be added now by dragging attributes into the hierarchy.

If fields were selected when the hierarchy was created, they will be included in the hierarchy and can be rearranged by dragging then to a different level.

To clean up the dimension we can also hide fields by highlighting the fields (control + to highlight multiple) and selecting ‘Hide from Client Tools’
Finally, the names for all objects (tables, attributes and hierarchies) can be changes by using the Rename option from the popup. (Right clik àrename).

The the dimension (again, should I be using that word ?) is finished, it looks as excpected in both the designer (below) and browsing the model in SSMS (right).

Perhaps, I’ve been using SSAS for too long but I find this functionality is very intuitive and easy to use. What can I say but well done!

3 thoughts on “BISM – Dimension Hierarchies

  1. Hi..
    can u please help me?

    a task is given to me to find attribute keys of all dimension in a given cube.
    i can identify keys when i open the cube in BIDS but i can identify them in analysis services or programatically.

    will you please help me if thier is any way to get key attribute of dimension through mdx or adomd client?

  2. Pingback: Vorschau Excel PowerPivot V2 (SQL Server “Denali” CTP3) « JJ's Blog

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 )

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