Difference between ER Modeling and Dimensional Modeling

ER model is used for logical representation or the conceptual view of data. It is a high level of the conceptual data model. It forms a virtual representation of data that describes how all the data are related to each other. It is a complex diagram that is used to represent multiple processes. It helps to describe entities, attributes, and relationships. It helps to analyze data requirements systematically to produce a well-designed database. At the view level, the ER model is considered a good option for designing databases.

Data in a warehouse are usually in the multidimensional form. Dimensional modeling prefers keeping the table denormalized. The primary purpose of dimensional modeling is to optimize the database for faster retrieval of the data. The concept of Dimensional Modelling was developed by Ralph Kimball and consists of “fact” and “dimension” tables. The primary purpose of dimensional modeling is to enable business intelligence (BI) reporting, query, and analysis. 

Dimensional modeling is a form of modeling of data that is more flexible from the perspective of the user. These dimensional and relational models have their unique way of data storage that has specific advantages. Dimensional models are built around business processes. They need to ensure that dimension tables use a surrogate key. Dimension tables store the history of the dimensional information.

Difference between ER Modeling and Dimensional Modeling:

S.N     o	ER Modeling	Dimensional Modeling
1	It is transaction-oriented.	It is subject-oriented.
2	Entities and Relationships.	Fact Tables and Dimension Tables.
3	Few levels of granularity.	Multiple levels of granularity.
4	Real-time information.	Historical information.
5	It eliminates redundancy.	It plans for redundancy.
6	High transaction volumes using few records at a time.	Low transaction volumes using many records at a time.
7	Highly Volatile data.	Non-volatile data.
8	Physical and Logical Model.	Physical Model.
9	Normalization is suggested.	De-Normalization is suggested.
10	OLTP Application.	OLAP Application.
Ex	The application is used for buying products from e-commerce websites like Amazon.	Application to analyze buying patterns of the customer of the various cities over the past 10 years.

My Personal Notes arrow_drop_up

Save