A Comparison of Data Warehouse Design Models

A COMPARISON OF DATA WARE HOUSE DESIGN MODELS

A MASTER’S THESIS in Computer Engineer ing Atilim Univer sity

by BERIL PINAR BAŞARAN J ANUARY 2005

A COMPARISON OF DATA WARE HOUSE DESIGN MODELS

A THESIS SUBMITTED TO THE GRADUATE SCHOOL OF NATURAL AND APPLIED SCIENCES OF ATILIM UNIVERSITY BY BERIL PINAR BAŞARAN

IN PARTIAL FULFILLMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF SCIENCE IN THE DEPARTMENT OF COMPUTER ENGINEERING

J ANUARY 2005

i

Approval of the Graduate School of Natural and Applied Sciences

_____________________ Prof. Dr. Ibrahim Akman Director I certify that this thesis satisfies all the requirements as a thesis for the degree of Master of Science. _____________________ Prof. Dr. Ibrahim Akman Head of Department This is to certify that we have read this thesis and that in our opinion it is fully adequate, in scope and quality, as a thesis for the degree of Master of Science. _____________________ Prof. Dr. Ali Yazici Co-Supervisor _____________________ Dr. Deepti Mishra Supervisor

Examining Committee Members Prof. Dr. Ali Yazici Dr. Deepti Mishra Asst. Prof. Dr. Nergiz E. Çağıltay Dr. Ali Arifoğlu Asst. Prof. Dr. Çiğdem Turhan ii _____________________ _____________________ _____________________ _____________________ _____________________

ABSTRACT

A COMPARISON OF DATA WARE HOUSE DESIGN MODELS Başaran, Beril Pınar M.S., Computer Engineering Department Supervisor: Dr. Deepti Mishra Co-Supervisor: Prof. Dr. Ali Yazici January 2005, 90 pages

There are a number of approaches in designing a data warehouse both in conceptual and logical design phases. The generally accepted conceptual design approaches are dimensional fact model, multidimensional E/R model, starER model and object-oriented multidimensional model. And in the logical design phase, flat schema, terraced schema, star schema, fact constellation schema, galaxy schema, snowflake schema, star cluster schema and starflake schemas are widely used approaches.

References: [1] Romm M., Introduction to Data Warehousing, San Diego SQL User Group [2] Goyal N., Introduction to Data Warehousing, BITS, Pilani Lecture Notes [3] Franconi E., Introduction to Data Warehousing, Lecture http://www.inf.unibz.it/~franconi/teaching/2002/cs636/2 ,2002 Notes, [4] Pang L., Data Warehousing and Data Mining, Leslie Pang Web Site and Lecturer Notes [5] Gatziu S. and Vavouras A., Data Warehousing: Concepts and Mechanisms, 1999 [6] Thomas Connolly & Carolyn Begg., “Database Systems, 3th Edition”, AddisonWesley, 2002 [7] Gatierrez A. and Marotta A., An Overview of Data Warehouse Design Approaches and Techniques, Uruguay, 2000 [8] Reed Jacobson., “Microsoft® SQL Server 2000 Analysis Services”, ISBN 0-73560904-7, 2000 [9] Rizzi S., Open Problems in Data Warehousing., http://sunsite.informatik.rwthaachen.de/Publications/CEUR-WS/Vol-77/ DMDW 2003, Berlin, Germany [10] J. Han and M. Kamber, “Data Mining: Concepts and Techniques”, Chapter2: Data Warehouse and OLAP Technology for Data Mining, Barnes & Nobles, 2000 [11] W. H. Inmon, “Building the Data Warehouse, 3th Edition”, John Wiley, 2002 87 [12] Moody D. L. and Kortink M. A. R., From Enterprise Models to Dimensional Models: Methodology for Data Warehouse and Data Mart Design, http://sunsite.informatik.rwth-aachen.de/Publications/CEUR-WS//Vol-28/ http://sunsite.informatik.rwth DMDW 2000 , Stockholm, Sweden [13] Tryfona N., Busborg F., Christiansen J. G., starER: A Conceptual Model for Data Warehouse Design, Proceeding of the ACM 2nd International Workshop Data Warehousing and OLAP (DOLAP99), 1999 [14] Sapia C., Blaschka M., Höfling G., Dinter B., Extending the E/R Model for the Multidimensional Paradigm, Proceeding 1st International Workshop on Data Warehousing and Data Mining (DWDM98), 1998 [15] Golfarelli M., Maio D., Rizzi S., Conceptual Design of Data Warehouses from E/R Schemas, Proceeding of the 31st Hawaii International Conference on System Sciences (HICSS-31), Vol. VII,1998 [16] Golfarelli M., Maio D., Rizzi S., The Dimensional Fact Model: A Conceptual Model For Data Warehouses, International Journal of Cooperative Information Systems (IJCIS), Vol. 7, 1998 [17] Golfarelli M, Rizzi S., A Methodological Framework for Data Warehouse Design, Proceeding of the ACM DOLAP98 Workshop, 1998 [18] Lujan-Mora S., Trujillo J., Song I., Multidimensional Modeling with UML Package Diagrams, 21st International Conference on Conceptual Modeling (ER2002), 2002 [19] Trujillo J., Palomar M., An Object Oriented Approach to Multidimensional Database Conceptual Modeling (OOMD) , Proceeding 1st International Workshop on Data Warehousing and OLAP (DOLAP98), 1998 [20] Kimball R., http://www.dbmsmag.com/9708d15.html “A Dimensional Modeling Manifesto”, DBMS Magazine, Aug 1997 [21] Kimball R., “The Data Warehouse Toolkit”, John Wiley, 1996 [22] Martyn T., Reconsidering Multi-Dimensional Schemas, SIGMOD Record, Vol. 33, No. 1, 2004 [23] Elmasri R., Navathe S., “Fundamentals of Database Systems”, 3rd Edition, Addison-Wesley, 2000 [24] Ballard C., Herreman D., Schau D., Bell R., Kim E., and Valencic A., “Data Modeling Techniques for Data Warehousing”, IBM Redbook, IBM International Technical Support Organization, 1998 88 [25] Firestone J., Object-Oriented Data Warehousing, 1997 [26] Kimball R., Enforcing the Rules, 2000 , http://www.intelligententerprise.com/000818/webhouse.jhtml?_requestid=380244 [27] Kimball R., The Software Developer in Us, 2000, http://www.intelligententerprise.com/000908/webhouse.jhtml http://www.intelligententerprise.com/000908/webho [28] Microsoft Developer Network (MSDN) Library, XML Web Services Overview, October 2004 [29] Hahn K., Sapia C., and Blaschka M., Automatically Generating OLAP Schemata from Conceptual Graphical Models, Proceedings ACM 3rd International Workshop Data Warehousing and OLAP (DOLAP 2000), 2000 [30] Mora-Lujan S., Multidimensional Modeling Using UML and XML, Proceedings 16th European Conference on Object-Oriented Programming (ECOOP 2002), 2002 [31] Golfarelli M., Rizzi S., WAND: A Case Tool for Data Warehouse Design, Demo Proceedings of The 17th International Conference on Data Engineering (ICDE 2001), 2001 [32] Chaudhuri S., Dayal U., An Overview of Data Warehousing and OLAP Technology, ACM Sigmod Record, vol.26, 1997 [33] Golfarelli M., Rizzi S., Designing the Data Warehouse: Key Steps and Crucial Issues, Journal of Computer Science and Information Management, 1999 [34] Phipps C., Davis K., Automating Data Warehouse Conceptual Schema Design and Evaluation, DMDW’02, 2002 [35] Peralta V., Marotta A., Ruggia R., Towards the Automation of Data Warehouse Design, 2003 [36] Batini C., Ceri S., Navathe S., “Conceptual Database Design-An Entity Relationship Approach”, Addison-Wesley, 1992 [37] Abello A., Samos J., Saltor F., A Data Warehouse Multidimensional Data Models Classification, Technical Report, 2000 [38] Abello A., Samos J., Saltor F., A Framework for the Classification and Description of Multidimensional Data Models, Database and Expert Systems Applications, 12th International Conference, 2001 [39] Teklitz F., The Simplification of Data Warehouse Design, Sybase, 2000 89 [40] Prosser A., Ossimitz M., Data Warehouse Management, University of Economics and Business Admin., Vienna, 2000 [41] Ahmad I., Azhar S., Data Warehousing in Construction: From Conception to Application, First International Conference on Construction in the 21st Century (CITC2002) “Challenges and Opportunities in Management and Technology” , 2002 [42] Kimball R., Letting the Users Sleep, Part 1, DBMS, 1996, http://www.dbmsmag.com/9612d05.html http://www.dbmsmag.com/9612d05.ht [43] Kimball R., Letting the Users Sleep, Part 2, DBMS, 1997, http://www.dbmsmag.com/9701d05.html 90