I'm using AS2000.
I have a cube which has a measure called Gross Written Premium (GWP).
I want to calculate the Gross Earned Premium (GEP).
This is defined as: = GWP x ((Today's Date minus Effective Date)/(End
Date minus Effective Date))
So if the GWP is £730 & has a term of 365 days.
At the end of the term the GEP = GWP.
But one day into the term the GEP = GWP x (1/365) = £2, day two £4,&
so forth.
So each day the GEP changes.

The users don't need to have Effective & End Dates as dimensions.
The data does exist in the fact table though.
Is there a way to generate this measure without having to have
Effective & End Date dimensions?

Do I have to have them as dimensions to be able to create the
calculated member?
If this is the way to go what will the MDX look like?
Do I use the DateDiff() VBA fnction?
Will I need the CDate function?
Maybe:
GWP*(DateDiff("d",Date(),EffectiveDate.member)/(DateDiff("d",EndDate.member,EffectiveDate.member) )

Another alternative may be to calculate the GEP in the SQL fact table
every night.
Then the GEP would be a simple preaggreagted measure & perform much
quicker for the end user.
However the data volumes are not small; currently about 20 million rows
& growing by 15,000 a day.

Any help would be appreciated.

John

Hi John,

You didn't mention the retention of the data - as time goes by, are
records only retained for a fixed period? If not, users probably need
date dimensions to specify the date range within which they wish to
compute GWP and GEP, otherwise both will keep accruing over time.

There are 2 approaches to leveraging cube pre-aggregation that I can
think of:

- Create a GEP measure based on a SQL expression or view (the table
doesn't need to be physically updated), and reprocess the cube (or
active partitions) daily.

- If there are only a handful of possible policy durations (End -
Effective Date), GWP data could be organized by Effective Date and
Duration, and a factor applied to GWP for each active combination of
Effective Date and Duration.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***