none
ECF in OLAP cubes (PWA 2007) RRS feed

  • Question

  • The possibility to add Enterprise custom fields to the 14 OLAP Cubes is quite unpredictable. Some of them accept only Enterprise Resource custom field (while they already display Task fields), some accept only Task custom field (while they already display Resource fields)…. Is there any of the 14 Cubes which accept both Resource and Task enterprise custom fields?

    Is this feature more flexible in 2010?

    Thanks

    Saturday, January 8, 2011 1:59 PM

Answers

  • Hi,

    have you tried cube MSP_Portfolio_Analyzer? This one combines the Assignment Timephased and Resource Timephased cubes and will give you most possibilties to add custom fields.

    Keep in mind that you can't add all types of fields to a cube. Again, msProjectsExperts helps me not typing too much - thanks!!!. For a detailed description which kind of fields can be used, have a look at http://www.projectserverexperts.com/ProjectServerFAQKnowledgeBase/Understand%20custom%20fields%20in%20OLAP%20cubes.aspx.

    And no, there is no major change regarding cube concept in 2010. There is a big change how to create reports based on cubes.

    Does that help?
    Barbara

     

    • Marked as answer by WLID1966 Saturday, January 8, 2011 3:27 PM
    Saturday, January 8, 2011 2:15 PM
    Moderator
  • First of all, counting is a challenge in a data analysis view. But let's check first where you lost me.

    1. I have a task flag field named "Tunnel", no formula, no roll up to summary level. roll down to assignment level activated.
    2. I have a resourec flag field named "Machine", no formula, no roll up to summary level. roll down to assignment level activated.
    3. I have added both fields for all entities to cube: Tunnel on task level and twice on assignment level, Machine on resource level and twice on assignment level.
    4. I have created a new data analysis view.
    5. I have Tunnel_T_Assignment and Tunnel_task available. I have also Machine_Resource and Machine_R_Assignment. I am able to put all 4 fields to my view as column, row and filter. It is a flag, so I can't add as measure.
    6. I added Tunnel_T_Assignment and Machine_R_Assignment as filter.
    7. I added Projectlist to rows and time to columns.
    8. I created a calculated Calculated Total (sorry, no English system, may have a different name) with [Work]/8, assuming that people are working 100% to "count" them. With filter selection, I can now see how many FTEs are working per period on each project for tasks with Tunnel= No and Ressource Machene = No.

    I can see the FTEs as expected. But I may understand your requirement completely wrong?????? Please let me know!

    Barbara

    • Marked as answer by WLID1966 Saturday, January 8, 2011 8:22 PM
    Saturday, January 8, 2011 6:34 PM
    Moderator
  • Uuups, too long text. I think I wanted to write "6. I added Tunnel_Task and Machine_Resource as filter" Server is down now, so I will need to check tomorrow. We will get there ;-).

    I have activated roll down since it gives you more possiblities (and it's a automatism of my mouse when creating fields because of My Task views ;-)). And I was still not sure if I understood your request or if we would need it by any reason.

    Regards
    Barbara

    • Marked as answer by WLID1966 Saturday, January 8, 2011 9:29 PM
    Saturday, January 8, 2011 8:32 PM
    Moderator
  • Hi,

    you are absolutely correct! For example, if you want to display a task field in "My Task" (and allow changing) , you need to enable that on field definition by activating "roll down" (what is a kind of automatísm for me ;-)). Field value will be copied from task level to assignment level and can be different by several reasons: a PM can change in Project, your resource may be allowed to change in My Tasks, ...

    These data can than be found back in Tunnel_Task for task level and Tunnel_T_Assignment for assignment level.

    Does that help?
    Regards
    Barbara

    • Marked as answer by WLID1966 Sunday, January 9, 2011 11:12 AM
    Sunday, January 9, 2011 11:07 AM
    Moderator

