Is a star schema a denormalized schema?

Question

An OLAP database consists of data in denormalized form. This means data redundancy and this data redundancy helps retrieve data through less number of joins, hence facilitating faster retrieval.

But a popular design for OLAP database is fact-dimension model. Fact table will store numerical fact-based entries (# of Sales, etc.) while dimension tables will store "descriptive attributes" related to the fact, i.e. details of the customer to which the sale was made.

My question is, in this design, it does not seem denormalized at all, as all dimension tables will have foreign key references to the fact table. How is it different from an OLTP design?

Answer 1

The denormalization is in the dimension tables in a star schema: E. g. in a product table, you explicitly have many columns like several levels of product category in this one table, instead of having one table for each level, and using foreign keys referencing those values.

This means you have normalization with regard to facts, but stop normalizing on the dimension tables.

Furthermore, you often do not even completely normalize the facts. A typical example would be this: in a completely normalized table, you would use only two columns 'number of units sold' and 'price per unit', but in an OLAP database, it may make sense to redundantly have another column for the 'sales value' which could easily be calculated by multiplying units sold and the price per unit.

Answer 2

You can get the difference if you study first "highly normalized schemas".
https://www2.microstrategy.com/producthelp/10.6/ProjectDesignGuide/WebHelp/Lang_1033/Content/ProjectDesign/Highly_normalized_schema__Minimal_storage_space.htm

Will give you an example: Consider a "city" inside a "country" for a "person",
all what you need to store for a person is only his "city" because anyway that city resides in a "country". 
so you don't have also to store the "country" in the "person" table. 
This approach will have advantage of "minimal" storage. 
But as disadvantage it will be annoying to retrieve "country" for a "person"
 since you will have to do many joins to achieve that.

So regarding your question, in your design, if we stored both "city_id" and "country_code" in "person" table, 
this will cause little redundancy but as advantage it will be more easier to get "person" "country" by directly joining the two tables "Countries" and "person" together. 

Normalization main purpose is to remove redundancy. And to achieve data consistency. 
For example, in your case OLAP , developer can make mistake by inserting correct "city_id" and wrong "country_id" 
for example he can insert "Paris" as city and by mistake he can insert "Germany" as the country which is wrong.
If the schema is fully normalized, this cannot never happens since it will store only "Paris" "city id" in "party" table and will not store "country" id.

  So yes, OLAP is de-normalized since it allows data redundancy and developers (application) mistakes (if any).