Types of Data Warehousing Schema

************************************************************************

Data Warehousing Schema              

In designing the data models of data warehouses/ data marts, the most commonly used schema types are

1. Star schema
2. Snowflake schema
3. Fact Constellation Schema

We will see each schema one by one in details.

1. Star schema:-

Definition: The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram looks like star, with points radiating from center. Here, center of diagram/star is Fact table and points of diagram/star are dimension tables.

In this schema, fact table always sits in the middle and radially connected to surrounding objects (dimension tables). It looks like star. Here, each dimension is represented as single table. The primary key in each dimension table is related to a foreign key in fact table.

A star schema can be simple or complex depending upon number of fact tables and associated dimension tables. A simple star schema consists of one fact table and complex star schema can have more than one fact table. Below is the example of star schema:
In this example fact table is Sales (sits in center) & tables Item, Time, Branch, Location are dimension tables.

2. Snowflake schema:-

Definition: Snowflake schema is nothing but extension of star schema where every point/ dimension table of star explodes into more points. In star schema, each dimension/point is represented by single dimension table where as in snowflake schema dimension table is again normalized into multiple tables.

Main advantage of snowflake schema is improvement in query performance due to minimized disk space storage requirements and joining smaller lookup tables & main disadvantage of the snowflake schema is additional maintenance efforts needed due to increase in tables. For better understanding below is the example of snowflake schema:

In this example, fact table is Sales (sits in center) & tables Time , Item, Branch, Location are dimension table but you can see some of dimension tables (Item, Location) are again exploded/normalized into more tables such as tables Item extended to Supplier and table Location is extended to City.

3. Fact Constellation schema:-

This schema is used mainly for aggregate fact tables or if we need to split the fact table for better comprehension. The split of fact table is done only when we want to concentrate on aggregation over few facts and dimensions. The fact constellation architecture contains multiple fact tables that shares many dimension tables.For better understanding below example is given:

Here, two fact tables are there Sales and Shipping which shares many dimension tables such as dimension tables Item, Time, Location. This schema is very complicated to design.

************************************************************************************************************