EII is a label for technologies designed to perform federated data queries against multiple heterogeneous, distributed databases, data stores and sources. EII tools perform a valuable function. Just like ETL tools, most EII products are based on proprietary data models and query languages.

Existing tools and adaptations

Seeking to provide more efficient RTE (real-time enterprise) support, many EII vendors have added the use of central repositories to cache the results of repeatedly or recently executed queries. Such queries are often triggered by changes in external data or by time intervals. The EII tools try to provide message alerts by recognizing changes to the shared cached data. To allow external apps efficient access to the current state of the integrated data, these EII tools provide additional APIs and data models.

Drawbacks

A number of issues arise whenever the data changes:

1. Centrally-cached data can often become inconsistent with the externally stored data.

2. Queries need to be triggered to recalculate centrally-cached results.

3. The process of keeping the cache accurate relies on the availability, timeliness, accuracy, and security of the data queried.

4. As applications try to access the current value of data, the cache becomes a central bottleneck.

SQLstream Solutions using RAMMS

SQLstream can be used as an infrastructure to reach out to the external data sources, capture changes to the data, and then build and maintain a centralized data repository that is authomatically synchronized with those external data sources.

The SQLstream solution populates a centralized data warehouse based on technologies such as Mondrian (the leading Open Source Relational OLAP server):

SQLstream's RAMMS can help alleviate EII challenges in the following ways:

• Avoids costly polling and inefficient reevaluation of queries when the data in the databases is changing (the same data is often repeatedly requeried by each query)

• Can efficiently publish and push the data out to a wide range of external applications, avoiding unnecessary retransmission of data

• Can provide time- and logic-based data synchronization, continuously joined from multiple distinct data sources

• Can elegantly handle data consistency issues arising from changes to data

• Can easily generate alerts and trigger any other pushed-based behavior required

• Removes the problems of managing insecure, transient, or ephemeral remote data (such as data in a spreadsheet on a desktop or laptop computer), because SQLstream's RAMMS takes a copy of all changes to data sources and captures them in its generated messages capturing the incremental changes

The SQLstream solution, comprising its RAMMS to gather data from remotely queries objects coupled with a central Mondrian-based data warehouse, provides an excellent and elegant solution to 95% of the scenarios where leading EII solution vendors operate, leaving a relatively small but important niche where specialized EII vendors offer the better solution (such as where it is too expensive to manage and synchronize multiple copies of the data with RAM technology).

The SQLstream EII solution can also be used to update remote data by instrumenting the Mondrian data warehouse using a version of our RDBMS Data Change Capture module to generate streams of transaction messages that are then mediated by SQLstream into changes made to the external data.

Customer plug-ins are then written using SQLstream's SDK to interface to any required external data format.