Introduction to Data Warehousing

1Why data warehouses matter

• Data warehouses generalise and consolidate data in multidimensional space.
• Building one involves data cleaning, data integration and data transformation, so it is an important preprocessing step for data mining.
• They provide OLAP (On-Line Analytical Processing) tools for interactive analysis of multidimensional data at different granularities.
• Mining functions such as association, classification, prediction and clustering can be integrated with OLAP to mine at multiple levels of abstraction.

So the data warehouse is an important platform for data analysis, OLAP and data mining.

2What is a data warehouse?

Data warehousing provides architectures and tools for business executives to systematically organise, understand and use their data to make strategic decisions. Many firms have spent millions building enterprise-wide data warehouses.

• A data warehouse is a database maintained separately from the organisation's operational databases.
• It allows integration of a variety of application systems.

These four keywords are what set a data warehouse apart from relational DBs, transaction processing systems and file systems:

3OLTP vs OLAP

• OLTP systems handle online transaction and query processing: purchasing, inventory, manufacturing, banking, payroll, registration, accounting.
• OLAP systems serve knowledge workers doing data analysis and decision making, presenting data in various formats for diverse users.

4Why keep the warehouse separate?

Why not just run analysis queries on the operational database? Three reasons:

1. Performance of both systems - The operational DB is tuned for known tasks: indexing and hashing on primary keys, finding particular records. - Warehouse queries are complex. They compute large groups at summarised levels and need multidimensional organisation. - Running OLAP queries on the operational DB would substantially degrade operational performance.

2. Concurrency control and recovery - The operational DB processes many concurrent transactions and needs locking and logging. - OLAP queries need only read-only access for summarisation. - Applying concurrency and recovery mechanisms to OLAP would jeopardise concurrent transactions and reduce OLTP throughput.

3. Different data - Decision support needs historical data, which operational DBs usually don't keep. - Operational DBs hold detailed raw data that must be consolidated (aggregated, summarised) before analysis. - Data from different sources must be integrated.

5The multidimensional data model

Data warehouses and OLAP tools use a multidimensional data model, which views data as a data cube. A cube is defined by dimensions and facts.

2-D cube (Table 5-2)

Sales in Vancouver by time (quarter) × item type. Measure = dollars_sold (in thousands).

3-D, 4-D … n-D

• Add location (Chicago, New York, Toronto, Vancouver) and the 2-D table becomes a 3-D cube: a stack of 2-D tables, one per city (Table 5-3, Figure 5-1).
• Add supplier and you get a 4-D cube. It can be drawn as a series of 3-D cubes, one per supplier (Figure 5-2).

6Lattice of cuboids

Given a set of dimensions, we can make a cuboid for every possible subset of them. Each cuboid is a different level of summarisation. Together they form the lattice of cuboids, which is called a data cube.

OLAP operations (preview)

The lesson introduction names the OLAP operations used on cubes:

7Weka — the tool used in this course

The second video is the first lesson of "Data Mining with Weka" (University of Waikato). From Week 6 onwards you'll use Weka's Explorer to load datasets, apply filters, build classifiers and visualise data.

• Weka is a free, open-source data mining workbench written in Java, from the University of Waikato, New Zealand.
• The course focuses on using mining tools (the Explorer interface) rather than programming.

8Quick revision sheet