Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimensions or measurements?

2 posters

Go down

Dimensions or measurements? Empty Dimensions or measurements?

Post  S_Ana 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
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?


Regards, S.


Posts : 1
Join date : 2013-10-17

Back to top Go down

Dimensions or measurements? Empty Re: Dimensions or measurements?

Post  nick_white 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


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

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum