Adv. DBMS & DW

Chapter 9: Insurance Chapter 10: Factless Fact Tables

Hachim Haddouti

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Insurance

• • • • • • • • •

data: formulating processing transactions policies goals: measure profit/time/covered demography, sales rep, event transactions, which coverages are profitable item type, claims location, efficiency of claims handling TODO Main schemas for policy creation and claims processing Monthly snapshots Heterogeneous products And demographic dimensions

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Policy creation

policy represents a set of coverages, possibly of specific items Coverages are the real products such as fire, flood, theft, car coverages, and policy is HEADER for set of coverages sol to the insured party transactions: create/alter/cancel (with reason) policy create/alter/cancel (with reason) coverage on covered item rate/decline to rate coverage underwrite/decline to underwrite policy Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Dimensions

• dimensions: transaction date (TA entered into system), effective date (when TA legally effective), insured party (customer with multiple values is Big and dirty Dim), employee (agent, broker, rater, underwriter), coverage (deductibles or limits as facts), covered item (car, house,etc.), policy, transaction • • insured party probably dirty dimension -- big dimension possible other facts, like deductible amount • covered item is also big • only other fact is amount (variable meaning) (Fig 9.1, p 129) • Grain is individual transaction Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Claims processing

• • • • • • •

Claims against a specific coverage and coverage item.

Claimant may be new party for the insurance First of all, open claim reserve set/adjusted to anticipate likely claim payouts loss parties -- those involved in a claim, such as claimant, witnesses for each claim, payments to “experts” (doctors, lawyers, inspectors) as well as claimant need to identify responsible employee for each payment against an open claim

• • • • • •

transactions: open/reopen/close claim, set/reset/close reserve adjuster inspection/interview open/close lawsuit make/receive payment subrogate claim, … new dimensions: claimant, claim, third party (Fig 9.2, p 132)

–

Employee is responsible here for authorizing payments

–

claimant and third party are dirty dimensions.

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Policy and Claims

• Timings and counts of Tas of various types can be answered.

• And all other questions about the insurance company • But, bcs of numerous Tas it is difficult to get the status of policy or claim at any given time point (Recall Subscription Business case) • Same approach as in Cable TV example: monthly snapshot Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Monthly snapshots for policies and claims

• • snapshot view would require rolling all transactions forward from beginning of history...

• Remove dimensions: employee, claimant, third party, transaction • new dimension: status (just opened/reopened, claim pending, just closed) • Policy schema has grain by coverage by covered item by month • (Fig 9.3, p 134, 9.4 p 135) Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Transaction schemas with heterogeneous products

• • • • different coverages have very different sets of attributes (Fig 9.5, 9.6, p 136) custom covered item dimension able, custom coverage dim table for each type No custom fact table, only extending dimensions records.

4-25 different sets of tables • Snapshot schemas with heterogeneous products many additional specific numeric facts --> a custom snapshot table for each coverage type • • Minidimensions in insured party and covered item need accurate description of insured party and covered item both at creation time and current  split changeable attributes in one or more minidimensions, directl linked to the fact table.

minidimensions with all possibilities of changeable attributes retrieving these attributes  efficiency of Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Design summary

Design summary “

An appropriate design for a property and casualty insurance data warehouse is a short value chain consisting of policy creation and claims processing, where these two major processes are represented both by transaction fact tables and monthly snapshot fact tables. This data warehouse will almost certainly need to represent a number of heterogeneous products (coverage types) with appropriate combinations of core and custom dimension tables and fact tables.

Finally, the large insured party and covered item dimensions will need to be decomposed into one or more minidimensions in order to provide reasonable browsing performance and in order to accurately track these slowly changing dimensions.”

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Factless Fact Tables

Chapter 10

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Intro

• Previous examples: fact table with characteristic structure • Key values: administrative elements • Facts as measurements taken at the intersection of key values • Does fact table always contain facts?

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Factless Fact Tables

• no measured facts in some business processes • Two Variation of Factless fact table – Event tracking tables (events) – Coverage tables • Eg.

course attendance.

(Fig "Which courses were the most heavily attended?“ 10.1, • applications perform mostly counts, but no measured facts.

p 144) • Any one of the five keys can be used fort COUNT. (

select Professor, count (date_key) …. Group by professor

) Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

More event examples

• hospital patient treatment (Fig 10.2, p. 146) • parties involved in an accident (Fig 10.3, p. 146) • The Accident schema is able to represent complex accident with all involved parties • DP: “

Events are often modeled by a fact table containing a number of keys, each representing a participating dimension in the event. Such event tables often have no obvious numerical facts associated with them, and hence are called factless fact tables.”

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Coverage tables

• What about events that did not happen?

• Solution for the promotion questions  building a

coverage table

which records which items are on promotion in which store at which time. (Fig 10.4, p 148) • Coverage table is an inventory snapshot table for a chosen subset of the inventory (e.g. only promoted items need to be stored in coverage table) Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10

Coverage tables cont.

• What about the question promoted items which did not sell?

 Movement Fact table is also required.

• 2 behavioral group -- eg, items that sold on promotion from movement table and items on promotion from coverage table • Set difference between both groups is the answer • modeling events that didn't happen • PD: “

Coverage tables are often tables of events that didn't happen.

Coverage tables are usually factless in the same way as event tracking tables.”

Hachim Haddouti, adv. DBMS & DW CSC5301, Ch9&10