book

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

by Ralph Kimball, Margy Ross

April 2002

Intermediate to advanced

464 pages

12h 25m

English

Wiley

Read now

Unlock full access

Intended AudienceChapter PreviewNavigation AidsPurpose of Each ChapterChapter 1: Dimensional Modeling PrimerChapter 2: Retail SalesChapter 3: InventoryChapter 4: ProcurementChapter 5: Order ManagementChapter 6: Customer Relationship ManagementChapter 7: AccountingChapter 8: Human Resources ManagementChapter 9: Financial ServicesChapter 10: Telecommunications and UtilitiesChapter 11: TransportationChapter 12: EducationChapter 13: Health CareChapter 14: Electronic CommerceChapter 15: InsuranceChapter 16: Building the Data WarehouseChapter 17: Present Imperatives and Future OutlookGlossaryCompanion Web SiteSummary
1.1. Different Information Worlds1.2. Goals of a Data Warehouse1.2.1. The Publishing Metaphor1.3. Components of a Data Warehouse1.3.1. Operational Source Systems1.3.2. Data Staging Area1.3.3. Data Presentation1.3.4. Data Access Tools1.3.5. Additional Considerations1.4. Dimensional Modeling Vocabulary1.4.1. Fact Table1.4.2. Dimension Tables1.4.3. Bringing Together Facts and Dimensions1.5. Dimensional Modeling Myths1.5.1. Common Pitfalls to Avoid1.6. Summary
2.1. Four-Step Dimensional Design Process2.2. Retail Case Study2.2.1. Step 1. Select the Business Process2.2.2. Step 2. Declare the Grain2.2.3. Step 3. Choose the Dimensions2.2.4. Step 4. Identify the Facts2.3. Dimension Table Attributes2.3.1. Date Dimension2.3.2. Product Dimension2.3.3. Store Dimension2.3.4. Promotion Dimension2.3.5. Degenerate Transaction Number Dimension2.4. Retail Schema in Action2.5. Retail Schema Extensibility2.6. Resisting Comfort Zone Urges2.6.1. Dimension Normalization (Snowflaking)2.6.2. Too Many Dimensions2.7. Surrogate Keys2.8. Market Basket Analysis2.9. Summary
3.1. Introduction to the Value Chain3.2. Inventory Models3.2.1. Inventory Periodic Snapshot3.2.2. Inventory Transactions3.2.3. Inventory Accumulating Snapshot3.3. Value Chain Integration3.4. Data Warehouse Bus Architecture3.4.1. Data Warehouse Bus Matrix3.4.2. Conformed Dimensions3.4.3. Conformed Facts3.5. Summary
4.1. Procurement Case Study4.2. Procurement Transactions4.2.1. Multiple- versus Single-Transaction Fact Tables4.2.2. Complementary Procurement Snapshot4.3. Slowly Changing Dimensions4.3.1. Type 1: Overwrite the Value4.3.2. Type 2: Add a Dimension Row4.3.3. Type 3: Add a Dimension Column4.4. Hybrid Slowly Changing Dimension Techniques4.4.1. Predictable Changes with Multiple Version Overlays4.4.2. Unpredictable Changes with Single-Version Overlay4.5. More Rapidly Changing Dimensions4.6. Summary
5.1. Introduction to Order Management5.2. Order Transactions5.2.1. Fact Normalization5.2.2. Dimension Role-Playing5.2.3. Product Dimension Revisited5.2.4. Customer Ship-To Dimension5.2.5. Deal Dimension5.2.6. Degenerate Dimension for Order Number5.2.7. Junk Dimensions5.2.8. Multiple Currencies5.2.9. Header and Line Item Facts with Different Granularity5.3. Invoice Transactions5.3.1. Profit and Loss Facts5.3.2. Profitability—The Most Powerful Data Mart5.3.3. Profitability Words of Warning5.3.4. Customer Satisfaction Facts5.4. Accumulating Snapshot for the Order Fulfillment Pipeline5.4.1. Lag Calculations5.4.2. Multiple Units of Measure5.4.3. Beyond the Rear-View Mirror5.5. Fact Table Comparison5.5.1. Transaction Fact Tables5.5.2. Periodic Snapshot Fact Tables5.5.3. Accumulating Snapshot Fact Tables5.6. Designing Real-Time Partitions5.6.1. Requirements for the Real-Time Partition5.6.2. Transaction Grain Real-Time Partition5.6.3. Periodic Snapshot Real-Time Partition5.6.4. Accumulating Snapshot Real-Time Partition5.7. Summary
6.1. CRM Overview6.1.1. Operational and Analytic CRM6.1.2. Packaged CRM6.2. Customer Dimension6.2.1. Name and Address Parsing6.2.2. Other Common Customer Attributes6.2.3. Dimension Outriggers for a Low-Cardinality Attribute Set6.2.4. Large Changing Customer Dimensions6.2.5. Implications of Type 2 Customer Dimension Changes6.2.6. Customer Behavior Study Groups6.2.7. Commercial Customer Hierarchies6.2.8. Combining Multiple Sources of Customer Data6.3. Analyzing Customer Data from Multiple Business Processes6.4. Summary
7.1. Accounting Case Study7.2. General Ledger Data7.2.1. General Ledger Periodic Snapshot7.2.2. General Ledger Journal Transactions7.2.3. Financial Statements7.3. Budgeting Process7.3.1. Consolidated Fact Tables7.4. Role of OLAP and Packaged Analytic Solutions7.5. Summary

