Schema and Types of Schema in Data Warehouse

We know that  Dimensional modeling is the design concept used by many data warehouse designers to build their data warehouse. Now we will discuss about the Schema and Types of Schema in Data Warehouse.

What is Schema ?

In simple words, Schema is the logical description of entire database. Schema tells about how a database is designed and organized.

A schema is a collection of database objects, including tables, views, indexes, and synonyms.

There is a variety of ways of arranging schema objects in the schema models designed for data warehousing.The determination of which schema model should be used for a data warehouse is based upon the requirements and preferences.

Types Of Schema :

Based on the arrangement of database objects in different ways, Schema in data warehouse is divided mainly into two types. Types Of Schema in Data Warehouse are as below :

  1. Star Schema
  2. Snowflake Schema

1. Star Schema :-

Star Schema is the simplest and most used data warehouse schema.It is called a star schema because the entity-relationship diagram of this schema is similar to 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.

All measures in the fact table are related to all the dimensions that fact table is related to. In other words, they all have the same level of granularity.A star schema can be simple or complex. A simple star consists of one fact table; a complex star can have more than one fact table.

2. Snowflake Schema :-

Snowflake Schema is more complex than Star Schema and is often called as an extension of Star Schema. A snowflake schema is a logical arrangement of tables in a relational database such that the entity relationship diagram resembles a snowflake in shape.

The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions.The dimensions of a snowflake schema are elaborate, having multiple levels of relationships, and where child tables have multiple parent tables. Snow flake schema are useful when there are low cardinality attributes in the dimensions.


Leave a Reply