Unanswered: Historical Fact Tables - Do they exist?
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.
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.