Introducing Parent-Child Dimensions
As many of us have discovered from experience, Analysis Services can perform well with other-than-"white-bread" data warehouses as a source for cube building. The standard star schema, consisting of a discrete dimension table for every dimension present, is often an inadequate design for the data repositories of many of the complex organizations and sophisticated business environments that we find today. Variations upon even the snowflake theme often find their ways into the warehouse for various reasons. One of the sets of conditions behind these variations is that of unbalanced hierarchies.
As anyone who has been in the data warehouse/business intelligence field for very long knows, the classic unbalanced hierarchy is represented by the employee/supervisor scenario, where the lowest level members of the dimension under consideration are the employees. Supervisors of employees also exist within the dimension, as employees themselves, and as levels of the dimension, to which groups of lower level employees report. So is born the basis for many recursive reporting "quizzes", and for scenarios used in situations that range from client interviews of prospective consultants for reporting engagements to the well-known examples of ragged hierarchies/recursive relationships perpetually used in books or demonstrations.
In short, we are describing a dimension that differs from the standard snowflake arrangement, where the "child" member exists in an original dimension table, and the "parent", a higher-level member of the same dimension, resides in a separate table. The employee parent member and child member live in the same table, where both exist at the leaf level, and the parent member refers to a new row in the same table.
Our article will focus on the parent-child dimension, where both parent and child share the same table as we are describing. After preparing the environment, we will create a parent-child dimension using the Dimension wizard, which we will discover handles these special dimensions with ease. We will examine the Employee table contained within the FoodMart 2000 sample source database (FoodMart 2000.mdb), touching upon the elements to which we have referred, and then we will practice building a parent-child dimension using the Dimension Wizard.
A Look at the Tables...
To better understand the characteristics that underlie our the need for a parent-child dimension, let's examine the underlying basis for our exercises in this lesson, the Employees table in the FoodMart 2000 database. FoodMart 2000.mdb is installed as a sample when we perform the Typical installation of MSSQL Server 2000/Analysis Services.
1. Start Access 2000.
2. Select the FoodMart 2000 database, located by default in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples directory, by clicking on the ellipses (...) button, to navigate to the .mdb as shown in Illustration 1 below.
Illustration 1: Selecting the FoodMart 2000 Database
3. Highlight FoodMart2000.mdb, and click Open.
The FoodMart 2000 database opens and appears as shown below:
Illustration 2: The FoodMart 2000 Database
4. Right-click the Employee table.
5. Click Design View from the context menu, as shown in Illustration 3 below.
Illustration 3: Selecting Design View for the Employee Table
The Design View appears, as shown below.
Illustration 4: Employee Table-Design View
Every employee in the Employee table has a unique employee_id. The employee_id key provides the basis for the join to the corresponding key in the Sales fact tables that appear in our sample database when viewing it from the star schema perspective. We can see in the table that each employee also has a supervisor_id that identifies the corresponding employee (in the same table) who acts as the supervisor for the employee on which the supervisor_id appears.
The standard snowflake arrangement would be for the supervisor_id to provide the basis for a join that would be placed between this table and another table that contained the supervisor level members. The fact that supervisors and employees are all employees (all exist at the grain level in the Employee table), and that the resulting joins are between the supervisor_id of each employee and the employee_id of the supervisor's employee record, means that an unbalanced hierarchy will be present; that is, various hierarchical branches will contain different numbers of levels.
The Employee table is an excellent example of a parent-child table, where an unbalanced hierarchy exists for the dimension it houses. In many Business Intelligence/reporting engagements where such a table has been involved, I have created an alias of the table for each level of the hierarchy above the grain level, and assigned the desired fields from the aliases tables to the respective levels of the hierarchy upon which I wished to report. In the parent-child dimension we will create from the Employee table, each join will similarly be the basis of a new level within our dimension.
Let's get started by setting up our basic working environment, then we will call upon the Dimension Wizard to assist us in creating the parent-child dimension we have discussed.
6. Close the Employee table-design view.
7. Close the FoodMart 2000 database, and then close MS Access.
Page 3: Preparation for Creating the Parent-Child Dimension
See All Articles by Columnist William E. Pearson, III