Friday, October 21, 2011

Implementing Parent / Child Hierarchy in PowerPivot

We are currently implementing a Management Information Dashboard with Critical Success Factors and I ran into the problem of parent / child relationships in PowerPivot.
Since we are not working with Denali with the customer, I had to make a workaround to import the organisation structure into PowerPivot.

Special thanks to Alberto Ferrari and Marco Russo who made this possible on SQL!

Of course not every project is the same, so I ran into a few issues while walking through the steps of Ferrari & Russo.
The first issue I had was that my ParentID and NodeID were both a VarChar (don't ask me, I don't know why..), so I had to convert / cast them as an INT.
Second, this organisation structure has the first two layers which are always the same and have no other nodes on the same level. So my structure actually starts at level 3. I edited the last select statement to leave out the first NodeID's and also changed the ComputeParentNodeIdAtLevels to not select Level1 and Level2. This worked for me.

Several measures with parent / child relations