Design of a Data Warehouse

1Why build a data warehouse?

To build an effective warehouse you need a clear understanding and analysis of business needs. The four benefits:

2The four views of warehouse design

3Design approaches & methodology

Top-down, bottom-up or combined

Software-engineering steps

There are two development methodologies:

• Waterfall: structured, systematic analysis at each step before moving to the next, falling from stage to stage.
• Spiral: rapid generation of increasingly functional systems with short intervals between releases.

4The 4-step warehouse design process

1. Choose a business process to model, e.g. orders, invoices, shipments, inventory, account administration, sales, general ledger.
   • The process is organisational with many complex object collections → use a data warehouse model.
   • The process is departmental, focused on one kind of business process → use a data mart model.
2. Choose the grain: the fundamental, atomic level of data in the fact table, e.g. individual transactions or individual daily snapshots.
3. Choose the dimensions for each fact-table record, e.g. time, item, customer, supplier, warehouse, transaction type, status.
4. Choose the measures for each fact-table record, usually numeric additive quantities such as dollars_sold, units_sold.

5Three-tier data warehouse architecture

 TOP TIER     ┌─────────────────────────────────────────────┐
 Front-end    │ Query/report tools · Analysis · Data mining │
 client       └──────────────────────┬──────────────────────┘
 MIDDLE TIER  ┌──────────────────────┴──────────────────────┐
 OLAP server  │        ROLAP  or  MOLAP  OLAP server         │
              └──────────────────────┬──────────────────────┘
 BOTTOM TIER  ┌──────────────────────┴──────────┬───────────┐
 Warehouse    │ Data warehouse (relational DB)  │ Metadata  │
 DB server    │       + Data marts              │ repository│
              └──────────────────────┬──────────┴───────────┘
        Extract · Clean · Transform · Load · Refresh
              Operational databases    External sources

From the architecture point of view there are three warehouse models:

• Enterprise warehouse: covers the whole organisation.
• Data mart: a departmental subset.
• Virtual warehouse: a set of views over operational databases; easy to build but needs spare capacity on the operational servers.

6The metadata repository

Metadata = data about data. In a warehouse, metadata define the warehouse objects. The repository sits in the bottom tier.

• Metadata are created for data names and definitions.
• Extra metadata record timestamps of extracted data, the source of the information, and missing fields added by cleaning or integration.

What the repository should contain (six things):

7OLAP server models: ROLAP, MOLAP, HOLAP

8Weka 1.3 — Exploring datasets

The Weka video shows what's inside a dataset:

• ARFF format (Attribute-Relation File Format): a header with @relation, @attribute lines (name and type), then @data with one instance per line.
• Nominal attributes take values from a fixed set (e.g. outlook = sunny/overcast/rainy). Numeric attributes are numbers (e.g. temperature).
• The class attribute (usually the last one) is what we want to predict (e.g. play = yes/no in the weather dataset).

@relation weather
@attribute outlook {sunny, overcast, rainy}
@attribute temperature numeric
@attribute humidity numeric
@attribute windy {TRUE, FALSE}
@attribute play {yes, no}
@data
sunny,85,85,FALSE,no
overcast,83,86,FALSE,yes

9Quick revision sheet