Extraction layer on SOA/ESB architecture

Hi,

I'm going to start to develop a Data Warehouse on a company who is starting to move the different systems to a ESB, basically adapting a SOA architecture (simplify and split large services to small services and get them "talking" with each other through the ESB with SOAP messages). I wanted to know, based on your professional experience what are usually the best practices regarding the extraction layer:

1-Where should I get the data from? from the ESB or connect directly to service databases and do the extraction?
2-When connecting to the ESB, should I do a continuous data integration or request all the differential data during the less busy period (usually during the night)?
3-If receiving data as SOAP messages, how easy are they to integrate on the staging area?

Any tips and tricks from your experience please let me know

Regards!

All good questions, but the correct choice depends on specific use cases. All the options open to you may or may not be valid choices based on business need and specific operational scenarios.

Ask your IT developer at what place data is stable/finalized to retrieve? All Databases have data locking mechanism to ensure data is inserted or updated correctly.... the ACID semantics. ACID is an acronym that stands for atomicity, consistency, isolation, and durability.... (atomicity means a transaction either happens in totality or rolls back. Consistency means each modification to the database takes it from one consistent state to the other. Inconsistent and unresolved states do not exist. Isolation provides the assurance that some other process cannot modify a piece of data when an operation in progress is using it. Durability implies all committed data can be recovered from any sort of a system failure.)

If users are asking for real time data then you need to define with them the definition of real time... meaning 10 minutes delay or 30 minutes delay when they see the data from the source to DW. If users are not looking for real time data then load data after business hours ( again users should tell you about this too). Kimball described real time data load approach in detail in his DW tool book.

For real time or near real time warehouses, leverage the ESB. For overnight batch loads, hit the databases directly.