Dimensions or measurements?

View previous topic View next topic Go down

Dimensions or measurements?

Post  S_Ana on Tue Apr 29, 2014 6:08 am

Hi there,

I am designing life insurance model for Group Schemes (funds created for employees that belong to a company) and for retail business (individual policies).
I have read Kimball's chapter on insurance and a number of articles from this forum related to insurance.

First, I am trying to use same model for both, Group and Retail business (they follow similar business processes) and on source system they share the same tables.
Second, data volumes on policy will be big (because of retail component) and I am trying to avoid physical policy dimension (instead having degenerative policy dimension in the fact tables).

When a schema is created, policy is created (one policy per schema) and following attributes defined for a policy:
Free_cover_limit = (If reinsurance > Free_cover_limit then underwriting takes place, otherwise not).
Multiple = This is integer used on some products to determine sum_assured (i.e. multiple * annual_salary)
Emplyee_contribution_percentage = Percentage of the premium employee contributes
Unit_rate_per_million
Premium_Payment_Frequency (could be monthly, quarterly, annually, single...)
Waiting_period = Waiting period before policy owner can claim
Term - for how long a policy has been taken
...

Premium_Payment_Frequency is definitely a dimension.

Free_cover_limit , Multiple, Emplyee_contribution_percentage , Unit_rate_per_million - Are these measurements? I have stored them on Fct_Policy_Issue (grain is policy) as measurements, but I am confused if these are strictly measurements or can be considered as dimensions?

What about the Term and Waiting period, are they also dimensions or it depends how it will be used?

Another question, it doesn't sound right to store member_annual_salary in this table (currently we cannot distinguish if a member has 2 or more policies, it is 1:1 between member and policy, but in the future it might change). So, I should have another fact table on member level to store member_annual_salary?


TIA

Regards, S.

S_Ana

Posts : 1
Join date : 2013-10-17

View user profile

Back to top Go down

Re: Dimensions or measurements?

Post  nick_white on Tue Apr 29, 2014 1:43 pm

The decision whether something is a measure or an attribute can be tricky but I always start with the question "is it meaningful to aggregate this value?". If the answer is no - summing, averaging/etc. this value makes no sense - then it is almost certainly an attribute and belongs in a dimension.
Obviously there are always exceptions to this rule but I find this is a good starting point.
Looking at the measures/attributes you quote, I would ask does anyone actually want to report on them or are they just used as elements in a calculation? Possibly you could use them to calculate measures that are of interest (and that you hold in a fact table) and not actually hold this measures/attributes at all? For example, use Emplyee_contribution_percentage to calculate measures for Employee_Contributed_Amt and Employer_Contributed_Amt

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

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