Data Warehousing SCHEMAS

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

There are some special architectures which arranges the schema objects in the schemas models. The most common of them are :

* Star Schema
* Snowflake Schema
* Fact constellation Schema

Star Schema

It is one of the simplest architecture used in Data Warehousing. It is called the Star Schema because its diagram resembles a star with points radiating from center. The center of the star schema consists of facts (i.e. its a Fact Table) and the end points are the dimensions (i.e. Dimension Table).

The Fact tables in the star schema are in 3NF form whereas the Dimension tables are de-normalized.

Though its the simplest architecture, but its used by all as its suggested by Oracle.

Star Schema Formats :

Fact Table 

A typical Fact table has two types of columns i.e. Foreign Keys to dimension table and Measurements associated with those keys.

Dimension Table

Dimension Tables have data which have values that helps define that record. Ex. Take a dimension table from the above structure i.e. suppliers dimension table. If you look at the columnName you will get full information regarding that supplier key. The data in the dimension table can be of any level of granularity. The primary keys of each of the dimension tables are part of the composite primary key of the fact table.

Snowflake Schema

The Snowflake Schema is a more complex version of the Star Schema, as it has normalized Dimension Tables apart from the normalized Fact table in center.

We need to build complex queries with multiple joins in the tables thus is reduces the querying speed. And also from a developers point of view, he/she can't traverse through the data as easily as in Star Schema.

Fact Constellation 

A Fact constellation is also made up from a star schema, i.e it can be made by splitting the original star schema into more star schemes each of them describes facts on another level of dimension hierarchies.