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:

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.

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.

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.