x

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
more ▼

asked Mar 29, 2011 at 07:45 AM in Default

CraigBroadman gravatar image

CraigBroadman
21 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Mar 29, 2011 at 08:06 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 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, 2011 at 04:40 AM CraigBroadman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x60
x46
x31
x18

asked: Mar 29, 2011 at 07:45 AM

Seen: 1454 times

Last Updated: Mar 29, 2011 at 07:47 AM