DetailEstimateQueries
This feature is available in Enterprise and Ultimate editions.
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 Dropdowns 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