none
How do I get a Resource's Proposed Time to show in the OLAP reports? RRS feed

  • Question

  • I'm having an issue trying to gather/show a Resource's Proposed Time in the OLAPPortfolioAnalyzer OOTB report.  When looking into the data in Excel, the groupings show Committed, but part of that Committed data is actually Resource information that I know is Proposed.  In a nutshell, the line item appears as if it's time in the Committed bucket, when in fact it's supposed to be Proposed.

    Is there a way to show Proposed in this same report, in addition to Committed?  Basically showing a Resource's time grouped by Booking Type?  Or is that level of detail not possible here?

    I'm pulling out my hair here so any help'd be greatly appreciated.

    Thanks! - M


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Monday, December 16, 2013 3:36 PM

All replies

  • Hi Michael,

    Have the project been correctly published? and your OLAP cubes correctly processed?

    Adding the "booking type" in the line section of the pivot table should show proposed and commited timephased assignments.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Monday, December 16, 2013 3:54 PM
    Moderator
  • From what I can tell, they are.

    When constructing the report, the time shown for each of the Resources on the row is under the Committed group.  When I go in to the same project directly, and go into Build Team, those Resources are designated Proposed.

    So, the report from the OLAP data doesn't seem to distinguish Committed vs. Proposed.

    Am I missing something?

    Links to screens...

    - Constructed report in Excel based on OLAPPortfolioAnalyzer, all resources show Committed (http://sdrv.ms/1hWZVYv)

    - Build Team page, Resources for the same Project are designated Proposed (http://sdrv.ms/18R8VsT)

    Thanks - M


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog


    Monday, December 16, 2013 4:45 PM
  • Hi Michael, based on the screenshots, it seems like you did it right and that the OLAPPortfolioAnalyzer cube doesn't distinguish proposed versus committed work.

    Can you double check in the resource usage view, inserting the booking type column, that the assignment themshelves are proposed? I'm pretty sure they are, but so far this is my only guess..

    Then I'd advice you to use SQL queries in the Reporting DB to check your assignments booking types on the given projects, in order to precisely spot the issue.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Monday, December 16, 2013 4:56 PM
    Moderator
  • Have you changed the booking type from committed to proposed in recent time for resource. If this is the case then you rebuild your olap cube manually or wait for the for next rebuild. 

    I am asking this question because we all know that olap reports never give us current value as project server build olap cube as per defined time interval.


    kirtesh

    Monday, December 16, 2013 5:17 PM
  • Guillaume, selecting the Project from Project Center, then selecting Schedule on the left nav, then changing the view to Resources Summary displays the resources w/the Booking Type column. For all the identified resources they have Proposed. Given this, I'd assume that the OLAP cube build process doesn't seem to be bringing that information over.

    Can you direct me to the relevant tables in the ProjectServer_Reporting database for me to make sure that the Booking Type has the correct information, prior to cube build?

    Thanks! - M


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Monday, December 16, 2013 10:05 PM
  • The cube was built after that information was set.

    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Monday, December 16, 2013 10:06 PM
  • Hi Michael,

    Unfortunately, I'm not a developer so I'll not be able to help you in term of querying.

    But you could download the Project Server 2010 SDK (assuming we are talking about PS2010 version). The SDK proposes the complete definition of tables, views and fields of the Reporting Database. I'm guessing that you'll find Proposed versus committed assignments in a view that should be something like "MSP assignment userview".

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, December 17, 2013 1:28 PM
    Moderator
  • An interesting piece of information that I copied below that came from the online help files (http://office.microsoft.com/en-us/project-help/booking-type-fields-HP001043690.aspx)

    You assign proposed resources the same as committed resources. However, if you are using Project Server, you cannot publish assignments for proposed resources to Project Web Access. Note that work and remaining work values include proposed bookings as well as committed bookings.

    Does the above mean that the ability to differentiate Proposed v. Committed Resource information is *not* available in the OOTB OLAP cubes?

    Thanks! - M


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Tuesday, December 17, 2013 2:57 PM
  • Hi Michael,

    Actually you're right on this point : proposed assignment are not published to the resources in PWA, so they can't update any of them (enter actual work).

    But above the publication to the resources, proposed and committed assignments must anyway be differentiated in the OLAP cubes.

    Were you able to query the Reporting DB to check if the proposed assignments were actually set as "proposed"?


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, December 17, 2013 3:07 PM
    Moderator
  • I'm in the process (or, my colleague is) of checking out the data directly in ProjectServer_Reporting.

    Question: Aren't the OLAP cubes constructed from the information in _Reporting?  And, per the above statement, if they're not published (going into _Published), they won't be in _Reporting, for the cube build process to grab?


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Tuesday, December 17, 2013 4:02 PM
  • As far as I know (I can't test it right now), the proposed assignments are published in the publish DB (so in the reporting DB), it is just that they are not published to the PWA "my timesheet" and "my task" of the resources.

    Here is an excellent MPUG article: http://www.mpug.com/articles/how-to-remove-completed-and-cancelled-projects-and-tasks-from-timesheets-in-project-server/

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, December 17, 2013 4:19 PM
    Moderator
  • Thanks for the links, but so far I can't seem to get information in the OLAP cubes that differentiate time for Committed v. Booked Resources.

    Anyone else have any ideas?

    Thanks! - M


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Wednesday, December 18, 2013 4:25 PM
  • So, an interesting update I got from MS support...

    I opened up a ticket w/them cause I just couldn't get the info to show, but they were unable to duplicate the behavior I was seeing in their environment.  So, I packaged up my PS DBs (Archive, Draft, Published and Reporting) and sent them over to support.  Placing our DBs in their environment they were able to reproduce the issue we're seeing.

    After further research, support was able to identify that, if you Publish the project *through* Project Pro client, then the numbers, both Proposed and Committed, show as they should (meaning separate line items) in the OLAP reports.  We were able to verify that on our side: opened up Project Pro, published the project, rebuild the cubes, built the report, and saw line items for Committed and Proposed.

    However, not all folks that interface w/Project Server have a license for Project Pro, nor, as I understand it, is Project Pro a requirement to use Project Server.  So, support is digging deeper into this now, seeing if using Project Pro is the only way to get that information to surface (I hope not), or if there's some other issue there.

    Figured I'd update this as information comes in...I'll do so when I get a response from support.

    - M


    Michael Mukalian | Jan 2010 - Dec 2010 MVP SharePoint Services | MCTS: MOSS 2007 Configuration | http://www.mukalian.com/blog

    Monday, December 23, 2013 6:15 PM