Skip to main content

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