DW Schemas

Data Warehousing Schemas:



Before discussing about the Data warehousing schemas, let's understand what is actually a 'schema'.

Note: There are many questions which are being asked in an interview based on DW Schemas.

A schema is nothing but a collection of database objects which includes:

• Tables
• Views
• Indexes
• Synonyms
  
  Hence, there are a variety of ways of arranging schema objects in the Schema models designed for Data warehousing. These are as follows:
  
  
  1. Star Schema
  2. Snowflake Schema
  3. Galaxy Schema
     
     These are discussed in detail as follows:
     
     1. STAR SCHEMA:
     
     A star schema consists of one Fact table connected to a set of dimensional tables that means there will be one Fact table (i.e. a centralized table) which is referring to many dimensional tables. Hence a 1:m relationship is followed in this case. The important point to note here is that the data in the Dimension table is De-normalised.
     Also, this should be kept in mind that there is no limit on how many dimensional tables will refer to the fact table. I have tried to show it with the help of a diagram given below.
  
  
  
  
  
  
         2. SNOWFLAKE SCHEMA:
  
  A snowflake schema is the one in which the dimension table is further classified into 2 dimension  tables as shown in the diagram below. Hence 1 Fact table refers to many dimension table and further dimension tables is classified into other Dimension tables or they can also be called as the normalised tables.
  
  
  
  
  
  
  
  3. GALAXY SCHEMA:
  
  In the Galaxy Schema, multiple Fact tables share Dimension tables viewed as a collection of stars. Hence it is called 'Galaxy Schema'.