Can a hierarchy cross dimensions?

I have a situation where I need to show fact values for accounts, the customers that own those accounts and the people that managed those customers. Each level of this hiearchy may have $ associated with them. If I put them all in a *single* dimension where I can easily create the hierarchy, I end up with a dimension that is not really one *thing*. Is this a crazy modeling choice? I just can't seem to figure out how to express that hierarchy as well as the facts associated with elements on the way up the hierarchy unless I do put them into a single dimension. Any ideas? or suggestions would be great.

Thanks,

I don't understand your question... I can not imagine you are discussing a hierarchy that involves both customers and accounts, nor could I imagine a business process that would maintain such a beast.

It is not unusual to have a situation where a report involves multiple hierarchies. You could have a customer hierarchy and an account hierarchy and produce somthing that is arranged by customer and its hierachy, and, within customer, arranged by the account hierarchy... or visa-versa. This is implemented as separate hierarchies with separate bridge tables.

Or is your question referring to the mechanics of a bridge table? Then, yes, you need to populate the parent levels of the hierarchy within the same dimension table as the children. Or alternately, the hierarchy is implemented as a flat dimension table.

It can get confusing. A store location can roll up geographically and organizationally so it might appear that a dimension for a store might include the district, market and region it belongs to as well as the City, County, and State. And it's not really a question of "can you do it", but rather, "should you do it".

In this example, geography can apply to items other that Store so it should be it's own dimension and same with organization.

Accounts, account managers, and owners of the accounts should probably be separate dimensions. An account can have multiple owners and account managers, so you would run the risk of a many to many relationship.

Thank you both for your input! I really appreciate your comments. I'm still a little unsure about what to do though.

Given that each of the elements might appropriately be a single dimension (manager, customer and account), I still need to link them together into a single hierarachy. Which is to say, I start a cube view on this hierarchy - it shows the aggregate total for the manager, with the extra $ for the manager. I drill down to see the customers that manager owns. Now each of the customers is the aggregate of the accounts, with the extra $ for the customer. And a final drill takes me down to the account.

If I create three columns in the fact table to describe each of the elements, then the reality that accounts shift ownership is expressed in the fact table and cannot be overwritten (I think). For example, a customer managed by Joe in January, and managed by Sam in March will fall twice in the hierarchy when seen thru the fact table. But I need to see Sam and all his current customers - which would include all the data for that customer even though it was managed by Joe in January. If I write Joe in the manager table in January, I won't get that view. (right?).

I don't actually want a slowly changing dimension here, so I don't really want to express the rollup in the fact table.

If all the data was at the account level, it would seem to be a straightforward rollup - the dimension is account and the manager and customer were simply characteristics of the account that make up a hierarchy. The challenge seems to be in requirement that in addition to accounts, managers and customers will also have dollars associated with them. At least I think that is the challenge. I'm still not sure how one might do it without creating the so-called beastly dimension.

As to the bridge question: I think that they are used when there is a many to many relationship between elements in the hierarchy? I could be not understanding the bridge fully, but the elements in this particular hierarchy are quite strict. Every account belongs to one and only one customer which belongs to one and only manager. I'm looking for more details on bridge tables, though, to see if maybe I've missed an important concept.

Thanks.

Every account belongs to one and only one customer which belongs to one and only manager.

If that is the case, simply store the owning manger (with all related attributes) in the customer dimension. You may still have a manager dimension with a hierarchy which could be used for other purposes, but from the point of tracking customer/account activity, having a manager FK in the fact would be misleading. Assuming an account would not be reassigned to another customer, you should be ok with customer and account FKs in the fact.

If you need to track historical customer/manager relationships, this could be handled by another fact table. Also, if you do implement a manager dimension, I would include the manager FK in the customer dimension... not for snowflaking, but as an internal reference and to support reporting of customers via the manager hierarchy (i.e. customers under a manager and his boss, and his boss's boss, etc...).

Hi,
I'm facing a very similar problem. I have a COURSE dimension, each course belongs to a single course-category. Course-category has a recursive parent-child relationship, which is, each course-category belongs to a single parent course-category. Of course, I can include all course-category's attributes into the COURSE dimension. But this is the case where I want to build a bridge table to handle unlimited-depth hierarchy for course-category. Now I have 2 solutions in my head:
1. Building a separated course-category table together with the bridge table. COURSE dimension will have a FK to the course-category table. But by doing this is I'm snow-flaking the model.
2. Merging the course and course-category into a single COURSE dimension. In this case, I'm considering that each original course-category is just a course with children. Now I can have a single parent-child hierarchy within a dimension, together with its bridge table. Problem is that course-category and course can have same value of identifier in the source database.
I don't know which solution to choose. Or you could suggest a better solution. Thank you.

For a hierarchy bridge to work well, you need to do option 2... but don't get hung up on business identifiers.

Design dimensions so they have a natural key column that is independent of any business identifiers, then derive the appropriate natural key. In other words, build a string the combines some prefix, one for courses and the other for categories, and the identifier. This will make them unique for the purposes of specifying a natural key and deriving the primary key. The attributes, which include the business identifiers, will remain as is for users to report with.

Thank you for your suggestion