We always recommend to flatten the (fixed-depth) hierarchy information into the dimension. It is how we keep the star schema simple and nice.
However, it is sometime impossible for complex hierarchy (e.g. when hierarchy is not fixed-depth). In such cases, we use bridge table. It introduces a many to many relationship between the fact and dimension tables which is powerful but dangerous.
 |
| Figure: Bridge Table in Hierarchy Dimension |
It is powerful that we can easily move up or down through the bridged hierarchy dimension by using different join directions (see the figure above). It is dangerous, as you know, it involved a N to N relationship between the fact and dimension tables, if we don't query properly with such bridge table, we may double count (or even worse) the transactions and measures. Only experienced developers or users are allowed to query accessing a bridge table. Majority users should not have access to the bridge table.
Honestly, we won't recommend implementing bridge table unless we don't have alternative.
No comments:
Post a Comment