ER model and dimensional model

Data Modeling

ER model and dimensional model


  1.Please explain the difference between ER model and dimensional model in a simple manner

 2.Is it necessary that tables present in both ER model and Dimensional model should be in third normal form

3.Is Enterprise Datawarehouse(EDW) constructed using ER model?

4.Is Datamart constructed using Dimension model?

5.Does Star schema and snow flake schema come under dimensional model?

6.Please suggest some good books which explain data modelling concepts in a simple manner

Teradata Employee

Re: ER model and dimensional model


1. Well, in simple words, ER is a way of representing models, you can have Relational, Dimensional o Conceptual  models in ER diagrams. If you mean Relational by ER, the main difference is that Relational represents Business Rules and Dimensional represents Navigation Rules (how information flows versus how information is being shown)

2. Usually Relational models are 3NF and Dimensional could be under 3NF or another modeling technique as Stars or Snowflakes

3. It's a good practice for EDW to get constructed as a 3NF Relational Data Model, and using an ER Diagram for showing users and IT people its value. 3NF delivers more value and an application neutral view of the information.

4. A Datamart is a specific subject oriented part of the warehouse and is usually built as a Dimensional model, because it refers more to navigating the infromation.

5. Yes, they definitively do

Hope this helps!


Re: ER model and dimensional model

Thanks a lot sebastian


Re: ER model and dimensional model


On this page of my Database Answers Web Site I discuss the different Types of Data Models that can exist :-

I am not sure that there a set of single agreed definitions but in general ERDs have Relationships and Dimensional Models do not.

Some people like to show some Relationships in Dimensional Models, then you have Snowflakes, rather than Stars.

1) Dimensional Models are characterised by the importance of Dimensions, such as Dates, Customers and Products and Locations.

They are commonly used for answering questions about summary data such as - "How many products did we sell in October ?" or more precise questions such as "How many Products did a particular Customer buy on a spedcific date ?

They do not require any underatanding of the 3NF relationships between the Tables or entities that store the data.

Behind the Dimensional Models will be 3NF Models that show how, for example, Customers are related to Products, Locaitons and Dates.

Semantic Models are used to show business users how things they are interested in are related.

For example, a Data Warehouse might have a Party entity but this would appear in a Semantic Model as a Customer.

2) Teradata believes that a Data Warehouse should be 3NF.

One benefit of this is it helps a 'Single View of the Truth'  which is very important

Therefore a 'Best Practice' sequence of Steps would look like this - 

a) Design a 3NF Data Warehouse that would contain all the Entities and Relationshoips and hold all the data.

b) Create Dimensional Models for Data Marts for specific subsets of data required for families of Reports.

These are very 'user-friendly' because they tell the users exactly what data is available and the user does not have to understand how the data is related.

Of course, the danger is that the users can ask questions of the data that do not make sense.

c) Creating Semantic Models solves this problem.


Barry Williams


Re: ER model and dimensional model

Thanks a lot Barry