Dimensional Model

We have seen the definition of Facts and Dimensions along with some examples in my earlier post, Let us understand the Dimensional modelling and two most commonly used logical design techniques i.e. Star schema and Snowflake schema.

Dimensional Model:

Dimensional Model is a logical design technique that presents the data in a standard, intuitive framework that allows for high-performance access. Every dimensional model is composed of one table which contains a multi-part key and numerical measures, called the fact table, and a set of smaller tables called dimension tables. Each dimension table has a primary key that corresponds exactly to one of the attributes in the fact table.

Star Schema:

The star schema is the simplest data warehouse schema. It is called a star schema because the entity-relationship diagram of this schema resembles a star, with points radiating from a central table. The center of the star consists of a large fact table and the points of the star are the dimension tables.

A star schema is characterized by one OR more very large fact tables that contain the primary information in the data warehouse, and a number of much smaller dimension tables (OR lookup tables), each of which contains information about the entries for a particular attribute in the fact table.

Example:

Snowflake Schema:

The snowflake schema is a more complex data warehouse model than a star schema. It is called so because the diagram of the schema resembles a snowflake.

Snowflake schemas normalize dimensions to eliminate redundancy i.e. the dimension data has been segregated into multiple tables instead of one large table.

From the above example Dim_Product table has been normalized/decomposed into Dim_Product, Dim_Product_Type table; similarly Dim_Location table has been normalized/decomposed into Dim_Location and Dim_City table.

Example: