.NET Playground

SSAS Department Hierarchy with Nested Employees

Ok,

It's been a while since I posted this question on forums.microsoft.com with little result.

The issue is that in any SSAS implementation containing employees and departments, you're likely to get the scenario where you want to drill down by department through to the employee level. Add to that the requirements that

1. there be n number of departments, sub-departments and levels (where n is unknown and can change),
2. that employees can sit in any level of these departments, and
3. that employees can also move to different departments as time goes by (ie promotions, demotions, rotation etc)

IMHO, this is a pretty common scenario, yet couldn't find a proper solution anyway. Add to that the fact that I don't consider myself an expert in SSAS, and I'm too stubborn to go out & buy a book on the subject, I came up with this solution which solves my problem perfectly.

This was my original schema as it would appear in the database (albeit simplified for the purposes of the example).

As you can see, I have a fact table - vwCall which holds telephone call information, department which holds a hierarchical view of the departments, an Employee table, and a link table that links an employee to a department given a time period.

The first thing you need to do is to consolidate the department, employee & employeedepartment link table into a single named query. This was done thusly:

Let me explain. I've negated the DepartmentID and unioned it with the EmployeeID (from the vwCall view) to form a non-conflicting KeyID field. This is a unique key regardless if it's describing a department or employee. Next, I've queried in the unaltered DepartmentID and EmployeeID, to enable the named query to eventually be linked up with the fact table (vwCall). Finally I added the name, which is going to return either the Employee Name or Department Name.

To paint a picture, here is a sample set of the output data:

As you can see, employee records also have a ParentID, which links them back to their department. I'm using the fact table to build a hierarchy of the employees, as it adds in the date dimension and will handle cases where at one point in time, an employee might make a call from "Software", get rotated into the "Managment" department & make a call there. This will obviously show up as the employee appearing in both "Software" and "Management" departments, which will make sense to the OLAP user when they add a time dimension to the mix.

When that's done, the new schema in the data source view becomes:

Nice huh? That's the fact table linked on DepartmentID and EmployeeID. ParentID links back to KeyID to perform the Department-Department and Employee-Department link.

The final step in the whole process is to create the dimension.

Create a blank dimension and add the EmployeeHierarchy named query (above) to it. Drop in KeyID, set it's usage as "Key", then set the NameColumn to EmployeeHierarchy.Name.

Next, add in the ParentID attribute and set it's usage as Parent, and add it as an attribute relationship under KeyID if it's not done automatically.

Finally, add the dimension to the cube you want to use it with. Under the "Dimension Usage" for that cube, define the relationship type as "Fact", linking the fact table's EmployeeID field on KeyID of the dimension we just created.

It's then just a case of deploying and processing your cube.

I've heard people speaking about slowly changing dimensions as a solution to this, but as far as my requirements went, that solution wouldn't suffice. I feel that although this is slightly more complex on the data source view side of things, it gains through not having to redesign the underlying database schema and actually strips away complexity on the dimension designer side of things.

SSAS - it's a steep learning curve.

posted @ Tuesday, November 06, 2007 9:18 AM