Handling records in Fact when dimension is Type 2

Hello,

I have a question on loading fact when my dimension is of type 2.

Eg:

I have a customer information loaded into cust dimension with the following information on day 1.

CUSTKEY   CUSTID   CUSTNAME    CUSTADDR            CURRFLAG
100            10A        PETER          16 PARK PLAZA           N                -- Day1
101            10A       PETER          16 PARK ROAD            Y                -- Day2

Here, during day 1, my fact will have customer record with CUSTKEY as 100 but when customer sends address change request on day 2, a new record in dimension will be created but still my FACT points to the old record with CUSTKEY = 100.

Should we go for fact truncate and load in this case or my dimension should be type 1.

Please advise.

That's why it's called historical reporting. Your fact points to the values when they occurred. If you don't want this behavior, change the dimension to type 1.

Please refer forum discussion at -

http://forum.kimballgroup.com/t2584-modified-scd-2-implementation

I have faced similar issue and implemented the so called SCD Type 7. You can refer following page for the same -

http://www.kimballgroup.com/2013/02/05/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/ ( refer last section describing Type 7).

Regards,
Abhiraizada

Hi,

Thanks for your reply.

Do you mean Type 7 as inclusion of a mini dimension table with its key as a foreign key to Fact?