Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

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

Uploaded on 02/12/2012

tiuw
tiuw 🇺🇸

4.7

(18)

288 documents

1 / 18

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
Lecture 10:
More OLAP - Dimensional modeling
www.cl.cam.ac.uk/Teaching/current/Databases/
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12

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)