Hi Both,

Thanks for your replies...I'll provide a little more detail based on your response.

Source system has the following tables:

Policy table (a record for each policy)

Policy risk (a record for each policy/risk type/premium class per each policy renewal)

Claim Table (a record for each policy/claim/risk type/premium class) - this table contains the total paid out and the total recovered.

Claim Recovery Table (a record for each policy/claim/risk type/premium class/treaty/reinsurance) - this table holds the recovered.

A policy can have 1 to many associated risk types

A policy can have 1 to many associated premium types

A policy can have 0 to many claims

A claim can have 0 to many recoveries

So I'll create a simple policy dimension containing a record for each policy.

I have then toyed with the idea then of creating a mini dimension(s) to store the rapidly changing risk and premium types as each policy could end up with hundred of records. (each policy appears an average of 47 times on the policy risk table). What are your views on this?

I want to have a single fact table at the lowest grain possible. However I am struggling with the total paid figure which is not available at the same grain as recovery.