[et_pb_section][et_pb_row][et_pb_column type=”1_4″][et_pb_sidebar admin_label=”Sidebar” orientation=”left” area=”et_pb_widget_area_1″ background_layout=”light” /][/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]
Creating a Many to Many relationship
[/et_pb_text][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]To create a Many to Many relationship between a Fact Table and Dimension you need to create a Group Dimension and an Intermediary Bridge Fact Table.
For more information about this technique you should refer to the ‘Data Warehouse Toolkit’ book by Ralph Kimball or visit the following TechNet article which explains the same technique in Microsoft terms. http://technet.microsoft.com/en-us/library/ms345139.aspx.
- First create your Group Dimension, without any dimension attributes.
- Next create a intermediary Bridge Fact Table, this time without any measures. Associate the Fact table with both the target (of the many to many join) Dimension and the Group Dimension. Use the Standard pattern in the ETL.
- Open your target (of the many to many join) Fact Table, and associate it only with the Group Dimension.
In the Microsoft example in the above link,
- The Transaction Fact Table is the target Fact Table.
- The Customer Dimension is the Target Dimension.
- The CustomerAccount Fact Table is the Intermediary Bridge Fact Table.
- The Account Dimension is the Group Dimension.