The preceding procedure requires the maintenance of the source tables for the Department, Team, and User. It is possible, as an alternative, to create a Dynamic Hierarchy from existing exposed user Dimension tables, using table views, to allow the relinking of related user Dimensions. This has the advantage of being a self-maintaining hierarchy.

In the following example, Company Name, Account Number, and the User dimensions are linked to create a Hierarchy as follows.

SQL is used to create new views containing the unique values for each Dimension, allowing the necessary link fields to be created to support the required Hierarchy:

Company Name

CREATE VIEW dbo.DMCompany_List AS

SELECT DISTINCT dbo.D_DMDataField_CompanyName.CompanyName_key,

CASE dbo.D_DMDataField_CompanyName.CompanyName_key

WHEN 0 THEN ‘No Company Defined’

ELSE dbo.D_DMDataField_CompanyName.CompanyName

END AS CompanyName

FROM

dbo.D_DMDataField_CompanyName INNER JOIN

dbo.F_DMQueueLoad ON dbo.D_DMDataField_CompanyName.CompanyName_key = dbo.F_DMQueueLoad.VMAE_CompanyName_key

WHERE

(dbo.D_DMDataField_CompanyName.CompanyName <> ”)

Account Number

CREATE VIEW dbo.DMAccountNumber_List AS

SELECT DISTINCT dbo.D_DMDataField_AccountNumber. AccountNumber_key,

CASE dbo.D_DMDataField_AccountNumber. AccountNumber_key

WHEN 0 THEN ‘No Account Defined’

ELSE dbo.D_DMDataField_AccountNumber. AccountNumber

END AS AccountNumber,

dbo.F_DMQueueLoad.VMAE_CompanyName_key

FROM

dbo.F_DMQueueLoad INNER JOIN

dbo.D_DMDataField_AccountNumber ON dbo.F_DMQueueLoad.VMAE_AccountNumber_key = dbo.D_DMDataField_AccountNumber.AccountNumber_key

WHERE

(dbo.D_DMDataField_AccountNumber.AccountNumber <> ”)

NoteIn the preceding SQL, the use of the associated Fact table CompanyName Key field to provide the hierarchical link to the top-level Company name dimension.

Users

CREATE VIEW dbo.DMUser_List AS

SELECT DISTINCT dbo.D_DMUser.DMUser_key,

CASE dbo.D_DMUser.DMUser_key

WHEN 0 THEN ‘No User Defined’

ELSE dbo.D_DMUser.UserName

END AS UserName, dbo.F_DMQueueLoad.VMAE_AccountNumber_key

FROM dbo.D_DMUser INNER JOIN

dbo.F_DMQueueLoad ON dbo.D_DMUser.DMUser_key = dbo.F_DMQueueLoad.DMUser_key

WHERE

(dbo.D_DMUser.UserName <> ”)

NoteIn the preceding SQL, the use of the associated Fact table AccountNumber Key field to provide the hierarchical link to the middle-level Account Number dimension.

The three new views can now be linked using the Dimension wizard.

Select the Snowflake Schema.

Select the three new views created for the hierarchical “Company Accounts” Dimension.

Link the associated key fields to give the required hierarchical association.

Select the Name Fields for each level in the hierarchical Dimension.

Select the key fields to use at each dimension level.

Enter the Hierarchical Dimension name.

Figure 5-100The multilevel “Company Accounts” Dimension

Add the new (“existing”) shared Hierarchical Dimension to the Queue Load fact table using the cube editor.

After adding the “CompanyAccounts” Dimension and reprocessing, the results can be displayed.