Download Data Warehousing: Understanding Star Schema, Snowflake Schema, and Cube Aggregation and more Slides Database Management Systems (DBMS) in PDF only on Docsity! Lecture 10:
More OLAP - Dimensional modeling
Budget Sales and Profit
www.cl.cam.ac.uk/T eaching/current/Databases/
2 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 5 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 6 Another Star Schema time_key day day_of_the_week month quarter year time location_key street city province_or_street country location Sales Fact Table time_key item_key branch_key location_key units_sold dollars_sold avg_sales Measures item_key item_name brand type supplier_type item branch_key branch_name branch_type branch 7 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 10 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 81 11 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 1 81 2 48 12 Another Example 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, product • In SQL: SELECT date, sum(amt) FROM SALE GROUP BY date, prodId sale prodId date amt p1 1 62 p2 1 19 p1 2 48 drill-down rollup 15 Cube Operators day 2 c1 c2 c3 p1 44 4 p2 c1 c2 c3 p1 12 50 p2 11 8 day 1 c1 c2 c3 p1 56 4 50 p2 11 8 c1 c2 c3 sum 67 12 50 sum p1 110 p2 19 129 . . . sale(c1,*,*) sale(*,*,*) sale(c2,p2,*) 16 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 p2 * 44 4 48 c1 c2 c3 * p1 12 50 62 p2 11 8 19 * 23 8 50 81 day 1 * sale(*,p2,*) 17 Aggregation Using Hierarchies day 2 c1 c2 c3 p1 44 4 p2 c1 c2 c3 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)
