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.

Change default SQL instance to another version

I happened to install SQL Server 2000 on the machine where I already had SQL Server 2008 as default instance. I forgot to install SQL Server 2008 as named instance, so it actually overwrote SQL Server 2008 default instance. After installing SQL 2000, I was able to use SQL 2000 as default instance. After a few testing, I decided to uninstall SQL 2000 but there was some failure to do so because somehow uninst.isu cannot be found.

I didn't have much to dig into it since I need to use SQL 2008 right away. So I tried to change default SQL instance to SQL 2008. Of course this is not recommended by Microsoft and never will be. Please note this is only to change default instance location and does not uninstall SQL 2000, which should be done later seperately.

The steps are:

1) Stop SQL Server and SQL Agent services
2) To change default instance location, I did change the ImagePath of the following registry.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\ImagePath

Old path pointed to SQL 2000 and I changed it to:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

3) To change default SQL agent instance, I also change this registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT

Old path pointed to SQL Agent 2000 path and changed to :
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlagent.exe

4) Start SQL Server and SQL Agent services.

It worked for me, but might not work for you. I am documenting this for my future reference :-)