none
Work shown as FTE value RRS feed

  • Question

  • Hi, I need to show work calculated as FTE values instead of just hours per month.   Can I use a MDX expression in the OLAP cubes to calculate this eg Work/160 where 160 is the number of hours per month a FTE can work?  I have tried this already but I am not sure which cube to use?

    Thanks Steve

    Monday, April 20, 2015 11:50 AM

All replies

  • Hi Steve,

    Since the smallest time period in the OLAP cubes is the day, I'd rather use work/8 (if you have 8hrs a day).

    I'd sugges to use one of the timephased cubes: assignment timephased and MSP POrtfolio Analyzer (virtual cube).


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Monday, April 20, 2015 11:56 AM
    Moderator
  • thanks Guillaume, I have made the expression - "[work]/8"  is this correct?

    After running the cube build, which EPM view would I be able to see the measure in?

    thanks again

    Steve

    Monday, April 20, 2015 12:07 PM
  • Exactly Steve,

    I used to do the same with PS2010. Enter a name like Work FTE with the formula [work]/8. Replicate the procedure for all the appropriate timephased cubes. After building the cube, you'll have the additional data in the given cubes, accessible from the BI Center, reports. You should have 1 excel report per cubes, meaning 14 excel reports. You'll have also the data connections (ODC files) updated accordingly.

    See references below:


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |


    Monday, April 20, 2015 12:16 PM
    Moderator
  • Hi Guillaume, I have just realised that what I actually need is [work]/[capacity] but I cannot find Capacity, which cube is that in do you know?

    thanks

    Steve

    Tuesday, April 21, 2015 9:11 AM
  • Hi Steve,

    As per this link, the capacity should be available for creating a MDX measure.

    http://carl-projectserver.blogspot.fr/2011/06/return-of-data-geek-epm-olap-cube.html

    The process is to create the new measure and build the cube. You do not have (unfortunately) and validation after creating your formula which could tell you that it actually found the capacity. You have to build the cube and check the result.

    You'll find the capacity in the Resource Timephased cube:


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 9:27 AM
    Moderator
  • thanks, I have found Capacity in the Resource timephased cube but when I add it to a pivot chart and refresh the data there is no data coming through??  Not sure if I have missed something fundamental??

    Steve

    Tuesday, April 21, 2015 9:52 AM
  • Did you type [work]/[capacity]? Has the cube correctly been built?

    Note that you might have to write it in a different language depending on your instance base language.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 9:59 AM
    Moderator
  • Hi, I have just added the below measure to the Resource timephased cube , rebuilt the cube successfully and then added the measure to my pivot chart, again - no data??

    Tuesday, April 21, 2015 10:15 AM
  • Try with base capacity instead of capacity. Also check if this is the correct language.

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 10:22 AM
    Moderator
  • I have used base capacity and still it will not work Guillaume, interestingly when I attempt to insert base capacity on its own into the pivot table there is no data coming from that either!

    Tuesday, April 21, 2015 10:29 AM
  • Ok let's try another thing. The capacity is calculated based on a set of parameters in the server settings, Additional Server Settings. This allows you defining the time frame for the capacity calculation. Verify that you have correct parameters.

    Also try to check if the capacity is correctly calculated in the resource cube. Check also the work. The goal is to check if the issue comes from the capacity itself or from the mdx formula.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |


    Tuesday, April 21, 2015 10:48 AM
    Moderator
  • HI, the 1 and 12 months setting is as per your screenshot.  Work and Actual work are coming through ok
    Tuesday, April 21, 2015 10:59 AM
  • Thus you have to be aware that the capacity data will only be calculated within this timeframe. If you look outside it, you'll have no capacity returned.

    That's fine for work and actual. Is the capacity returning something in the defined timeframe? I mean not the mdx formula but the out of the box capacity measure.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 11:03 AM
    Moderator
  • Hi, no it is not returning anything in the timeframe ie 2015 Q2. Neither is Base Capacity returning any data. when I view via the out of the box resource availability webpage in PWA capacity is showing ok
    Tuesday, April 21, 2015 11:12 AM
  • I must say that I'm out of idea... At least we pointed out that the mdx might be ok and that the issue is related with the capacity not being calculated in the OLAP cubes.

    You might try to check with the BI Center sample report on the Reporting DB if the capacity is correct in the Reportin DB. But since it is ok in the resource availability PWA page, the Reporting DB should be ok.

    Maybe you could try to create a brand new OLAP DB..


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 11:29 AM
    Moderator
  • thanks for trying Guillaume,  cheers  Steve
    Tuesday, April 21, 2015 11:37 AM
  • just noticed that Capacity and Base capacity is coming through after all but is tailing off in the current quarter onwards, what actually determines capacity?  I mean I have circa 150 resources in the pool and they are all available and not on holiday for the remainder of the year! ??


    Tuesday, April 21, 2015 12:02 PM
  • Strange, it looks like the capacity server settings (1-, +12 months) but you already checked this setting...

    The base capacity is calculated based on the resource calendar. Then the capacity is the base capacity minus the resource calendar exceptions, such as holidays and non-working administrative time from timesheet. Meaning the capacity is equal or lower than the base capacity.

    Have you check the cube settings such as the departments (only a few resources might be considered), the cube date range...

    See this article for checking:

    https://technet.microsoft.com/en-us/library/ff465342.aspx


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 12:16 PM
    Moderator
  • Hi Guillaume, completely puzzled here, cannot find the problem, no department filters are in force.

    rgds

    Steve

    Tuesday, April 21, 2015 2:11 PM
  • Hi, no it is not returning anything in the timeframe ie 2015 Q2. Neither is Base Capacity returning any data. when I view via the out of the box resource availability webpage in PWA capacity is showing ok

    Can you check this again out of the Q1 period? I'm wondering if the reporting DB is ok. If you see the same thing in the OLAP and reporting DB (capacity only for Q1) then the cube is ok and it could be a reporting DB sync issue.

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, April 21, 2015 2:17 PM
    Moderator
  • I have done some more research on this and apparently a sync job sometimes gets missed that deals with the capacity time range?

    Thanks

    Steve

    Wednesday, April 22, 2015 3:05 PM
  • So can you see the capacity in the cube now?

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, April 22, 2015 3:08 PM
    Moderator