Hierarchies: Flatten vs. Bridge

View previous topic View next topic Go down

Hierarchies: Flatten vs. Bridge

Post  ctheiss on Mon Aug 12, 2013 1:22 pm

I've been doing a lot of reading, trying to figure out the pros and cons of each approach for dealing with hierarchical data.

It seems like the generally preferred method is bridge, but I'm not exactly sure why as flattened hierarchies seem easier to use for analysts.  For the purpose of this discussion, please assume Type I changes (straight updates, no history captured), and a reasonable maximum depth (say, 10).

Bridge
    Pro
      * Handles ragged hierarchies and unbalanced hierarchies more cleanly
   Cons
     * More complicated to use
      * Drilling-up and drilling-down require issuing new queries and are more complicated
      * Slower

Flattened
    Pro
      * Easier to use and understand
     * Faster
   Cons
     * Need special rules to handle ragged and unbalanced hierarchies
      * You need to emit two queries to read the table (? I don't understand this one, I read about it here: http://www.kimballgroup.com/2009/08/17/five-alternatives-for-better-employee-dimension-modeling)

In both cases, maintaining additivity/semi-additivity requires special rules.

I would love it if anybody could weigh in with their experiences.  Was one method a much better fit in a particular situation?  Did I miss any pros/cons?  Did the end-users prefer one method over another?

Thanks!
   Corey

ctheiss

Posts : 2
Join date : 2013-08-12
Age : 37
Location : Toronto, Canada

View user profile

Back to top Go down

Re: Hierarchies: Flatten vs. Bridge

Post  ngalemmo on Mon Aug 12, 2013 1:34 pm

I would not say a bridge is necessarily slower than a flattened hierarchy. Your other comments are valid.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Hierarchies: Flatten vs. Bridge

Post  ctheiss on Mon Aug 12, 2013 1:46 pm

ngalemmo wrote:I would not say a bridge is necessarily slower than a flattened hierarchy.  Your other comments are valid.
I also got this information from http://www.kimballgroup.com/2009/08/17/five-alternatives-for-better-employee-dimension-modeling.

I agree with you; there would definitely be more rows in a bridge table than a flattened hierarchy table, but only by a multiple of (max-depth).  An index would probably make the speed difference unnoticeable.

ctheiss

Posts : 2
Join date : 2013-08-12
Age : 37
Location : Toronto, Canada

View user profile

Back to top Go down

Re: Hierarchies: Flatten vs. Bridge

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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