8.1. Time-Stamped Transaction Tracking in a Dimension8.2. Time-Stamped Dimension with Periodic Snapshot Facts8.3. Audit Dimension8.4. Keyword Outrigger Dimension8.4.1. AND/OR Dilemma8.4.2. Searching for Substrings8.5. Survey Questionnaire Data8.6. Summary
9.1. Banking Case Study9.2. Dimension Triage9.2.1. Household Dimension9.2.2. Multivalued Dimensions9.2.3. Minidimensions Revisited9.3. Arbitrary Value Banding of Facts9.4. Point-in-Time Balances9.5. Heterogeneous Product Schemas9.5.1. Heterogeneous Products with Transaction Facts9.6. Summary
10.1. Telecommunications Case Study10.2. General Design Review Considerations10.2.1. Granularity10.2.2. Date Dimension10.2.3. Degenerate Dimensions10.2.4. Dimension Decodes and Descriptions10.2.5. Surrogate Keys10.2.6. Too Many (or Too Few) Dimensions10.3. Draft Design Exercise Discussion10.4. Geographic Location Dimension10.4.1. Location Outrigger10.4.2. Leveraging Geographic Information Systems10.5. Summary
11.1. Airline Frequent Flyer Case Study11.1.1. Multiple Fact Table Granularities11.1.2. Linking Segments into Trips11.2. Extensions to Other Industries11.2.1. Cargo Shipper11.2.2. Travel Services11.3. Combining Small Dimensions into a Superdimension11.3.1. Class of Service11.3.2. Origin and Destination11.4. More Date and Time Considerations11.4.1. Country-Specific Calendars11.4.2. Time of Day as a Dimension or Fact11.4.3. Date and Time in Multiple Time Zones11.5. Summary
12.1. University Case Study12.2. Accumulating Snapshot for Admissions Tracking12.3. Factless Fact Tables12.3.1. Student Registration Events12.3.2. Facilities Utilization Coverage12.3.3. Student Attendance Events12.4. Other Areas of Analytic Interest12.5. Summary
13.1. Health Care Value Circle13.2. Health Care Bill13.2.1. Roles Played By the Date Dimension13.2.2. Multivalued Diagnosis Dimension13.2.3. Extending a Billing Fact Table to Show Profitability13.2.4. Dimensions for Billed Hospital Stays13.3. Complex Health Care Events13.4. Medical Records13.4.1. Fact Dimension for Sparse Facts13.5. Going Back in Time13.5.1. Late-Arriving Fact Rows13.5.2. Late-Arriving Dimension Rows13.6. Summary
14.1. Web Client-Server Interactions Tutorial14.2. Why the Clickstream Is Not Just Another Data Source14.2.1. Challenges of Tracking with Clickstream Data14.2.2. Specific Dimensions for the Clickstream14.3. Clickstream Fact Table for Complete Sessions14.4. Clickstream Fact Table for Individual Page Events14.5. Aggregate Clickstream Fact Tables14.6. Integrating the Clickstream Data Mart into the Enterprise Data Warehouse14.7. Electronic Commerce Profitability Data Mart14.8. Summary
15.1. Insurance Case Study15.1.1. Insurance Value Chain15.1.2. Draft Insurance Bus Matrix15.2. Policy Transactions15.2.1. Dimension Details and Techniques15.2.2. Alternative (or Complementary) Policy Accumulating Snapshot15.3. Policy Periodic Snapshot15.3.1. Conformed Dimensions15.3.2. Conformed Facts15.3.3. Heterogeneous Products Again15.3.4. Multivalued Dimensions Again15.4. More Insurance Case Study Background15.4.1. Updated Insurance Bus Matrix15.5. Claims Transactions15.6. Claims Accumulating Snapshot15.7. Policy/Claims Consolidated Snapshot15.8. Factless Accident Events15.9. Common Dimensional Modeling Mistakes to Avoid15.10. Summary
16.1. Business Dimensional Lifecycle Road Map16.1.1. Road Map Major Points of Interest16.2. Project Planning and Management16.2.1. Assessing Readiness16.2.2. Scoping16.2.3. Justification16.2.4. Staffing16.2.5. Developing and Maintaining the Project Plan16.3. Business Requirements Definition16.3.1. Requirements Preplanning16.3.2. Collecting the Business Requirements16.3.3. Postcollection Documentation and Follow-up16.4. Lifecycle Technology Track16.5. Technical Architecture Design16.5.1. Eight-Step Process for Creating the Technical Architecture16.6. Product Selection and Installation16.7. Lifecycle Data Track16.8. Dimensional Modeling16.9. Physical Design16.9.1. Aggregation Strategy16.9.2. Initial Indexing Strategy16.10. Data Staging Design and Development16.10.1. Dimension Table Staging16.10.2. Fact Table Staging16.11. Lifecycle Analytic Applications Track16.11.1. Analytic Application Specification16.11.2. Analytic Application Development16.12. Deployment16.13. Maintenance and Growth16.14. Common Data Warehousing Mistakes to Avoid16.15. Summary
17.1. Ongoing Technology Advances17.2. Political Forces Demanding Security and Affecting Privacy17.2.1. Conflict between Beneficial Uses and Insidious Abuses17.2.2. Who Owns Your Personal Data?17.2.3. What Is Likely to Happen? Watching the Watchers . . .17.2.4. How Watching the Watchers Affects Data Warehouse Architecture17.3. Designing to Avoid Catastrophic Failure17.3.1. Catastrophic Failures17.3.2. Countering Catastrophic Failures17.4. Intellectual Property and Fair Use17.5. Cultural Trends in Data Warehousing17.5.1. Managing by the Numbers across the Enterprise17.5.2. Increased Reliance on Sophisticated Key Performance Indicators17.5.3. Behavior Is the New Marquee Application17.5.4. Packaged Applications Have Hit Their High Point17.5.5. Application Integration Has to Be Done by Someone17.5.6. Data Warehouse Outsourcing Needs a Sober Risk Assessment17.6. In Closing

