Data Analysis Reporting on #FTE Generic vs. #FTE Specific assigned byQuarter

[AaronD]

Greetings,

I'm looking to be able to generate a graph comparing
- the number of FTE Generic Resources assigned to all tasks (200%
Generic = 2FTE)
- the number of FTE non-Generic Resources assigned to all tasks

Using the default MSP_Portfolio_Analyzer cube I can easily get the
above information, but only in terms of Work (hours in this case), not
FTE.

The conversion from Work to FTE would be easy, however, no cube
provides 'Duration'.

One possibly way for overcoming this is to calculate the average
number of hours in a quarter (since we're reporting by quarter) and
divide the work value by this number to get FTE.

The (possible Major) issue with this is that any task that starts or
ends (or both) mid-quarter would seem to have an inaccurate value
reported for that quarter (not to mention that there are different
#hrs in different quarters).

Any suggestions for better ways to create this graph (FTE Y axis,
Quarters X axis, showing two bars per quarter - one for Generic FTE,
one for non-Generic FTE.)

The background/desire is that Generics would be used to track
unfulfilled demand... thus a quarter with 15 FTE Generic remaining
indicates that 15 people will eventually need to be allocated to meet
the demand of all the tasks (this could be further broken down by
specialty, etc... but it gives upper-level management a good idea of
how the department is doing capacity/demand wise)

Thanks!
Aaron

 

[Ben Howard]

Hi AaronD,

My guess is that you are nearly there. You need to drop the time dimension
into the column field, and then select (and drill down) into the two quarters
you need. Assuming you have the rest of the info, this will get you going.
As you are effectively looking at work by quarter by resource (generic and
real) the issue of a task splitting over quarters doesn't exist, the work
will be represented in both quarters (assuming it starts in one quarter and
ends in another).

 

[AaronD]

Hi Ben,

Thanks for your response. Let me try and explain a little better after
thinking about this some more.

I've been able to use time as a dimension (so things are broken down
by quarter).
Also, as you suggested, having a task start or end mid-quarter isn't
the issue I thought it would be, since logically, you'd want a 5FTE
task that ends half-way through a quarter to show up as 2.5FTE for
that quarter.

The issue that remains is: How do I translate Work (which seems to be
my only available related measure) into FTE, when it appears that I
can't get duration or units out of any of the cubes.

As it is, I have the graph exactly as I want it, save for the fact
that instead of 5 FTE for a quarter, it shows up as something like
2640hrs... I need to convert that 2640 into 5FTE.

I could divide the work by hours-in-a-quarter, however, this would
limit the view to always be quarters (what if somebody wanted to drill
down into months?)
Another problem with this approach is that some quarters have 520hrs,
some 528, and some 512hrs - so calculations would be off unless I
could somehow get the number of hours in each particular quarter.

Is there a way to retrieve the number of hours in whatever is being
used as the dimension in the column field? (e.g., it would return 512,
520 or 528 for the correct quarters when viewing quarters, and would
also return the correct number of hours if the view were changed to be
by months instead)

Thanks again,
Aaron

 

[Gary Chefetz]

Aaron:

Have you considered creating a calculated measure or measures for the cube
or view? You can do this either in the view itself or, better still, through
the Project Server interface.

 

[AaronD]

Hi Gary,

Have you considered creating a calculated measure or measures for the cube
or view? You can do this either in the view itself or, better still, through
the Project Server interface.

I would love to be able to create a calculated measure for this (and
it seems I would need to). The issue is that I can't find the other
half of the equation (duration) necessary to perform the calculation.

Ideally, I'd be able to get the [number of work units contained in the
current time dimension] to use as the division for [work], which would
then return #FTE.

Do you know of a way to retrieve this? (e.g. for quarters, it would be
the number of hours in each quarter, same for months if the view were
changed, etc...)

Thanks again,
Aaron

 

[Ben Howard]

Hi Aaron,
You don't need duration. The OLAP cube holds values of work in days.
Consider the following 3 scenarios.

