Schemas for the Multidimensional Model

1Why a different schema for warehouses?

• Relational databases (OLTP) are designed with the entity-relationship (ER) model: a set of entities and the relationships between them.
• A data warehouse needs a concise, subject-oriented schema that supports on-line data analysis.
• The most popular model for a warehouse is the multidimensional model. It comes in three forms:

In all three there is a fact table (holds the measures plus keys to the dimensions) and dimension tables (describe each dimension).

2Star schema

A central fact table connected to one table per dimension. It is the simplest schema.

• Example (Figure 6-1): the sales fact table has keys to time, item, branch, location and two measures, dollars_sold and units_sold.
• Each dimension is exactly one table.

3Snowflake schema

A star schema whose dimension tables are normalised (split into extra tables). The diagram branches out like a snowflake.

• Example (Figure 6-2):
• item → item(item_key, item_name, brand, type, supplier_key) + supplier(supplier_key, supplier_type)
• location → location(location_key, street, city_key) + city(city_key, city, province_or_state, country)

4Fact constellation (galaxy schema)

Multiple fact tables that share dimension tables. It is a collection of stars, hence also called a galaxy schema.

Example (Figure 6-3): two fact tables.

time, item and location are shared between both fact tables.

5Data warehouse vs data mart

6Defining cubes with DMQL

A Data Mining Query Language (DMQL) specifies mining tasks the way SQL specifies relational queries. Warehouses and marts are defined with two primitives:

define cube <cube_name> [<dimension_list>]: <measure_list>
define dimension <dimension_name> as (<attribute_or_subdimension_list>)

Star schema in DMQL

define cube sales_star [time, item, branch, location]:
    dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars),
    units_sold = count(*)
define dimension time     as (time_key, day, day_of_week, month, quarter, year)
define dimension item     as (item_key, item_name, brand, type, supplier_type)
define dimension branch   as (branch_key, branch_name, branch_type)
define dimension location as (location_key, street, city, province_or_state, country)

define cube creates the central fact table (4 dimensions plus the measures). Each define dimension creates one dimension table.

Snowflake schema in DMQL

The only change is that sub-dimensions are nested inside the parent dimension:

define dimension item as (item_key, item_name, brand, type,
                          supplier(supplier_key, supplier_type))
define dimension location as (location_key, street,
                          city(city_key, province_or_state, country))

Defining supplier inside item implicitly creates a supplier_key in the item table. city inside location implicitly creates a city_key.

Fact constellation in DMQL

Define the sales cube as in the star schema, then define shipping and reuse the shared dimensions:

define cube shipping [time, item, shipper, from_location, to_location]:
    dollar_cost = sum(cost_in_dollars), unit_shipped = count(*)
define dimension time  as time in cube sales
define dimension item  as item in cube sales
define dimension shipper as (shipper_key, shipper_name,
                             location as location in cube sales, shipper_type)
define dimension from_location as location in cube sales
define dimension to_location   as location in cube sales

"as … in cube sales" is how a dimension is shared between fact tables.

7Concept hierarchies

A concept hierarchy is a sequence of mappings from low-level concepts to higher-level, more general concepts.

                        all
              ┌──────────┴──────────┐
           Canada                  USA
       ┌──────┴──────┐        ┌─────┴──────┐
 British Columbia  Ontario  New York    Illinois
       │              │        │            │
   Vancouver      Toronto   New York     Chicago

• Many hierarchies are implicit in the schema. If location has attributes number, street, city, province_or_state, zip_code and country, a total order gives: street < city < province_or_state < country (Figure 6-5).
• Roll-up climbs the hierarchy (city → country). Drill-down goes back down.

8Weka 1.2 — Exploring the Explorer

The Weka video walks through the Explorer, the main interface you'll use:

• Preprocess tab: open a dataset (ARFF file), see the list of attributes, the number of instances, and per-attribute statistics and a histogram. Filters are also applied here.
• Other tabs: Classify, Cluster, Associate, Select attributes, Visualize.
• Datasets have instances (rows) and attributes (columns). One attribute is usually the class.

9Quick revision sheet