Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Updating Fact Table : Best practice

Go down

Updating Fact Table : Best practice Empty Updating Fact Table : Best practice

Post  marric Wed May 05, 2010 2:31 pm

Hi,

Ssystem used :
Windows 2003 srv
SQL Server 2005 STD for the OLTP
SQL Server 2005 Ent for the DW with
- SSIS for the ETL
- SSAS for the cube
ASP.NET control to display the OLAP for the user

I know theres is not "one size fits all" solution but I would like to have input about the best pratice when dealing with fact table update.

We are using a OLTP database to manage paramedic events. Those events are entered in the OLTP database and they can be changed later for many reason (Sometimes they enter wrong date and it can affect the measure about how long the call took, the time spent on the crash site, .....). So we need to update the fact table to fix the error (All possible date errors can't be trapped in the ETL process).

Users don't need to keep track of the change made in fact table.

So my question is ... It is ok if, in my ETL, I check for the new row and add them in the DW and I run a query that update all the existing row already in the DW. Is this method against best pratices ? Should I wipe the fact table each time and add the new records ?

thanks for the input ... and sorry for the poor english ... i'm french canadian !

richard

marric

Posts : 8
Join date : 2010-02-24

Back to top Go down

Updating Fact Table : Best practice Empty Re: Updating Fact Table : Best practice

Post  ngalemmo Wed May 05, 2010 4:23 pm

Either updating existing rows or delete/insert specific rows will work Which one is better depends on which is easier and the particular database system you are working with. In the case of SQL Server it doesn't matter much, an update-in-place will probably be more efficient (less work for the system to maintain indexes).
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum