Home Forums SQL Server 2005 Business Intelligence Design for Alternate Hierarchies on Parent-Child Dimension SSAS 2K5? RE: Design for Alternate Hierarchies on Parent-Child Dimension SSAS 2K5?

  • Hey Mark,

    I've been working on an extremely similar problem. The issue with simply adding additional Parent_Keys is that each dimension in SSAS will allow only a single P-C hierarchy to be defined. So, you can go and create these, but you'd have to create a new, fully independent 'Account' dimension (yes, the full dimension) to house each of the additional PC hierarchies.

    An alternate approach is to flatten the PC hierarchies out, effectively taking a self-referencing row-based approach and making it in to a column based approach. This then makes your attributes the same as any other user hierarchy. There are still some gotchas in this approach (for e.g. where the hierarchy is ragged, I am pushing the lowest child node value all the way through to the leaf level), but in general, will allow you to have multiple hierarchies in the dimension.

    Steve.