Data Sources

Data warehouses are intended to provide information to decision makers. To do so, data warehouses must gather and consolidate data from many sources in the organization into a consistent set of data that accurately reflects the organization's business operation and history.

Organizations often have multiple online transaction processing (OLTP) systems to capture daily business operations. These OLTP systems are seldom designed at the same time as data warehouses. They may even be designed by different organizations, which is often the case when organizations grow through acquisitions and mergers. Database schemas and data element identification keys often vary from database to database. For example, the customer table in the OLTP of an acquired company may contain many of the same customers and products as the acquiring company but use a different identification system. Data extracted from these OLTP systems must be transformed into a common representation.

Legacy systems that have been in use for many years often contain denormalized data as well as unusual data identification designs and limited query flexibility.

Data critical for business analysis may even reside on individual desktop computers in personal databases and spreadsheets, especially in organizations that developed and grew without a central information technology group. Such data must also be captured into the data warehouse.

Sources of data to be used in the data warehouse must be identified and techniques developed for extracting the data from them. Data Transformation Services (DTS) provides powerful tools for extracting and transforming data from diverse data sources. For more information, see DTS Overview and DTS Basics.