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

Chapter 16

Insurance

We bring together concepts from nearly all the previous chapters to build a DW/BI system for a property and casualty insurance company in this final case study. If you are from the insurance industry and jumped directly to this chapter for a quick fix, please accept our apology, but this material depends heavily on ideas from the earlier chapters. You'll need to turn back to the beginning of the book to have this chapter make any sense.

As has been our standard procedure, this chapter launches with background information for a business case. While the requirements unfold, we'll draft the enterprise data warehouse bus matrix, much like we would in a real-life requirements analysis effort. We'll then design a series of dimensional models by overlaying the core techniques learned thus far.

Chapter 16 reviews the following concepts:

• Requirements-driven approach to dimensional design
• Value chain implications, along with an example bus matrix snippet for an insurance company
• Complementary transaction, periodic snapshot, and accumulating snapshot schemas
• Dimension role playing
• Handling of slowly changing dimension attributes
• Mini-dimensions for dealing with large, rapidly changing dimension attributes
• Multivalued dimension attributes
• Degenerate dimensions for operational control numbers
• Audit dimensions to track data lineage
• Heterogeneous supertypes and subtypes to handle products with varied attributes and facts
• Junk dimensions for miscellaneous indicators
• Conformed ...