24/7 Data Warehoue and load strategy

[goodmans]

Hi DWH Gurus,

I know you have good suggessions for this topic, Please share with me. Please help me finding a good solution.

I got a 24/7 Warehouse. As an ETL artect can you give me how we can handle delta load on this 24/7 used Warehouse.

Regards

G

 

[johnherman]

If the underlying database is RDBMS, you would load using a single transaction so that all new activity hits at once, with a single commit at the end of the transaction. You could also have a control table which contained the date/time of the last update.

Another method would be to use replication or some variation of replication from the source system(s) to the target systems. But again, you would want to maintain the integrity of a transaction. If someone was transferring $100 from checking to savings, you wouldn't want to post the withdrawal from checking without also providing commit to the deposit into savings.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[MDXer]

I think John has stumbled across the problem with my bank account. I show that I take money out but never to commit to putting it in.

 

[goodmans]

I started to realie that this topic is not as small as I thought. Any more suggessions?. like maintaining 2 sets of those tables which are very complicated like explained above.

Regards
G

 

[goodmans]

Ohh sorry, so you mean replication of warehouse tables?.

Regards
G