DetailEstimateQueries

This topic explains how to use the detail estimate queries option in Agility.

Detail Estimate Trend

            SELECT  D.DateKey, SUM(DetailEstimate) AS [Detail Estimate]
            FROM    Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey=D.DateKey
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            WHERE  ProjectName = 'Call Center'
            GROUP BY D.DateKey
        ORDER BY D.DateKey

Detail Estimate By Sprint

            declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

            select IterationName, sum(DetailEstimate) TotalDetailEstimate
            from Dim.Iteration
            join Dim.Date EndDate on EndDate.DateKey=IterationEndDateKey
            left join Fact.Workitem Fact on Fact.IterationKey=Iteration.IterationKey and Fact.DateKey=case when EndDate.DateKey < @lastDateKey then EndDate.DateKey else @lastDateKey end
        group by IterationName

Detail Estimate by Custom Drop-downs on Task

            SELECT  D.Datekey, ComponentName, SUM(DetailEstimate) AS [Detail Estimate]
            FROM    Fact.Workitem WI
            join Dim.Date D on WI.DateKey = D.DateKey
            join Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            join Dim.Project P ON P.ProjectKey = PP.AncestorKey
            join Dim.Component C on WI.ComponentKey = C.ComponentKey
            WHERE   ( ProjectName = 'Call Center' )
            GROUP BY D.DateKey, ComponentName
        ORDER BY D.DateKey

Current Member Load

For Current Member load you need two queries. The fist tells us the Remaining ToDo for each Member

            SELECT  Member.MemberName,
            SUM(ToDo) as ToDo
            FROM  Fact.Workitem
            INNER JOIN Dim.Date ON Workitem.DateKey = Date.DateKey
            INNER JOIN Dim.AssetState on Workitem.PrimaryAssetStateKey = AssetState.AssetStateKey
            INNER JOIN Dim.PrimaryWorkitem ON Fact.Workitem.PrimaryWorkitemKey = Dim.PrimaryWorkitem.PrimaryWorkitemKey
            INNER JOIN Dim.Project ON Workitem.ProjectKey = Project.ProjectKey
            INNER JOIN Bridge.WorkitemOwner ON Fact.Workitem.WorkitemKey = Bridge.WorkitemOwner.WorkitemKey
            AND Fact.Workitem.DateKey = Bridge.WorkitemOwner.DateKey
            INNER JOIN Dim.Member on Bridge.WorkitemOwner.MemberKey = Dim.Member.MemberKey
            WHERE   (Workitem.DateKey = (select max(DateKey) from Fact.Workitem)) and ( ProjectName = 'Release 1.0' )
            GROUP BY Member.MemberName
        ORDER BY Member.MemberName

The Second tells us the total done

            select Member.MemberName, Iteration.IterationName, sum(Effort) as Done
            from Fact.Effort
            join Dim.Member on Member.MemberKey = Effort.MemberKey
            JOIN Dim.Project ON Project.ProjectKey = Effort.ProjectKey
            JOIN Dim.Iteration ON Iteration.IterationKey = Effort.IterationKey
            where ProjectName='Release 1.0' and IterationName = 'Month C 1st Half'
        group by MemberName, IterationName

Estimation Accuracy

            declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

            select
            isnull(X.MemberKey,Y.MemberKey) MemberKey,
            isnull(X.IterationKey,Y.IterationKey) IterationKey,
            AllocatedDetailEstimate,
            AllocatedToDo,
            Done = isnull(Done, 0),
            EstimationAccuracy = case when AllocatedDetailEstimate is not null and (isnull(AllocatedToDo,0) + isnull(Done, 0)) > 0 then
            AllocatedDetailEstimate / (isnull(AllocatedToDo,0) + isnull(Done, 0))
            end
            from (
            select
            isnull(MemberKey, -1) MemberKey,
            IterationKey,
            sum(AllocatedDetailEstimate) AllocatedDetailEstimate, 
            sum(AllocatedToDo) AllocatedToDo 
            from (
            select
            Workitem.DateKey,
            Workitem.WorkitemKey,
            IterationKey,
            AllocatedDetailEstimate=DetailEstimate/isnull(OwnerCount, 1),
            AllocatedToDo=ToDo/isnull(OwnerCount, 1)
            from Fact.Workitem
            left join (
            select DateKey, WorkitemKey, count(MemberKey) OwnerCount
            from Bridge.WorkitemOwner
            group by DateKey, WorkitemKey
            ) Owners on Owners.DateKey=Workitem.DateKey and Owners.WorkitemKey=Workitem.WorkitemKey
            ) Workitem
            left join Bridge.WorkitemOwner on WorkitemOwner.WorkitemKey = Workitem.WorkitemKey and Workitem.DateKey = WorkitemOwner.DateKey
            where Workitem.DateKey = @lastDateKey
            group by
            WorkitemOwner.MemberKey,
            Workitem.IterationKey
            ) X
            full join (
            select MemberKey, IterationKey, sum(Effort) Done
            from Fact.Effort
            group by MemberKey, IterationKey
        ) Y on X.MemberKey=Y.MemberKey and X.IterationKey=Y.IterationKey