Chapter 4. Building a Data Integration Process

It is easy to see the potential benefits of having a well-designed data warehouse that provides a single, consistent view of the information in a business. However, in today's business world, rapid growth, acquisitions, and mergers lead to multiple source systems and different ways of representing information within most businesses. Before we can reach the goal of a consistent data warehouse, we need to bring together the many different data sources that contain all your business data.

In this phase of developing your Business Intelligence (BI) solution, your task is to populate the dimension and fact tables in your data warehouse. Integrating all your data sources into the data warehouse is not always as straightforward as you would hope. You might be surprised by the many different ways the same data is stored by different departments. Some data may be in spreadsheets, some in text files, and some in various relational databases. The schema probably won't be in the shape you would like for the data warehouse. You will find that some developers have creative ways of representing various bits of information.

The interesting part of integrating data is really more about transforming data into a consistent model than it is about moving data between sources and destinations. In this chapter, we show how you can build solutions to bring data out of almost any data source, but more importantly how you can massage or transform that data so that it all converges and becomes compliant with your dimensional model.