OLAP operations and star schema in DMQL

Typical OLAP operations

        Roll-up (up the hierarchy)
   city -> country        ^
        |                 |
   Drill-down       Slice / Dice / Pivot

Consider a sales cube with dimensions time (quarter), location (city) and item (type).

1. Roll-up (drill-up): aggregates data by climbing a concept hierarchy or by removing a dimension. Example: city → country, so the sales of Kathmandu and Pokhara become the sales of Nepal.
2. Drill-down (roll-down): the reverse; goes from summary to more detail, by stepping down a hierarchy or adding a dimension. Example: quarter → month.
3. Slice: selects one value on one dimension, giving a sub-cube with one dimension fewer. Example: time = "Q1" gives a 2-D location-by-item table.
4. Dice: selects values on two or more dimensions, giving a smaller sub-cube. Example: (location = Kathmandu or Pokhara) and (time = Q1 or Q2) and (item = mobile or laptop).
5. Pivot (rotate): rotates the axes of the view to show the data from a different perspective. Example: swap rows (item) and columns (location).
6. Other: drill-across (query over several fact tables) and drill-through (go down to the back-end detail records).

Star schema in DMQL

In DMQL (Data Mining Query Language), a cube is defined with define cube <cube_name> [<dimension_list>]: <measure_list>, and each dimension with define dimension <dimension_name> as (<attribute_list>). A dimension that reuses one already defined is written define dimension <name> as <first_dimension_name> in cube <cube_name>. Because the item, time and measure attributes are not legible in the scan, the usual attributes are assumed (marked below).

define cube shipping [time, item, shipper, from_location, to_location]:
    dollars_cost = sum(cost_in_dollars),
    units_shipped = count(*)

define dimension time as
    (time_key, day, day_of_week, month, quarter, year)   -- assumed

define dimension item as
    (item_key, item_name, brand, type, supplier_type)    -- assumed

define dimension from_location as
    (location_key, street, city, province_or_state, country)

define dimension to_location as from_location in cube shipping

define dimension shipper as
    (shipper_key, shipper_name,
     location as from_location in cube shipping, shipper_type)

The from_location and to_location dimensions both use the same location table (a role-playing dimension), so to_location is defined by reusing it. The shipper dimension also refers to the location table through its location_key. (The figure prints the attribute as "province_or_street"; it means province_or_state.)