Barely a ragged hierarchy
3 posters
Page 1 of 1
Barely a ragged hierarchy
I am modeling the Air Forcde organization structure. They have Headquarters (top of org hierarchy), MAJCOMs, NAFs, Wings, Groups, and Squardrons (bottom of org hierarchy). All but ten out of three thousand organizations fit nicely into one level of this structure. The issue with the remaining ten is they may be a same level reporting to another organization in the same level (i.e. wing reporting to wing).
Well over 90% of the queries would be handled correctly with the typical flattened hierarchy. By business rule, if an organizaiton chain has multiple organizations at the same-level, the "higher" organization is used. Below, I have proposed an "exception" flatten hierarchy to the above problem. Does my proposal seem sound or should I go with a ragged-hierarchy design?
My proposal consists of three extra "exception" columns to handle the same-level orgs.
Org dimension
org_key
Headquarters
MAJCOM
NAF
Wing
Group
Squadron
Excpetion 1 Org
Exception 1 Level
Exception 2 Org
Exception 2 Level
Exception 3 Org
Exception 3 Level
Actual Org
So, if I have 1st SQDN reporting to 1st Group reporting to 1st Wing reporting to 1st Center (NAF-level) reporting to 1st NAF reporting to 1st MAJCOM reporting to AF HQ, data in the table would look like:
If a user wants to see just 1st MAJCOM, they run Actual Org = "1st MAJCOM". If a user wants to see 1st MAJCOM and all subordinates, the user runs MAJCOM = "1st MAJCOM". Likewise, to see 1st Center, Actual Org = "1st Center". But, to see 1st Center and all subordinates, the user would have to run Excpetion_1 = "1st Center". I think this will work. I'm looking for some feedback. If I go with this, I'm leaning toward have a parent_org attribute as well. Thoughts?
Thanks a million.
Well over 90% of the queries would be handled correctly with the typical flattened hierarchy. By business rule, if an organizaiton chain has multiple organizations at the same-level, the "higher" organization is used. Below, I have proposed an "exception" flatten hierarchy to the above problem. Does my proposal seem sound or should I go with a ragged-hierarchy design?
My proposal consists of three extra "exception" columns to handle the same-level orgs.
Org dimension
org_key
Headquarters
MAJCOM
NAF
Wing
Group
Squadron
Excpetion 1 Org
Exception 1 Level
Exception 2 Org
Exception 2 Level
Exception 3 Org
Exception 3 Level
Actual Org
So, if I have 1st SQDN reporting to 1st Group reporting to 1st Wing reporting to 1st Center (NAF-level) reporting to 1st NAF reporting to 1st MAJCOM reporting to AF HQ, data in the table would look like:
Key | HQ | MAJCOM | NAF | Wing | Group | Squadron | Excp 1 | Excp 1 Level | Excp 2 | Excp 2 Level | Excp 3 | Excp 3 Level | Actual Org | |
1 | AF HQ | AF HQ | ||||||||||||
6 | AF HQ | 1st MAJCOM | 1st MAJCOM | |||||||||||
18 | AF HQ | 1st MAJCOM | 1st NAF | 1st NAF | ||||||||||
89 | AF HQ | 1st MAJCOM | 1st NAF | 1st Center | NAF | 1st Center | ||||||||
101 | AF HQ | 1st MAJCOM | 1st NAF | 1st Wing | 1st Center | NAF | 1st Wing | |||||||
123 | AF HQ | 1st MAJCOM | 1st NAF | 1st Wing | 1st Group | 1st Center | NAF | 1st Group | ||||||
188 | AF HQ | 1st MAJCOM | 1st NAF | 1st Wing | 1st Group | 1st Sqdn | 1st Center | NAF | 1st Sqdn |
If a user wants to see just 1st MAJCOM, they run Actual Org = "1st MAJCOM". If a user wants to see 1st MAJCOM and all subordinates, the user runs MAJCOM = "1st MAJCOM". Likewise, to see 1st Center, Actual Org = "1st Center". But, to see 1st Center and all subordinates, the user would have to run Excpetion_1 = "1st Center". I think this will work. I'm looking for some feedback. If I go with this, I'm leaning toward have a parent_org attribute as well. Thoughts?
Thanks a million.
schnedar- Posts : 4
Join date : 2009-04-23
Re: Barely a ragged hierarchy
The devil is always in the details. If its 'almost' ragged, then its ragged. Besides, treating any hierarchy as ragged gives you a lot more flexibility should structures change. Introduction of new levels or removal of existing ones would cause a world of hurt using a flattened structure, while there would be no effect if it was implemented as a ragged structure.
Re: Barely a ragged hierarchy
On the other hand -- isn't there always another hand? -- ragged hierarchies are beastly. This is especially true if you (now or in the future) plan to track Type2 changes in the dimension. Ragged hierarchies are challenging to use in the relational world, and in both relational and olap they tend to have poor query performance. For that reason I always encourage people to attempt to smoosh a ragged hierarchy into a flat structure, as you are doing.
The second thing that I encourage people to do is to make thier official organization hierarchies contain enough levels to match their business needs. I look forward to hearing about your success in getting the USAF to change its official hierarchies.
From the clear but small structural example that you provided, I can't tell if the extra levels occur at fixed points in the tree, or can be anywhere. From your example it looks like there's a missing level called Center (often NULL) that reports to NAF. If so, I'd see how the users take to inserting the Center level. You'd need to copy down the parent or copy up the child to fill in the NULL. This would be a lot easier for them to use than the Exceptions structure.
In any case, I would always include the ParentOrg in the table.
The second thing that I encourage people to do is to make thier official organization hierarchies contain enough levels to match their business needs. I look forward to hearing about your success in getting the USAF to change its official hierarchies.
From the clear but small structural example that you provided, I can't tell if the extra levels occur at fixed points in the tree, or can be anywhere. From your example it looks like there's a missing level called Center (often NULL) that reports to NAF. If so, I'd see how the users take to inserting the Center level. You'd need to copy down the parent or copy up the child to fill in the NULL. This would be a lot easier for them to use than the Exceptions structure.
In any case, I would always include the ParentOrg in the table.
Similar topics
» [Solved] Ragged Hierarchy, Bridge Table and SCD2
» Asset Hierarchy in Transmission Utiltiy
» Help with ragged multiple hierarchy design
» Ragged Hierarchy with Bridge table
» Ragged Product Category Hierarchy and Bridge table
» Asset Hierarchy in Transmission Utiltiy
» Help with ragged multiple hierarchy design
» Ragged Hierarchy with Bridge table
» Ragged Product Category Hierarchy and Bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum