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.


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.

| improve this answer | |
  • 1
    And you can always join the fact table to the dimension, retrieve the business key, join again to the dimension on the business key + expiryDate = '9999-12-31 23' to report on a fact with the latest, rather than the as-of dimension version. If you do this a lot you might 'push down' the business key to the fact table. – David Browne - Microsoft Apr 4 '18 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.