All replies

  • Hi,

    have you tried cube MSP_Portfolio_Analyzer? This one combines the Assignment Timephased and Resource Timephased cubes and will give you most possibilties to add custom fields.

    Keep in mind that you can't add all types of fields to a cube. Again, msProjectsExperts helps me not typing too much - thanks!!!. For a detailed description which kind of fields can be used, have a look at http://www.projectserverexperts.com/ProjectServerFAQKnowledgeBase/Understand%20custom%20fields%20in%20OLAP%20cubes.aspx.

    And no, there is no major change regarding cube concept in 2010. There is a big change how to create reports based on cubes.

    Does that help?
    Barbara

     

    • Marked as answer by WLID1966 Saturday, January 8, 2011 3:27 PM
    Saturday, January 8, 2011 2:15 PM
    Moderator
  • Hi Barbara,

    Yes, I was using the MSP_Portfolio_Analyzer cube which proposes the most numerous dimensions.

    I succeeded in adding a Flag Resource custom field, but I'm not able to add a Flag Task custom field ;-(

    I'll try again,

    Have a good weekend!

    Saturday, January 8, 2011 3:20 PM
  • Hi,

    if this is a flag field with "Roll-Up" enabled, you can't add it to a cube. In this case behaviour is similar to a formula. Could this be the reason?
    Roll-down is no issue, since there is no calculation, but copying data from task to assignment level.

    Enjoy your weekend!
    Barbara

     

    Saturday, January 8, 2011 3:53 PM
    Moderator
  • Thanks for the hint Barbara,

    But the Calculation for summary rows is "None" for both flag fields

    Saturday, January 8, 2011 4:06 PM
  • Hmmm, so you have a flag field on task level, without formula and no roll-up activated and you can't add it to the cube. Sorry for asking this question: when configuring the cube, you changed the entity to select fields from (I am sure that you did, but I want to find the difference what we are doing)?

    What is your field named? The first letter has to be a normal letter, no numeric, no special characters (even no _).

    Let me know, I am quite curious!
    Barbara

    Saturday, January 8, 2011 4:11 PM
    Moderator
  • Thanks for your investigation!

     - The Task Flag field is named "Tunnel". No formula. Calculation for summary rows is "None".

     - The Resource Flag field is named "Machine". No formula. Calculation for summary rows is "None".

    Purpose of this cube: to count the number of human resources in a specific place, on a timescale, for security reasons. (Without counting the machines which also are "Work resources").

     

    Saturday, January 8, 2011 5:38 PM
  • First of all, counting is a challenge in a data analysis view. But let's check first where you lost me.

    1. I have a task flag field named "Tunnel", no formula, no roll up to summary level. roll down to assignment level activated.
    2. I have a resourec flag field named "Machine", no formula, no roll up to summary level. roll down to assignment level activated.
    3. I have added both fields for all entities to cube: Tunnel on task level and twice on assignment level, Machine on resource level and twice on assignment level.
    4. I have created a new data analysis view.
    5. I have Tunnel_T_Assignment and Tunnel_task available. I have also Machine_Resource and Machine_R_Assignment. I am able to put all 4 fields to my view as column, row and filter. It is a flag, so I can't add as measure.
    6. I added Tunnel_T_Assignment and Machine_R_Assignment as filter.
    7. I added Projectlist to rows and time to columns.
    8. I created a calculated Calculated Total (sorry, no English system, may have a different name) with [Work]/8, assuming that people are working 100% to "count" them. With filter selection, I can now see how many FTEs are working per period on each project for tasks with Tunnel= No and Ressource Machene = No.

    I can see the FTEs as expected. But I may understand your requirement completely wrong?????? Please let me know!

    Barbara

    • Marked as answer by WLID1966 Saturday, January 8, 2011 8:22 PM
    Saturday, January 8, 2011 6:34 PM
    Moderator
  • Thanks again Barbara,

    What I missed obviously was to use the Tunnel_T_Assignment and the Machine_R_Assignment dimensions and to place them as filters.

    I didn't used the "Roll down, unless manually specified" so I changed them too. Are you sure they are usefull?

    Now I get values but I still have a problem: the logic filters would be :

    Tunnel_T_Assignment = Yes (Tasks which are running in the tunnel)

    Machine_R_Assignment = No (Only human resources)

    But if I set any filter to "Yes", I lost every values : the pivot table is empty.

    I'll study that tomorrow. Thanks for your great help !

     

    Saturday, January 8, 2011 8:22 PM
  • Uuups, too long text. I think I wanted to write "6. I added Tunnel_Task and Machine_Resource as filter" Server is down now, so I will need to check tomorrow. We will get there ;-).

    I have activated roll down since it gives you more possiblities (and it's a automatism of my mouse when creating fields because of My Task views ;-)). And I was still not sure if I understood your request or if we would need it by any reason.

    Regards
    Barbara

    • Marked as answer by WLID1966 Saturday, January 8, 2011 9:29 PM
    Saturday, January 8, 2011 8:32 PM
    Moderator
  • Great !!!!

    That works !!!

    That's exactly what I need!

    Thanks a lot Barbara

    Best regards

     

    Saturday, January 8, 2011 9:35 PM
  • Hi,

    A last question could be : what is the difference between the fields:

    • Tunnel_Task and Tunnel_T_Assignment
    • Machine_Resource and Machine_R_Assignment

    I suppose that ..._Task is a data at the task level

    ""              ""   ...T_Assignment is the data at the assignment level.

    Any suggestion?

    Thanks    

    Sunday, January 9, 2011 10:30 AM
  • Hi,

    you are absolutely correct! For example, if you want to display a task field in "My Task" (and allow changing) , you need to enable that on field definition by activating "roll down" (what is a kind of automatísm for me ;-)). Field value will be copied from task level to assignment level and can be different by several reasons: a PM can change in Project, your resource may be allowed to change in My Tasks, ...

    These data can than be found back in Tunnel_Task for task level and Tunnel_T_Assignment for assignment level.

    Does that help?
    Regards
    Barbara

    • Marked as answer by WLID1966 Sunday, January 9, 2011 11:12 AM
    Sunday, January 9, 2011 11:07 AM
    Moderator
  • Thanks for everything Barbara,

    Enjoy your weekend!

    Sunday, January 9, 2011 11:16 AM
  • You are welcome. I am working on Project 2010 currently, so I like some diversion by 2007 ;-).

    Have also a nice weekend (as much as is left)!
    Barbara

    Sunday, January 9, 2011 11:19 AM
    Moderator