The following procedure was used to test the creation of a hierarchical Dimension in a custom cube based on the Queue Load fact table. This was set up to demonstrate the feasibility of using a department-employee table structure allowing the possibility of an employee moving from one team to another, for example.

1) A copy of VMAE database was made to preserve the standard PA architecture as VMAETEST in SQL Enterprise Manager.
2) Create a new cube QueueLoad1 using SQL Analysis Manager.
3) Add Department-Employee Dimension as follows:
4) In SQL Analysis Manager:

a) Select Snowflake Schema from the Add New Dimension wizard.
b) Select the three linked tables for the Department-Employee.
(See the section “SQL Statements for Table Creation and Load to Test the DepartmentName Dimension” with the SQL script used to build and populate the tables.)
dbo.D_Department
dbo.D_Team
dbo.D_User
Click next; this should automatically make the correct joins. Click Next.
c) Select the levels for the Dimension and click Next:
DepartmentName
Team Name
User Name
d) Select the member key columns for each level as
DepartmentName dbo.D_DepartmentName.Department_key
Team Name dbo.D_Team.Team_key
User Name dbo.D_User.User_key
and click Next.
e) In the Select Advanced options, select all three options:
Changing Dimensions option
Ordering and Uniqueness of members
Storage Mode and member groups
and click Next.
f) In the Set changing property option, click No, the Dimension is not changing radio button.

NoteThis MUST be set since, although it requires a full reprocess for changes to the cubes, it is the only option which allows users to exist in more than one team.
(Only click Yes, new dimension is a changing dimension radio button if the members are unique – not the case for users who move teams.)
Click Next.

g) For the Sort option, ensure that ordering at each level is by Name. Leave the defaults as Unique among members at the top level and among siblings at the next levels down and click Next.
h) Select create MOLAP (should be the default) and leave other fields as default.
i) Click Next and then enter the Dimension Name as DepartmentName_Multi (leave the share this dimension with other Hierarchies box ticked). Click the Finish command button.
j) Link the new multilevel Dimension DepartmentName_Multi from the User name key, dbo.D_User.User_key, to the exposed fact table key field, DMUser_key, field name on the Queue Load Fact table.
In the test VMware system used, the field is set as an int to allow linkage to the standard Queue Load DMUser_key field.