Parent-child dimensions in SQL Server 2005 with Analysis Services MDX

More on Analysis Services and hierarchy dimensions: How to use SQL Server 2008 hierarchyid data type Tutorial: SQL Server 2005 Analysis Services How to use the LEFT vs. RIGHT OUTER JOIN in SQL

 Similarly, if your business serves institutional customers, then each entity could be a "child" of another larger organization or division. So the smallest customer could be a sub-department, which rolls up to a department, group, market, region, division and finally the full organization.

Your reports and analytical views will often need to group such related records together. A common example would be to examine the manager's sales along with the sales of all employees who work for this manager.

Handling such recursive relationships in SQL can be tricky because each record can have a different number of parent records. Sometimes this condition is referred to as unbalanced or ragged hierarchy. For example, let's review a very small "employees" table:

CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](20) NOT NULL,
[Title] [nvarchar](30) NULL,
[ReportsTo] [int] NULL
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
)
ALTER TABLE [dbo].[Employees] ADD
CONSTRAINT [FK_Employees_Employees1] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

Let's say we only have four records in this table, as follows:

In this fictitious organization, Andrew is the "big dog" and his record has no parent because he doesn't have a direct supervisor. Janet's record has one parent, Margaret's record has two and Steven's has three.

How would you retrieve an organizational chart from your employee table? Let's say we want to retrieve records for Steven and his immediate supervisor. That's relatively easy:

SELECT
a.EmployeeID,
a.Name,
a.Title,
b.Name AS SupervisorName,
a.ReportsTo
FROM Employees a
LEFT JOIN Employees b ON a.ReportsTo = b.EmployeeID
WHERE a.EmployeeID = 5

But this only gives us Steven and Margaret. To get the next person in the hierarchy, we'd have to join the Employees table to itself, twice. To get the full hierarchy we'd need three joins, like the following:

SELECT a.EmployeeID,
a.Name,
a.Title,
b.Name AS SupervisorName,
c.Name AS NextSupervisorName,
d.Name AS TopSupervisorName
FROM Employees a
LEFT JOIN Employees b ON a.ReportsTo = b.EmployeeID
LEFT JOIN Employees c ON b.ReportsTo = c.EmployeeID
LEFT JOIN Employees d ON c.ReportsTo = d.EmployeeID
WHERE a.EmployeeID = 5

Result:

This works OK on a fictitious company sample with a handful of employees, but it should be apparent that retrieving a hierarchy with recursive relationships with SQL can get difficult to manage very quickly. The query becomes even more cumbersome if we want to retrieve the sales of Steven along with each of his manager's sales. For each manager, we should include her sales along with sales of her subordinates:

