Bridge tables are sometimes an essential requirement for dimensional modeling. Although bridge tables may not be the most ideal solution, certain situations warrant their usage. Simply put, bridge tables are required when a fact row might correspond to more than one row in the dimension table. There are however many cases where this could occur. Ragged hierarchies, multivalued attributes, many-to-many dimensions are some of the main scenarios where bridge tables are required. Another common pattern across bridge tables is that they are used when there is frequent changes or a possibility of frequent changes to the information contained in the dimension tables. Of these examples we will focus on the scenario of multivalued dimensions.
After choosing the grain of the fact table, we sometimes discover that a single fact table row could contain multiple rows in the dimension table. In such situations, we "snowflake" the dimension and connect a bridge table to it. Let us take the example where there are multiple suppliers for an item. In order to build the bridge, we require to create a supplier "group" dimension table which contains a separate supplier group primary key and attributes of the group. After this, we link the supplier group bridge table to this newly created supplier group dimension containing all possible supplier key values for the specific supplier group key. The supplier group also links to a supplier dimension containing attributes of the supplier. The bridge creation process is complete. It should be noted that the same creation process can be applied for a many-to-many dimension relation.
Bridge tables relieve much complexity from the dimensional schema, but add some complexity to the query. From a dimensional modelling purists point of view however, this is not the preferred solution. In an ideal situation, all dimension tables must be connected to the fact table, and not other dimension tables. A purist therefore proposes that we create an artificial fact table resulting in a multi-star schema model (Reference 1). In our above example, we will have a fact table called item_supplier_fact containing a row for every supplier-item. Any calculations and derived attributes pertaining to this grouping will be placed in the new artificial fact table.
References:
After choosing the grain of the fact table, we sometimes discover that a single fact table row could contain multiple rows in the dimension table. In such situations, we "snowflake" the dimension and connect a bridge table to it. Let us take the example where there are multiple suppliers for an item. In order to build the bridge, we require to create a supplier "group" dimension table which contains a separate supplier group primary key and attributes of the group. After this, we link the supplier group bridge table to this newly created supplier group dimension containing all possible supplier key values for the specific supplier group key. The supplier group also links to a supplier dimension containing attributes of the supplier. The bridge creation process is complete. It should be noted that the same creation process can be applied for a many-to-many dimension relation.
Bridge tables relieve much complexity from the dimensional schema, but add some complexity to the query. From a dimensional modelling purists point of view however, this is not the preferred solution. In an ideal situation, all dimension tables must be connected to the fact table, and not other dimension tables. A purist therefore proposes that we create an artificial fact table resulting in a multi-star schema model (Reference 1). In our above example, we will have a fact table called item_supplier_fact containing a row for every supplier-item. Any calculations and derived attributes pertaining to this grouping will be placed in the new artificial fact table.
References:
1) Darmawikarta, D. (2009, December). Solving
Many-to-Many Relationship in Dimensional Modeling. Retrieved from PACKT
Publishing:
http://www.packtpub.com/article/solving-many-to-many-relationship-dimensional-modeling
No comments:
Post a Comment