book

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

Name: The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling
ISBN: 9780471200246

by Ralph Kimball, Margy Ross

April 2002

Intermediate to advanced

464 pages

12h 25m

English

Wiley

Read now

Unlock full access

Copyright
ACKNOWLEDGMENTS
INTRODUCTION
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. Dimensional Modeling Primer
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. Retail Sales
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. Inventory
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. Procurement
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. Order Management
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. Customer Relationship Management
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. Accounting
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. Human Resources Management
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. Financial Services
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. Telecommunications and Utilities
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. Transportation
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. Education
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. Health Care
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. Electronic Commerce
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. Insurance
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. Building the Data Warehouse
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. Present Imperatives and Future Outlook
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
Glossary

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

Chapter 9. Financial Services

The financial services industry encompasses a wide variety of businesses, including credit card companies, brokerage firms, and mortgage providers. This chapter will focus primarily on retail banks given that most readers have some degree of personal familiarity with this type of financial institution. A full-service bank offers a breadth of products, including checking accounts, savings accounts, mortgage loans, personal loans, credit cards, and safe deposit boxes. This chapter begins with a very simplistic schema. We then explore several schema extensions, including handling of the bank's broad portfolio of heterogeneous products that vary significantly by line of business.

As we embark on a series of industry-focused chapters, we want to remind you that they are not intended to provide full-scale industry solutions. While various dimensional modeling techniques will be discussed in the context of a given industry, the techniques certainly are applicable to other businesses. If you don't work in financial services, you still need to read this chapter. If you do work in financial services, remember that the schemas in this chapter should not be viewed as complete.

Note

Chapter 9 discusses the following concepts:

Dimension triage to avoid the "too few dimensions" trap
Household dimensions
Associating individual customers with accounts using a bridge table
Multiple minidimensions in a single fact table
Value banding of facts for reporting purposes
Point-in-time ...

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.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

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

Publisher Resources

ISBN: 9780471200246Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

by Ralph Kimball, Margy Ross

Chapter 9. Financial Services

Note

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.