1 / 18

Lecture 10: More OLAP - Dimensional modeling

Learn about dimensional modeling in data warehouses, including star schemas, snowflake schemas, and fact constellations. Understand cube aggregation and how to use hierarchies for rollup and drill-down. Explore examples and operators for aggregating data.

wayneconn
Download Presentation

Lecture 10: More OLAP - Dimensional modeling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 10:More OLAP - Dimensional modeling www.cl.cam.ac.uk/Teaching/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

  3. Star

  4. Star Schema

  5. Terms • Fact table • Dimension tables • Measures

  6. item time item_key item_name brand type supplier_type time_key day day_of_the_week month quarter year location branch location_key street city province_or_street country branch_key branch_name branch_type Another Star Schema Sales Fact Table time_key item_key branch_key location_key units_sold dollars_sold avg_sales Measures

  7. Dimension Hierarchies sType store city region è snowflake schema è constellations

  8. Cube Fact table view: Multi-dimensional cube: dimensions = 2

  9. day 2 day 1 3-D Cube Fact table view: Multi-dimensional cube: dimensions = 3

  10. Aggregates • Add up amounts for day 1 • In SQL: SELECT sum(amt) FROM SALE • WHERE date = 1 81

  11. Aggregates • Add up amounts by day • In SQL: SELECT date, sum(amt) FROM SALE • GROUP BY date

  12. Another Example • Add up amounts by day, product • In SQL: SELECT date, sum(amt) FROM SALE • GROUP BY date, prodId rollup drill-down

  13. Aggregates • Operators: sum, count, max, min, median, ave • “Having” clause • Using dimension hierarchy • average by region (within store) • maximum by month (within date)

  14. rollup drill-down Cube Aggregation Example: computing sums day 2 . . . day 1 129

  15. Cube Operators day 2 . . . day 1 sale(c1,*,*) 129 sale(c2,p2,*) sale(*,*,*)

  16. Extended Cube * day 2 sale(*,p2,*) day 1

  17. day 2 day 1 Aggregation Using Hierarchies customer region country (customer c1 in Region A; customers c2, c3 in Region B)

  18. day 2 day 1 Pivoting Fact table view: Multi-dimensional cube:

More Related