Defining Parent Attribute Properties in a Parent-Child Hierarchy

A parent-child hierarchy is a hierarchy in a dimension that is based on two table columns that together define the hierarchical relationships among the members of the dimension. One column, called the member key column, identifies each dimension member; the other column, called the parent column, identifies the parent of each dimension member. The NamingTemplate property of a parent attribute determines the name of each level in the parent-child hierarchy, and the MembersWithData property determines whether data for parent members should be displayed.

For more information, see Defining a Parent-Child Hierarchy, Working with Attributes in Parent-Child Hierarchies

Note

When you use the Dimension Wizard to create a dimension, the wizard recognizes tables that contain rows that have parent-child relationships and automatically defines the parent-child hierarchy for you.

In the tasks in this topic, you will create a naming template that defines the name for each level in the parent-child hierarchy in the Employee dimension. You will then configure the parent attribute to hide all parent data, so that only sales for leaf-level members are displayed.

Browsing the Employee Dimension

To browse the Employee dimension

1. In Solution Explorer, double-click Employee.dim in the Dimensions folder to open Dimension Designer for the Employee dimension.

2. Click the Browser tab, verify that Employees is selected in the Hierarchy list, and then expand the All Employees member.

   Notice that Ken J. Sánchez is the top-level manager in this parent-child hierarchy.

3. Select the Ken J. Sánchez member.

   Notice that the level name for this member is Level 02. (The level name appears after Current level: immediately above the All Employees member.) In the next task, you will define more descriptive names for each level.

4. Expand Ken J. Sánchez to view the names of the employees who report to this manager, and then select Brian S. Welcker to view the name of this level.

   Notice that the level name for this member is Level 03.

5. In Solution Explorer, double-click Analysis Services Tutorial.cube in the Cubes folder to open Cube Designer for the Analysis Services Tutorial cube.

6. Click the Browser tab.

7. In the Metadata pane, expand Measures, expand Reseller Sales, right-click Reseller Sales-Sales Amount, and then select Add to Data Area.

8. In the Metadata pane, expand Employee, and then drag the Employees hierarchy to the Drop Row Fields Here area of the Data pane.

   All the members of the Employees hierarchy are added to the Data pane in a collapsed view.

9. In the Data pane, expand the Level 02 column of the Employees hierarchy, and then continue expanding levels to view the members of levels 02 through 05.

   The following image shows the Data pane with levels 02 through 05 of the Employees hierarchy expanded.

   

   Notice that the sales that each manager in Level 04 made are also displayed in Level 05. This is because each manager is also an employee of some other manager. In the next task, you will hide these sale amounts.

Modifying Parent Attribute Properties in the Employee Dimension

To modify parent attribute properties in the Employee dimension

1. Switch to Dimension Designer for the Employee dimension.

2. Click the Dimension Structure tab, and then select the Employees attribute hierarchy in the Attributes pane.

   Notice the unique icon for the attribute. This icon signifies that the attribute is the parent key in a parent-child hierarchy. Notice also, in the Properties window, that the Usage property for the attribute is defined as Parent. This property was set by the Dimension Wizard when the dimension was designed and the wizard detected the parent-child relationship.

3. In the Properties window, click the ellipsis button (...) in the NamingTemplate property cell.

   The Level Naming Template dialog box appears. In this dialog box, you define the level naming template that determines the level names in the parent-child hierarchy that are displayed to users as they browse cubes.

4. In the second row, the * row, type Employee Level * in the Name column, and then click the third row.

   Notice under Result that each level will now be named "Employee Level" followed by a sequentially increasing number.

   The following image shows the changes in the Level Naming Template dialog box.

   

5. Click OK.

6. In the Properties window for the Employees attribute, in the MembersWithData property cell, select NonLeafDataHidden to change this value for the Employees attribute.

   This will cause data that is related to non-leaf level members in the parent-child hierarchy to be hidden.

Browsing the Employee Dimension with the Modified Attributes

To browse the Employee dimension

1. On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.

2. When deployment has successfully completed, switch to Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect on the toolbar of the Browser tab.

3. In the left pane of the designer, select Employees in the Employee dimension, and then drag this hierarchy to the Drop Row Fields Here area. Expand the hierarchy several times to display the first five levels.

   Notice that the level names are now more descriptive and that the sales values for each manager are no longer displayed. Notice also, however, that the total for each level displays the total for each employee, including the hidden amount for the manager. In Lesson 10 you will learn how to enable visual totals so that the total for Employee Level 05 reflects only those values that are actually visible to the user.

   The following image shows the changes that you made to the Employees hierarchy.

   

Next Task in Lesson

Automatically Grouping Attribute Members

See Also

Other Resources

Defining a Parent-Child Hierarchy
Working with Attributes in Parent-Child Hierarchies

Help and Information

Getting SQL Server 2005 Assistance