Posts Tagged ‘employee hierarchy’

MDX #8 – Why the employee parent-child hierarchy is not sorted

June 13, 2012 Leave a comment

Dimension attributes are always pre-sorted

In my previous post, MDX #7 – Get only 10 elements from a MDX query, I mentioned that dimension attributes are always pre-sorted.

This is obvious with the following query.


Parent-child hierarchy is sorted from top to bottom

However, this result does not seem to be sorted in any noticeable order.


Until you realize that the Employees hierarchy in the Employee dimension is a parent-child hierarchy (you can follow this link to see how a typical parent-child hierarchy is constructed:


The sorting is done from top to bottom. So by default, Jae B. Pak will appear before David R. Campbell, because manager Amy E. Alberts appears before Stephen Y. Jiang.

Now we know that the sorting order is working as expected. But you still want to sort the result alphabetically by employee names.

Use MemberValue function to sort by dimension

The Order function is normally used to sort results by measures. For sorting by dimensions, we can use the Order function and the MemberValue function (or the Member_name) (you can follow this link to see a very good comparison between MemberValue and Member_name and member_key: Use it as the 2nd parameter for the Order function. 


Change the 3rd parameter from ASC to BASC to break the parent-child hierarchy

The above result is the same as before. Nothing changes. That is because we forgot to tell the MDX engine to break the parent-child hierarchy, and stop sorting from top to bottom.

Changing the 3rd parameter from ASC to BASC will do the trick.


Now the result is sorted alphabetically by employee names, but many of them have no sales.

Let’s add a Non Empty command to the Y axis.


Now we know that a parent-child hierarchy is indeed also pre-sorted, and we also know at least one way now to sort it differently.

Dimension Role-Playing in Employee Comp Reporting

March 17, 2011 Leave a comment

The employee hierarchy table is just one physical table, with a date range to keep employee’s employment history. This same employee hierarchy table can be used sometimes to show different business functional teams, for example, LAS VS. Underwriters on the same fact table. 

I can simply create two SQL views to handle this, or just use two sub-queries if I don’t want to create too many SQL views.



This is called dimension role-playing. Role-playing in a data warehouse occurs when a single dimension simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table, but each of the roles should be presented in a separated labeled view.  

Another common example in the employee productivity reporting, is the date dimension. We will just need one single date dimension table, but very often, we need to have a second date, such as application date, besides the primary decision date, on the same fact table. Again we only need to create two different SQL views to handle this, with only one underlying physical date dimension table.

%d bloggers like this: