Updating Fact Records

Please advice on designing the Fact Table in the following scenario:

- Fact records can get updates. The scenario is such that when there are corrections to data in the source table, there could be updates to the Dimension Surrogate Key present in the fact tables. In such case do we need to have a surrogate key with Effective date and End date in the Fact Table.

You could create a logical delete flag and create a view of the fact table where LogicalDelete = 0

Then you wouldn't need to bother with updating keys, and you maintain a history by inserting new records.

Or simply delete and reload. I don't know what you thing the effective dates would give you.

Thanks John. Sounds good to me....

As far as Fact data is concerned, composite dimension keys form the primary key of the Fact record.
In my case since this composite dim keys change, do I need to have a surrogate key for Fact record.
Because the Updates expected to Fact record are 50 in a Million...

I'm guessing you are running Oracle?

It's been 6 years since I last worked on Oracle so I don't remember it too well.

Do you need a primary key on your fact table? What benefit does it give you?

** If you are updating the fact record then what would you get by maintaining history in dimension ?? **



1) What john suggested.

2) Have start date and end date in the dimension but don't change the surrogate key and use same surrogate key to update the fact record.

Also let us know what is the business reason to update the fact records ? For example if you want to subtract returned items from the items sold then in this type of cases we should not take this approach, and always remember that updating fact is something NON_DWH so whenever you want to do that find alternative ways to achieve that, else you may face problems because DWH is not something which is build for temporary usage or ony for forseeable future.

Hi Vishy,
Thanks for the reply. We are design for Labour Management Warehouse.
The scenario is to track attendance of each employee.
We have Absence Types and different reasons for the same as dimension
ex: Absence Type - Sick, Holiday, Vacation
Reason (Associated with Each Absence Type) - Sick - Illness, Sick - Tooth Ache etc

We have Fact records that track Attendance:

Ex: Emp1 -> Sick ill -> (Absence date) - 12-Mar-2012 to 14-Mar-2012 - 2 days (Absence days)

So Sick ill is Dimension Key in the Fact table, 2 days later there might be correction from the source that reason is not Sick -ill but Sick Tooth Ache and absence endate is 15-Mar and 3 days Absence date.

This correction needs to be updated in the Fact.

Let me know if these details are file.

Vishy, can you tell me what would be the right approach to handle this

I have the same case recently. The correction is done within one month, so we just simply truncate the latest two month data and insert. It's quite easy to handle this in ETL.

Hi,

update will always happen in a particular month or it can be done after say 3 months or a year.
If you have a time limit defined for update then you can delete the old record and add new one but if you are also intrested how many updates are comming then it is better to store the original (do a soft delete ).
So it really depends upon if you are intrested in having a track on all the updates.

thanks

1) Either delete those rows and insert new ones

or

2) Have a flag with each fact row to show it is the latest one. While inserting row first time have value 1 for this row and when you update it make it 0 and make it 1 for the new row, this way you will be able to maintain some history.

---------------------------------------------------
If size is the issue then first approach would be better, also if it is happening too many times try to load data in first week of next month so that all correct data would be available to you and you are not forced to correct data in fact. You have to handle this strategically and take some mid-way route where you maintain what client wants and also there is less correction/recorrection.

I did a bank related project where we ask countries to provide their data so that it can be inserted in DWH, but we knew there is highly likelyhood that data might be incorrect so we mentioned to our incountry users that they need to provide files till 8th businesss day of next month so that they can do their own check before providing data to us.

sometime just at the end of the month clients don't have much time to look at the data they are providing so we felt let us pass it to clients so that they know what they are providing for DWH.

I can think of two options depending on whether you really need to keep the incorrect fact records at all.

1. If you don't want to see the incorrect fact in DW at all, a simple and yet very effective approach is to remove previously loaded fact records for the day/period in which you want to have correction in place. You then reload the facts from the source containing the correct fact-dim relationship for that day/period using your normal ETL. It's like reproduce facts for a period of time in history. In this approach, you should partition your fact table sensibly to minimise the impact of frequent records removal from the fact table. You may need to leverage 'Sliding Window' concept facilitated by table partitioning feature to achieve the best performance on a big fact table.

2. Maybe it's an overkill, but if you really want to keep track of all fact records regardless of correct or incorrect ones, a good approach is to add an audit dimension to your fact table. A single FK will provide rich set of information about what has happened to the fact record and how it should be used.

As Hang suggested, try to have a surrogate key for a fact table row.

http://www.kimballgroup.com/html/designtipsPDF/DesignTips2006/KU81FactTableSurrogateKeys.pdf

Hang / Vishy,
Thanks for the responses.

We do have the need for tracking all the changes in the warehouse.
Hang - Can you give the example of Audit Dimension you are talking about.

Thanks,
Ranjit

Please refer to the following article:

http://www.kimballgroup.com/html/designtipsPDF/DesignTips2001/KimballDT26AddingAnAudit.pdf