DetailEstimateQueries

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

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