Hierarchy within single dimension or two dimensions

I am unsure of the best way to model a specific business scenario and was looking for some input. I will explain it in terms of a common example, the Product dimension. Let's say that every "product" is assigned to a specific "department". How would you model this relationship if department has a few other attributes besides "name" that we are also interested in, such as department number, department size, and department manager? In this case "department" is part of a hierarchy with "product", but it is also an entity that has attributes and could potentially be a stand alone dimension in other fact tables. If you model it as a separate dimension, how do you keep the hierarchy?

Thanks!

It's a little tough to say for sure without detailed information but in general I'd first determine if any of those other attributes aren't really attributes but are rather stand alone dimensions. I think this is where you're going with this.

So the question is whether or not, for example, department is actually a stand-alone entity. I'd ask the business this question and to get confirmation. If it is, then it's a new dimension. Same with manager. That's really probably an employee. The business should be able to provide that answer. Chances are you'll find these new entities have a few attributes of their own.

If you determine that these are separate entities then it's really three dimensions and the intersection of these dimensions (along with any measures) make up a single fact record. Then any new facts that use these same dimensions can just hook right in.

Your dimensional hierarchy kinda goes away after this but if you determine they're distinct entities then maybe the hierarchy wasn't truly a hierarchy to begin with. Or there's always the option of ordering and grouping your dimensional attributes once you bring your facts back in your reporting tool. The hierarchy could be achieved through reporting then.

It's probably not impossible to keep the hierarchy by storing the surrogate keys of your new dimensions within your product dimension as attributes but that gets really convoluted and confusing and is probably just asking for trouble. If any of those dimensions are type 2 then it becomes unbearable to maintain. I'd steer clear of that if possible.

I'd push these questions to the business (and your source system expert) and get them to define exactly what these entities are, or if they even are truly new entities. Once that's done you should have the information you need to move forward.

Thanks for the response. I think you confirmed what I was thinking which is that there is no way to preserve the hierarchy if you have to break attributes into more than one dimension (in this case department and product). Logically, there is a hierarchy between product and department (every product is assigned/owned by a department...one to many relationship) so the business wants to be able to roll-up and drill-down on that hierarchy in a sales fact table. At the same time, the business has reporting needs that cause the department attributes to be included in a fact that the product doesn't belong to (i.e. a fact table that tracks HR stuff for a department).

At last thought, I was planning on having a stand along department dimension to tie into the HR fact table and then have the department attributes in the product dimension as well with a hierarchy defined. It is similar to your suggestion using surrogate keys and, as you stated, it doesn't seem like the best approach, but I can't think of another way. I may just eliminate the defined hierarchy and, like you said, handle it in the reporting tool. I feel like it will make ad hoc analysis more confusing though for the end users.

jimbo1580 wrote:At last thought, I was planning on having a stand along department dimension to tie into the HR fact table and then have the department attributes in the product dimension as well with a hierarchy defined. It is similar to your suggestion using surrogate keys and, as you stated, it doesn't seem like the best approach, but I can't think of another way.

That could work. Instead of storing surrogate keys in the dimension you could just store the attributes themselves. You would, of course, have to call them something else. For example, you could call them "Product Department", "Product Manager" or something like that. It could be confusing to your end users though because they'd have essentially two different versions of the same information, one set coming from the product dimension and one set coming from the separate dimensions. Keeping the hierarchy within the dimension would allow a tool like Business Objects to still be able to drill up and down the dimension, making ad hoc easier.

I'd get a reading from the business on how important it is to keep this hierarchy. Sometimes people do things just because that's the way they've always done it and they've never questioned it. If it truly is indispensable to them then I think your only option is to store those attributes in the product dimension in addition to your new stand-alone dimensions, differentiating them by name. After that it's a training issue.