Parent-Child Dimensions
Before we create a new employee-based dimension, right-click the Employees
named query in the data source view designer and choose Explore Data.
Examine the data shown. You will see that Suyama, King, and Dodsworth
report to Buchanan, and that Buchanan and all other employees report to
Fuller (except Fuller himself, of course). Our task will be to create a
special dimension that represents these relationships, in a hierarchy,
without transforming the structure of the dimension table itself. To do
this, you invoke the Dimension Wizard.
Tip
Adding a new dimension to a cube is a two-step process: You must first create a database dimension, and then you add it to the cube.
To create the database dimension, you must invoke the Dimension Wizard.
You can invoke the wizard by choosing the New Dimension... option on
the Dimensions node shortcut menu in Solution Explorer or the
Project/New Dimension... option on the main menu. However, the New
Dimension option invokes the wizard directly and requires that you later
add the database dimension to your cube as a separate step. You
add an existing database dimension to a cube by using the Add Cube
Dimension dialog box, which you invoke via the Cube/Add Cube
Dimension... main menu option, which is available when the cube designer
is open and its Cube Structure or Dimension Usage tab is active. You
can also select the Add Cube Dimension... option on the shortcut menu in
the Cube Structure tab’s Dimensions pane or on the Dimension Usage tab.
You can invoke the Dimension Wizard directly from the Add Cube
Dimension dialog box itself; this allows you to compress the two steps
into one. |
Accept the default
selections on the Select Build Method, Select Data Source View, and
Select Dimension Type pages. On the Select The Main Dimension Table
page, select the Employees named query from the Main Table drop-down list, select EmployeeId as the key column, and select EmployeeName as the member name column (Figure 7).
On the Select Dimension Attributes page, make sure the check box next to the ReportsTo column is checked. Accept the default setting of Regular on the Specify Dimension page.
On the Define
Parent-Child Relationship page, confirm that the check box at the top of
the page is selected and that the Reports To column is selected in the
Identify Parent Attribute drop-down list, as shown in Figure 8.
The Dimension Wizard
detects that our new dimension might contain a parent-child attribute
and allows us to specify which field (ReportsTo, in this case) should serve as its basis. Note that the ReportsTo value for Andrew Fuller is NULL, indicating that he reports to no one but that several other employees report to him.
Click Next to advance to
the Detecting Hierarchies page, and click Next again when the detection
process has completed. On the Review New Hierarchies page, you will see
that no hierarchies were detected; this is the expected result, so click
Next to advance to the Completing The Wizard page. Notice the special
parent-child icon displayed next to the Reports To attribute (Figure 9).
Type Org Chart
as the name of the new dimension, and then click Finish. Now open the
new dimension in the dimension designer (you might be placed there
automatically, depending on how you launched the Dimension Wizard).
The Org Chart dimension now contains an attribute called Employees that displays values from the EmployeeName column and is keyed on the EmployeeId column. This attribute will serve as the child level of our parent-child dimension. For clarity, rename this attribute Employee Name by using its shortcut menu (Rename option) or the Properties window (Name property).
The parent-child hierarchy is already defined because the Reports To attribute’s usage is set to Parent.
You can see this from the attribute’s icon, its shortcut menu (the Set
Attribute Usage/Parent option is checked), and the Properties window
(the Usage property is set to Parent). But we still need a little refinement: Set the NameColumn property of the Reports To attribute to (new), select EmployeeName in the Object Binding dialog box (from the Source Column list box), and then click OK. This ensures that, like the Employee Name attribute, Reports To will be properly configured to display the employee’s name and be keyed on the employee’s ID (stored in the ReportsTo column).
Save and deploy your
changes, and then click the dimension designer’s Browser tab (if you are
not placed there automatically). Make sure Reports To
is selected in the Hierarchy drop-down list, and then drill down on
each member in the tree view below it. You should see the entire
reporting hierarchy properly displayed, and the collection of employees
at each level sorted alphabetically, as shown in Figure 10.
The dimension
browser confirms that our parent-child attribute has been correctly
configured, for the most part. Note that Fuller and Buchanan are shown
as reporting to themselves. We will address this anomaly shortly. Select
Employee Name from the Hierarchy drop-down list, and you’ll see all employees listed, alphabetically, in a nonhierarchical fashion.
Note
If you want to eliminate the standalone Employee Name attribute hierarchy, you can do so by setting the Employee Name attribute’s AttributeHierarchyVisible property to False.
Doing this allows you to keep the attribute in the dimension’s
structure—which you must do because it is the key attribute—but hide the
attribute from client applications. |
Now reselect Reports To
from the Hierarchy drop-down list, select the first Fuller, Andrew node
in the tree view, and notice the name Level 02 listed next to the Current Level label just above the tree view, as shown in Figure 11.
Click the first
Buchanan, Steven node and then the Dodsworth, Anne node and notice that
their levels are named Level 03 and Level 04, respectively. These level
names are generated by Analysis Services because the hierarchy is
generated automatically and was not explicitly designed by you.
We can change these level names on the Dimension Structure tab by simply using the NamingTemplate property of the ReportsTo
attribute. In the Properties window, click the property’s ellipsis
button to bring up the Level Naming Template dialog box. Fill out the
dialog box as shown in Figure 12, and click OK.
The Level Naming Template
dialog box allows entry of custom level names for parent-child
attributes/hierarchies. By supplying names for levels 2, 3, and 4, we
will override the Analysis Services–generated names Level 02, Level 03, and Level 04. Finally, to prevent employees with direct reports from being listed twice, set the MembersWithData property to NonLeafDataHidden.
Save
and deploy your changes, and confirm on the Browser tab that the
hierarchy is now structured properly and that its levels are
intelligently named. (You might need to reconnect for the change to
appear.) If you haven’t already done so, add the Org Chart dimension to the Sales
cube using the Add Cube Dimension dialog box in the cube designer, and
then save and deploy your changes once more. Now query the new Org Chart.Reports To hierarchy and any measure of your choice on the cube’s Browser tab (reconnecting first, if necessary).
Drill
down through the levels to see that you can now query the cube data by
CEO, Manager, and Employee. You might also notice that the some of the
level totals exceed the sum of their members; this is because we have
hidden all data associated with the non-leaf members of the hierarchy.
Setting the Reports To attribute’s MembersWithData property back to NonLeafDataVisible would eliminate the totaling problem, but it would cause those employees to be listed twice, as we saw earlier.
