Data Warehouse Architectures

Data warehouses and their architectures vary depending upon the specifics of an organization’s situation. Three common architectures are:

• Data Warehouse Architecture (Basic)
• Data Warehouse Architecture (with a Staging Area)
• Data Warehouse Architecture (with a Staging Area and Data Marts)

Data Warehouse Architecture (Basic)
Figure 1 shows a simple architecture for a data warehouse. End users directly access data derived from several source systems through the data warehouse.

Figure 1 Architecture of a Data Warehouse

In Figure 1, the metadata and raw data of a traditional OLTP system is present, as is an additional type of data, summary data. Summaries are very valuable in data warehouses because they pre-compute long operations in advance. For example, a typical data warehouse query is to retrieve something like August sales. A summary in Oracle is called a materialized view.

Data Warehouse Architecture (with a Staging Area)

In Figure 1, you need to clean and process your operational data before putting it into the warehouse. You can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management. Figure 1-3 illustrates this typical architecture.

Figure 2 Architecture of a Data Warehouse with a Staging Area

Data Warehouse Architecture (with a Staging Area and Data Marts)
Although the architecture in Figure 2 is quite common, you may want to customize your warehouse’s architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. Figure 3 illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales.

Figure 3 Architecture of a Data Warehouse with a Staging Area and Data Marts