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 many-tomany relationships. This method allows quick query processing by using a concatenated attribute with minimal overhead. Other issues addressed are data redundancy, weighting factors, storage requirements, and performance concerns.

[1]  Matteo Golfarelli,et al.  A methodological framework for data warehouse design , 1998, DOLAP '98.

[2]  Chris Adamson,et al.  Data Warehouse Design Solutions , 1998 .

[3]  Wolfgang Lehner,et al.  Normal forms for multidimensional databases , 1998, Proceedings. Tenth International Conference on Scientific and Statistical Database Management (Cat. No.98TB100243).

[4]  Timos K. Sellis,et al.  Data Warehouse Schema and Instance Design , 1998, ER.

[5]  Barbara Dinter,et al.  Extending the E/R Model for the Multidimensional Paradigm , 1998, ER Workshops.

[6]  Gottfried Vossen,et al.  Conceptual Data Warehouse Design , 2000 .

[7]  Dennis Murray,et al.  Data warehousing in the real world - a practical guide for building decision support systems , 1997 .

[8]  Nectaria Tryfona,et al.  starER: a conceptual model for data warehouse design , 1999, DOLAP '99.

[9]  Goetz Graefe,et al.  Multi-table joins through bitmapped join indices , 1995, SGMD.

[10]  Yannis E. Ioannidis,et al.  Bitmap index design and evaluation , 1998, SIGMOD '98.

[11]  Barbara Dinter,et al.  The OLAP market: state of the art and research issues , 1998, DOLAP '98.

[12]  Torben Bach Pedersen,et al.  Multidimensional data modeling for complex data , 1999, Proceedings 15th International Conference on Data Engineering (Cat. No.99CB36337).

[13]  William A. Giovinazzo.  Object-Oriented Data Warehouse Design: Building A Star Schema , 2000 .

[14]  Il-Yeol Song,et al.  The translation of star schema into entity-relationship diagrams , 1997, Database and Expert Systems Applications. 8th International Conference, DEXA '97. Proceedings.

[15]  Don Meyer,et al.  Building a better data warehouse , 1997 .

[16]  Daniel L. Moody,et al.  From enterprise models to dimensional models: a methodology for data warehouse and data mart design , 2000, DMDW.

[17]  Il-Yeol Song,et al.  Data warehouse design for pharmaceutical drug discovery research , 1997, Database and Expert Systems Applications. 8th International Conference, DEXA '97. Proceedings.