none
Analysis service cubes - Utilisation report, how to extract resource, team assignment information RRS feed

  • Question

  • Hi,

    I am using Excel 2013 and attached an external data source from analysis services OLAP cube that Project Server 2013 builds.

    The cube I am using is portfolio analyser.

    I would like categorise the resources by which team they are part of firstly, i.e. Project Management, Technical Services, Delivery

    I have created a PowerPivot table and would like group each user by team and then by resource name, capacity, work and availability. Time across the top, so I can see this for Year, month, and weekly utilisation.

    How can I get the team that each is resource is assigned from this cube?

    Thanks. 





    • Edited by cdmit2010 Tuesday, December 17, 2013 1:30 AM
    Tuesday, December 17, 2013 1:13 AM

Answers

  • Quite hard to say since I got no PS2013 instance in front of me to test it, but try this:

    Go to central admin > Service Application > Project Server Service Application > OLAP Database Management > Configuration of Cube > Cube Dimensions > Assignment > then add Team to the dimension list and build the cube.

    Another hint (again it can't check if I'm right...), edit the team custom field and under "Calculation for assignment rows", click "Roll down unless manually entered" so the contents of this custom field are to be distributed across assignments.

    Just as a comment, the team OOB functionality is usually used to assign tasks to a team (ie developers team), then resources from the team will self-assign tasks. The department feature might corespond better to your usage.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    • Marked as answer by cdmit2010 Tuesday, December 17, 2013 9:50 PM
    Tuesday, December 17, 2013 8:53 PM
    Moderator

All replies

  • Hi cdmit2010,

    Is your group field a custom field or the team native field in PS2013?

    In case this is a resource custom field, you have to go in the OLAP configuration and add it as a dimension. Note that you cannot add a custom field in an OLAP cube if it is a multivalue field.

    If you're using the default "team" field, eventhough I can't test it right now, I'm guessing that this field can be added as a resource and assignment dimension as well in the OLAP configuration.

    Here is a useful link on how to configure OLAP DB: http://technet.microsoft.com/en-us/library/ff465342.aspx

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, December 17, 2013 2:07 PM
    Moderator
  • Hi Guillaume,

    I think the field I require is listed in the Enterprise Custom Fields and Lookup Tables section of PWA, however I believe it was a default field that came pre-configured OOB after install. The exact field I require is located if you go to; PWA Settings > Security >Manage Users > Select User> Team Details > Team Name.

    As you can see by the description next team name, there are instructions advising on how to populate the team names for your organisation. So it was a pre-configured field. 

    ----------------------------

    Team Details    

    Team Details are optional and are used to define team membership and the team resource that represents a team. Before you set these options use "Server Settings"/"Enterprise Custom Fields and Lookup Tables" to create a lookup table that contains your team names, and edit the "Team Name" resource custom field to use this lookup table.

    Team Name is used to indicate team membership - each resource in a team will have the same value for Team Name. 

    The Team Assignment Pool check box is selected for the team resource, used when assigning tasks to the team. Often a generic resource will be used with the assignment owner field set as the team manager.

    -------------------------------------------

    So I have entered in all my appropriate organisation team names in the Lookup Table for Custom Field; Team.

    I have assigned each resource to one of the team names.

    Now I would like to create a PowerPivot Table and incorporate the resource team to be able to create a utilisation report based on each team within my organisation.

    After receiving the link from you above I have gone to central admin > Service Application > Project Server Service Application > OLAP Database Management > Configuration of Cube > Cube Dimensions > Resource >

    I have added "Team_Resource" to the selected dimension list. Then I completed a "Build Now".

    When I go back to my PowerPivot Excel sheet and refresh data I am able to see the new field however, it is not the field that I need.

    Can you confirm which field I would need to add?

    Is this field a multi field?

    Appreciate your assistance.

    Thanks


    • Edited by cdmit2010 Tuesday, December 17, 2013 8:46 PM
    Tuesday, December 17, 2013 8:43 PM
  • Quite hard to say since I got no PS2013 instance in front of me to test it, but try this:

    Go to central admin > Service Application > Project Server Service Application > OLAP Database Management > Configuration of Cube > Cube Dimensions > Assignment > then add Team to the dimension list and build the cube.

    Another hint (again it can't check if I'm right...), edit the team custom field and under "Calculation for assignment rows", click "Roll down unless manually entered" so the contents of this custom field are to be distributed across assignments.

    Just as a comment, the team OOB functionality is usually used to assign tasks to a team (ie developers team), then resources from the team will self-assign tasks. The department feature might corespond better to your usage.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    • Marked as answer by cdmit2010 Tuesday, December 17, 2013 9:50 PM
    Tuesday, December 17, 2013 8:53 PM
    Moderator
  • Thank you Guillaume,

    You are right in relation to the team being assigned to tasks, and probably the more appropriate field would be department.

    I have checked the OLAP cube and department is available and can be displayed correctly on my report.

    Will use department instead.

    Thank you for all the assistance, very much appreciated.

    Tuesday, December 17, 2013 9:50 PM