none
Updating foreign keys in fact table

    Question

  • I am loading fact table and looking up with the dimensions to get the foreign key inserted into fact, but where lookup does not match i insert the value 0 which refers to unknownmember, now if my value is changed and i need to update the foreign key in fact in place of 0 how would i do that, because i am trying to update the values with which i am looking up? Can anyone help on this please
    mercredi 20 juin 2018 22:14

Réponses

  • Hi Ajay,

    Ideally this is against the rules of DW principles. You should not update the fact records. The best practice is to not load these rows into a fact table and rather store these records in a separate reject handling table and once the dimension keys are available then you can reload these fact records.

    However if you still need to update then you can write a stored procedure (something like fact correction stored procedure) which updates the keys for records where the key value=0.

    You can schedule this SP weekly or daily depending on the volume of data in your fact table.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Marqué comme réponse AjayDhanwani jeudi 12 juillet 2018 11:54
    mercredi 4 juillet 2018 13:32

Toutes les réponses

  • Hi Ajay,

    Ideally this is against the rules of DW principles. You should not update the fact records. The best practice is to not load these rows into a fact table and rather store these records in a separate reject handling table and once the dimension keys are available then you can reload these fact records.

    However if you still need to update then you can write a stored procedure (something like fact correction stored procedure) which updates the keys for records where the key value=0.

    You can schedule this SP weekly or daily depending on the volume of data in your fact table.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Marqué comme réponse AjayDhanwani jeudi 12 juillet 2018 11:54
    mercredi 4 juillet 2018 13:32
  • The way this is dealt with in a typical DW setup is as follows

    1. Every ETL process for the fact will have a step which will populate dimension table with missing business key values and with default values for all other attributes in case it cant find the corresponds business keys from dimension table to lookup and get the surrogate keys. This usually happens when dimension job didn't bring latest data compared to fact (may be due to some failure in job or due to difference in the process times between dim and fact tables). These facts are called Early Arriving Facts and the dimension table entries created this way with default values so as to preserve the referential integrity relationship are called Inferred Members. This is indicated usually by means of a  flag called IsInferred which is set to 1 for these rows

    2. In every process for dimension data load, there will be a branch for processing these inferred members created during previous ETL job run. What this does it to update the values of other attributes based on business keys when IsInferred = 1. This will ensure the referential integrity is kept intact and you dont need to do any further updates/modification to fact table foreign keys

    see more details here

    https://www.mssqltips.com/sqlservertip/1446/handle-early-arriving-facts-in-sql-server-integration-services-ssis/

    http://www.sqlservercentral.com/articles/Data+Warehouse/71233/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    mercredi 4 juillet 2018 13:49