Heterogeneous joins sound complicated if not obscene. And the latter is what representatives of a major software development shop thought when asked about their development tools ability to support heterogeneous joins. “Huh .. who in the heck would want to do that?” was implied if not explicitly stated. Well, now that EAI-Enterprise Application Integration and M&A-Mergers and Acquisitions are all the rage again, my bet is that more developers have the need to join two tables together with the slight complication that they reside in two different vendors databases. Hence the heterogeneous. Hence the fly in the soup … or ointment.
Heterogeneous joins even beteween two ANSI 99 standard relational databases are hard if not impossible to do. Assume for the time being the two databases ae on the same server – otherwise there will be the need to interject some ETL -Extract Transform Load or Web Service speak at the beginning of these remarks. So one database will act as master and requestor of data – say the rest of the production data from a newly acquired subsidiary. So now all that is required is to get the connection – oops, delay of game to get all the roles and permissions set up properly. Now all we have to is extract the records – oops their product code key system is different from ours, in fact their product table is similar but different enough to require creation of several dummy variables. We could use a view but then we are just one step away from a batch ETL load process and requirements are for “real-time ” data and integration….
Heterogeneous joins is one of the root causes for application silos and isolated islands of information. Sure you can get at data from the SCM and ERP systems – but dont ask for it in anything close to realtime and expect to have to replicate or ETL the data … and maybe merge one of the databases into the other to get fast realtime queries and BI view into the data. In sum heterogeneous databases is why EAI, BAM, BPM, EII and other software solutions are in business.
Think of the forever separate, barely interoperable relational databases as the price IT shops pay for database innovation.
(c)JBSurveyer