Data ware house concepts

What i s Fact?
A summarizable  numeric value used to monitor the business flow is called the Fact/measure.
Fact will act as Key performance indicator for business.
Example: salesamount,costamount,profit,units in stock etc.

Dimension : Dimension is descriptive Text used to analyse the facts
example  product,customer

Business Key:
In a dimension table which column loading the data of OLTP table primary key is called business key.
Usage :To join the dimension tables and OLTP tables in data loading Process.

Surrogate Key:
A primary key and auto generated column in a dimension table is called Surrogate Key.
Usage: 

• Surrogate key provides uniqueness to identify a row in the dimension table 
• Surrogate key used to combined the dimension table and fact table.

Dimension Table : Dimension attributes presented in dimension table .Every dimension should have key column i.e primary key.

Fact table: Facts/measures stored in fact table .Fact table contains Foreign key referred from dimension table .

Dimension Types :

1. Standard Dimension
2. Parent child Dimension 
3. Confirmed Dimension
4. Degenerated Dimension
5.  Role Play Dimension

Fact/Measure Types :

1. Additive measure
2. semi additive measure
3. Non-additive Measure 

The three schema models we use to organize the dimension tables fact tables in dimensional modelling .

1.Star schema:

• Fact Table surrounded by dimension tables looks like star .
• Dimension tables are de-normalized.
• No relation  between dimension tables 

2.Snow Flake schema:

• Fact table surrounded by few dimension tables only.
• Dimension tables are normalized.
• Relationship between the Dimension tables .

3.Integrated Schema Model:

• Combination of star and Snowflake schema
• Confirmed dimensions 
• fact less fact tables 
• degenerated dimensions.