EstimateQueries

This topic explains how to run the estimate queries in Agility.

Project Burndown

This section contains queries used to retrieve Project Burndown data.

Here's the query to get all open estimates for a project called 'Call Center' and all it's child projects.

            SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
            JOIN Dim.Date D ON D.DateKey = WI.DateKey
            WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center'
            GROUP BY D.Date
        ORDER BY D.Date

Project Burndown filtered to a specific Iteration

To filter these results by iteration, you can join the table to the query.

            SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
            JOIN Dim.Date D ON D.DateKey = WI.DateKey
            JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
            WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center' AND I.IterationName = 'Month A 1st Half'
            GROUP BY D.Date
        ORDER BY D.Date

Project Burndown filtered to a specific Theme

Here's another example where burndown data is filtered to a specific theme:

            SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
            JOIN Dim.Date D ON D.DateKey = WI.DateKey
            JOIN Dim.Theme T ON WI.ThemeKey = T.ThemeKey
            WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center' AND T.ThemeName = 'Customer Management'
            GROUP BY D.Date
        ORDER BY D.Date

Project Burndown that does not include child projects

Finally, If you are not interested in including child projects, you can remove the JOIN to Tree.ProjectParent and change the Dim.Project join.

            SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Project P ON P.ProjectKey = WI.ProjectKey
            JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
            JOIN Dim.Date D ON D.DateKey = WI.DateKey
            WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center'
            GROUP BY D.Date
        ORDER BY D.Date

