Dimension Level

View previous topic View next topic Go down

Dimension Level

Post  guly on Sat Apr 27, 2013 7:25 pm

I have a question re auto-insurance.

A policy transaction fact gets created for each transaction (Policy alter, create, cancel etc)

Policy Transaction Fact
Transaction Date ID (FK)
Coverage ID (FK)
Covered Item ID (FK)
Policy ID (FK)
PolicyHolder ID (FK) ---> Bridge --> Insured Driver
Transaction Type ID (FK)
Policy Number (DD)
Transaction Number (DD)
Premium

A claim can be created within the policy transaction for each driver:

(Policy-->Driver)1:M
(Driver-->Claim)1:M

Claim Fact
Claim Date ID (FK)
Insured Driver ID (FK)
Policy ID (FK)
Claim Type ID (FK)
Policy Number (DD)
Transaction Number (DD)
Claim Transaction Number (DD)
Claim Amount

The question is: what's the best approach to conform the claim and transaction fact? Should I use the degenerate dimensions (Policy Number, Transaction Number) or should I create a transaction dimension? The lowest granular level is the auto-claim, so is creating a transaction dimension valid? The insured drivers natural key gets regenerated with each policy transaction (driver details can change with each transaction), meaning everytime a transaction fact is created a insured driver dimension is as well. Any help/advise would be appreciated.




guly

Posts : 1
Join date : 2013-04-27

View user profile

Back to top Go down

Re: Dimension Level

Post  cjrinpdx on Fri May 03, 2013 1:52 pm

You want to relate a conformed dimension to your policy and claims fact tables. With my limited knowledge of the situation I would suggest creating a PolicyInfo dimension. Are you using the MS stack of tools? I ask because Iím not sure if SSAS could use one DD (or fact based dimension) on two fact tables (measure groups). For that reason I would ditch the DD and create a proper PolicyInfo dimension. Good luck.

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum