What is the difference between data modelling and dimensional modelling?

Question

I've been learning about Data warehousing concepts and I found these 2 topics little confusing. I've read multiple blog posts and I understood that data modelling consists of three steps

1. Conceptual Data Model
2. Logical Data Model
3. Physical Data Model

and in data warehousing we need to perform certain steps:

Step 1: Identify the dimensions

Step 2: Identify the measures

Step 3: Identify the attributes or properties of dimensions

Step 4: Identify the granularity of the measures

Are these modelling techniques related to each other? If yes, how are this related. If someone asks, how to design a data warehouse, what should be the correct answer. Where does these modelling techniques comes in while designing a data warehouse.

It would be really helpful, if someone could provide me any link/resource about data modelling and dimensional modelling scenarios.

Answer 1

As the name suggests, a conceptual model is very high level and does not correspond directly to what actually gets built. Logical/physical models do correspond to what you are actually going to build - the difference between the two is that a logical model is system-independent while a physical model is tied to the platform/DB where it is going to be deployed. However they are fundamentally identical in that most modelling tools can automatically generate a physical model from a logical one (and vice versa).

A dimensional model is a type of logical/physical model, in the same way that OLTP, Inmon, Data Vault, etc. are types of logical/physical model. There are normally best practices defined for the steps required to design each of these model types - and you have listed the steps specific to designing a Dimensional model.

So for a given data domain (e.g. a Sales organisation), you would normally have a single Conceptual model and then multiple logical/physical models. Usually these would be one transactional model and one analytical model; the transactional model could be OLTP or NoSQL (or whatever suits your requirements/technology the best); the analytical model could be Dimensional, Inmon, Graph, etc. - again whatever suits your data/analytical requirements the best.