SSAS - create dimension hierarchies for composite primary key table

Creating hierarchical dimension in SSAS is a common task, but if one uses existing table as source and the table has composite primary key it might not be simple task. Let's take an example. Here is an table [TypeTable] that is using composite primary key with two columns - TypeId and SubTypeId.

TABLE: TypeTable
TypeId : int
SubTypeId : int
TypeName : nvachar(100)
SubTypeName : nvachar(100)

Primary Key
TypeId + SubTypeId
In order to build hierarchical dimension from this table:

1) In SQL Business Intelligence Development Studio, open Data Source View design view.

2) Rightclick on the table [TypeTable] and choose [New Named Calculation...]. Key columns are TypeId + SubTypeId but there is no Name column corresponding for this composite key. So new name column should be added. So enter calculation column name and an appropriate expression. In our example, it can be specified as below:

3) Launch new Dimension Wizard from Dimension folder. In Dimension wizard, choose [Use an existing table] and select key columns and [TypeKeyName] in Name column.

4) Go next. In Select Dimension Attributes, select all attributes. Go next. Give [DimType] for diemension name.

5) Now in Dimension design window, click [Type Id] in [Attributes] pane. This is the key column generated from Dimension Wizard. Rename it to [TypeKey] (well, in order not to be confused with TypeId column name) Please note that Key Column, Name Column, Usage properties for [TypeKey] are automatically and correctly set by Wizard. But other attributes such as [Type Name] and [Sub Type Name] are not.

6) Update Key Column, Name Column properties for [Type Name] and [Sub Type Name]. That is, for [Type Name] attribute, specify [TypeId] in KeyColumn and [TypeName] in NameColumn property.

For [Sub Type Name] attribute, specify [TypeId] + [SubTypeId] in KeyColumn and [SubTypeName] in NameColumn property.

7) Now it is time to build dimension hierarchy. Drag and drop TypeName, SubTypeName one by one from Attributes pane to Hierarchies pane.

8) In Attributes pane, you will see [Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.] message when hovering over [DimType] node. To remove this warning, click [TypeName] in [Attributes] pane and change property [AttributeHierarchyVisible] to False. Repeat same thing to [SubTypeName] and [TypeKey].

9) Goto Attribute Relationship tab. Drag [SubTypeName] and drop onto [TypeName]. And click hallow Arrow between TypeKey and SubTypeName and change [RelationType] property to Rigid.

 

Now new dimension hierarchy is built from composite key table.