A
parent-child hierarchy is a hierarchy in a dimension that is based on two table
columns. Together, these columns define the hierarchical relationships among
the members of the dimension. The first 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.
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 the sales for leaf-level
members are displayed.
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 Cubesfolder
to open Cube Designer for the Analysis Services Tutorial cube.
6.
Click the Browser tab.
7.
Click the Excel icon, and then click Enable when prompted to enable connections.
8.
In the PivotTable Field List, expand Reseller Sales.
Drag Reseller
Sales-Sales Amount to
the Values area.
9.
In the PivotTable Field List, expand Employee,
and then drag the Employeeshierarchy to the Rows area.
All
the members of the Employees hierarchy are added to column A of the PivotTable
report. The following image shows the Employee hierarchy expanded. 
Notice
that the sales made by each manager in Level 03 are also displayed in
Level 04. This is because each manager is also an employee of another
manager. In the next task, you will hide these sale amounts.
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 this attribute. This icon signifies that the attribute is
the parent key in a parent-child hierarchy. Notice also, in the Properties
window, that theUsage property for the attribute is defined
as Parent.
This property was set by the Dimension Wizard when the dimension was designed.
The wizard automatically detected the parent-child relationship.
3.
In the Properties window, click the ellipsis button (...) in the NamingTemplateproperty cell.
In
the Level
Naming Template 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 MembersWithDataproperty
cell, select NonLeafDataHidden to change this value for the Employeesattribute.
This
will cause data that is related to non-leaf level members in the parent-child
hierarchy to be hidden.
1.
On the Build menu
of SQL Server Data Tools (SSDT), 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 theBrowser tab.
3.
Click the Excel icon, and then click Enable.
4.
Drag Reseller Sales-Sales Amount to the Values area.
5.
Drag the Employees hierarchy
to the Row Labels area.
The
following image shows the changes that you made to the Employees hierarchy.
Notice that Stephen Y. Jiang no longer appears as an employee of himself.
No comments:
Post a Comment