4 approaches to combining data sources and what they mean to you

A common feature that data analysts and data engineers often look for in a business intelligence (BI) reporting tool is the ability to combine data from different databases, especially data spread across two or more database vendors, such as PostgreSQL, MySQL, SQL server, etc.

This article explores the pros and cons of different philosophies adopted by BI vendors and what it means to you.

Let me start with an example

Your company is an upstart in the ecommerce space and you have implemented an order and delivery management system that stores data in an SQL server. Additionally, you collect website traffic and visit related information, and store it in a separate PostgreSQL database.

Your marketing team and the founders want to understand the best channels that result in a purchase. Thus, your reporting solution needs to combine data from both databases and run fast enough to provide quick drill-downs and filtering on the visualizations you intend to build.

A general solution

Some BI reporting tools allow you to use a feature or, perhaps, even write one SQL that combines tables from both data sources in one query.

Let’s look at how the general design of such a solution would work at a high level:

1. Move data from one source—usually the one with fewer rows (let’s say this is PostgreSQL)—into a “temporary” location.
2. Execute the query and fetch results from the more voluminous dataset (SQL Server).
3. Combine the two data sets in the temporary location and provide the final result.

Note: Some BI products may have optimized one or more steps better than others, but the constraints introduced by querying and combining two data sets are the same.

4 possible approaches

The difference lies in where that “temporary” location is, leading to four possible approaches:

1. Data is not stored anywhere and is stitched together in run time.
2. Data is processed in-memory (no persistence).
3. Data is processed in a cache database, which is usually provided by the BI tool vendor (with persistence).
4. Data is copied over to a common database and all reporting goes against this common database (not temporary).

1. Data is not stored anywhere and is stitched together in run time

The first approach is provided by BI vendors that typically have a product that runs on a server and essentially use the memory, bandwidth, and computing resources of the server to combine the two datasets. The best example of this is IBM Cognos BI.

The disadvantage is, of course, in performance. Combining data from two sources takes time, as it involves executing queries, retrieving the results via a data transfer protocol, and stitching the data sets on the server. There can be delays in any of these steps, especially if one or more of the data sources are slow to return the query results.

Data virtualization

What all of this means to you