Here is a rundown of the problem that I am facing with this insurance
data.

Essentially, for all of my data there is one view that they are using
called Premium_Risk_Debit_View. This makes it pretty easy for me since
I can just use this view for basically my fact table. Within this
table there are 2 dates that are important. They are called
Cover_Start_Date and Cover_End_Date. What these are is when the policy
line (each record is a line) starts coverage, and when it ends
coverage.

Now along with various other columns that I can use to create
dimensions there is one primary column needed for all of my measures.
This column is called Written_Amt. This column in and of itself is for
Written Premium. Meaning, what the premium will be for this entire
policy line item. So if it starts coverage on Jan 1 and ends on Apr 1,
the entire written premium is $500. Pretty straight forward and I can
aggregate as normal.

Here is where the big problems and trickiness comes into play. There
are also 2 other measures that we need to see called Earned Premium and
Unearned Premium. I will give you a definition for Earned Premium
because if I get one I will get the other.

Earned Premium - This is the amount of premium we have earned for this
policy line. So, if the policy started on Jan 1 and today is March 2,
this number represents what percentage of the total written premium we
have earned. This is calculated by this formula:

Earned Premium = (Written Premium / Total
Days in Premium) * Range of Days Selected


In their current structure the user gives a range of dates. So if they
want to know what was the total Earned Premium for February, they would
just give the range of February 1 to February 28. So if we were to
throw this in the calculation it would look like this:

Earned Premium = ($100 / 90 Days) * 28

Now the kicker to all of this is that when they ask for the Earned
Premium for a range they mean ALL the earned premium. Not just the
Earned Premium for Policy Lines that have a Cover Start Date and a
Cover End Date in the range. So if I select my range as February ALL
policy lines that are current in that range get included. Meaning if a
policy line's start date is in or before the range it gets included,
and if its end date is in or after of the range it gets included.

Looking at this from a cube perspective, that means I can't really use
any kind of Time dimension based off the Cover State Date and/or Cover
End Date. Because, if I choose Cover Start Date, then when I select
that as my Time member it will exclude all the records that don't have
a Cover Start Date in that range, and that is wrong. I need those
records that are before as long as their Cover End Date is within the
range, or outside it on the far end.

So without getting into anything more confusing, I basically have no
column in the view to use for a time dimension. Also, I have to
provide a mechanism for them to choose a range for their queries.
Meaning, that they may want to see the Earned Premium for January 1
through January 15. Thus, making the possible need for 2 time
dimensions.

I am not really afraid of the date range issue. Reason being that I
think I can create a Web App that will just allow the user to enter in
a range, and I can do my MDX on the fly. Creating sets and members as
needed. My problem is that I am envisioning a cube with no time
dimension at all, and me just doing everything behind the scenes.

So, that is pretty much the long overview of what I am facing right
now. If anyone has any thoughts, insight, whatever please post. If
nothing else I would like to get some discussion going on it to give me
things I haven't thought about. Really appreciate everyone's time.

V

Hi Voorshwa,

You can add 2 time dimensions to allow the user to select Range Start
and End dates, as described in this earlier post. With AS 2005, a single
time dimension can be reused in multiple roles, which need not be used
in measure groups:

http://groups.google.co.uk/group/mic...r.olap/msg/275
64ddc49de36a1?hl=en&

From: Chris Webb
Date: Thurs, Feb 6 2003 8:13 am
Groups: microsoft.public.sqlserver.olap

Rather than let the user enter a date manually, you might want to do the
following instead: create two Time dimensions in your cube, one where
the
user can select the start of the time range and one where they select
the
end of the time range. You can then use calculated measures to return
the
aggregated values. Here's how you do this:

In your existing cube, call your Time dimension something like 'Start
Date'.
Then copy this dimension, and paste it into the same database; you will
get
a dialog asking you for a new name for this dimension, and you should
call
it something like 'End Date'.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for replying, Deepak.

However, that won't solve my problem because it will only include those
records that have a Cover Start and a Cover End in the range I choose.
I need to also include those records that lie outside the range. It's
a pretty complex problem, but I believe I figured out a way to do it.

I essentially decided I was thinking of the problem in the wrong way.
Instead of trying to create some kind of range and then doing the
calculations on the fly, I decided to make the data into a bit more of
a summary. Basically, I created a summary fact table that calculated
all of the Earned Premium on a certain day for all policy lines. This
is what they are doing now, and there really is no need to go down to
the individual policy line. Thus, I can give them the summary info
they need and everyone is happy.

Again, thanks for your reply and hopefully I will continue on this
forward path, rather than the backwards one I was on.

V

Deepak Puri wrote: