From ER Models to Star Schemas: Mapping From Operational Databases to Data Warehouse Designs Daniel L. Moody Department of Cybernetics Czech Technical University e-mail: dmoody@labe.felk.cvut.cz School of Business Systems, Monash University e-mail: dmoody@infotech.monash.edu.au Mark A.R. Kortink Kortink and Associates Melbourne, Australia e-mail: mark@kortink.com Abstract Dimensional modelling is a conceptual modelling technique developed for designing data warehouse structures. It has become the predominant approach to designing data warehouses in practice and has proven to be highly successful in developing database structures that can be used directly by end users. This paper examines the nature of dimensional modelling and its relationship to traditional Entity Relationship (ER) modelling. It shows that a dimensional model is just a restricted form of an ER model, and that there is quite a straightforward mapping between the two. Understanding the relationship between the two types of models can help to bridge the gap between operational system (OLTP) design and data warehouse (OLAP) design. It also helps to resolve the difficult problem of matching supply (operational data sources) and demand (end user information needs) in data warehouse design. Finally, it results in a more complete dimensional design, which is less dependent on the designer’s ability to choose the “right” dimensions. The paper also reports some preliminary results from empirical testing of the approach. Keywords Dimensional model, Entity Relationship (ER) model, star schema, data warehouse, OLAP INTRODUCTION Dimensional Modelling Dimensional modelling is a conceptual modelling technique developed for designing data warehouses (Kimball, 1996; 1997; 2002). Its objectives are to create database structures that end users can easily understand and write queries against, and to optimise query performance. It has become the predominant approach to designing data warehouses in practice and has proven to be a major breakthrough in developing database structures that can be understood and used directly by end users. Dimensional modelling is based on a single, highly regular data structure called a star schema. A star schema consists of one central table called the fact table (which forms the centre of the star), surrounded by a number of dimension tables (which form the points of the star) (Figure 1): Moody, Kortink (Paper #282) 14th Australasian Conference on Information Systems Page 1 26-28 November 2003, Perth, Western Australia