what are the TYPES OF DIMENSION TABLES IN DATA WAREHOUSE?

There are several dimension types are available

CONFORMED DIMENSION:

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.

DEGENERATED 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.

JUNK 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.

DIRTY 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

 

EXERICE-1:

no name sal
100 Bhaskar 1500
101 Mohan 2000
103 Sanjeev 2000

 

no name sal
100 Bhaskar 1000
101 Mohan 1500
102 Srikanth 2000

 

After implementing SCD Type1

no name sal
100 Bhaskar 1500
101 Mohan 2000
102 Srikanth 2000
103 Sanjeev 2000

 

Type-I:

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)

 

Type-II:

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

no name sal Effective_Strat_Date Effective_End_Date
100 Bhaskar 1000 2011-01-31 2012-04-05
101 Mohan 1500 2011-01-31 2012-04-05
102 Srikanth 2000 2011-01-31 2012-04-05
100 Bhaskar 1500 2011-02-01 Null
101 Bhaskar 2000 2011-02-01 Null
103 Sanjeev 2000 2011-02-01 Null

 

Type-III:

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

Static Dimension

Dynamic Dimension

Related Posts Plugin for WordPress, Blogger...

Be the first to comment

Leave a Reply

Your email address will not be published.


*