From ER Models to Dimensional Models: Bridging the Gap between OLTP and OLAP Design, Part I (original) (raw)

BRIDGING THE GAP Dimensional modeling is a database design technique developed specifically for designing data warehouses. Its objectives are to create database structures that end users can easily understand and write queries against, and to optimize query performance. It has become the predominant approach to designing data warehouses in practice and has proven to be a major breakthrough in developing databases that can be used directly by end users. Dimensional modeling is not based on any theory, but has clearly been very successful in practice. This article, the first in a two-part series, examines the nature of dimensional modeling and proposes a possible explanation for why it has been so successful. This article also explodes the popular myth that traditional ER modeling and dimensional modeling are fundamentally different and somehow incompatible. It shows that a dimensional model is just a restricted form of an ER model, and that there is a straightforward mapping between the two. An ER model can be transformed into a set of dimensional models by a process of selective subsetting, denormalization, and (optional) summarization. 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 can also help 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.