How to combine information from multiple databases/servers

Challenges

Often there is a requirement to answer questions with data from multiple sources. For example there might be a number of shops/school/departments/etc each with its own database and you want to find something out across them all. Depending on the details, similar sounding problems often need different solutions.

For example the following scenarios appear similar but might need quite different solutions:

• A) There are 3 shops, they all use the same sales software but the data for each is in its own database, we need to know about sales across all 3.
• B) There are 3 shops, they all use the different sales software and the data for each is in its own database, we need to know about sales across all 3.
• C) There is one shop, with sales software and a promotional website which operate independently, we need to know how offers on the website affect product sales.

So what's the difference?

In example A we would expect the data we need to be in the same format, in the same tables in each of the separate databases. In example B we would expect the information to be in each database but in different formats and tables for each one. In example C we need to tie up different types of information from the different systems. These are the scenarios we come across most often.

And what do we need to do?

• A) We need to find a mechanism to retrieve data from all the source databases, and aggregate the data
• B) We need to find a mechanism to retrieve data from all the source databases and we need to be able to standardise the format and meaning of the data from the different systems before aggregating the data. (This may also involve mapping different values (e.g. product IDs) between the systems.)
• C) We need to find a mechanism to retrieve data from both source databases and we need to be able match the data between the systems (e.g. product IDs).

Broadly speaking B is the most difficult scenario. Of course we are never really talking about just one question, there are always more questions so we need a generic solution.

Implementing data retrieval from multiple databases/servers

Whatever the scenario you are dealing with you have a number of options for how to actually implement retrieving and combining the data.

• X) Query the multiple sources on demand, on the fly, for each enquiry
• X1) Query the multiple sources on demand, on the fly, for each enquiry, using a query across multiple databases
• X2) Query the multiple sources on demand, on the fly, for each enquiry, individually, and combine the result programmatically
• Y) Periodically combine the data from the multiple sources into a single database from which you can get the results for all the questions (queries)

next...