A data warehouse architecture is primarily based on the business processes of a business enterprise taking into consideration the data consolidation across the business enterprise with adequate security, data modeling and organization, extent of query requirements, meta data management and application, warehouse staging area planning for optimum bandwidth utilization and full technology implementation.
A D V E R T I S E M E N T
Data warehouse architecture is Similar to a blueprint when constructing a house. Developing a data warehouse architecture yields:
a communications tool, providing members of the Data Warehouse Group and beyond a clear picture of what makes up the data warehouse, how such components work together, etc.
a learning tool, helping to avoid the common trial-and-error approach to learning about a new system.
a cross-check for the project plan, ensuring that the project plan is accurate, reasonable (in terms of timeframes and resources), and comprehensive (i.e. that key architectural tasks are not forgotten).
Data warehouses and their architectures vary depending upon the specifics of an organization's situation. Three common architectures are:
Data Warehouse Architecture (Basic)
The figure above shows a simple architecture for a data warehouse. End users directly access data derived from several source systems through the data warehouse. 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.
Data Warehouse Architecture (with a Staging Area)
The figure above illustrates Data Warehouse Architecture with a Staging Area. The operational data needs to be cleaned and processed before being put into the warehouse. This can be achieved programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management.
Data Warehouse Architecture (with a Staging Area and Data Marts)
Although the Data Warehouse Architecture with a Staging Area is quite common, warehouse's architecture can be customised for different groups within an organization. Data marts are added for this purpose which are systems designed for a particular line of business. The figure above 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.
Need of a Sound Architecture
A sound data warehouse architecture enables:
extensibility by anticipating future end-user needs and providing a "roadmap" that reveals where such needs are addressed
reusability by documenting reusable components, processes, etc.
improved productivity by enabling reusability and revealing where specific tools may be necessary to automate data warehouse processes