Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Historical Fact Tables - Do they exist?

    Hi all,

    I'm am in the process of remodeling an existing project but I have one particular table that I am not sure how to evolve.

    We have a customer table that I am taking from the transactional application. I have broken this down into dimensions but now I have a situation where I have a fact table where the associated FKs will be forever changing. For example, Today my customer may record a specific mobile number which I have logged into a dimension with it's own PK and stored the FK in the fact. Tomorrow that mobile number may change so the new number gets logged into the Dimesion with a new PK, and the fact table needs to reflect the new FK.

    My question is; Should I be overwriting a fact table and therefore only ever retain a live picture, or is there such a thing as a historic fact table where I can treat in the same manner as a slowly changing dimension with a Eff and Exp Date and a Current Flag? The PK on this fact table isn't relevant to the business.

    Does any have a suggestion?


  2. #2
    Join Date
    Jun 2004
    Provided Answers: 1
    I would sit down & decide what exactly it is you're trying to achieve:
    Do you have a customer, who can have one or more mobile phone records? OR do you have a mobile phone number, that can have one or more customers?
    Do you need to keep historical data? If so, is it kept by number of updates/changes? Or age?
    Is there a better candidate for your FK?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts