Greetings All,

I'm new to Data warehousing and MS OLAP therefore I'm not sure about
the terminology. I have run into a problem with the frist Cube I'have
started on. Therefore my Cube itself maybe incorrect.

I have to build a cube to analyse the number of Employee in an
organiztion. There are several dimensions and the employees status
could change regularly. I populate the fact table on a weekly basis.

For example lets say on the 7th of May there are 5 employees,
employeeId from 1 to 5. Employee 1,2 and 3 are tagged as deployable on
that day. When we take the data for the 14th of May, 1,2 have become
not deployable and 4 and have become deplayable. If I view the data per
day that counts are correct. But if I take view the data for the month
of May the total of deployable Employees would be shown as 5, where as
I want the picture for the last day for that month.

Is it possible with MDX to get a measure from the previous level to the
next level?

Thanks in Advance,
Darshan

Try creating a measure that aggregates as Distinct Count.

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

"Darshan" <quasidarsh (AT) yahoo (DOT) com> wrote

Hi All,

I tried the distinct count but didn't have much luck with it, Since the
DW is tracking emplyee status changes the cumilative distinct count for
a period is not what we needed but the values of the last day or week
or month.

I was able to solve the problem by setting the custom rollup formula of
the Time dimension to Time.CurrentMember.LastChild. Now the cube is
behaving the way we wanted.

Thanks for your Help.

Kind regards,
Darshan