How to store multiple hierarchies within a dimension

Hi

If we have an account dimension, say DimAccount.
How can we store multiple hierarchies within this dimension - for use by a Cube etc.

Both these options only cater for one hierarchy, how can we represent multiple hierarchies?

Option 1:

DimAccount
AccountKey
AccountName
ParentAccountKey
etc.


Option 2:

DimAccount
AccountKey
AccountName
ReportingLayout
ReportingHeader
ReportingSegment1
ReportingSegment2
ReportingSegment3
etc.

Not sure what OLAP platform you are usinig, but SSAS will allow you to define multiple custom hierarchies on a dimension. As long as all the attributes exist in the dimension you should be able to define both the hierarchies you list.

What I have found to be tricky in SSAS is defining relatiionships between attributes--it seems a little "backwards" in how you define atribute relationships. If you don't define any attribute relationships, the default is that all attributes are dependentant directly on the dimension key. Defining attribute relationsihps helps the cube build/process more efficently and helps with building custom hierarchies.

I've found GL account hierarchies can get real messy real fast... particularly if you are using them for reporting (Balance sheet, statement of ops, etc...).

A bridge is the most flexible structure and you can include a hierarchy type column to discriminate which hierarchy should be used when.