1
Lecture 10:
More OLAP - Dimensional modeling
www.cl.cam.ac.uk/Teaching/current/Databases/
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community
Ask the community for help and clear up your study doubts
University Rankings
Discover the best universities in your country according to Docsity users
Free resources
Our save-the-student-ebooks!
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Data Warehousing: Understanding Star Schema, Snowflake Schema, and Cube Aggregation, Slides of Database Management Systems (DBMS)
An in-depth exploration of data warehousing concepts, focusing on dimensional modeling, star schema, snowflake schema, fact constellations, and cube aggregation. It covers the concepts of fact tables, dimension tables, measures, and dimension hierarchies, as well as the use of operators and the 'having' clause for aggregation.
Typology: Slides
2011/2012
1 / 18
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Data Warehousing: Understanding Star Schema, Snowflake Schema, and Cube Aggregation and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
1
Lecture 10:
More OLAP - Dimensional modeling
www.cl.cam.ac.uk/Teaching/current/Databases/
Conceptual Modeling of Data
Warehouses
Modeling data warehouses: dimensions & measures
- Star schema: A fact table in the middle connected to a set of
dimension tables
- Snowflake schema: A refinement of star schema where some
dimensional hierarchy is normalized into a set of smaller
dimension tables, forming a shape similar to snowflake
- Fact constellations: Multiple fact tables share dimension tables,
viewed as a collection of stars, therefore called galaxy schema or
fact constellation
Star Schema
sale
orderId
date
custId
prodId
storeId
qty
amt
customer
custId
name
address
city
product
prodId
name
price
store
storeId
city
Terms
• Fact table
• Dimension tables
• Measures sale
orderId date custId prodId storeId qty amt
customer custId name address city
product prodId name price
store storeId city
Dimension Hierarchies
store storeId cityId tId mgr s5 sfo t1 joe s7 sfo t2 fred s9 la t1 nancy
city cityId pop regId sfo 1M north la 5M south
region regId name north cold region south warm region
sType tId size location t1 small downtown t2 large suburbs
store
sType
city region
snowflake schema
constellations
Cube
sale prodId storeId amt p1 c1 12 p2 c1 11 p1 c3 50 p2 c2 8
c1 c2 c p1 12 50 p2 11 8
Fact table view:
Multi-dimensional cube:
dimensions = 2
Aggregates
sale prodId storeId date amt p1 c1 1 12 p2 c1 1 11 p1 c3 1 50 p2 c2 1 8 p1 c1 2 44 p1 c2 2 4
• Add up amounts for day 1
• In SQL: SELECT sum(amt) FROM SALE
WHERE date = 1
Aggregates
sale prodId storeId date amt p1 c1 1 12 p2 c1 1 11 p1 c3 1 50 p2 c2 1 8 p1 c1 2 44 p1 c2 2 4
• Add up amounts by day
• In SQL: SELECT date, sum(amt) FROM SALE
GROUP BY date
ans date sum
Aggregates
• Operators: sum, count, max, min,
median, ave
• “Having” clause
• Using dimension hierarchy
– average by region (within store)
– maximum by month (within date)
Cube Aggregation
day 2 c1 c2 c p1 44 4 p2 c1 c2 c p1 12 50 p2 11 8
day 1
c1 c2 c p1 56 4 50 p2 11 8
c1 c2 c
sum 67 12 50
sum
p1 110
p2 19
drill-down
rollup
Example: computing sums
c1 c2 c3 * p1 56 4 50 110 p2 11 8 19
- 67 12 50 129
Extended Cube
day 2 c1^ c2^ c3^ * p1 44 4 48 p
- 44 4 48
c1 c2 c3 * p1 12 50 62 p2 11 8 19
- 23 8 50 81
day 1
sale(,p2,)
Aggregation Using
Hierarchies
day 2 c1 c2 c p1 44 4 p2 c1 c2 c p1 12 50 p2 11 8
day 1
region A region B p1 56 54 p2 11 8
customer
region
country
(customer c1 in Region A; customers c2, c3 in Region B)