Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

One or two fact tables? Expand / Collapse
Author
Message
Posted Thursday, August 8, 2013 3:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
Hello,

I am currently working on a dimensional model for insurance claims.

When a claim is made money is paid out by the insurance company at a policy level (stored in a transactional format at source).

Money can also be recovered against a claim. This is stored in the source system in a transaction level but at a lower level of detail than policy.

Having one fact table for claims logically makes the most sense but the payments made in the insured does not break down below policy level.

I wanted to throw this out there to get some ideas on how best to design this if poss?

Thanks
Post #1482191
Posted Thursday, August 8, 2013 3:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 13,639, Visits: 10,532
Is a claim bound in any way to a policy? If yes and claim is just a smaller level of detail of a policy, than I would create just one fact table at the claim level.

If you need data at the policy level, you just aggregate it.
It's a best practice to create your fact tables at the lowest level of detail (aka the grain) possible.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482194
Posted Thursday, August 8, 2013 4:14 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:02 AM
Points: 689, Visits: 2,779
Koen Verbeeck (8/8/2013)
Is a claim bound in any way to a policy? If yes and claim is just a smaller level of detail of a policy, than I would create just one fact table at the claim level.

If you need data at the policy level, you just aggregate it.
It's a best practice to create your fact tables at the lowest level of detail (aka the grain) possible.


Yep +1


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1482205
Posted Thursday, August 8, 2013 5:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
Hi Both,

Thanks for your replies...I'll provide a little more detail based on your response.

Source system has the following tables:
Policy table (a record for each policy)

Policy risk (a record for each policy/risk type/premium class per each policy renewal)

Claim Table (a record for each policy/claim/risk type/premium class) - this table contains the total paid out and the total recovered.

Claim Recovery Table (a record for each policy/claim/risk type/premium class/treaty/reinsurance) - this table holds the recovered.

A policy can have 1 to many associated risk types
A policy can have 1 to many associated premium types
A policy can have 0 to many claims
A claim can have 0 to many recoveries

So I'll create a simple policy dimension containing a record for each policy.

I have then toyed with the idea then of creating a mini dimension(s) to store the rapidly changing risk and premium types as each policy could end up with hundred of records. (each policy appears an average of 47 times on the policy risk table). What are your views on this?

I want to have a single fact table at the lowest grain possible. However I am struggling with the total paid figure which is not available at the same grain as recovery.
Post #1482226
Posted Thursday, August 8, 2013 5:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 13,639, Visits: 10,532
Is it possible to post some sample data (this may be fake).
It would be easier to wrap my head around.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482230
Posted Thursday, August 8, 2013 6:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
Ok thanks for your help:

declare @policy table
(policy_number int)

insert into @policy
select 100
union
select 101
union
select 102

declare @policy_risk table
(policy_number int,
risk_type int,
premium_class char(3))

insert into @policy_risk
select 100, 1, 'AA1'
union
select 100, 1, 'ACC'
union
select 100, 1, 'GHT'
union
select 100, 1, 'DES'
union
select 100, 1, 'FIR'
union
select 101, 1, 'FIR'
union
select 101, 1, 'SEC'
union
select 101, 1, 'PPL'
union
select 102, 2, 'STA'
union
select 102, 2, 'KLS'
union
select 102, 2, 'ZZA'
union
select 102, 3, 'HTD'
union
select 102, 3, 'DTR'

declare @claim table
(claim_number int,
policy_number int,
risk_type int,
premium_class char(3),
total_paid money,
total_recovered money)

insert into @claim
select 1, 101, 1, 'FIR', 345, 0
union
select 2, 102, 2, 'STA', 7465.21, 100


declare @claim_recoveries table
(claim_recovery_id int identity(1,1),
claim_number int,
policy_number int,
risk_type int,
premium_class char(3),
treaty char(3),
reinsurance_id int,
total_recovered money)

insert into @claim_recoveries
select 2, 102, 2, 'STA', 'QSH',50, 25
union all
select 2, 102, 2, 'STA', 'AAA',51, 25


select * from @policy
select * from @policy_risk
select * from @claim
select * from @claim_recoveries

Post #1482245
Posted Thursday, August 8, 2013 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 13,639, Visits: 10,532
The total_recovered value in claim (100) is not the same as the sum of the recoveries in claim_recoveries (2 x 25). Is this normal?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482250
Posted Thursday, August 8, 2013 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
Apologies! My mistake sorry.

It should total the 100. Updated code attached:

declare @policy table
(policy_number int)

insert into @policy
select 100
union
select 101
union
select 102

declare @policy_risk table
(policy_number int,
risk_type int,
premium_class char(3))

insert into @policy_risk
select 100, 1, 'AA1'
union
select 100, 1, 'ACC'
union
select 100, 1, 'GHT'
union
select 100, 1, 'DES'
union
select 100, 1, 'FIR'
union
select 101, 1, 'FIR'
union
select 101, 1, 'SEC'
union
select 101, 1, 'PPL'
union
select 102, 2, 'STA'
union
select 102, 2, 'KLS'
union
select 102, 2, 'ZZA'
union
select 102, 3, 'HTD'
union
select 102, 3, 'DTR'

declare @claim table
(claim_number int,
policy_number int,
risk_type int,
premium_class char(3),
total_paid money,
total_recovered money)

insert into @claim
select 1, 101, 1, 'FIR', 345, 0
union
select 2, 102, 2, 'STA', 7465.21, 100


declare @claim_recoveries table
(claim_recovery_id int identity(1,1),
claim_number int,
policy_number int,
risk_type int,
premium_class char(3),
treaty char(3),
reinsurance_id int,
total_recovered money)

insert into @claim_recoveries
select 2, 102, 2, 'STA', 'QSH',50, 50
union all
select 2, 102, 2, 'STA', 'AAA',51, 50


select * from @policy
select * from @policy_risk
select * from @claim
select * from @claim_recoveries

Post #1482298
Posted Thursday, August 8, 2013 7:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 13,639, Visits: 10,532
OK, now that I see the data, I think you should have 2 fact tables, because indeed they are at two different grains.

Recovery is at the treaty level, while total paid is at the claim level.

Also the fact that some claims don't have recoveries makes me think in the direction of two fact tables.
But that's OK. When you analyse the data later on in Excel, you drag the total_paid measure next to the pivot and claims to the rows. When you add the recovery, it will automatically aggregate the data to the claims level.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482304
Posted Thursday, August 8, 2013 8:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
Thanks for your time on this. The conclusion I kept coming to was two fact tables as well.

What did you think about the mini dimension solution for those more frequently changing attributes?
Post #1482356
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse