Calculating Totals and Date Durations

[tim_goodsell]

Hi

We are creating an insurance data warehouse which is very similar to what is in the toolkit book. This warehouse has a claims accumulating snapshot fact table containing the latest status of a claim and also a monthly periodic snapshot table containing the status of a claim at the end of the month. The monthly periodic table contains multiple flags concerning claim received date, claim event date, claim closed date etc (all date keys)

We intend to use SSAS and SSRS to produce reports containing info concerning the number of new claims received during a month (by claim type) and the average duration of a claim (also by claim type at the end of the month.

My question is whether i need to add attributes to the periodic monthly snapshot table to indicate that the claim was new during the month (flag field = 0 or 1) and a column to indicate the number of days the claim has been open (claim closed date or end of month date - claim received date) before creating the SSAS cube or build all the calculations in SSAS.

Regards

Tim

[ngalemmo]

Flags don't hurt if it makes things easier. And duration certainly helps simplify queries. You may also want to include an open flag for claims that have not closed as of the end of the month.