Data Warehouse Concepts

Types of tables in Data warehouse:

1. Dimensions

2. Facts

Dimensions: These are attributes about facts.  Dimensions stores textural description about the business.

Facts:It stores business measurements. Facts are actual  transactions or values being analyzed.  They contain composite primary key where each attribute of primary key is a foreign key to the dimension table.

Fact table consists of keys and measures.

Types of facts:

1. Additive facts:

Business  measurements in a fact table that can be summed up in the fact table.

2. Semi additive facts

Business  measurements in a fact table that can be summed up Through only some dimension keys in the fact table.

3. Non Additive facts

Business  measurements in a fact table that can be summed up across any  dimension keys in the fact table

4. Cumulative fact table: It describes what was happend over a period of time.  This fact table consists of additive facts.

5. Snapshot fact table: It describes the state of things in a particular instance of time. This consists of semi additive and non additive facts.

Types of Dimensions:

1. Confirmed Dimension:

Dimensions that is shared across multiple facts or Dimension that is directly shared to join more fact tables.

Shrunken Dimensions: dimension that is subset to another dimensions or dimensions that is not directly linked to the fact table.

2. Junk dimensions:

Dimension that is organized based on low cordinality indicator of flags. Like male or female , payment mode cash or card.