ARCHITECTURE AND PROCESSES IN A DATA WAREHOUSE
The architecture and processes in an enterprise data warehouse (EDW) will typically look as illustrated in Exhibit 5.1. The exhibit is the pivot for the rest of this chapter.
As opposed to the approach we've used so far in this book, we will now discuss the data warehouse based on the direction in which data and information actually move (from the bottom up). Our point of departure in previous chapters has been the direction that is dictated by the requirements for information (from the top-down). The bottom-up approach here is chosen for pedagogical reasons and reflects the processes that take place in a data warehouse. This does not, however, change the fact that the purpose of a data warehouse is to collect information required by the organization's business side.
As is shown by the arrows in Exhibit 5.1, the extract, transform, and load (ETL) processes create dynamics and transformation in a data warehouse. We must be able to extract source data into the data warehouse, transform it, merge it, and load it to different locations. These ETL processes are created by an ETL developer.
ETL is a data warehouse process that always includes these actions:
- ? Extract data from a source table.
- ? Transform data for business use.
- ? Load to target table in the data warehouse or different locations outside the data warehouse.
The first part of the ETL process is an extraction from a source table, staging table, or from a table within the actual data warehouse. A series
Exhibit 5.1 Architecture and Processes in a Data Warehouse of business rules or functions are used on the extracted data in the transformation phase. In other words, it may be necessary to use one or more of the transformation types in the following section.