Data warehouse Schemas

The schema is a logical description of the entire database. The schema includes the name and description of records of all record types including all associated data-items and aggregates. Likewise the database the data warehouse also require the schema. The database uses the relational model on the other hand the data warehouse uses the Stars, snowflake and fact constellation schema. In this chapter we will discuss the schemas used in data warehouse.

Star Schema

•  In star schema each dimension is represented with only one dimension table. 
• This dimension table contains the set of attributes.
•  In the following diagram we have shown the sales data of a company with respect to the four dimensions namely, time, item, branch and location.
•  There is a fact table at the centre.
•  This fact table contains the keys to each of four dimensions. The fact table also contain the attributes namely, dollars sold and units sold.

Note: Each dimension has only one dimension table and each table holds a set of attributes. For example the location dimension table contains the attribute set {location_key, street, city, province_or_state, country}. This constraint may cause data redundancy. For example the "Vancouver" and "Victoria" both cities are both in Canadian province of British Columbia. The entries for such cities may cause data redundancy along the attributes province_or_state and country.
 Snowflake Schema

•  In Snowflake schema some dimension tables are normalized. 
• The normalization split up the data into additional tables.
•  Unlike Star schema the dimensions table in snowflake schema is normalized for example the item dimension table in star schema is normalized and split into two dimension tables namely, item and supplier table.
• Therefore now the item dimension table contains the attributes item_key, item_name, type, brand, and supplier-key. 
• The supplier key is linked to supplier dimension table. The supplier dimension table contains the attributes supplier_key, and supplier_type.

 

Note: Due to normalization in Snowflake schema the redundancy is reduced therefore it becomes easy to maintain and save storage space.

Fact Constellation Schema

• In fact Constellation there are multiple fact tables. This schema is also known as galaxy schema.
• In the following diagram we have two fact tables namely, sales and shipping.
• The sale fact table is same as that in star schema.
•  The shipping fact table has the five dimensions namely, item_key, time_key, shipper-key, from-location. 
• The shipping fact table also contains two measures namely, dollars sold and units sold. 
• It is also possible for dimension table to share between fact tables. For example time, item and location dimension tables are shared between sales and shipping fact table.