TypeId : int
SubTypeId : int
TypeName : nvachar(100)
SubTypeName : nvachar(100)
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.