1 week duration task allocated at 100% to one person = 5 days work, and will
give 8 hours per day.
1 week duration task allocated at 50% to one person - 2.5 days work, giving
4 hours per day.
1 week duration task allocated at 200% to one person - 10 days work, giving
16 hours per day.

If you set this up in a plan, then in the OLAP cube, for a single day you
would see for each day a total of 16+4+8 =28 hours work. For a week you
would see 140 hours work.

You need to calc this into FTEs, well assuming you work 8 hours per day,
then the calculated measure you need to create in the cube configuration
settings is called FTE and the calc is [work]/8. You need to do this for the
MSP_Portfolio_Analyser cube. Then rebuild the OLAP cube.

With the scenario above, for each day you'll need 28/8 = 3.5 FTES, and for
the week, 140/8*5 = 3.5 also. Whichever way you cut it it works. The
quarters work themselves out too.
--
Thanks, Ben.

Please rate this post if it helped.
http://appleparkltd.spaces.live.com/

Hi Gary,

Have you considered creating a calculated measure or measures for the cube
or view? You can do this either in the view itself or, better still, through
the Project Server interface.

I would love to be able to create a calculated measure for this (and
it seems I would need to). The issue is that I can't find the other
half of the equation (duration) necessary to perform the calculation.

Ideally, I'd be able to get the [number of work units contained in the
current time dimension] to use as the division for [work], which would
then return #FTE.

Do you know of a way to retrieve this? (e.g. for quarters, it would be
the number of hours in each quarter, same for months if the view were
changed, etc...)

Thanks again,
Aaron

 

[AaronD]

Hi Ben,

You need to calc this into FTEs, well assuming you work 8 hours per day,
then the calculated measure you need to create in the cube configuration
settings is calledFTEand the calc is [work]/8.  You need to do this forthe
MSP_Portfolio_Analyser cube.  Then rebuild the OLAP cube.

With the scenario above, for each day you'll need 28/8 = 3.5 FTES, and for
the week, 140/8*5 = 3.5 also.  Whichever way you cut it it works.  The
quarters work themselves out too.

I just tried out your suggestion and could not make it work. I now
have two fields:
FTE8Hrs: [Measures].[Work] / 8
which is as you suggested

FTE: [Measures].[Work] / 520
Which is the average number of hours in a quarter

FTE displays the correct result whereas your suggested approach
displays in the hundreds (for a resource pool of ~15 FTE)

It appears as though the view takes the entire sum of all work in the
view, and then divides it by the number provided (8 or 520), rather
than taking the work in a day, dividing by the number (8 or 520) and
then summing this result.

Is there something I'm doing incorrectly in the view? Some way to
force it to behave as you suggest?

Thanks again,
Aaron

 

[Ben Howard]

Hi Aaron, email me directly with screen shots of what you are trying to do.
You'll get my address off www.applepark.co.uk
--
Thanks, Ben.

Please rate this post if it helped.
http://appleparkltd.spaces.live.com/

Hi Ben,

You need to calc this into FTEs, well assuming you work 8 hours per day,
then the calculated measure you need to create in the cube configuration
settings is calledFTEand the calc is [work]/8. You need to do this for the
MSP_Portfolio_Analyser cube. Then rebuild the OLAP cube.

With the scenario above, for each day you'll need 28/8 = 3.5 FTES, and for
the week, 140/8*5 = 3.5 also. Whichever way you cut it it works. The
quarters work themselves out too.

I just tried out your suggestion and could not make it work. I now
have two fields:
FTE8Hrs: [Measures].[Work] / 8
which is as you suggested

FTE: [Measures].[Work] / 520
Which is the average number of hours in a quarter

FTE displays the correct result whereas your suggested approach
displays in the hundreds (for a resource pool of ~15 FTE)

It appears as though the view takes the entire sum of all work in the
view, and then divides it by the number provided (8 or 520), rather
than taking the work in a day, dividing by the number (8 or 520) and
then summing this result.

Is there something I'm doing incorrectly in the view? Some way to
force it to behave as you suggest?

Thanks again,
Aaron