Comparing Integration Approaches

The table below summarizes the pros and cons of each approach to integration.

	Federated Federated Database	Plugin Platform Specific	ETL Extract, Transform, Load
Handles Simple Mappings	✓	✓	✓
Handles Complex Mappings Complex mappings are master-detail mappings between systems where data elements don't naturally line up	✓	✓
Handles Simple Customs Mappings Ability to handle mappings other than those that are pre-configured	✓		✓
Handles Complex Custom Mappings	✓
Bi-Directional Sync Capability	✓	sometimes	seldom
Fully Configurable Can be modified to meet changing business requirements	✓		✓
Access Live Data	✓	usually
Single Database Makes all data from all systems appear as though it is in single database	✓
SQL Queries Across Non-Database Sources	✓
Uses SQL For Mapping, Transformations, Sync	✓
Uses Proprietary Language For Mapping, Transformations, Sync			✓
Setup & Configuration	Easy	Very Easy	Complex

Federated Integration

A Federated Database is the newest approach to solving long standing integration challenges. Essentially you have a single database (in the cloud) that has federated links to some or all of your other systems. The existing systems in this case can be on-prem applications with underlying databases (e.g. on-prem ERP) or newer cloud based SaaS offerings accessible only via APIs. federated drivers link their respective systems into your federated database. This makes it appear as though all of your data from all of your systems is in one big database. You can issue normal SQL queries to select data and read/write/update data across systems. API programming is eliminated. Synchronization between systems can be live. "Integration" becomes just like moving data around in a single database.

ETL Integration

Most integrations today still rely on some form of Extract, Transform, Load (ETL). Essentially the data is extracted from one system, transformed in some way, and loaded into another system. This if often performed nightly. There are two primary problems with this approach: synchronization and stale data. The largest challenge is synchronization. If the data is flowing only from system A to system B this is generally not an issue. However, if system B needs to change that data in some way and send those changes back to system A, things can quickly get complicated when you need to prevent one system from overwriting changes made by another system. Bi-directional sync is often avoided in ETL for this reason. The benefit of the ETL approach is that the data mappings are typically customizable and therefore can meet changing business requirements.

Plugin-Based Integration

Although there are many types of plugins used for integration, plugins typically try to provide semi-live integration and therefore can sometimes overcome the bi-directional sync issues and data freshness issues that plague ETL-based integrations. However, plugin-based integrations typically have very limited configuration options and are often only viable for the simplest of integrations. Businesses looking for an enterprise-class solution generally need more flexibility than a plugin can provide.