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

1. ACCTG 6910Building Enterprise & Business Intelligence Systems(e.bis) Dimensional Modeling II Olivia R. Liu Sheng, Ph.D.Emma Eccles Jones Presidential Chair of Business

2. TechnicalArchitectureDesign ProductSelection &Installation End-UserApplicationSpecification End-UserApplicationDevelopment The Business Dimensional Lifecycle Business Requirement Definition DimensionalModeling PhysicalDesign Data StagingDesign &Development ProjectPlanning Deployment MaintenanceandGrowth Project Management

3. Outline • Table structure, types, characteristics and terminology • Design steps • Dimensional models with varying types of fact and dimension tables

4. Types of Facts • Transactional facts (transactions or line items in transactions) • Snapshots • Factless facts

5. Types of Dimensions • Role playing dimensions • Heterogeneous dimensions • Slowly changing dimensions • Large dimensions • Many-to-many dimensions

6. Keys and Attributes • Primary key - a column whose value uniquely identifies each row (record) in the table. • Attributes – columns in a table that are not designated as the primary key. • Foreign key – a non-primary-key attribute for a table that corresponds to a primary key of another table.

7. Attributes in DW tables • Dimension Table • One Primary Key • Dimension Attributes • Fact table • Primary key --- A collection of primary keys from all its associated dimension tables • All warehouse keys in fact table are foreign keys referring to its associated dimension tables • All/part of warehouse keys in fact table form the primary key of fact table • Fact Attributes

8. Attributes in DW tables Data warehouse keys generated by the system

9. Keys and Grain • Keys • Primary or natural keys (from source systems) • Warehouse or synthetic keys (generated by a data warehouse tool) • Grain • The level of detail of fact measures described in the DW, e.g., sales transactions from order line items by order date, product and customer

10. Single-Fact-Table Data Warehouse Design Decisions • The business questions in focus and source information systems* • The grain of the fact table • The dimensions tables and keys • The fact attributes and dimension attributes *All DW attributes must be mapped to or derived from source attributes

11. Single-Fact-Table Data Warehouse Design Decisions • The business questions in focus and source information systems • The grain of the fact table • The dimensions tables and keys • The fact attributes and dimension attributes

12. Sample Business Questions • Report Sales in terms of – (total) amt, (total) qty and (avg.) price • Report Sales by PRODUCT name and/or category name • Report Sales by CUSTOMER name, city and/or or state • Report Sales by ORDER date, month, year, holiday, special event or other time constraints • Report using a combination of the measures and constraints

13. Relational Schema of B.com B2B System Orders ( Order_No, SID, BID, CID,Order_date) OrderLine (Order_No, Line_ID, PID, Actual_Del_Date, Target_Del_Date, Arrival_Date, Shipping_Fee, Tax, Quantity, Unit_Price,Defect_on_arrival) Delivery ( SID, CID, Unit_shipping_fee, UNIT_DEL_TIME) Contract ( CID, Contract_Name, Payment_term, Payment_num) Payment ( PaymentID, OrderNO, Pay_Amount, Date)

14. Relational Schema of B.com B2B System Category ( CAT_ID, CAT_Name) Product ( PID, CAT_ID, P_Weight, P_Life, P_Name) Supplier ( SID, S_Name, S_City, S_State, S_Country) Product_Supply ( PID, SID, Unit_Price, Quantity_in_Stock, Production_in_Week) Buyer ( BID,B_Name, CityID, B_Type) Buyer_City ( CityID, C_Name, C_State, C_Country, C_Tax)

15. Single-Fact-Table Data Warehouse Design Decisions • The business questions in focus and source information systems • The grain of the fact table • The dimensions tables and keys • The fact attributes and dimension attributes

16. Grain of the Fact Table Type of fact table: transactional facts • Potential grains: order or orderline • Constraints: order date, product, customer • Grain: sales from orderline (by order date, product, and customer)

17. Single-Fact-Table Data Warehouse Design Decisions • The business questions in focus and source information systems • The grain of the fact table • The dimensions tables and keys • The fact attributes and dimension attributes

18. Dimension Tables and Keys Key dimension tables jointly make up the primary key for a fact table

19. Single-Fact-Table Data Warehouse Design Decisions • The business questions in focus and source information systems • The grain of the fact table • The dimensions tables and keys • The fact attributes and dimension attributes

20. Determine Fact Attributes

21. Types of Fact Attributes • Additive fact attributes can be added along any dimension.

22. Types of Fact Attributes • Non-additive fact attributes cannot be added along any dimension.

23. Types of Fact Attributes • Semi-additive fact attributes can be added along some dimensions.

24. Time Dimension • Data warehouse needs an explicit time dimension table instead of just a time attribute (e.g, ORDERDATE). • Save computation effort and improve query performance • Complex queries regarding calendar calculation are hidden from end users of data warehouse.

25. Time Dimension Besides the time attribute, time dimension table includes the following additional attributes: • Day_of_week (1-7); Day_number_in_month (1-31); • Day_number_in_year (1-365) • Week_number (1-52); month (1-12), Quarter (1-4) • Holiday_flag (y/n) • Fiscal_quarter, Fiscal_year

26. Determine Dimension Attributes

27. Avoid Snowflake Designs

28. Avoid Snowflake Design Snowflake structure

29. Avoid Snowflake Schemas • Tradeoff of avoiding snowflake • Advantage: improve query performance and easy of understanding • Disadvantage: require more storage space