Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions - Page 8

As we have seen, the unbalanced, single-table nature of the underlying source for the parent-child dimension mandates special considerations in its creation. Fortunately, the Dimension Wizard facilitates ease in the design of these special dimensions in a largely prompted process. We are also granted a considerable degree of latitude in the presentation of the levels in our parent-child dimensions.

Let's examine some of the steps we can take to control presentation of the HumanResource dimension that we have created.

1. Click the "+" sign to the immediate left of the HumanResource dimension in the cube tree to expand it.
2. Select Employee ID level below the HumanResource dimension.
3. Click the Advanced tab in the Properties window.
4. Click the Level Naming Template property.
5. Click the ellipsis (...) button.
6. In the Name field, type the word President.

As shown in Illustration 28 below, when we begin typing in the Name field, to the right of the asterisk (*) symbol, we notice that a new line appears directly beneath the line in which we are typing, where the asterisk moves. The field occupied by the asterisk on the line in which we are typing becomes a 1. This is similar to the behavior of an MS Access database when we type in similar fashion to a table therein.

7. Type in the following Names for the corresponding levels in the Level Naming Template property as above:

Level Naming Template
Name Field Setpoints
Level No.				Name
1				President (already entered)
2				Sr VP
3				VP
4				Manager
5				Asst Manager
6				Supervisor
7				Operative

Table 2: Level Naming Template Setpoints for Name Field

After adding the items in Table 2 above, the Level Naming Template input box will resemble that shown below:

Illustration 28: The Completed Level Naming Template Input Box (Partial View)

8. Click OK.
9. Click the HumanResource dimension in the cube tree of the Cube Editor to select it.
10. Click the Advanced Tab in the Properties pane, underneath the cube tree of the Cube Editor.
11. Change the All Level property to No, as shown below:

Illustration 29: Removing the All Level from the Display

12. Click the Data tab to initialize the Preview pane on the right side of the Cube Editor window.
13. Expand the President and all lower levels by double-clicking each level heading.

The preview pane appears similar to the one partially illustrated below:

Illustration 30: The Preview Pane (Partial View) Shows Results of Our Modifications

There is one extended refinement that we would need to make in a "real world" scenario, however. Even though we have renamed the various levels in our example to more accurately reflect the names with which the information consumers might be familiar, we need to build in logic to handle the unbalanced hierarchy for our presentation to be accurate. Our source table, employee, contains a field that indicates the level at which any given employee actually exists in the food chain. Employee 51, Brian Binai, and employee 52, Concepcion Lozada, shown as VP's in our display, actually belong at lower levels (HQ Finance and Accounting). Because the recursive hierarchy we have discussed places these with others reporting to the Senior VP, our basic level handling structure would conclude that they are VP's, simply because they report to a Senior VP. When there are "skipped levels" in between some of the employees and the supervisors to whom they report, special steps need to be taken to allow us to simply display reporting hierarchy, and to enhance it with the level information built into the display.

This scenario would be easily handled within the Skipped Levels property of the Employee ID level, if we had a field in the source data to tell the number of levels to skip. The Position ID number that appears in our employee source data table (we can review the data by clicking the Schema tab, right-clicking the heading of the table, and selecting Browse Data) might be useful in deriving this value, but will not suffice on its own. Hence we discover yet another item to investigate in the source data in our real world environments, prior to assuming we have all we need to create a cube.

As we have taken the customization of the display about as far as we can within the scope of our lesson, let's look at another important concept: enabling values for dimension level members.

Page 9: Enabling Values at a Parent Level

See All Articles by Columnist William E. Pearson, III