ESB and ETL

[gramanero]

I am extremely new to the Kimball group (and DW/BI) and I am struggling to figure out some answers to architectural questions. Please forgive my ignorance and if I incorrectly state something.

What I would like to know is, does the Kimball approach (not sure if there is a better way to phrase that) prohibit an ETL process from taking advantage of an ESB? In order to isolate the structure of one or more application databases from the ETL process, can an ESB be utilized to essentially pump the application data into the ETL process such that changes to the application do not negatively impact the ETL process?

Does the Kimball approach address the idea of application isolation in order to reduce negative impact on other systems such as DW/BI? Or is the nature of DW/BI supposed to be tightly bound to the application database structures?

I don't know that there is a right or wrong answer as to integrating ESB and ETL systems, but from various things that I have read it seems like these two solutions are complimentary.

Any advice would be great and even pointing me off to some resources that may already exist that address what I am asking would be great as well.

Thank you.

[LAndrews]

The Kimball architecture definately supports the use of and ESB architecture - it can be a practical source of information to load into the DW.

What you'll find in practice is that the ESB will be one of many sources of data.

Many years ago I built a solution in an organization with a fairly mature ESB architecture - we were able to use if for most of our conformed/Enterprise dimensional content, but we also found a need to get transactional and domain specific dimensional info directly from the source applications.

So yes - consider it a data source when performing your data analysis, but don't limit yourself to data within the ESB.

Hope this helps.

[gramanero]

Yes, that does help. Thank you. One of the items of discussion being thrown around where I work is the idea of isolation and by that I mean isolating the ETL process from the application specific tables in order to reduce the risk of breaking the ETL process when the application team changes their database structure. By using an ESB as a data source for the ETL process based on a well-defined contract, application isolation would be achieved and therefore significantly reduce the chance of the ETL process breaking. Is isolation from application changes a valid concern with respect to the ETL process? Or would all the plumbing be an unnecessary level of abstraction and complexity added to the system? I guess I am not sure where the boundaries or separation of concerns should exist between ETL and the application data. The ETL guys seems to favor diving right into the application database structures and the application guys prefer more isolation such that when they change their structure there is little or no fan out of negative impact.

Again, thank you for the response!

[ngalemmo]

Its hard to say if an ESB approach truly isolates ETL from change.  Realistically, if they introduce new data to an operational table, it is reasonable to expect that that data would need to get into the data warehouse.  As a result, the messaging would need to change and the ETL processes would need to change, not to mention the DW schema.

Using an ESB for bulk transfers of large amounts of data could inflict an excessive burden on the system, particularly if they wrap the data in XML, which can easily bloat the amount of data being transferred significantly plus the overhead of creating, then parsing, the XML.

Give the ETL team their due.  Database changes should not be casual events, but modifying an ETL mapping to accommodate a database change is usually very simple.

[gramanero]

I agree that database changes should not be taken lightly and I am definitely not telling or recommending that the ETL team cannot work directly with the application databases. This is really more of a learning discussion for me to get some opinions in an area that I do not have a lot of experience in. As far as using XML and data volume, I agree with your points as well on that; however we rely on JSON which will typically have smaller payload sizes and while a large volume of traffic is a concern and will be looked into, I believe it can be managed.

Thank you for your responses. This has been very helpful.

[David70]

I am extremely new to the Kimball group (and DW/BI) and I am struggling to figure out some answers to architectural questions. Please forgive my ignorance and if I incorrectly state something.
s

[Sponsored content]