Updating Fact Table : Best practice

View previous topic View next topic Go down

Updating Fact Table : Best practice

Post  marric on 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

View user profile

Back to top Go down

Re: Updating Fact Table : Best practice

Post  ngalemmo on 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).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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