Data warehouse architecture and components

A data warehouse architecture shows how data moves from the sources into the warehouse and then to users. The common three-tier architecture is:

 Operational DBs   External   Flat files
        \             |          /
         v            v         v
     [ Extract - Clean - Transform - Load ]
                      |
                      v
   +----------------------------------------+
   |        DATA WAREHOUSE SERVER           |
   | Detailed data | Summary data | Metadata|
   |   Data marts (dependent)               |
   +----------------------------------------+
                      |
              [ OLAP Server ]
                      |
   Query/report | OLAP/analysis | Data mining tools

Components

1. Data sources: operational databases (ERP, CRM, billing), flat files and external data. They are heterogeneous.
2. ETL / back-end tools: extract data from the sources, clean it (remove errors, fill missing values), transform it (format, aggregate, derive) and load it into the warehouse. Periodic refresh keeps it up to date.
3. Warehouse database (bottom tier): usually a relational DBMS storing detailed data and lightly and highly summarised data.
4. Metadata repository: data about data: source definitions, schema, transformation rules, data lineage, load history and business definitions.
5. Data marts: department-specific subsets of the warehouse, dependent or independent.
6. OLAP server (middle tier): ROLAP, MOLAP or HOLAP engine that gives the multidimensional view and performs roll-up, drill-down, slice and dice.
7. Front-end tools (top tier): query and report tools, OLAP/analysis tools and data mining tools used by managers and analysts.
8. Management and administration: security, backup and recovery, monitoring and scheduling.