Hi folks,

I'm building a data warehouse for an insurance company and need to build a periodic snapshot so that I can provide measures such as [policies in force], [premium to date] and [premium earned].

I'm looking for best practice for maintaining the table. Transactions will have a transaction date and an effective date. The most important date for my purposes is the effective date. This means that transactions may impact previously inserted rows in the snapshot.

At this point, I'm looking inserting my new rows to the transaction grained fact table and then cursor based processing along the lines of;

for each distinct policy in the freshly staged data
__delete from snapshot table where policy = policy
__@cummulative_premium = 0
__@premium_earned = 0
__for each transaction in transaction fact table by effective date
____@cummulative_premium = @cummulative_premium + premium
____@premium_earned = premium/datediff(day,expiry_date,_effective_date)
____do while @date< [effective date for next transaction] or [expiry_date]
______insert into snapshot table
____loop
__next
next

What is considered best practice for maintaining periodic snapshots? Is there a set based solution that would be a better alternative to a cursor solution.