Header Level Dimension for a Fact Table

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.

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.

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

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.

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.