Correcting Historical Data in Fact tables

[sujayparanjpe]

HI,
I am facing an issue about how to reload the historical data in fact tables. Below is the problem statement.

I have a supply chain fact table that shows the demand data reported against the supply chain. Here the demand data is the fact while supply chain is dimension. The fact table data is refreshed monthly. Few month back I had to reload fact table for certain reasons. During this reload the fact table data got refreshed as per latest supply chain dimension data. Due to this some demand data is projected gains incorrect supply chain.

I want to refresh the fact data again so that data for the first month is reloaded with the latest snapshot of supply chain dimension in the first month, second month is reloaded with the latest snapshot of supply chain dimension in the second month.

If the supplier for a particular product changed in third month then first month should show the previous supplier while the third should show the new supplier.

The dimension is SCD Type 2 with Valid_From and Valid_To logic in place and Valid_to = NULL represents the active record.

Can somebody help me and suggest an approach to deal with this issue?

 

[Thargy]

sujay,

this looks like an 'Indian Interview' question.

Please state the business requirement here, post the create table statements for the relevant tables, insert statements for these tables, and a statement of desired output.

If this is your job, and you want someone to do your job for you, try elsewhere.

Regards

T