Header Level Dimension for a Fact Table

View previous topic View next topic Go down

Header Level Dimension for a Fact Table

Post  raghuk on Wed Jul 29, 2009 9:42 am

Hello,

This is an EDW solution for an insurance company that I've been designing.

I have

FactMonthEndTrans (This is per policy per coverage for every month)
===============
- MonthEndSnapshotDateKey
- PolicyKey
- CoverageKey
- AgentKey
- WrittenPremium
- EarnedPremium
- ...

At the end of the month, I also need to capture info like

1) Is this a new policy?
2) # of existing vehicles
3) # of newly-added vehicles...
4) ...etc.

These are all at the month-end to Policy level (no coverage info needed). Eg.,
Jun 2009 - Policy#123,Y,1,1

etc.

Should I create a new Dimension like the following?

DimMonthEndSummary (Per policy for every month)
- MonthEndSnapshotDateKey
- PolicyKey
- IsNewPolicy
- ExistingVehicleCount
- NewVehicleCount
- PolicyStatus

Here, the Date and Policy become outriggers and hence deviating from Star schema? Is this a good solution for my problem?

Any help is appreciated.

Thanks,

Raghu.

raghuk

Posts : 8
Join date : 2009-06-16

View user profile

Back to top Go down

Re: Header Level Dimension for a Fact Table

Post  ngalemmo on Wed Jul 29, 2009 12:47 pm

You need to create an aggregate snapshot fact table (a very common practice) NOT a dimension table. Policy status, new policy, etc, could be implemented as a junk dimension. You may also want to include other measures such as original premium amount and premium change amount as well as other dimensions as appropriate.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Header Level Dimension for a Fact Table

Post  raghuk on Wed Jul 29, 2009 2:46 pm

Thank you! I just forgot about Aggregate Snapshot... Thats what happens if you try to learn/do a lot in few days...

raghuk

Posts : 8
Join date : 2009-06-16

View user profile

Back to top Go down

Re: Header Level Dimension for a Fact Table

Post  raghuk on Fri Jul 31, 2009 3:26 pm

ngalemmo wrote:You need to create an aggregate snapshot fact table (a very common practice) NOT a dimension table. Policy status, new policy, etc, could be implemented as a junk dimension. You may also want to include other measures such as original premium amount and premium change amount as well as other dimensions as appropriate.

I was thinking hard about what you mentioned about junk dimensions here...

I have to report based on the following.

CurrentBodilyInjuryRank (High BI, Low BI, Medium BI),
PriorBodilyInjuryRank,
credit class,
Vehicles added for NEW policy for this month,
Vehicles added for EXISTING policy for this month,
Under aged driver count for this policy for this month,
Total drivers for this month.
Policy Status such as (new business, active, amended, new-business/amended, cancelled, reinstated, cancelled/reinstated etc....)

Are you suggesting I should create a junk dimension consisting of all the above?

Thank you,

Raghu.

raghuk

Posts : 8
Join date : 2009-06-16

View user profile

Back to top Go down

Re: Header Level Dimension for a Fact Table

Post  ngalemmo on Mon Aug 03, 2009 12:34 pm

Maybe, maybe not. I can't say for sure without understanding the nature of the data and the business objectives. But, it is something worth considering.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Header Level Dimension for a Fact Table

Post  Sponsored content


Sponsored content


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