Dimensional model VS De-normalized model

Question

I am asking this question in context of Data Warehousing only.

Are Dimensional models & De-normalized models the same or different ? As far as I have heard from DW enthusiast, there is nothing called Normalized or De-normalized data model.

But my understanding is, breaking down the Dimensions i.e. Snow-flaking is the Dimensional model. Whereas the model with flattened hierarchy dimensions is called a De-normalized data model. Both are data modelling concepts in Data Warehousing.

I need your expert advice on this.

And what we can we call the data model that does not have surrogate keys but instead has the primary keys - codes from the operational (OLTP) system to join Fact-Dimension together?

Answer 1

A Dimensional model is normally thought of as 'denormalised', because of the way dimension tables are handled.

A data warehouse with 'snowflaked' dimensions can still be called a dimensional model, but they're not the advice of Kimball, whose approach is what most people think of when they think of dimensional modelling.

Breaking down the dimensions (i.e. snowflaking) is normalising those tables, and dimensional modelling (as described by Kimball) suggests avoiding snowflaking where possible, although people of course sometimes do, for all sorts of reasons. The model with flattened hierarchy dimensions is a denormalised data model, and this is the main thing that people mean when they talk of a dimensional model.

As for a system that doesn't have surrogate keys: that could also be called a data warehouse also, you could also call it a dimensional model, but is against the recommended approach by Kimball (whether for better or worse!).