asked
Techniques for writing transaction information for a Data Warehouse and a Cube
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 - I change the description and click save - This runs an "Update" on the live database to change the description - This "inserts" a record in the "transaction" database to say the description has been changed B) I am creating an Invoice... I have an Invoice Header and Invoice Lines. On save this will: - Save the Invoice Header to the live database - Save the Invoice Lines to the live database - Save the Invoice Header to the "Transaction" database - Save the Invoice Lines to the "Transaction" database - An ETL process that will run overnight will transfer this information to the Data Warehouse database to say that an Invoice was created and the product code has been changed. - The cube will be processed. 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][1] as this may help you achieve what you want without the hassle of building it yourself. [1]:
http://msdn.microsoft.com/en-us/library/bb522489.aspx
Thanks WilliamD - I will look further in to Change Data Capture - although if this is 2008 R2 only then it might cause us a problem. Out of interest - how were your tables structured? We are thinking of having a completely separate database for transaction data, as you said, thiese tables become huge and we don't want it filling up the live database...
