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

asked Mar 29 '11 at 07:45 AM in Default

CraigBroadman
21 ● 2 ● 2 ● 3

(comments are locked)

1 answer:

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.

answered Mar 29 '11 at 08:06 AM

WilliamD
25.4k ● 16 ● 18 ● 41

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...

Mar 30 '11 at 04:40 AM CraigBroadman

(comments are locked)