Aggregate dated events in Analysis Services OLAP Cube

Question

I have a table with columns that store dates of various events, like so:

<pre>
PersonID  DatePassedExam1 DatePassedExam2
   1      NULL            NULL
   2      01-11-2012      NULL
   3      01-12-2012      10-12-2012
</pre>    

I want to build a cube to see counts of people who have passed exam1 and exam2 by person attributes and time, e.g. year to date.

So,

YTD for Oct2012: exam1count=0, exam2count=0  
YTD for Nov2012: exam1count=1, exam2count=0  
YTD for Dec2012: exam1count=2, exam2count=1  

I'm guessing this needs semi-additive aggregation?

I can't make changes in the database (without difficulty) and am not using Enterprise edition.

Any advice gratefully received.

Thanks, Dal

Answer 1

I would unpivot your table to pass the columns: PersonID , Exam , DatePassed. Your sample data would result in 1 row for PersonID 2 and 2 rows for PersonID 3.

I would then create an Exam dimension and link it.

Then I would create the measure as a Distinct Count of PersonID.