Sql-server – Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2

business-intelligenceetlsql serverssis

I'm working on the production of a Data Warehouse with SSIS, and today we create the first dimension with SCD of type 2. The fact table is linked in the DIM_CIPESSOA dimension through the idcipessoa column.

When updating the dimension, as shown below, a new PK is generated and with this we need to update the FK in the fact table to connect the lines correctly.

Dimension Table:

idcipessoa  cgccpf_AltKey  tppessoa nomepessoa        effectiveDate            expiryDate               IsCurrent
----------- -------------- -------- ----------------- ------------------------ ------------------------ ---------
88393       00000000001    1        CUSTUMER OLD      2018-04-02 11:52:47.040  2018-04-02 22:29:05.243  0
169852      00000000001    1        CUSTOMER UPDATED  2018-04-02 22:55:08.540  9999-12-31 23:59:59.997  1

Fact Table

idFact  idcipessoa  tppessoa 
------- ----------- -------- 
1       88393       1.99

So, as in the example above, we need a strategy to align the fact table to be pointed to the new new key 169852.

Best Answer

No, you wouldn't update the fact table.

The fact record is tied to the version of the customer which existed between 2018-04-02 11:52:47.040 and 2018-04-02 22:29:05.243 in which they had a 1.99 tppessoa

If they generated a 3.1 tppessoa at 2018-04-03 00:00:00.000 then your fact table would look something like

idFact  idcipessoa  tppessoa 
------- ----------- -------- 
1       88393       1.99 
2       169852      3.1

Then you can see the activity for this customer as they have changed over time.

If you wanted to have the fact table's idcipessoa updated as the dimension changes, that's not a type dimension.

Related Question