DW Overview: Data Warehouse Architecture
Below is a diagram showing a typical data warehouse architecture.
Figure 1. Data Warehouse Architecture
Data warehouse architecture should include tools for the following:
- extracting data from multiple operational databases and external sources
- cleaning, transforming, and integrating the data
- loading data into the data warehouse
- periodically refreshing the data warehouse to reflect updates at the source and to purge data from the data warehouse
In addition to the main data warehouse, there may be several departmental data marts. Data marts are departmental subsets focused on selected subjects. Data in data warehouses and data marts are stored and managed by one or more data warehouse servers. The data warehouse servers present multidimensional views of data to a variety of front end tools, including analysis tools, query/reporting tools, and data mining tools. And there is a repository for storing and managing metadata and tools which monitor and administer the data warehousing system.
What are some different architectural alternatives? Many organizations want to implement an integrated enterprise warehouse that collects information about all different aspects of the whole organization. This requires extensive business modeling which may take a long time and cost a lot in order to complete it successfully. As an alternative to this, some organizations choose to settle for data marts instead. This enables faster roll out in comparison to the enterprise wide warehouse, but it may lead to complex integration problems in the long run.