- Year not given · 3+4+3 marks
OLAP vs OLTP; schema modeling; star DMQL
Short answer
OLTP is for transactions; OLAP for analysis. Warehouse conceptual model is multidimensional with star, snowflake and fact constellation schemas. In DMQL, a star schema is defined by define cube with measures and define dimension for each dimension, e.g. define cube sales_star [time, item, branch, location] with dollars_sold = sum(sales_in_dollars).
Differentiate between OLAP and OLTP. Explain the conceptual modeling of Data Warehouse Schemas. How do you define Star Schema using DMQL?
OLAP vs OLTP
| Point | OLTP | OLAP |
|---|---|---|
| Purpose | Day-to-day transactions | Analysis, decision support |
| Users | Clerks, clients, DBAs | Managers, analysts, executives |
| Data | Current, detailed, flat relational | Historical, summarised, multidimensional |
| Database design | ER model, normalised, application-oriented | Star/snowflake, subject-oriented |
| Access | Read/write, short transactions | Mostly read, complex queries |
| Unit of work | Short, simple transaction | Complex query with aggregates |
| Records per access | Tens | Millions |
| Number of users | Thousands | Hundreds |
| DB size | MB to GB | GB to TB |
| Metric | Transaction throughput | Query response time |
Conceptual modelling of data warehouse schemas
The conceptual model of a warehouse is multidimensional, made of a fact table and dimension tables. There are three common schemas:
- Star schema: one central fact table linked to one denormalised table per dimension. Simple and fast.
- Snowflake schema: a star whose dimension tables are normalised into more tables to reduce redundancy; more joins.
- Fact constellation (galaxy): multiple fact tables share dimension tables; models several related business processes.
Star: Snowflake:
Time Item Item -> Supplier
\ / \
SALES -- Branch SALES -- Location -> City
Defining a star schema in DMQL
DMQL defines a cube and its dimensions. Example (sales):
define cube sales_star [time, item, branch, location]:
dollars_sold = sum(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)
The measures become the fact table columns and each dimension a table joined through its key.
✓ Answer checked by Claude Opus 5.5 (high effort)
Related questions
- OLAP operations and star schema in DMQL · Data Mining, chapter 2
- OLAP vs OLTP; schema, fact, dimension · Data Mining, chapter 2
- OLAP vs OLTP; data cube, operations · Data Mining, chapter 2
- Data warehouse schemas · Data Mining, chapter 1
- DMQL; transactional vs warehouse database · Data Mining, chapter 2
- OLAP vs OLTP; OLAP operations, fact, dimension · Data Mining, chapter 2
- Differentiate OLTP and OLAP · Information System, chapter 4 · asked 5×