Dimension Table Vs Fact Table

Dimension Table features

·         It provides the context /descriptive information for a fact table measurements.

·          Provides entry points to data.

·          Structure of Dimension - Surrogate key one or more other fields that compose the natural key (nk) and set of  attributes.

·          Size of Dimension Table is smaller than Fact Table.

·          In a schema more number of dimensions are presented than Fact Table.

·          Surrogate Key is used to prevent the primary key (pk) violation(store historical data).

·          Values of fields are in numeric and text representation.

Fact Table features

·         It provides measurement of an enterprise.

·         Measurement is the amount determined by observation.

·         Structure of Fact Table - foreign key (fk) Degenerated Dimension and Measurements.

·         Size of Fact Table is larger than Dimension Table.

·         In a schema less number of Fact Tables observed compared to Dimension Tables.

·         Compose of Degenerate Dimension fields act as Primary Key.

·         Values of the fields always in numeric or integer form.

The main difference between dimension and the fact table is that Dimension preserves the historical data (like in case of type2) we will have to use update strategy and other transformations to make that happen but fact will be a direct load with few one or more lookups from the dimension and also since the fact and dimenision has the foriegn key relationship the dimension has to be loaded first before the fact.

I think there won't be any logic difference in a mapping to load dimension table & fact table. We can load the dimension table directly but we can't load the fact table first. So to load the fact table we need to load the dimension table first. Also while loading the fact table we will make a lookup on the dimensioin table cause the fact table contains the measures/facts & the foreign keys which are primary keys in the dimension tables surrounded to that fact table. We can load the dimension table & fact table in one mapping by using the Target Load Order/Target Load Plan in informatica.

Target 1 (Dimension Table)
Target 2 (Fact Table)

Dimention Table - A pure dimention table is a collection of primary keys
Fact Table - A pure fact table is collection of foreign keys.

Fact table contains numeric facts. i.e. key performence indicatiors. A dimention table is a primary key foregin key relation to fact tbale.

To load the fact table we need to load the dimension table first. Also while loading the fact table we will make a lookup on the dimensioin table, cause the fact table contains the measures/facts & the foreign keys which are primary keys in the dimension tables surrounded to that fact table. We can load the dimension table & fact table in one mapping by using the "Target Load Order/Target Load Plan" in informatica.

What is SCD.?

SCD: Slowly changed dimension

The SCD how to Capture the change data with respect to the time.

For ex: In an organization emp sal is chaged 
        according to his perfomence after chaned sal we kept in 
        datawarehouse. 

(Datawarehouse maintain a Historical data )
there are 3 types of SCDs.
Type1: It will maintain curren data only. It does't 
maintain historical data.
Type2: It maintain current and historical data.
Type3: It maintain current and previous data it mease 
Parcial data only maintain.
So generelly datawarehouser is maintain Historical data 
hence Type 2 is useful