SELECT
(SELECT COUNT(*) FROM employees a1
INNER JOIN orders b ON a1.employeeid = b.employeeid
WHERE a1.employeeid = a.employeeid) AS [steven's sales],
(SELECT COUNT(*) FROM employees a1
INNER JOIN orders b ON a1.employeeid = b.employeeid
WHERE a1.employeeid = a2.employeeid
OR a1.employeeid = a.employeeid) AS [margaret's sales],
(SELECT COUNT(*) FROM employees a1
INNER JOIN orders b ON a1.employeeid = b.employeeid
WHERE a1.employeeid = a3.employeeid
OR a1.employeeid = a.employeeid
OR a1.employeeid = a2.employeeid
OR a1.employeeid = a3.employeeid) AS [janet's sales],
(SELECT COUNT(*) FROM employees a1
INNER JOIN orders b ON a1.employeeid = b.employeeid
WHERE a1.employeeid = a4.employeeid
OR a1.employeeid = a.employeeid
OR a1.employeeid = a2.employeeid
OR a1.employeeid = a3.employeeid
OR a1.employeeid = a4.employeeid) AS [andrew's sales]
FROM employees a INNER JOIN employees a2
ON a.reportsto = a2.employeeid
INNER JOIN employees a3 ON a2.reportsto=a3.employeeid
INNER JOIN employees a4 ON a3.reportsto=a4.employeeid
WHERE a.employeeid = 5

Result:

You can easily implement this type of relationship with a parent-child dimension using Analysis Services MDX.

You don't have to make any changes to your relational table in order to create a parent-child dimension. Simply follow the dimension wizard screens, much like designing any other regular dimension. The wizard automatically detects the recursive relationship if you have specified primary key and foreign key constraints within your relational dimension. Alternatively you could also define logical relationships within your data source view.

In our example, the column "ReportsTo" references the "EmployeeID" column. Once you have created a parent-child dimension, you should set the MembersWithData property of your attribute hierarchy to NonLeafDataHidden. This property determines whether to display non-leaf (meaning members not on the bottom level of the parent-child hierarchy) attribute members. By default the parent-child hierarchy is given the same name as the dimension table. I renamed the hierarchy to "org_chart" for clarity.

Once you process your dimension, the employees will show up as follows (Figure 1):

Figure 1: Hierarchy results after processing dimensions in Analysis Services MDX.

If you had left MembersWithData at its default value (NonLeafDataVisible), you'd see the values of Andrew under Andrew, Janet under Janet and so on. Indeed, if you wanted to see Andrew's orders without adding orders of his subordinate employees to his total you would choose NonLeafDataVisible.

With MDX, getting the organizational structure starting from Steven involves executing a very simple query, as follows:

SELECT ASCENDANTS(employees.org_chart.steven) ON 0,
{measures.[orders count]} ON 1
FROM [cube name]

Results:

Similarly, to retrieve all employees of Janet you could issue the following MDX query:

SELECT
DESCENDANTS(employees.org_chart.Janet) ON 0,
{measures.[orders count]} ON 1
FROM [cube name]

Results:

What if you wanted to see Janet's sales excluding the sales of employees who report to her? You'd use the attribute hierarchy associated with the dimension key, as opposed to the parent-child hierarchy, as follows:

SELECT
employees.employees.Janet ON 0,
{measures.[orders count]} ON 1
FROM [cube name]

Results:

Alternatively, you could exploit DataMember function to retrieve the sales associated only with Janet while querying the parent-child hierarchy, as follows:

SELECT
employees.org_chart.Janet.DataMember ON 0,
{measures.[orders count]} ON 1
FROM [cube name]

Parent-child dimensions can handle any number of levels, even though you only define a single level. Unlike SQL Server you don't have to worry about adding variable number of joins to your MDX statements in order to retrieve parents (or children) of a particular member.

Parent-child hierarchy promotes members to the top level based on the value of the RootMemberIF property. In the example shown earlier in this tip, Andrew appears on the top level of the hierarchy because the value of ReportsTo column is null. Let's see what happens if we update the row for Margaret's so that employee id and reports to column have the same value, equal to 4. Now both Margaret and Andrew appear on top of the hierarchy (Figure 2).

Figure 2: Results after updating rows so employee id and reports to column have the same value, equal to 4.

The default value of the RootMemberIF property is ParentIsBlankSelforMissing. This value indicates that each member whose record has NULL or blank values in the ReportsTo column, as well as those whose employee id and reports to

Stay up-to-date with BI and Data Warehousing: Receive SQL Server-related news, tech tips and more delivered directly to your Inbox. Sign up for our Business Intelligence and Data Warehousing newsletter now.

 columns have identical values, will be promoted to the top level of the hierarchy. Alternatively, you could choose ParentIsBlank, ParentIsMissing or ParentIsSelf values for this property, depending on your needs.

Another interesting property of parent-child hierarchies is NamingTemplate. In a regular hierarchy, each level is named after the attribute it is based on. For example, in a date hierarchy you would have levels of year, quarter, month, etc. Since you only define a single attribute for the parent-child hierarchy, it cannot inherit the name for various levels based on attribute names. Instead, it assigns an ordinal number to levels. By default, levels would be called Level 01, Level 02, Level 03 and so on. You can override this default behavior and provide a more descriptive naming template. For example, in our sales scenario we could call levels Salesperson01, Salesperson02, etc. To do so, set the value of NamingTemplate property to "Salesperson *".

ABOUT THE AUTHOR
Baya Pavliashvili is a DBA manager with HealthStream, the leader in online healthcare education. In this role, he oversees database operations supporting over one million users. His primary areas of expertise include performance tuning, replication and data warehousing. You can reach Pavliashvili at baya.pavliashvili@healthstream.com.

MEMBER FEEDBACK TO THIS TIP

Do you have a comment on this tip? Let us know.