As we have seen
the definition of Facts and Dimensions along with some examples yesterday, Let
us understand the Dimensional modeling and two most commonly used
logical design techniques i.e. Star schema and Snowflake schema.
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.
Snowflake schemas normalize dimensions to eliminate redundancy i.e. the dimension data has been segregated into multiple tables instead of one large table.
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.
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.
