How better managing parent-child hierarchies in PowerPivot v2

Question

Hi,

I've a dwh with a fact table and some dimensions. One of these dimensions represents the chart of accounts (or the accounts of the general ledger). Each account (each row) has an account id and the related account parent id. Moreover, the depth of the leafs isn't fixed. The dws is the data source for a SSAS cube generated by a 3-rd party application with some user interfaces that allow a good navigation from the root to the leaf. I think to import the fact table and the related dimensions, and so the account dimension, inside PowerPivot and then using the PATH function to create a calculated column for the account dimension and a calculated column for each items of the calculated PATH by using the PATHITEM function. But when I create an hierarchy and select it for a PowerPivot table I can see some empty cells, due to different depth of the account path.

How can I better manage this scenario and implement correctly the parent-child hierarchies, typical for the chart of accounts?

Many thanks!

Answer 1

Hi pscorca

You may want to check Alberto Ferrari's blog entry on handling Parent/Child in ragged hierarchies.  His technique can effectively deal with empty cells, even if an equivalent of 'HideMemberIf' property is not available in PowerPivot/Tabular models.

Check it out here: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/19/parent-child-hierarchies-in-tabular-with-denali.aspx

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Hi Javier, thanks for your interesting reply!

Is it possible to download the Alberto's sample?

Many thanks!