MDX Get Hierarchy Value

Question

I've searched and searched and can't find the answer for what seems should be fairly simple.

I have a hierarchy of employees in a dimension. Employees have points (in a fact table). I can easily see the points summed properly when I drag my hierarchy onto rows. The problem is that employees are judged differently based on job code, which doesn't necessarily correspond to levels in the hierarchy.

For instance, I have Project Engineer Is. They are judged on individual points. I can pull Name onto rows, filter by job code, and put points into measures and I have exactly what the user wants to see.

Project Engineer IIs are a different story. They are judged on TEAM points. I pull name onto rows, filter by job code, and I need a calculated measure to sum up all points belonging to them, just as it would if I had dragged the hierarchy onto the rows. The problem is I can't seem to relate the Name or EmployeeID to the hierarchy in order to get that team value. In fact I can't even grab the Employee ID primary key because it is hidden under the parent child hierarchy. I have to grab a copy of it.

How can I accomplish this requirement? I've experimented with a lot of different MDX (a language in which I am no expert) but the crucial block seems to be that I cannot get the hierarchy member based upon the row value. It is as if they are totally unrelated, when, in fact, I know that my copy of the key relates to one and only one specific member of the hierarchy. For this reason, I am also thinking that it might not be the MDX so much as the dimension attributes/relationships/properties that may need changing.

Please help! And let me know if there are any questions I can answer about the cube or requirement.

Update: I have set up the dimension using a bridge table and a many to many relationship as described at the following link : http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf on page 70. I didn't mention it because I didn't think it was relevant and it clouds the question. I set it up this way because there is both a formal and an informal hierarchy. Conceptually, I don't see how it matters, because the parent child relationship is exactly what you would expect - there is a parent field which corresponds to a key value.

How have you set up the dimension table? How have you set up the hierarchy? What do you mean by this?: "I can't even grab the Employee ID primary key because it is hidden under the parent child hierarchy" — Tab Alleman
I mean that the key field does not show in the cube browser. All other fields do, but instead of the key field, I see the hierarchy with a label of the parent field name. There is no way to put the key field on rows so far as I can see, even though the attribute hierarchy enabled property is set to true. So I made a copy of it in a second column to force it to show up. But it isn't linked to the hierarchy. — DavidJones
If you create an attribute and assign your key field to it, you should be able to assign it to rows in the cube browser, by dropping that attribute on rows. Is that what you're after? — Tab Alleman
I'm not sure exactly what you mean, but I was able to solve my problem. Thank you for your help! — DavidJones

DavidJones DavidJones · Accepted Answer · 2014-07-24T20:07:34

I was able to solve this problem.

I used the following MDX to create a named set to essentially give me what I couldn't get before - the actual leaf members of the parent child hierarchy, not a copy thereof.

DESCENDANTS([Vw Dim Employee Hierarchies].[PIDH Name].&[37Informal]
                , [Vw Dim Employee Hierarchies].[PIDH Name].Levels.Count - 1
                , LEAVES)

Member 37Informal is the root of the informal hierarchy, I specified the bottom level, and the leaves option returns all leaf level members of the hierarchy between the root member and the level specified.

I was then able to create the "TeamPoints" calculated member that I needed by comparing the value of each member from the named set to its parent on the hierarchy. If it was the same name, then I summed up his points and all of his siblings points. If not, I just returned his points. Code below:

CASE WHEN [Vw Dim Employee Hierarchies].[PIDH Name].CurrentMember.Parent.Name =
                [Vw Dim Employee Hierarchies].[PIDH Name].CurrentMember.Name
    THEN SUM(
            {[Vw Dim Employee Hierarchies].[PIDH Name].CurrentMember.Siblings
                    * UNION({[Vw Dim Employee Hierarchies].[Job Code].&[ENGOPROF]}
                            , {[Vw Dim Employee Hierarchies].[Job Code].&[PROJENG]}
                            , {[Vw Dim Employee Hierarchies].[Job Code].&[INTRNENG]})}
            , [Measures].[Points])
    ELSE [Measures].[Points]
    END

The key was really the named set. Without it, I couldn't get all of the leaf nodes from my hierarchy on rows, which I needed for my calculated member to evaluate properly in the context of the CurrentMember.

MDX Get Hierarchy Value

1 Answers