Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 7, 2002

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

By William Pearson

Working with Levels in a Parent-Child Dimension

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.














President (already entered)








































Asst Manager


























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

Since the hierarchy in the organization with which we are working has only one person at the top level (Ms. Nowmer), we have no real need for the "All" level in our displays. We can remove it by simply setting the All Level property to No. Furthermore, our Preview pane headings match our new setpoints, customizing the headings to be more appropriate and less confusing within the context of our business environment.

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM