Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Barely a ragged hierarchy

3 posters

Go down

Barely a ragged hierarchy Empty Barely a ragged hierarchy

Post  schnedar Wed Jun 17, 2009 10:20 am

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
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
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.


Posts : 4
Join date : 2009-04-23

Back to top Go down

Barely a ragged hierarchy Empty Re: Barely a ragged hierarchy

Post  ngalemmo Thu Jun 18, 2009 12:35 am

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.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

Back to top Go down

Barely a ragged hierarchy Empty Re: Barely a ragged hierarchy

Post  Joy Fri Jun 19, 2009 2:19 pm

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.

Posts : 20
Join date : 2009-02-03
Location : Kimball Group

Back to top Go down

Barely a ragged hierarchy Empty Re: Barely a ragged hierarchy

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum