---
title: "OLAP operations and star schema in DMQL"
subject: "Data Mining"
chapter: 2
key: "semester_7/data_mining:olap-operations-star-dmql"
source: https://www.bctengineer.com/q/semester_7/data_mining/olap-operations-star-dmql
site: BCT Engineering
---

# OLAP operations and star schema in DMQL

Data Mining · Chapter 2: Data Pre-processing

- Asked in: 2066 Bhadra, 4+6 marks
- Video: [OLAP operations with real life example | Data Warehouse](https://www.youtube.com/watch?v=BLqE2EKiAy4) — Gate Smashers
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_7/data_mining/olap-operations-star-dmql
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-2

## Question

Explain the typical OLAP operations over warehouse multidimensional data. Define the following star schema using DMQL.

[Figure: Shipping star schema. Central "Shipping Fact Table" with the keys time_key [?], item_key [?], shipper_key, from_location, to_location and measures dollars_cost [?], units_shipped [?]. Linked dimension tables: item and time (attributes not legible in the scan); location (location_key, street, city, province_or_street, country), linked to from_location and to_location; shipper (shipper_key, shipper_name, location_key, shipper_type).]

## Short answer

OLAP operations: roll-up (aggregate up hierarchy), drill-down (detail down), slice (fix one dimension), dice (select multiple dimensions), pivot (rotate axes). Star schema in DMQL defines cube with dimensions (time, item, shipper, from/to location) and measures (dollars_cost sum, units_shipped count), using role-playing dimensions for locations.

## Answer

#### Typical OLAP operations
```text
        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).

```text
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.)

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. Roll-up from city to country:
   A. Gives more detail
   B. Aggregates by climbing a hierarchy
   C. Rotates axes
   D. Selects one value
   Answer: B. Roll-up aggregates; drill-down is the reverse. (Key point: Roll-up)

2. Pivot (rotate) does what?
   A. Rotates axes to show data from a different perspective
   B. Selects one value
   C. Moves up hierarchy
   D. Queries several facts
   Answer: A. e.g. swap rows (item) and columns (location). (Key point: Pivot)

3. In the shipping schema, from_location and to_location:
   A. Are two different location tables
   B. Reuse the same location table as role-playing dimensions
   C. Are measures
   D. Are removed
   Answer: B. Both defined as location in cube. (Key point: Role-playing dimension)

4. Slice time = Q1 on a 3-D cube gives:
   A. A 2-D location-by-item table
   B. A 4-D cube
   C. A single number
   D. No change
   Answer: A. A slice reduces one dimension. (Key point: Slice vs dice)

## Related in Data Mining

- [OLAP vs OLTP; schema modeling; star DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-star-dmql) — Data Mining, chapter 2
- [OLAP vs OLTP; schema, fact, dimension](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-fact-dimension) — Data Mining, chapter 2
- [OLAP vs OLTP; OLAP operations, fact, dimension](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-operations-fact-dimension) — Data Mining, chapter 2
- [OLAP vs OLTP; data cube, operations](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-data-cube-fact-dimension) — Data Mining, chapter 2
- [DMQL; transactional vs warehouse database](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Admql-transactional-vs-dw) — Data Mining, chapter 2
- [Compare star, snowflake, fact constellation](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Astar-snowflake-constellation) — Data Mining, chapter 1

## Related in other subjects

- [Central tendency measures; wage distribution](https://www.bctengineer.com/question.md?key=semester_5%2Fprobability_and_statistics%3Acentral-tendency-wages-approx) — Probability and Statistics, chapter 1
- [RECORD.txt item cost with VAT](https://www.bctengineer.com/question.md?key=semester_1%2Fcomputer_programming%3Afile-record-txt-invoice) — Computer Programming, chapter 10