Dimensional Modeling

1. Dimensional Modeling Chapter 2

2. The Dimensional Data Model An alternative to the normalized data model • Present information as simply as possible (easier to understand) • Return queries as quickly as possible (efficient for queries) • Track the underlying business processes (process focused)

3. The Dimensional Data Model • Contains the same information as the normalized model • Has far fewer tables • Grouped in coherent business categories • Pre-joins hierarchies and lookup tables resulting in fewer join paths and fewer intermediate tables • Normalized fact table with denormalized dimension tables.

4. Customer #Cust No F Name L Name Ads1 Ads2 City State Zip Tel No CC No Expire Rental #Rental No Date Clerk No Pay Type CC No Expire CC Approval Owner of Line #Line No Due Date Return Date OD charge Pay type Requestor of Holder of Name for Video #Video No One-day fee Extra days Weekend Title #Title No Name Vendor No Cost GB Video E-R Diagram

5. Line LineID OD Charge OneDayCharge ExtraDaysCharge WeekendCharge DaysReserved DaysOverdue CustID AddressID RentalId VideoID TitleID RentalDateID DueDateID ReturnDateID Customer CustID Cust No F Name L Name Video VideoID Video No Address AddressID Adddress1 Address2 City State Zip AreaCode Phone Title TitleID TitleNo Name Cost Vendor Name Rental Date RentalDateID SQLDate Day Week Quarter Holiday Rental RentalID Rental No Clerk No Store Pay Type Due Date DueDateID SQLDate Day Week Quarter Holiday Return Date ReturnDateID SQLDate Day Week Quarter Holiday GB Video Data Mart

6. Fact Table Measurements associated with a specific business process • Grain: level of detail of the table • Process events produce fact records • Facts (attributes) are usually • Numeric • Additive • Derived facts included • Foreign (surrogate) keys refer to dimension tables (entities) • Classification values help define subsets

7. Dimension Tables Entities describing the objects of the process • Conformed dimensions cross processes • Attributes are descriptive • Text • Numeric • Surrogate keys • Less volatile than facts (1:m with the fact table) • Null entries • Date dimensions • Produce “by” questions

8. Bus Architecture • An architecture that permits aggregating data across multiple marts • Conformed dimensions and attributes • Drill Down vs. Drill Across • Bus matrix

9. Keys and Surrogate Keys A surrogate key is a unique identifier for data warehouse records that replaces source primary keys (business/natural keys) • Protect against changes in source systems • Allow integration from multiple sources • Enable rows that do not exist in source data • Track changes over time (e.g. new customer instances when addresses change) • Replace text keys with integers for efficiency

10. Slowly Changing Dimensions Attributes in a dimension that change more slowly than the fact granularity • Type 1: Current only • Type 2: All history • Type 3: Most recent few (rare) Note: rapidly changing dimensions usually indicate the presence of a business process that should be tracked as a separate dimension or as a fact table

11. Fact Table Dimension with a slowly changing attribute

12. Date Dimensions • One row for every day for which you expect to have data for the fact table (perhaps generated in a spreadsheet and imported) • Usually use a meaningful integer surrogate key (such as yyyymmdd 20060926 for Sep. 26, 2006). Note: this order sorts correctly. • Include rows for missing or future dates to be added later.

13. Degenerate Dimensions • Dimensions without attributes. (Such as a transaction number or order number.) • Put the attribute value into the fact table even though it is not an additive fact.

14. Snowflaking(Outrigger Dimensions or Reference Dimensions) • Connects entities to dimension tables rather than the fact table • Complicates coding and requires additional processing for retrievals • Makes type 2 slowly changing dimensions harder to maintain • Useful for seldom used lookups

15. M:N Multivalued Dimensions • Fact to Dimension • Dimension to Dimension • Try to avoid these. Solutions can be very misleading.

16. Multivalued Dimensions SALESREP SalesRepKey Name Address ORDERS (FACT) SalesRepKey ProductKey SalesRepGrpKey CustomerKey OrderQty SALESREP-ORDER-BRIDGE SalesRepKey SalesrepGroupKey Weight= (1/NumReps)

17. Hierarchies Group data within dimensions: SalesRep • Region • State • County • Neighborhood Problem structures • Variable depth • Frequently changing

18. Heterogeneous Products • Several different kinds of entry with different attributes for each • (The sub-class problem)

19. Aggregate Dimensions • Dimensions that represent data at different levels of granularity • Remove a dimension • Roll up the hierarchy (provide a new shrunken dimension with new surr-key that represents rolled up data)

20. Junk Dimensions • Miscellaneous attributes that don’t belong to another entity, usually representing processing levels • Flags • Categories • Types

21. Fact Tables • Transaction • Track processes at discrete points in time when they occur • Periodic snapshot • Cumulative performance over specific time intervals • Accumulating snapshot • Constantly updated over time. May include multiple dates representing stages.

22. Aggregates • Precalculated summary tables • Improve performance • Record data an coarser granularity