Hierarchies: Flatten vs. Bridge
2 posters
Page 1 of 1
Hierarchies: Flatten vs. Bridge
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
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 : 45
Location : Toronto, Canada
Re: Hierarchies: Flatten vs. Bridge
I would not say a bridge is necessarily slower than a flattened hierarchy. Your other comments are valid.
Re: Hierarchies: Flatten vs. Bridge
I also got this information from http://www.kimballgroup.com/2009/08/17/five-alternatives-for-better-employee-dimension-modeling.ngalemmo wrote:I would not say a bridge is necessarily slower than a flattened hierarchy. Your other comments are valid.
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 : 45
Location : Toronto, Canada
Similar topics
» How to write SQL query to flatten the hierarchy
» Hierarchies
» Hierarchies in dimensions.
» Dimension with two hierarchies
» Snowflaking and hierarchies
» Hierarchies
» Hierarchies in dimensions.
» Dimension with two hierarchies
» Snowflaking and hierarchies
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum