Different Granularity Data - Life Insurance Model

[panunes]

Hi,

I'm currently developing a policy snapshot fact table for a life insurance company. I read the Kimball multidimensional guide, but I have a doubt regarding the fact table granularity.

Normally we store the data at the coverage level, but I have some issues regarding that. I have some policies that do not have coverages but are still active. In the OLTP system, some retirement investment plans products do not have the normal base coverage (death). This happens when the clients give money to the insurance company to be applied in a investment fund, like putting on a bank deposit, where the risk is zero. If the person dies, the money stays there for the beneficiaries.

Well, those policies need to be reported on the policies analysis, but they are not relevant for the coverage level analysis (for example reserves analysis, premium analysis...). I was wondering about the best way to handle this. I can see three options in here:

- Create two fact tables, one at coverage level and one at the policy level.
- Create a "Life" artificial coverage, to store those values (i don't like this option, because I'm creating something that does not exist in the OLTP system)
- Keep only one fact table and for those policies the coverage dimension surrogate key would be "-1 - Unknown"


Can someone help me on this?

Many Thanks,

Pedro Nunes