An Analysis of Many-to-Many Relationships Between Fact and Dimension Tables in Dimensional Modeling

Star schema, which maintains one-to-many relationships between dimensions and a fact table, is widely accepted as the most viable data representation for dimensional analysis. Realworld DW schema, however, frequently includes many-to-many relationships between a dimension and a fact table. Having those relationships in a dimensional model causes several difficult issues, such as losing the simplicity of the star schema structure, increasing complexity in forming queries, and degrading query performance by adding more joins. Therefore, it is desirable to represent the many-to-many relationships with correct semantics while still keeping the structure of the star schema. In this paper, we analyze many-to-many relationships between a dimension table and a fact table in dimensional modeling. We illustrate six different approaches and show the advantages and disadvantages of each. We propose two ad-hoc methods that maintain a star schema structure by denormalizing the dimensions to avoid m...

• Type: Conference
• Year: 2001
• Where: DMDW
• Authors: Il-Yeol Song, William Rowen, Carl Medsker, Edward F. Ewen
• Keywords: DMDW 2001, Fact Table, Management, Many-to-many Relationships, Star Schema