Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension Level

2 posters

Go down

Dimension Level Empty Dimension Level

Post  guly 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

Back to top Go down

Dimension Level Empty Re: Dimension Level

Post  cjrinpdx 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

Back to top Go down

Back to top

- Similar topics

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