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

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!