From ER Models to Star Schemas
From ER Models to Star Schemas: Mapping From Operational Databases to Data Warehouse Designs
Daniel L. Moody and Mark A. R. Kortink
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.
From ER Models to Dimensional Models - Part I
From ER Models to Dimensional Models: Bridging the Gap between OLTP and OLAP Design, Part I
Daniel L. Moody and Mark A. R. Kortink
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.
From ER Models to Dimensional Models - Part II
From ER Models to Dimensional Models: Bridging the Gap between OLTP and OLAP Design, Part I
Daniel L. Moody and Mark A. R. Kortink
The first article in this series, which appeared in the Summer 2003 issue of the Journal of Business Intelligence (Moody and Kortink, 2003), described how to design a set of star schemas based on a data model represented in Entity Relationship (ER) form. However as in most design problems, there are many exceptions, special cases and alternatives that arise in practice. In this article, we discuss some advanced design issues that need to be considered.
These include:
- Alternative dimensional structures: snowflake schemas and starflake schemas
- Slowly changing dimensions
- Minidimensions
- Heterogeneous star schemas (dimensional subtypes)
- Minidimensions
- Dealing with non–hierarchically structured data in the underlying ER model:
- Many-to-many relationships
- Recursive relationships
- Subtypes and supertypes
The same example data model as used in the first article is used throughout to illustrate these issues.
From Enterprise Models to Dimensional Models
From Enterprise Models to Dimensional Models: A Methodology for Data Warehouse and Data Mart Design
Daniel L. Moody and Mark A. R. Kortink
This paper describes a method for developing dimensional models from traditional Entity Relationship models. This can be used to design data warehouses and data marts based on enterprise data models.
The first step of the method involves classifying entities in the data model into a number of categories. The second step involves identifying hierarchies that exist in the model. The final step involves collapsing these hierarchies and aggregating transaction data to form dimensional models.
A number of design alternatives are presented, including a flat schema, a terraced schema, a star schema and a snowflake schema. We also define a new type of schema called a star cluster schema. This is a restricted form of snowflake schema, which minimises the number of tables while avoiding overlap between different dimensional hierarchies.
Individual schemas can be collected together to form constellations or galaxies. We illustrate the method using a simple example.