Warehouse architecture and three phases

General architecture

A warehouse system takes data from operational sources, integrates it in a central store and delivers it to users through query and analysis tools.

 Source systems
      |  (load manager)
      v
 +-----------+  warehouse   +-----------------+
 |   LOAD    |  manager     |  Detailed data  |
 |  MANAGER  |------------->|  Summary data   |
 +-----------+      |       |  Metadata       |
                    v       +-----------------+
             Archive/backup        ^
                                   | (query manager)
                            +--------------+
                            |QUERY MANAGER |
                            +--------------+
                                   |
                              End-user tools

Main parts: source systems, the load manager (extract, clean, load), the warehouse manager (transform, aggregate, index, back up, maintain metadata), the query manager (route and schedule queries, return results), storage (detailed data, summaries, metadata, data marts) and end-user access tools (query, OLAP, data mining).

Three phases of development

1. Planning / requirement phase: identify business goals, users, subject areas and data sources; do feasibility, cost and risk analysis; choose the architecture (top-down, bottom-up or hybrid) and the project team.
2. Design and construction phase: design the dimensional model (star/snowflake schema, fact and dimension tables), design the ETL process, build the metadata, select the hardware, DBMS and tools, then build the load, warehouse and query managers and the data marts.
3. Deployment and maintenance phase: load the initial data, test with users, train staff and release the system. Then operate it: refresh the data regularly, tune performance, manage security, backup and archiving, handle change requests, and extend the warehouse with new subject areas.

The process is iterative: each new subject area repeats these phases.