Metadata and DW architecture

Metadata is "data about data". In a warehouse it describes the structure, content, origin and use of data: schema, source-to-target mappings, transformation rules, aggregation rules, load history, data lineage, business definitions and access rights. It is stored in a metadata repository and is used by ETL tools, managers and end users.

Example: column sales_amt is "bill total in NPR, source POS.BILL.TOTAL, loaded daily".

Architecture of a data warehouse

 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

• Bottom tier: warehouse database server (relational), fed by back-end ETL tools (extract, clean, transform, load, refresh) from the operational and external sources. It holds detailed data, summary data, metadata and data marts.
• Middle tier: OLAP server (ROLAP, MOLAP or HOLAP) that presents the multidimensional view.
• Top tier: front-end tools for query and report, analysis (OLAP) and data mining.
• Metadata repository and monitoring/administration support the whole system.