There are several dimension types are available
The data will be load only once in a table and it will re-use across multiple projects.
If a dimension table shared with more than one fact table (or) having foreign key more than one fact table. Then that dimension table is called confirmed dimension.
If a fact table act as dimension and it’s shared with another fact table (or) maintains foreign key in another fact table and its satisfy lateral requirements of the customer.such a table called degenerated dimension.
A junk dimension contains text values,attribute, genders,(male/female),flag values(True/false) and which is not use full to generate reports. Such dimensions is called junk dimension.
If a record occurs more than one time in a table by the difference of non key attribute such a table is called dirty dimension
SLOWLY CHANGING DIMENSIONS:
There are 3 types of SCD‘s Available in DWH.
Type1: It always maintains current data and updated data
Type2: It always maintains current data and full historical data
Type3: It always maintains current data and partial historical data
After implementing SCD Type1
In SCD Type-I If a record exists in source table and not exists in target table then simply insert a record into target table (103 record) if a record exists in both source and target tables then simply update source record into target table(100,101)
While implementing SCD Type-II there are two extra columns are maintained in target called Effective Start Date and Effective End Date .Effective start date is also part of primary key.
If a record exists in source and not exists in target table then simply insert records into target table. while inserting put Effective Start Date is equal to current date and effective end date set null.
If a record exists in both source and target tables even though we are inserting a source record into target table but before insert a record into target table the existing record in target table update effective End Date=CurrentDate-1.
Now insert source record into target table effective start date=Current Date and Effective End Date=Null
If a record exists in source and not exists in target table then simply insert records
Into target table. While inserting put Effective start Date is equal to Current Date and Effective End Date set Null.
If a record exists in both source and target tables then check target table count group by primary key if count=1 then update Effective End Date=Current Date-1 then simply insert source record into target record.
If count greater than one then delete a record into target table group by primary key where Effective End Date=Not Null. Now update target record Effective End Date=Current Date-1 Then simply insert source record into target.
and Having few more SCD Types.
we are not concentrating few dimensions like
Rapidly Changing Dimension