Understanding the Difference between ER and Dimensional Modeling
Understanding the Difference between ER and Dimensional Modeling
Dimensional Modeling (DM) is a logical design technique that seeks to present data in a standard format that is intuitive and allows for high-performance access [1].
Fundamental idea of dimensional modeling is that every type of business data can be represented as a Cube or Hypercube. Dimensional Modeling is a favorite modeling technique in data warehousing. In DM, a model of tables and relations is constituted with the purpose of optimizing decision support query performance in relational databases, relative to a measurement or set of measurements of the outcome(s) of the business process being modeled [1].
In contrast, conventional E-R models are constituted to (a) remove redundancy in the data model, (b) facilitate retrieval of individual records having certain critical identifiers, and
(c) therefore, optimize On-line Transaction Processing (OLTP) performance [1].
The Table 1 shows the comparison between these two models.
Attributes to compare Function
Table
Purpose
Structure
Component
Attributes
ER data model
Standard for on-line transactional processes
(OLTP)[1], While there is consensus in the field of data warehousing on the desirability of using DM/star schemas in developing data marts, there is an on-going controversy over the form of the data model to be used in the data warehouse.
The "Inmonites," support a position identified with Bill
Inmon, and contend that the data warehouse should be developed using an E-R model
[2].
One table per entity
Minimize data redundancy, optimize Update
A complex maze of hundreds of entities linked with each other[4] Split as per the entities
All attributes for an entity including textual as well as numeric, belong to the entity table DM data model
Standard for BI reporting and on-line analytical processes
(OLAP)
One fact table for data organization Maximize understandability, optimize for
Dimensional Modeling (DM) is a logical design technique that seeks to present data in a standard format that is intuitive and allows for high-performance access [1].
Fundamental idea of dimensional modeling is that every type of business data can be represented as a Cube or Hypercube. Dimensional Modeling is a favorite modeling technique in data warehousing. In DM, a model of tables and relations is constituted with the purpose of optimizing decision support query performance in relational databases, relative to a measurement or set of measurements of the outcome(s) of the business process being modeled [1].
In contrast, conventional E-R models are constituted to (a) remove redundancy in the data model, (b) facilitate retrieval of individual records having certain critical identifiers, and
(c) therefore, optimize On-line Transaction Processing (OLTP) performance [1].
The Table 1 shows the comparison between these two models.
Attributes to compare Function
Table
Purpose
Structure
Component
Attributes
ER data model
Standard for on-line transactional processes
(OLTP)[1], While there is consensus in the field of data warehousing on the desirability of using DM/star schemas in developing data marts, there is an on-going controversy over the form of the data model to be used in the data warehouse.
The "Inmonites," support a position identified with Bill
Inmon, and contend that the data warehouse should be developed using an E-R model
[2].
One table per entity
Minimize data redundancy, optimize Update
A complex maze of hundreds of entities linked with each other[4] Split as per the entities
All attributes for an entity including textual as well as numeric, belong to the entity table DM data model
Standard for BI reporting and on-line analytical processes
(OLAP)
One fact table for data organization Maximize understandability, optimize for