Content preview from The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

Glossary

24/7: Operational availability 24 hours a day, 7 days a week.
3NF: See Third normal form.
Accumulating snapshot fact table: Type of fact table with multiple dates representing the major milestones of a relatively short-lived process or pipeline. The fact table is revisited and updated as activity occurs. A record is placed in an accumulating snapshot fact table just once, when the item that it represents is first created. Contrast with Periodic snapshot fact table and Transaction-grain fact table.
Activity-based costs: Costs that are reported as a measure of the activity required rather than on an unchanging standard value. See Allocations.
Additive (facts): Measurements in a fact table that can be added across all the dimensions. Ratios and unit prices are not generally additive.
Ad hoc queries: Queries that are formulated by the user on the spur of the moment. The ad hoc attack refers to the difficulty a database has in anticipating the pattern of queries. The more that queries are ad hoc, the more symmetric the database model must be so that all queries look the same. This is one of the strengths of the dimensional modeling approach.
Aggregate navigator: Layer of software between the client and the relational data that intercepts the client's Structured Query Language (SQL) and transforms that SQL, if it can, to use aggregates that are present somewhere in the data warehouse. The aggregate navigator, by definition, shields the user application from needing to know if an aggregate is present. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition

Ralph Kimball, Margy Ross

Data Modeling, A Beginner's Guide

Andy Oppel

Learning Data Modeling

Michael Blaha

Fundamentals of Data Engineering

Joe Reis, Matt Housley

Publisher Resources

ISBN: 9780471200246Purchase book

Glossary

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition

Data Modeling, A Beginner's Guide

Learning Data Modeling

Fundamentals of Data Engineering

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.