Hi all.

We're working on a Timekeeping cube (SSAS 2005) that provides KPIs such as
Utilization and Chargeability. Chargeability is defined as 'Total hours
worked' / 'Available hours' where available hours is the number of business
hours per time slice.

For the individual resource, the measure calculates fine since a normal
business week is 40 hours. What I think I need to do is to determine the
number of resources and use it as a factor for the number of hours. As an
example, if I have 10 resources, for any given day the 'Available Hours'
would be 80, for the week, 400, etc etc. I've tried taking my resource
dimension and creating a new measure group from it to yield the 'Resource
Count' measure and I've even tried changing it to a DistinctCount
aggregation, but to no avail. As the time slices roll up, the numbers are
overstated.

I'd like to know the best practice way to do this as I see it being a common
problem to solve.

Thanks.

-Tim

OK. Figured it out.

Have to take DistinctCount of whatever the key is you're looking for in the
fact table.

I needed resource, so created a measure based on the key called resource
count then set its aggregation to DistinctCount. Works like a charm.


"Tim Dot NoSpam" <Tim.NoSpam (AT) hughes (DOT) net> wrote