Updating Fact!

Hello:

I work with research related data in a university and every award granted has an associated account in the financial system. So, every time a researcher makes a purchase or payment, a record enters into the transaction fact table with associated data from account dim & award dim.

However, at times an account is linked to wrong award which is fixed a few days later but until then, some of the transactions already took place and enter the fact table with incorrect data. Now that they realized and fixed the problem, what about the data that is already in transaction fact table with incorrect data? Should I go back and fix the data by updating the row OR 'end' the row and create a new row with fixed data? OR go with what the users want (which is just update the incorrect row) OR something else.

Please suggest.

Thanks,
-zaci

Generally, go with what the users want. Doing an update in place is the simplist approach, as long as you don't need to update a PK column.

Thank you ngalemmo.

Hello ngalemmo:

You said, "as long as you don't update a PK column...". If I go down that route, I would have to update an FK in Fact table that points to the correct award/account this time. You lost me when you said a PK column, can you please explain?

Thanks,
-zaci

It is not a good accounting approach/principle to update an old transaction amount in the source system . In accounting general ledge, a mistake is corrected by creating a new entry with negative amount of a mistake to cancel out the mistake. This happens at the source system. Here is an example.

Acct#, itemId, date, amount, Trans reason, old itemId
A1, 1,2/1/2015, $100,  grant money received
A1,2,2/2/2015, $30, water bill paid  <--- Here User put incorrect water bill . The actual bill was $50
A1, 3,2/3/2015, $10, power bill paid
A1,4, 2/4/2015, - $30, wrong water bill paid , 2   <--- negative amount to cancel out the mistake
A1,5,2/4/2015, $50, water bill paid  <-- Correct entry of water bill

All accounting system have that functionality as I describe above because it is one of a principles of book keeping. The issue is many users do not follow accounting principles because they do not have correct training or they are just lazy. Talk to the business user that you prefer if a correction is made as described above at the source system. If a business user is not agreed, then you just update the amount for that specific transaction. Data warehouse should match with the source system. So do not close a row with incorrect amount and open up a new one. By the way, If you have a transaction fact table then there should not be a open or close date.  A transaction fact shows what happened in the source system in a given day, so you need only a transaction date column.

Hello zoom;

That's a good point - reminder of the book keeping principle. Like you suggested, I will go back to the users and run it by them and see how they feel and take it from there.

Secondly, this TRANSACTION_FACT table is going to have a bunch of FK columns (together form a unique key) and one of them is going to be DATE_SID from DATE_DIM. Is that what you were inquiring? This is supposed to have the date from the transaction system.

Thanks,
-zaci