Data Warehouse Schema Types

Star Schema:

Star Schema is a simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.

Note: Star Schema is a de-normalized form

Benefit of Start Schema: 
Star schema is probably most popular schema in dimensional modelling because of its simplicity and flexibility. In a Star schema design, any information can be obtained just by traversing a single join, which means this type of schema will be ideal for information retrieval (faster query processing). In this all hierarchies (or levels) of the members of a dimension are stored in the single dimension table. 

Snowflake Schema:

A snowflake schema is similar to a star schema structure but normalized through the use of outrigger tables. Here one or more dimension tables are connected with other dimension table as well as with the central fact table.

Note: Star Schema is a normalized form

Benefit of Snowflake Schema: 

In OLAP, this Snowflake schema approach increases the number of joins and poor performance in retrieval of data. In few organizations, they try to normalize the dimension tables to save space. Since dimension tables hold less space, Snowflake schema approach may be avoided.

Fact Table:
The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.

Fact table can be added across several dimensions. Fact tables store different types of measures like additive, non additive and semi additive measures.

A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables). 

Fact table that contains no measures or facts. These tables are called as Factless Fact tables.

Measure Types:

• Additive - Measures that can be added across all dimensions. 
• Non Additive - Measures that cannot be added across all dimensions. 
• Semi Additive - Measures that can be added across few dimensions and not with others.