SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 2) - Parent-Child Dimensions

9/17/2012 7:14:20 PM

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.


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).

Figure 7. The Select The Main Dimension Table page of the Dimension Wizard

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.

Figure 8. The Define Parent-Child Relationship page of the Dimension Wizard

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).

Figure 9. The dimension designer’s Completing The Wizard page, with a parent-child dimension selected

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.

Figure 10. The dimension designer Browser tab with our parent-child attribute selected

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.


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.

Figure 11. The dimension designer Browser tab with generic level name highlighted

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.

Figure 12. The Level Naming Template dialog box

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.

  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - Database Maintenance
  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - SQL Server Components
  •  Microsoft Visual Basic 2008 : Processing and Storing Data in SQL Server 2005 - Optimizing the LINQSQL Class
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 4) - Direct Data Access - The DataReader
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 3) - Direct Data Access - Creating a Connection, The Select Command
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 2) - SQL Basics
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 1) - Understanding Databases, Configuring Your Database
  •  SQL Server 2008 : Failover clustering - Installing a clustered SQL Server instance
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us