1 / 20

ACCTG 6910 Building Enterprise & Business Intelligence Systems (e.bis)

ACCTG 6910 Building Enterprise & Business Intelligence Systems (e.bis). Dimensional Modeling VI. Olivia R. Liu Sheng, Ph.D. Emma Eccles Jones Presidential Chair of Business. Outline. Factless Facts (Ch. 2, 12, 15) Snapshot Facts (Ch. 3) Slowly Changing Dimensions (Ch. 4)

lloyd
Download Presentation

ACCTG 6910 Building Enterprise & Business Intelligence Systems (e.bis)

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. ACCTG 6910Building Enterprise & Business Intelligence Systems(e.bis) Dimensional Modeling VI Olivia R. Liu Sheng, Ph.D.Emma Eccles Jones Presidential Chair of Business

  2. Outline • Factless Facts (Ch. 2, 12, 15) • Snapshot Facts (Ch. 3) • Slowly Changing Dimensions (Ch. 4) • M-to-M Relationships – Multi-valued Dimensions (Ch. 9, 13)

  3. Factless Facts: Measuring Occurrences of Relationships or Events Faculty Student 1 m m Course (Ternary) Relationship: One or many students take one or many courses from one teacher Measure: # of times the whole or a partial relationship occurs

  4. Factless Facts No attributes other than the DW keys of dimension tables are in the fact table Faculty Dimension Student Dimension Enrollment Fact Faculty Key Student Key Course Key Time Key Course Dimension Time Dimension

  5. Factless Facts Classroom 1 1 m Faculty Semester m Course Relationship: a teacher teaches in a classroom 1 or many courses over one or many semesters Measure: # of times the whole or a partial relationship occurs

  6. Factless Facts Faculty Dimension Room Dimension Room Assignment Fact Room Key Faculty Key Course Key Time Key Course Dimension Time Dimension

  7. Snapshot Facts: The Simplest Inventory Schema Inventory Fact time_key product_key warehouse_key quantity_on_hand Time Dimension Product Dimension Warehouse Dimension Accumulative measure

  8. Slowly Changing Dimension

  9. CID CName State City 101 Jon Arizona Tucson 102 Tom Arizona Tucson 103 Mark Arizona Phoenix Slowly Changing Dimension • Values of attributes in dimension tables may evolve over time. For example, customers moved from one city to another city. Salt Lake City Utah Tom moved from Tucson to Salt Lake City

  10. CID CID CName CName State State City City 101 101 Jon Jon Arizona Arizona Tucson Tucson 102 102 Tom Tom Arizona Utah Tucson Salt Lake city 103 103 Mark Mark Arizona Arizona Phoenix Phoenix Slowly Changing Dimension • There are three ways to handle slowly changing dimension. • Method 1: Overwrite old values with new values

  11. Slowly Changing Dimension Drawbacks of method 1: Historical information is totally lost. We will never know that customer 102 lived in Tucson before. Moreover, when listing sales by city, all the sales of customer 102 will be counted as part of Salt Lake City sales, although 102 was in Tucson before.

  12. CID CID CName CName State State Original City City Current City 101 Jon Arizona Tucson 101 Jon Arizona Tucson Tucson 102 Tom Arizona Tucson 102 Tom Arizona Tucson Salt Lake City 103 Mark Arizona Phoenix 103 Mark Arizona Phoenix Phoenix Slowly Changing Dimension • Method 2: Add a new attribute to record current value of the changing attribute. Current State Arizona Utah Arizona

  13. Slowly Changing Dimension Drawbacks of method 2: Only partial Historical information (original & current) is kept. Considering that customer 102 moved from Tucson to Phoenix then to Salt Lake City, the customer information of customer 102 only includes Tucson and Salt Lake City.

  14. Slowly Changing Dimension Method 3: Add a new dimension record whenever change occurs  keep all the information. Warehouse key Utah Salt Lake City Utah

  15. Multi-Value Dimension • Most of the dimension tables have a 1-m relationship with the fact table • Product  Sale, Customer  Sale, SalesDate  Sale… • What if there is a m-to-m relationship between a dimension and a fact?

  16. Multi-Value Dimension The above table is a visit table from a clinic. We want a factless fact table and preserve medication information

  17. Multi-Value Dimension Time VISIT Physician Patient m to m !! Med. A physician may prescribe 1 or many medications at a patient visit A physician may prescribe the same medication at different visits

  18. Multi-Value Dimension If we have a “grouping” table for medication….

  19. Medication Group Bridage is what we call a bridge table Medication Group Bridge Medication Dimension Medication Group 1 1 m m

  20. Multi-Value Dimension Medication Group Bridge 1 1 Medication Medication Group m m VISIT Physician Patient Time

More Related