Hello All

I am trying to calculate the number of times a student has taken a
test.

The fact table is as below:

stuID testsubject testdate score pass_status
1 math 4/2/2002 10 fail
1 math 7/1/2002 11 fail
1 math 2/2/2003 12 fail
1 reading 2/3/2003 9 fail
1 math 1/1/2004 20 fail
1 reading 1/2/2004 10 fail
1 writing 1/4/2004 3 fail
1 math 4/1/2004 21 fail
1 reading 4/2/2004 11 fail
1 math 7/1/2004 22 fail
1 reading 7/2/2004 12 fail
1 math 2/1/2005 25 pass
1 reading 2/2/2005 13 fail
1 reading 4/1/2005 15 pass

what i'd like to do is to have a running (cumulative) total as a
calculated member that would sum up the number of times a student has
taken any of the subjects. that is if i select math as the subject at
some date it will tell me that student 1 had taken math n times. The
fact table contains data further back in time than shown here. So if a
student just can't seem to be able to pass (poor soul) just as I can't
seem to figure this out, his cumulative total will be quite high till
he/she passes. A smart student would only need to sit for each subject
once to pass.

The cube currently uses the stuID, score as a measures (count and sum
respectively), testsubject and pass_status as regular dimensions and
testdate (time) dimension (year, month).

Any help and suggestions for better improving the design etc will be
very much appreciated.

psmith

Hi Psmith,

This design will work but only after you did a crossjoin of the StudentID,
Subject and then slice by date. Then you will get the value for the count
measure that you are looking for. But if you look at just the students you
will get the count of the total number of tests take by that student.

What are you seeing?

"psmith28" <kwaku_duro (AT) hotmail (DOT) com> wrote

Hello David

thanks for the response. i was doing the distinct count to get the
total number of students. little did i realise that just the count
would also yield the number of times a student had taken the tests.
can you also write the crossjoin that would yield the count ie if you
can spare the time. i'd like to see that.

Psmith
David Botzenhart wrote: