Skip to Content
0
Former Member
Apr 12, 2016 at 09:54 AM

HOW TO LOAD A FACT TABLE USING SOURCE BASED CDC

280 Views

Good day everyone,


I am doing the STAR SCHEMA Of the Mantis Bug Tracker

I have attached my star schema , I want to design the Fact table thinking about incremental loading using Source-based CDC.

I have two options 1) or 2)

1) Must I include in the fact table the two fields Star_Date and End_Date ? as you can se below.

2) As you can see in the snapshot In the Fact Table I have the ratio field NM_Incidencia which is the ID of the Bug, it is auto-increment.

I have also in the operational the date when this bug was saved -> FC_Fecha_grabacion_incidencia .

I have created a timestamp -> FC_Proceso which contains the sysdate when the first Full Loading was done.

In order to load fact table :

Load all the records in Fact Tables that FC_Fecha_grabacion_incidencia > FC_Proceso

Please tell wich method is much better for Source-based CDC. 1) Or 2) ¿?



1) -> Star_Date and End_Date


2)-> FC_Fecha_grabacion_incidencia > FC_Proceso




Thanks in advance .

Best Regards

Antonio





SCD TYPE 2

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key. Here 2 new column columns are inserted called start_date and end_date.

Let consider below given data is our target data after the first run.

ID

Name

IDate

designation

Start_Date

End_Date

1

John

2002.12.01

A

2002.12.01

9000.12.31

2

Jay

2002.12.01

A

2002.12.01

9000.12.31

3

Jasil

2002.12.01

A

2002.12.01

9000.12.31

During the next run, Consider the designation of John is changed to ‘B’ on the date 2003.12.01 then the output will be.

ID

Name

IDate

Designation

Start_date

End_date

1

John

2002.12.01

A

2002.12.01

2003.12.01

2

Jay

2002.12.01

A

2002.12.01

9000.12.31

3

Jasil

2002.12.01

A

2002.12.01

9000.12.31

4

John

2003.12.01

B

2003.12.01

9000.12.31

Here a new row is inserted and the end_date of the first row will be the start date of the newly updated value. (note:-‘ 9000.12.31’ is the default value)

In BODS it can be implemented by using following transformation.

Attachments

SCD2.JPG (18.3 kB)