We are looking to implement functionality in to our C#/SQL Server software to write transaction data to a separate database that will be run through an ETL process overnight in to a Data Warehouse and ultimately processed in to a Cube.
I can explain a little further with a couple of examples.
A) I am going to change the description of a particular product
B) I am creating an Invoice... I have an Invoice Header and Invoice Lines.
On save this will:
Does this sound logical? Secondly, does any one have any tips or techniques that might help?Thanks in advance
This technique would work, just bear in mind that you are storing the header and lines multiple times in your transaction table/database.
They had/have a similar system in place, where I used to work. It stored the entire history of the order and orderlines (all changes). This table became the largest one in the database, becuase it was decreed important to store all the columns of the original row.
You can take a look at [change data capture] as this may help you achieve what you want without the hassle of building it yourself.: http://msdn.microsoft.com/en-us/library/bb522489.aspx
answered Mar 29 '11 at 08:06 AM