none
Original Estimation in TFS

    Question

  • I'm trying to program an SSRS report to report TFS hours each emplyee is assigned to in different projects for a certain sprint.

    In the Olap DB I found a field OriginalEstimate but it shows up empty for all the WIs (while the remaining work shows the correct number).

    In our TFS website I don't see where the OriginalEstimate field is either. So how where is this DB field set in the user interface?

    Another question, where can I search for the path for certian measures/ dimentions (ex: where is the sprint dimention?)

    Thanks a lot


    Namnami

    Thursday, May 10, 2012 10:37 AM

Answers

  • First, to help you solve your problem. Getting the information you're after is going to be every difficult. In the first place because the data isn't for grabs, it's hidden away in the work item history (Revision), second because it's not data which really suits your purpose. Take these examples:

    • As developers update the Hours remaining field in the scrum template, it doesn't tell you anything about the hours actually spent. Say you managed to fix a bug in a lot less time than orgininally estimated and set the hour remaining from 32 to 0, but only spent 4 hours to fix it. How many hours will you assign to that user? You might be able to get the original estimate, but it doesn't help you get the actual hours spent.
    • Another issue is that multiple users may have worked on an item together (that should happen if you're doing scrum/agile properly), a task or work item can only be assigned to one user at a time, so you'll only be able to link back changes in the Hours Remaining work to that one person. Also, as an item gets re-assigned to a more senior developer or a specialist, he might update the hours remaining to a lower value due to  the fact that he can do it faster.

    To solve this for real you have a few options:

    1. Change the process template and add the CMMI fields Original Estimate and Hours Spent to the work items.
    2. Implement Project Server and have people submit a timecard at the end of the day. You can link multiple timecards to the same work item. Your reports will be coming out of Project Server, not TFS in this case.
    3. Don't use the Scrum process template, but the MSF CMMI template or another process that actually cares about the information you're after.

    But I do want to stress the following: Evaluate with your organisation why you need to know exactly how much time each person on your scrum team spent on which work items. There is a reason why scrum doesn't care. For one, it's water under the bridge, you can't change it anymore. Second, it's not useful information outside of the team, only the team should know, and normally they do. They should use this information to improve their game and to make better forecasts in the future. Third, it tries to make every person individually accountable; In Scrum only the team as a whole is accountable. If one developer has been unable to perform during a sprint, the team should have joined in to solve the issue at hand. I strongly suggest to discuss this with the Scrum master of the team you're trying to get this information of as he should try to influence the organisation in order to establish a batter basis of trust.

    If you really want to report something, report the Velocity of the team. In Story Point, not hours that is.

    Back to your question

    As to your question on sprint lengths, these are part of the Sprint work item and that work item is linked to an iteration. I don't think there is an actual Sprint Dimension in the warehosue, you'll be using the iteration dimension and will have to look up the start/end dates from the associated sprint item.


    My blog: blog.jessehouwing.nl

    • Marked as answer by namnami Monday, May 14, 2012 11:01 AM
    Sunday, May 13, 2012 9:24 AM
  • The cube contains all the fields of all your collections on the TFS server. A project created with the Agile or CMMI process template will have that field and it will create the field in the cube. Scrum projects don't have that field, so if your project is created with the Scrum process template your Task work item does not have that field and the value in the cube will be blank.

    You can customize your Task work item to add the field. You can use the process editor in the power tools for that purpose or you can use witadmin.

    Links:

    Power tools TFS 11 Beta: http://visualstudiogallery.msdn.microsoft.com/27832337-62ae-4b54-9b00-98bb4fb7041a
    Power tools TFS 2010: http://visualstudiogallery.msdn.microsoft.com/c255a1e4-04ba-4f68-8f4e-cd473d6b971f
    WIT customization: http://msdn.microsoft.com/en-us/library/ms243849


    Please remember to mark the replies as answers if they help.

    • Marked as answer by namnami Monday, May 14, 2012 11:00 AM
    Thursday, May 10, 2012 2:26 PM
  • First, lets go back to scrum. Scrum doesn't assign work items to all ream members at the start of the sprint. You check the total availability of the team members and verify that the work breakdown doesn't overload the team as a whole. Also, if you have specialists in your team you will need to verify that you don't overload their specialty. The latter can usually be solved by pairing up. This comes back to team responsibility vs personal responsibility.

    Reality often differs and you might need to assign work. Discuss this with your scrum master, as it's a sign of a team not following scrum as it was intended. You can use the agile workbooks from the MSF agile process template with the scrum template should you need this. This is a real-time, but doesn't support historical queries.

    There's also a few 3rd party products that can help you in this area. The TFS Work Item Manager from Telerik as a stand-alone tool or Urban Turtle, which integrates with Team WebAccess come to mind. (Note: most features from Urban Turtle have been integrated with TFS 11 which will be released later this year).

    If you are assigning work ahead of time talk to the scrum master, as this is usually a sign of a team that is still learning and has embraced some type of command and control strategy to combat their fears and risks, while they should not tie themselves down too much at the start of each sprint. Uncertainty to a certain extent is a good thing.

    If you want to check this after the fact then only the API or the warehouse can help you. I could not find a recent article on this, but this article against the 2008 data warehouse might help you.


    My blog: blog.jessehouwing.nl


    • Edited by Jesse Houwing Sunday, May 13, 2012 10:44 AM
    • Marked as answer by namnami Monday, May 14, 2012 11:00 AM
    Sunday, May 13, 2012 10:42 AM
  • Yes, that is part of the Agile Workbooks I mentioned before.

    Reality often differs and you might need to assign work. Discuss this with your scrum master, as it's a sign of a team not following scrum as it was intended. You can use the agile workbooks from the MSF agile process template with the scrum template should you need this. This is a real-time, but doesn't support historical queries.

    My blog: blog.jessehouwing.nl

    • Marked as answer by namnami Monday, May 14, 2012 10:59 AM
    Monday, May 14, 2012 9:43 AM

All replies

  • I'm trying to program an SSRS report to report TFS hours each emplyee is assigned to in different projects for a certain sprint.

    In the Olap DB I found a field OriginalEstimate but it shows up empty for all the WIs (while the remaining work shows the correct number).

    In our TFS website I don't see where the OriginalEstimate field is either. So how where is this DB field set in the user interface?

    Another question, where can I search for the path for certian measures/ dimentions (ex: where is the sprint dimention?)

    (TFS 2010 SCRUM- VS 2005 SP1)

    Thanks a lot


    Namnami

    Thursday, May 10, 2012 10:42 AM
  • Scrum isn't interested in the original estimate, as such it isn't used in the process template for scrum. The MSF CMMI process template does use it.

    My blog: blog.jessehouwing.nl

    Thursday, May 10, 2012 11:56 AM
  • The cube contains all the fields of all your collections on the TFS server. A project created with the Agile or CMMI process template will have that field and it will create the field in the cube. Scrum projects don't have that field, so if your project is created with the Scrum process template your Task work item does not have that field and the value in the cube will be blank.

    You can customize your Task work item to add the field. You can use the process editor in the power tools for that purpose or you can use witadmin.

    Links:

    Power tools TFS 11 Beta: http://visualstudiogallery.msdn.microsoft.com/27832337-62ae-4b54-9b00-98bb4fb7041a
    Power tools TFS 2010: http://visualstudiogallery.msdn.microsoft.com/c255a1e4-04ba-4f68-8f4e-cd473d6b971f
    WIT customization: http://msdn.microsoft.com/en-us/library/ms243849


    Please remember to mark the replies as answers if they help.

    • Marked as answer by namnami Monday, May 14, 2012 11:00 AM
    Thursday, May 10, 2012 2:26 PM
  • See also the duplicate post: http://social.msdn.microsoft.com/Forums/en-US/tfsprocess/thread/75bc0b90-70df-4e48-a8c4-f527a7aeeeea

    Please remember to mark the replies as answers if they help.

    Thursday, May 10, 2012 2:26 PM
  • I need to make a TFS report showing how many development hours each employee took for certain sprint. So I can't make that report in SCRUM TFS without adding private customization? (Do you know what the Rev field means- maybe that could help?).

    Another question, where can I search for the location of certain measures/ dimentions (ex: where is the sprint dimention?)


    Namnami


    • Edited by namnami Sunday, May 13, 2012 5:35 AM addition
    Sunday, May 13, 2012 5:34 AM
  • First, to help you solve your problem. Getting the information you're after is going to be every difficult. In the first place because the data isn't for grabs, it's hidden away in the work item history (Revision), second because it's not data which really suits your purpose. Take these examples:

    • As developers update the Hours remaining field in the scrum template, it doesn't tell you anything about the hours actually spent. Say you managed to fix a bug in a lot less time than orgininally estimated and set the hour remaining from 32 to 0, but only spent 4 hours to fix it. How many hours will you assign to that user? You might be able to get the original estimate, but it doesn't help you get the actual hours spent.
    • Another issue is that multiple users may have worked on an item together (that should happen if you're doing scrum/agile properly), a task or work item can only be assigned to one user at a time, so you'll only be able to link back changes in the Hours Remaining work to that one person. Also, as an item gets re-assigned to a more senior developer or a specialist, he might update the hours remaining to a lower value due to  the fact that he can do it faster.

    To solve this for real you have a few options:

    1. Change the process template and add the CMMI fields Original Estimate and Hours Spent to the work items.
    2. Implement Project Server and have people submit a timecard at the end of the day. You can link multiple timecards to the same work item. Your reports will be coming out of Project Server, not TFS in this case.
    3. Don't use the Scrum process template, but the MSF CMMI template or another process that actually cares about the information you're after.

    But I do want to stress the following: Evaluate with your organisation why you need to know exactly how much time each person on your scrum team spent on which work items. There is a reason why scrum doesn't care. For one, it's water under the bridge, you can't change it anymore. Second, it's not useful information outside of the team, only the team should know, and normally they do. They should use this information to improve their game and to make better forecasts in the future. Third, it tries to make every person individually accountable; In Scrum only the team as a whole is accountable. If one developer has been unable to perform during a sprint, the team should have joined in to solve the issue at hand. I strongly suggest to discuss this with the Scrum master of the team you're trying to get this information of as he should try to influence the organisation in order to establish a batter basis of trust.

    If you really want to report something, report the Velocity of the team. In Story Point, not hours that is.

    Back to your question

    As to your question on sprint lengths, these are part of the Sprint work item and that work item is linked to an iteration. I don't think there is an actual Sprint Dimension in the warehosue, you'll be using the iteration dimension and will have to look up the start/end dates from the associated sprint item.


    My blog: blog.jessehouwing.nl

    • Marked as answer by namnami Monday, May 14, 2012 11:01 AM
    Sunday, May 13, 2012 9:24 AM
  • Thanks for your answer, that quite cleared my view on things.

    The main need for this report is to check in the beginning of sprint that each employee took the appropriate amount of hours for the sprint (compared to his working hours).

    Maybe I could display the remainingWork as it was at a certain date (for a certain Sprint) so that could display how many hours each employee took in the begiing of the sprint.

    I undertand it is somewhere in the work item history/ revision- but how do I get there exactly?

    In addition, it would have been prefered to have a realtime report for this, not from the olap 2 hour cube. But I don't know how.

    At first glance it looks like a very basic report I would have thought it to be very easy. Just to see how many hours each employee took for the sprint so as to see he filled his working time appropriately.

    Your help appreciated, thanks.


    Namnami

    Sunday, May 13, 2012 10:09 AM
  • First, lets go back to scrum. Scrum doesn't assign work items to all ream members at the start of the sprint. You check the total availability of the team members and verify that the work breakdown doesn't overload the team as a whole. Also, if you have specialists in your team you will need to verify that you don't overload their specialty. The latter can usually be solved by pairing up. This comes back to team responsibility vs personal responsibility.

    Reality often differs and you might need to assign work. Discuss this with your scrum master, as it's a sign of a team not following scrum as it was intended. You can use the agile workbooks from the MSF agile process template with the scrum template should you need this. This is a real-time, but doesn't support historical queries.

    There's also a few 3rd party products that can help you in this area. The TFS Work Item Manager from Telerik as a stand-alone tool or Urban Turtle, which integrates with Team WebAccess come to mind. (Note: most features from Urban Turtle have been integrated with TFS 11 which will be released later this year).

    If you are assigning work ahead of time talk to the scrum master, as this is usually a sign of a team that is still learning and has embraced some type of command and control strategy to combat their fears and risks, while they should not tie themselves down too much at the start of each sprint. Uncertainty to a certain extent is a good thing.

    If you want to check this after the fact then only the API or the warehouse can help you. I could not find a recent article on this, but this article against the 2008 data warehouse might help you.


    My blog: blog.jessehouwing.nl


    • Edited by Jesse Houwing Sunday, May 13, 2012 10:44 AM
    • Marked as answer by namnami Monday, May 14, 2012 11:00 AM
    Sunday, May 13, 2012 10:42 AM
  • Thanks a lot.

    Now how would I get remainingWork as it was at a certain date (so that could display how many hours each employee took in the begiing of the sprint)? How to use revision / history?


    Namnami

    Sunday, May 13, 2012 11:04 AM
  • did you check the last two links from my post? I personally haven't dug deeper than those, so I can't tell you exactly how to do this.


    My blog: blog.jessehouwing.nl

    Sunday, May 13, 2012 11:59 AM
  • Thanks I missed that.

    I came over an individual capacity report in MSDN:(third picture on the page)

    http://msdn.microsoft.com/en-US/library/dd997796.aspx

    This looks like what I need. Do you know how I can get it?

    I found this:

    http://msdn.microsoft.com/en-us/realdevelopment/gg465265

    but not sure if this is built in and how to get it.


    Namnami


    • Edited by namnami Monday, May 14, 2012 8:40 AM added info
    Monday, May 14, 2012 8:08 AM
  • Yes, that is part of the Agile Workbooks I mentioned before.

    Reality often differs and you might need to assign work. Discuss this with your scrum master, as it's a sign of a team not following scrum as it was intended. You can use the agile workbooks from the MSF agile process template with the scrum template should you need this. This is a real-time, but doesn't support historical queries.

    My blog: blog.jessehouwing.nl

    • Marked as answer by namnami Monday, May 14, 2012 10:59 AM
    Monday, May 14, 2012 9:43 AM