Program Burndown

            SELECT D.DateKey, SUM(Estimate) AS [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey = D.DateKey
            JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
            JOIN Bridge.ProjectProgram BPP ON WI.ProjectKey = BPP.ProjectKey
            JOIN Dim.Program P ON BPP.ProgramKey = P.ProgramKey
            WHERE ProgramName = 'Next Release'
            and AssetStateName = 'Active'
            GROUP BY D.DateKey
        ORDER BY D.DateKey

Project Burndown with a Custom Check box

            SELECT  WI.DateKey, SUM(Estimate) AS [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey = D.DateKey
            JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Dim.RoadmapItem R ON WI.RoadMapItemKey = R.RoadMapItemKey
            WHERE ProjectName = 'Call Center' and AssetStateName = 'Active' and RoadMapItemName = 'Yes'
            GROUP BY WI.DateKey
        Order By WI.DateKey

Project Burndown with a Custom Dropdown

            SELECT  WI.DateKey, SUM(Estimate) AS [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey = D.DateKey
            JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Dim.ShirtSize ON WI.ShirtSizeKey = ShirtSize.ShirtSizeKey
            WHERE ProjectName = 'Call Center' and AssetStateName = 'Active' and ShirtSizeName = 'Large'
            GROUP BY WI.DateKey
            Order By WI.DateKey
            {sql}

            h6. Alternate Project Burndown Query
            {code:sql}SELECT  Date,
            Project.ProjectName,
            SUM(Workitem.Estimate) AS [Open Estimate]
            FROM    Tree.ProjectParent AS T,
            Dim.Project,
            Fact.Workitem
            INNER JOIN Dim.Date ON Workitem.DateKey = Date.DateKey
            INNER JOIN Dim.AssetState ON Workitem.PrimaryAssetStateKey = AssetState.AssetStateKey
            where   T.AncestorKey = Project.ProjectKey
            AND T.DescendantKey = Workitem.ProjectKey
            AND AssetStateName = 'Active'
            GROUP BY Date,
        Project.ProjectName

Create an Epic Burndown

Here's an example of how to get open estimates for an epic:

            SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
            FROM Fact.PrimaryWorkitem WI
            JOIN Tree.EpicParent ET ON ET.DescendantKey = WI.EpicKey
            JOIN Dim.Epic E ON E.EpicKey = ET.AncestorKey
            JOIN Dim.AssetState A ON A.AssetStateKey = WI.PrimaryAssetStateKey
            JOIN Dim.Date D ON D.DateKey = WI.DateKey
            WHERE A.AssetStateName = 'Active' AND E.EpicName = 'Order Check'
            GROUP BY D.Date
        ORDER BY D.Date

Create a Goal Burndown

            SELECT D.DateKey, SUM(Estimate) AS [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey = D.DateKey
            JOIN Dim.AssetState A ON WI.PrimaryAssetStateKey = A.AssetStateKey
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Bridge.PrimaryWorkitemGoal PWIG ON WI.PrimaryWorkitemKey = PWIG.PrimaryWorkitemKey AND WI.DateKey = PWIG.DateKey
            JOIN Dim.Goal G ON PWIG.GoalKey = G.GoalKey
            WHERE AssetStateName = 'Active'
            and ProjectName = 'Call Center'
            and GoalName = 'Increase Customer Satisf. by 10%'
            GROUP BY D.DateKey
        ORDER BY D.DateKey

Cumulative Flow

            select DateKey, WorkitemStatusName,  sum(Estimate) Estimate
            from Fact.Workitem WI
            join Dim.WorkitemStatus S on WI.WorkitemStatusKey=S.WorkitemStatusKey
            group by DateKey, WorkitemStatusName
        ORDER BY DateKey

Using Custom Measures

The following query shows Business Value being added over time.

            SELECT D.Date, SUM(E.PrimaryWorkitemCustom_BusinessValue) [Business Value]
            FROM Fact.Workitem E
            join Tree.ProjectParent ET ON ET.DescendantKey = E.ProjectKey
            join Dim.Project ED ON ED.ProjectKey = ET.AncestorKey
            join Dim.AssetState A ON A.AssetStateKey = E.PrimaryAssetStateKey
            join Dim.Date D ON D.DateKey = E.DateKey
            WHERE A.AssetStateName = 'Closed' AND ED.ProjectName = 'Call Center'
            GROUP BY D.Date
        ORDER BY D.Date

Theme Roadmap

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

            select ThemeName, IterationName, sum(Estimate) Estimate
            from Fact.Workitem WI
            join Dim.Iteration I on I.IterationKey=WI.IterationKey
            join Dim.Theme T on T.ThemeKey=WI.ThemeKey
            where WI.DateKey=case when I.IterationEndDateKey < @lastDateKey then I.IterationEndDateKey else @lastDateKey end
        group by ThemeName, IterationName

Total Estimate Trend

            select DateKey, AssetStateName, sum(Estimate) Estimate
            from Fact.Workitem
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.AssetState on AssetState.AssetStateKey=Workitem.PrimaryAssetStateKey
            where  ProjectName='Release 1.0'
            group by DateKey, AssetStateName
        order by DateKey

Total Estimate Trend for a Team

            select WI.DateKey, sum(Estimate) as [Open Estimate]
            from Fact.Workitem WI
            join Dim.AssetState on Dim.AssetState.AssetStateKey = WI.PrimaryAssetStateKey
            join Dim.Team on Team.TeamKey=WI.TeamKey
            where AssetStateName = 'Active' and TeamName='Team A'
            group by DateKey
        order by DateKey

Estimate Trend for a MemberGroup

            SELECT Date,  SUM(Estimate) AS [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey = D.DateKey
            JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Bridge.WorkitemOwner O ON O.WorkitemKey = WI.WorkitemKey AND O.DateKey = WI.DateKey
            join Bridge.MemberGroup BMG on BMG.MemberKey = O.MemberKey
            join Dim.MemberGroup DMG on BMG.MemberGroupKey = DMG.MemberGroupKey
            WHERE ProjectName = 'Call Center'
            and AssetStateName = 'Active'
            and MemberGroupName = 'Local Developers'
            GROUP BY Date
        ORDER BY Date

Estimate trend for a Member

            SELECT  D.DateKey, SUM(Estimate) AS [Open Estimate]
            FROM Fact.Workitem WI
            JOIN Dim.Date D ON WI.DateKey = D.DateKey
            JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
            JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
            JOIN Tree.ProjectParent PP on PP.DescendantKey=WI.ProjectKey
            JOIN Dim.Project P on P.ProjectKey=PP.AncestorKey
            JOIN Bridge.WorkitemOwner O ON WI.WorkitemKey = O.WorkitemKey AND WI.DateKey = O.DateKey
            JOIN Dim.Member M on O.MemberKey = M.MemberKey
            WHERE ProjectName = 'Release 1.0'
            and AssetStateName = 'Active'
            and AssetTypeName in ('Story', 'Defect')
            and MemberName = 'Andre Agile'
            GROUP BY D.DateKey
        ORDER BY D.DateKey

Velocity

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

            select IterationName, sum(Estimate) Estimate
            from Dim.Iteration I
            join Dim.Date D on D.DateKey=I.IterationEndDateKey
            join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            where ProjectName='Call Center'
        group by IterationName

Velocity by Priority

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

            select IterationName, WorkitemPriorityName, sum(Estimate) Estimate
            from Dim.Iteration I
            join Dim.Date D on D.DateKey=I.IterationEndDateKey
            join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.WorkitemPriority on WorkitemPriority.WorkitemPriorityKey=Workitem.WorkitemPriorityKey
            where ProjectName='Call Center'
        group by IterationName, WorkitemPriorityName

Velocity by Project

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

            select IterationName, Project.ProjectName, sum(Estimate) Estimate
            from Dim.Iteration I
            join Dim.Date D on D.DateKey=I.IterationEndDateKey
            join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
            join Dim.Project Project on Project.ProjectKey=Workitem.ProjectKey
            where RootProject.ProjectName='Company'
        group by IterationName, Project.ProjectName

Velocity by Status

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

            select IterationName, WorkitemStatusName, sum(Estimate) Estimate
            from Dim.Iteration I
            join Dim.Date D on D.DateKey=I.IterationEndDateKey
            join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.WorkitemStatus on WorkitemStatus.WorkitemStatusKey=Workitem.WorkitemStatusKey
            where ProjectName='Call Center'
        group by IterationName, WorkitemStatusName

Velocity by Story Complexity

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

            select IterationName, StoryRiskName, sum(Estimate) Estimate
            from Dim.Iteration I
            join Dim.Date D on D.DateKey=I.IterationEndDateKey
            join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.StoryRisk on StoryRisk.StoryRiskKey=Workitem.StoryRiskKey
            join Dim.AssetType on Dim.AssetType.AssetTypeKey = Workitem.AssetTypeKey
            where ProjectName='Call Center' and AssetTypeName = 'Story'
        group by IterationName, StoryRiskName

Velocity by Story Type

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

            select IterationName, StoryTypeName, sum(Estimate) Estimate
            from Dim.Iteration I
            join Dim.Date D on D.DateKey=I.IterationEndDateKey
            join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.StoryType on StoryType.StoryTypeKey=Workitem.StoryTypeKey
            join Dim.AssetType on Dim.AssetType.AssetTypeKey = Workitem.AssetTypeKey
            where ProjectName='Call Center' and AssetTypeName = 'Story'
        group by IterationName, StoryTypeName

Details Roadmap

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

            select P.ProjectName, IterationName, PrimaryWorkitemName, Estimate
            from Fact.Workitem WI
            join Dim.PrimaryWorkitem PWI on PWI.PrimaryWorkitemKey=WI.WorkitemKey
            join Dim.Project P on P.ProjectKey=WI.ProjectKey
            join Dim.Iteration I on I.IterationKey=WI.IterationKey
            join Tree.ProjectParent PP on PP.DescendantKey=WI.ProjectKey
            join Dim.Project RP on RP.ProjectKey=PP.AncestorKey
            where
        WI.DateKey=@lastDateKey and RP.ProjectName='Company'

Project Roadmap

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

            select ProjectName, IterationName, sum(Estimate) Estimate
            from Fact.Workitem WI
            join Dim.Iteration I on I.IterationKey=WI.IterationKey
            join Dim.Project P on P.ProjectKey=WI.ProjectKey
            where WI.DateKey=case when IterationEndDateKey < @lastDateKey then IterationEndDateKey else @lastDateKey end
        group by ProjectName, IterationName

Parking Lot Report

            declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem
            declare @project nvarchar(4000); select @project='Release 1.0'

            select ThemeArea, ThemeName, StoryCount, DefectCount, ActiveEstimate, ClosedEstimate
            from Dim.Theme
            left join (
            select ThemeKey, Story StoryCount, Defect DefectCount
            from (
            select ThemeKey, AssetTypeName
            from Fact.Workitem
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.AssetType on AssetType.AssetTypeKey=Workitem.AssetTypeKey
            where Workitem.DateKey=@lastDateKey and ProjectName=@project
            ) X
            pivot (count(AssetTypeName) for AssetTypeName in (Story, Defect)) pvt
            ) Counts on Counts.ThemeKey=Theme.ThemeKey
            left join (
            select ThemeKey, Active ActiveEstimate, Closed ClosedEstimate
            from (
            select ThemeKey, AssetStateName, Estimate
            from Fact.Workitem
            join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.AssetState on AssetState.AssetStateKey=Workitem.PrimaryAssetStateKey
            where Workitem.DateKey=@lastDateKey and ProjectName=@project
            ) X
            pivot (sum(Estimate) for AssetStateName in (Active, Closed)) pvt
        ) Estimates on Estimates.ThemeKey=Theme.ThemeKey