Point in time balances

[gymbob]

Hi, I am pretty new to Analysis Services and I have come across a small problem to do with point in time balances, I guess that this is similar shiver72's post titled 'Date Range Problem'. I am not really after a solution as such (although that would be great :-), more interested in a pointer to some resource(s) which I can read up on myself.

Here is the scenario:

A student enrols into a course that has a start and finish date. This enrolment has a certain value based upon its length, the course type etc which means that each
enrolment's 'value' can vary at any time between the start and finish dates.

For example a student may enrol in a full year basket weaving course with a value of 0.0500, they then enrol in a part year course titled 'Dimensional Modelling 101' with a value of 1.000

...and their enrolments would look like this...

01-JAN-2005 31-DEC-2005 0.0500
02-JAN-2005 15-JUN-2005 1.0000

The client wishes to know at any point in time the 'value' of their enrolments. These values can vary due to a student starting or finishing their enrolment, withdrawing, having their
enrolment suspended etc, etc

Now, in the Data Warehouse Toolkit it mentions point in time balances in the 'Financial Services' section but it uses SQL to prove the point which is no use to me because I am using MS Analysis Services to dynamically create the result.

As it says in the book its no use creating a row in the fact table to represent the value for each day that the enrolment is in effect because with 400,000 enrolments that works out to over 140 million rows. The alternative is to create one row for each occurence of the variation in the value of the enrolment and then the facts would be completely additive and useful.

I would be looking at using something similar to the following:

fact table
----------
fkdate
value
fksudent
fkcourse
<possibly some degenerate measure like the UID of the course enrolment held in the source system>

dimensions
----------
Student - and of course a whole heap of related dimensions like gender, age, ethnicity etc
Course

For example given the above information the fact table would look like this:

(Student UID is 2005123, course UIDs are 1000 and 1001)

20050101 0.0500 2005123 1000
20050102 1.0000 2005123 1001
20050615 -1.0000 2005123 1001
20051231 -0.0500 2005123 1000

That way I can run a query at, say 15-JUN-2005 and sum the values and it will come out with the correct balance - and yes I know that this idea is straight out of the book :-)

OK, if you've read to this point then I take it that you are an extremely patient person and therefore will forgive my next question.

My question is this, my clients use simple tools like Excel pivot tables to gouge the information they want out of the warehouse, sure, I can write an MDX query to get a point in time balance (just as soon as I learn a bit more about it) but is there _any_ way/means/algorithm/trick/way to hold your head to one side, that I can use so that they can still just 'drag and drop' using the pivot table service as opposed to having to me having to create an MDX-based report for them ?

cheers

[gymbob]

Hi all; so, I went ahead and did what I described above, I now have a decent fact table and a calculated member which looks something like this:

SUM(
YTD(),
[Measures].[MyValue])

and as long as you don't expect too much from it it seems to work reasonably well. The main problem of course is that the member depends upon having data available on the day in question (because I only include days in my calendar where there is a corresponding date in at least one of my fact tables).

So, for example if I want to have a report that compares the ytd figures for 03-Mar-2005 and 03-Mar-2004 there is no problem - just as long as there is some sort of data on both dates.

Although it goes against the grain (pun intended) I guess I may have to include dates in my calendar where there is not necessarily any associated fact data - or even better create a separate calendar so that I now have two, one for associating directly with my fact tables and one for handling the case where the calculation itself depends upon being able to calculate to the date in question.

[gymbob]

Hi all, discovered quite early on in the piece that the YTD function wasn't going to give me exactly what I required as it reset the on change of each level (in this case I only have month and year levels in my dimension) so that at the start of each month or year I would be back to zero. After a few minutes thought I went with this option where Calendar Date is each date in my dimension, Calendar Year is the year and so on...

iif([Date].CurrentMember.Level.Name="Calendar Date",
Sum({OpeningPeriod():
[Date].CurrentMember.PrevMember},[Measures].[MyValue])
,
Sum({OpeningPeriod([Date].[Calendar Year]):
[Date].CurrentMember.PrevMember},[Measures].[MyValue])
)

This gets around the problem of not actually having a particular date in my dimension and also handles the issue of having different aggregation requirements for the different levels. I tried to use a custom rollup to give me a more maintainable approach but couldn't get it to work.