From Enhanced ER to Multidimensional Model: A Methodology for Data Warehouse Design

Abstract

Schema transformation is the process of generating dimensional models from conceptual models. Transformation methodologies use entity relationship diagrams as input and provides guidelines to produce resultant dimensional models. Previous studies reveal that only ERD models are used for the transformation. Many techniques have been introduced for the conversion of basic ERD model into different data warehouse schemas i.e. star schema, snowflake schema etc. After applying and concluding that existing transformation techniques of ER model are not capable enough to generate star model when EER is provided as an input, this study is conducted to design data warehouse schema using Enhanced ER model as it possesses some additional features that provide more detailed information about the database system. A semi-automated technique is developed to produce star models from EERs. The study provides the solution for the complex associations i.e. disjoint and overlapping entities by translating them into hierarchies and then, after applying some rules, produce dimensions of the star model. It is thus declared by the results that proposed technique helps the novice and intermediate designer to develop data warehouse models more precisely.