Data Mart Query Examples

This reference provides SQL query examples for common reporting scenarios against the Digital.ai Agility Data Mart.

Overview

The following query examples demonstrate how to extract meaningful data from the Data Mart for reporting and analysis. All examples use standard SQL and can be adapted to your specific needs.

tip

These are starting points. Modify table names, column selections, and WHERE clauses to match your specific reporting requirements.

note

Replace project names, iteration names, and date ranges with values from your Digital.ai Agility instance.

Count Queries

Count queries help you track the number of workitems that meet specific criteria over time.

Story Trend by Priority

Count active stories in a project, grouped by priority over time.

SELECT 
    DateKey, 
    WorkitemPriorityName as [Priority], 
    COUNT(*) as [Count]
FROM Fact.Workitem WI
JOIN Tree.ProjectParent T ON T.DescendantKey = WI.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON P.ProjectKey = T.AncestorKey
JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
JOIN Dim.WorkitemPriority WIP ON WI.WorkitemPriorityKey = WIP.WorkitemPriorityKey
WHERE AssetTypeName = 'Story' 
  AND ProjectName = 'Call Center' 
  AND AssetStateName = 'Active'
GROUP BY DateKey, WorkitemPriorityName
ORDER BY DateKey

Story Trend by Team

Count active stories in a project, grouped by team over time.

SELECT 
    DateKey, 
    TeamName as [Team], 
    COUNT(*) as [Count]
FROM Fact.Workitem WI
JOIN Tree.ProjectParent T ON T.DescendantKey = WI.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON P.ProjectKey = T.AncestorKey
JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
JOIN Dim.Team TM ON WI.TeamKey = TM.TeamKey
WHERE AssetTypeName = 'Story' 
  AND ProjectName = 'Call Center' 
  AND AssetStateName = 'Active'
GROUP BY DateKey, TeamName
ORDER BY DateKey

Test Trend

Track the number of tests over time.

SELECT 
    DateKey, 
    COUNT(*) as [Count]
FROM Fact.Workitem WI
JOIN Tree.ProjectParent T ON T.DescendantKey = WI.ProjectKey
JOIN Dim.AssetState S ON WI.SecondaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON P.ProjectKey = T.AncestorKey
JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
WHERE AssetTypeName = 'Test' 
  AND ProjectName = 'Call Center' 
  AND AssetStateName = 'Active'
GROUP BY DateKey
ORDER BY DateKey

Defect Trend

Track the number of defects over time.

SELECT 
    DateKey, 
    COUNT(*) as [Count]
FROM Fact.Workitem WI
JOIN Tree.ProjectParent T ON T.DescendantKey = WI.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON P.ProjectKey = T.AncestorKey
JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
WHERE AssetTypeName = 'Defect' 
  AND ProjectName = 'Call Center' 
  AND AssetStateName = 'Active'
GROUP BY DateKey
ORDER BY DateKey

Custom Measure Queries

Query custom fields (custom measures) defined on workitems.

Custom Measure on Tasks

Sum a custom measure field on tasks for a specific iteration.

SELECT 
    DateKey, 
    SUM(TaskCustom_HighDetailEstimate) [HighDetailEstimate]
FROM Fact.Workitem WI
JOIN Dim.AssetState S ON WI.SecondaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
JOIN Dim.SecondaryWorkitem SWI ON WI.SecondaryWorkitemKey = SWI.SecondaryWorkitemKey
WHERE IterationName = 'Month C 1st Half' 
  AND AssetStateName = 'Active' 
  AND SecondaryWorkitemType = 'Task' 
  AND ProjectName = 'Release 1.0'
GROUP BY DateKey
ORDER BY DateKey

Custom Measure on Primary Workitems

Sum a custom business value field on closed primary workitems.

SELECT 
    D.Datekey, 
    SUM(PrimaryWorkitemCustom_BusinessValue) [Business Value]
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 A ON A.AssetStateKey = WI.PrimaryAssetStateKey
JOIN Dim.Date D ON D.DateKey = WI.DateKey
WHERE AssetStateName = 'Closed' 
  AND ProjectName = 'Call Center'
GROUP BY D.DateKey
ORDER BY D.DateKey

note

Custom measure column names follow the pattern [AssetType]Custom_[FieldName]. Replace with actual custom field names from your instance.

Effort Queries

Track effort (time spent) logged against workitems.

Effort Trend

Calculate cumulative effort over a date range.

DECLARE @startDate datetime, @days int
SELECT @startDate = '2026-01-01', @days = 60

SELECT 
    Date.DateKey, 
    ISNULL(SUM(Effort.Effort), 0) EffortToDate
FROM Dim.Date
LEFT JOIN (
    SELECT DateKey, Effort
    FROM Fact.Effort
    JOIN Tree.ProjectParent T ON T.DescendantKey = Effort.ProjectKey
    JOIN Dim.Project RootProject ON RootProject.ProjectKey = T.AncestorKey
    WHERE RootProject.ProjectName = 'Call Center'
) Effort ON Effort.DateKey <= Date.DateKey
WHERE Date.Date >= @startDate 
  AND Date.Date < DATEADD(D, @days, @startDate)
GROUP BY Date.DateKey
ORDER BY Date.DateKey

Effort Trend for a Specific Member

Track cumulative effort for an individual team member.

DECLARE @startDate datetime, @days int
SELECT @startDate = '2026-01-01', @days = 60

SELECT 
    Date.DateKey,
    ISNULL(SUM(Effort.Effort), 0) EffortToDate
FROM Dim.Date
LEFT JOIN (
    SELECT DateKey, Effort
    FROM Fact.Effort
    JOIN Tree.ProjectParent T ON T.DescendantKey = Effort.ProjectKey
    JOIN Dim.Project RootProject ON RootProject.ProjectKey = T.AncestorKey
    JOIN Dim.Member M ON M.MemberKey = Effort.MemberKey
    WHERE RootProject.ProjectName = 'Call Center'
      AND M.MemberName = 'Joe Gehring'
) Effort ON Effort.DateKey <= Date.DateKey
WHERE Date.Date >= @startDate 
  AND Date.Date < DATEADD(D, @days, @startDate)
GROUP BY Date.DateKey
ORDER BY Date.DateKey

Effort Trend for a Member Group

Track cumulative effort for a group of team members.

DECLARE @startDate datetime, @days int
SELECT @startDate = '2026-01-01', @days = 60

SELECT 
    Date.DateKey,
    ISNULL(SUM(Effort.Effort), 0) EffortToDate
FROM Dim.Date
LEFT JOIN (
    SELECT DateKey, Effort
    FROM Fact.Effort
    JOIN Tree.ProjectParent T ON T.DescendantKey = Effort.ProjectKey
    JOIN Dim.Project RootProject ON RootProject.ProjectKey = T.AncestorKey
    JOIN Bridge.MemberGroup BMG ON BMG.MemberKey = Effort.MemberKey
    JOIN Dim.MemberGroup MG ON MG.MemberGroupKey = BMG.MemberGroupKey
    WHERE RootProject.ProjectName = 'Call Center'
      AND MG.MemberGroupName = 'Team A'
) Effort ON Effort.DateKey <= Date.DateKey
WHERE Date.Date >= @startDate 
  AND Date.Date < DATEADD(D, @days, @startDate)
GROUP BY Date.DateKey
ORDER BY Date.DateKey

Effort Trend for a Sprint

Track daily effort within a specific iteration.

SELECT 
    E.DateKey, 
    SUM(E.Effort) AS [Effort]
FROM Fact.Effort E
JOIN Dim.Iteration I ON E.IterationKey = I.IterationKey
JOIN Dim.Project P ON E.ProjectKey = P.ProjectKey
WHERE I.IterationName = 'Sprint 15' 
  AND P.ProjectName = 'Release 1.0'
GROUP BY E.DateKey
ORDER BY E.DateKey

Effort Trend for a Team

Track daily effort for a specific team.

SELECT 
    E.DateKey, 
    SUM(E.Effort) AS [Effort]
FROM Fact.Effort E
JOIN Dim.Team T ON E.TeamKey = T.TeamKey
JOIN Dim.Project P ON E.ProjectKey = P.ProjectKey
WHERE T.TeamName = 'Platform Team' 
  AND P.ProjectName = 'Release 1.0'
GROUP BY E.DateKey
ORDER BY E.DateKey

Current Member Load

Show current workload (ToDo) for each team member.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Workitem

SELECT 
    M.MemberName,
    SUM(WI.ToDo) AS [Current Load]
FROM Fact.Workitem WI
JOIN Dim.Member M ON WI.PrimaryOwnerKey = M.MemberKey
JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
JOIN Dim.AssetState A ON A.AssetStateKey = WI.PrimaryAssetStateKey
WHERE WI.DateKey = @dateKey
  AND P.ProjectName = 'Call Center'
  AND A.AssetStateName = 'Active'
GROUP BY M.MemberName
ORDER BY [Current Load] DESC

Estimate Queries

Track estimate values for workitems.

Estimate Trend for Stories

Track total estimate for active stories over time.

SELECT 
    WI.DateKey, 
    SUM(WI.Estimate) AS [Total Estimate]
FROM Fact.Workitem WI
JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
WHERE AT.AssetTypeName = 'Story'
  AND P.ProjectName = 'Release 1.0'
  AND S.AssetStateName = 'Active'
GROUP BY WI.DateKey
ORDER BY WI.DateKey

Estimate by Priority

Group estimate totals by priority for a specific date.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Workitem

SELECT 
    WP.WorkitemPriorityName,
    SUM(WI.Estimate) AS [Total Estimate]
FROM Fact.Workitem WI
JOIN Dim.WorkitemPriority WP ON WI.WorkitemPriorityKey = WP.WorkitemPriorityKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
WHERE WI.DateKey = @dateKey
  AND P.ProjectName = 'Release 1.0'
  AND S.AssetStateName = 'Active'
GROUP BY WP.WorkitemPriorityName
ORDER BY [Total Estimate] DESC

Detail Estimate Queries

Work with detail estimates (sum of task/test estimates).

Detail Estimate for Primary Workitems

Track detail estimate trend for stories and defects.

SELECT 
    WI.DateKey,
    SUM(WI.DetailEstimate) AS [Detail Estimate]
FROM Fact.PrimaryWorkitem WI
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE P.ProjectName = 'Release 1.0'
  AND S.AssetStateName = 'Active'
GROUP BY WI.DateKey
ORDER BY WI.DateKey

Active Detail Estimate with Subtotals

Calculate detail estimate with subtotals by project and iteration.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Workitem

SELECT 
    P.ProjectName,
    I.IterationName,
    SUM(WI.DetailEstimate) AS [Detail Estimate]
FROM Fact.Workitem WI
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE WI.DateKey = @dateKey
  AND S.AssetStateName = 'Active'
GROUP BY ROLLUP(P.ProjectName, I.IterationName)
ORDER BY P.ProjectName, I.IterationName

Issue Queries

Query issue tracking data.

Open Issues by Project

Count open issues grouped by project.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Issue

SELECT 
    P.ProjectName,
    COUNT(*) AS [Open Issues]
FROM Fact.Issue I
JOIN Dim.Project P ON I.ProjectKey = P.ProjectKey
JOIN Dim.AssetState A ON I.AssetStateKey = A.AssetStateKey
WHERE I.DateKey = @dateKey
  AND A.AssetStateName = 'Active'
GROUP BY P.ProjectName
ORDER BY [Open Issues] DESC

Issues by Priority and Category

Analyze issues by priority and category.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Issue

SELECT 
    IP.IssuePriorityName,
    IC.IssueCategoryName,
    COUNT(*) AS [Issue Count]
FROM Fact.Issue I
JOIN Dim.IssuePriority IP ON I.PriorityKey = IP.IssuePriorityKey
JOIN Dim.IssueCategory IC ON I.CategoryKey = IC.IssueCategoryKey
JOIN Dim.AssetState A ON I.AssetStateKey = A.AssetStateKey
WHERE I.DateKey = @dateKey
  AND A.AssetStateName = 'Active'
GROUP BY IP.IssuePriorityName, IC.IssueCategoryName
ORDER BY IP.IssuePriorityName, IC.IssueCategoryName

Request Queries

Query request (feature request) data.

Requests by Status

Count requests grouped by status.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Request

SELECT 
    RS.RequestStatusName,
    COUNT(*) AS [Request Count]
FROM Fact.Request R
JOIN Dim.RequestStatus RS ON R.RequestStatusKey = RS.RequestStatusKey
WHERE R.DateKey = @dateKey
GROUP BY RS.RequestStatusName
ORDER BY [Request Count] DESC

Requests by Priority

Analyze requests by priority level.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Request

SELECT 
    RP.RequestPriorityName,
    COUNT(*) AS [Request Count]
FROM Fact.Request R
JOIN Dim.RequestPriority RP ON R.PriorityKey = RP.RequestPriorityKey
WHERE R.DateKey = @dateKey
GROUP BY RP.RequestPriorityName
ORDER BY [Request Count] DESC

Test Run Queries

Query test execution data.

Test Runs by Status

Count test runs grouped by status.

SELECT 
    TS.TestStatusName,
    COUNT(*) AS [Test Run Count]
FROM Fact.TestRun TR
JOIN Dim.TestStatus TS ON TR.TestStatusKey = TS.TestStatusKey
JOIN Dim.Date D ON TR.DateKey = D.DateKey
WHERE D.Date >= '2026-01-01' 
  AND D.Date < '2026-02-01'
GROUP BY TS.TestStatusName
ORDER BY [Test Run Count] DESC

Test Runs by Build

Track test runs associated with specific builds.

SELECT 
    BP.BuildProjectName,
    TS.TestStatusName,
    COUNT(*) AS [Test Run Count]
FROM Fact.TestRun TR
JOIN Dim.BuildProject BP ON TR.BuildProjectKey = BP.BuildProjectKey
JOIN Dim.TestStatus TS ON TR.TestStatusKey = TS.TestStatusKey
JOIN Dim.Date D ON TR.DateKey = D.DateKey
WHERE D.Date >= '2026-01-01' 
  AND D.Date < '2026-02-01'
GROUP BY BP.BuildProjectName, TS.TestStatusName
ORDER BY BP.BuildProjectName, TS.TestStatusName

ToDo Queries

Track remaining work (ToDo) on workitems.

ToDo Trend

Track total remaining work over time.

SELECT 
    WI.DateKey,
    SUM(WI.ToDo) AS [Total ToDo]
FROM Fact.Workitem WI
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE P.ProjectName = 'Release 1.0'
  AND S.AssetStateName = 'Active'
GROUP BY WI.DateKey
ORDER BY WI.DateKey

ToDo by Team

Show current ToDo grouped by team.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Workitem

SELECT 
    T.TeamName,
    SUM(WI.ToDo) AS [Team ToDo]
FROM Fact.Workitem WI
JOIN Dim.Team T ON WI.TeamKey = T.TeamKey
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE WI.DateKey = @dateKey
  AND P.ProjectName = 'Release 1.0'
  AND S.AssetStateName = 'Active'
GROUP BY T.TeamName
ORDER BY [Team ToDo] DESC

Miscellaneous Queries

Additional useful query patterns.

Velocity by Iteration

Calculate team velocity (Done) for each iteration.

SELECT 
    I.IterationName,
    I.IterationBeginDate,
    I.IterationEndDate,
    SUM(WI.Done) AS [Velocity]
FROM Fact.Workitem WI
JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.Date D ON WI.DateKey = D.DateKey
WHERE P.ProjectName = 'Release 1.0'
  AND D.Date = I.IterationEndDate
GROUP BY I.IterationName, I.IterationBeginDate, I.IterationEndDate
ORDER BY I.IterationBeginDate

Workitem Age

Calculate how long workitems have been in the current project.

DECLARE @dateKey int, @currentDate datetime
SELECT @dateKey = MAX(DateKey) FROM Fact.Workitem
SELECT @currentDate = Date FROM Dim.Date WHERE DateKey = @dateKey

SELECT 
    PW.PrimaryWorkitemNumber,
    PW.PrimaryWorkitemName,
    PW.PrimaryWorkitemCreateDateUTC,
    DATEDIFF(DAY, PW.PrimaryWorkitemCreateDateUTC, @currentDate) AS [Age in Days]
FROM Fact.Workitem WI
JOIN Dim.PrimaryWorkitem PW ON WI.PrimaryWorkitemKey = PW.PrimaryWorkitemKey
JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE WI.DateKey = @dateKey
  AND P.ProjectName = 'Release 1.0'
  AND S.AssetStateName = 'Active'
ORDER BY [Age in Days] DESC

Epic Progress

Track progress on epics by comparing done vs. estimate.

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Epic

SELECT 
    E.EpicName,
    SUM(WI.Estimate) AS [Total Estimate],
    SUM(WI.Done) AS [Total Done],
    CASE 
        WHEN SUM(WI.Estimate) > 0 THEN 
            (SUM(WI.Done) / SUM(WI.Estimate)) * 100
        ELSE 0 
    END AS [Percent Complete]
FROM Fact.Workitem WI
JOIN Dim.Epic E ON WI.EpicKey = E.EpicKey
JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE WI.DateKey = @dateKey
  AND S.AssetStateName = 'Active'
GROUP BY E.EpicName
ORDER BY [Percent Complete] DESC

Query Optimization Tips

Use Indexes Effectively

Fact Tables:

Always filter by DateKey for semi-additive facts
Include dimension foreign keys in JOIN conditions
Use appropriate WHERE clause to limit result sets

Dimension Tables:

Filter by Name columns (e.g., ProjectName, IterationName)
Use AssetStateName to filter active vs. closed items
Join on Key columns (indexed foreign keys)

Performance Best Practices

Date Filtering:

-- Good: Filter on DateKey
WHERE WI.DateKey = @specificDateKey

-- Better for ranges: Use Date dimension
JOIN Dim.Date D ON WI.DateKey = D.DateKey
WHERE D.Date >= '2026-01-01' AND D.Date < '2026-02-01'

Project Hierarchy:

-- Use Tree.ProjectParent for hierarchical queries
JOIN Tree.ProjectParent T ON T.DescendantKey = WI.ProjectKey
JOIN Dim.Project P ON P.ProjectKey = T.AncestorKey
WHERE P.ProjectName = 'Root Project'

Avoid:

Querying semi-additive facts without date filtering
Using SELECT * when you only need specific columns
Joining unnecessary dimensions
Cartesian products (missing JOIN conditions)

Common Patterns

Latest Date Pattern:

DECLARE @dateKey int
SELECT @dateKey = MAX(DateKey) FROM Fact.Workitem
-- Then use @dateKey in WHERE clause

Date Range Pattern:

DECLARE @startDate datetime, @endDate datetime
SELECT @startDate = '2026-01-01', @endDate = '2026-02-01'

JOIN Dim.Date D ON WI.DateKey = D.DateKey
WHERE D.Date >= @startDate AND D.Date < @endDate

Active Items Pattern:

JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
WHERE S.AssetStateName = 'Active'

Fact Tables Reference - Complete fact table documentation
Dimension Tables Reference - Complete dimension table documentation
Bridge Tables - Many-to-many relationship tables
Data Mart - Data Mart overview and architecture
Build Custom Reports - Use queries in custom reports

Overview​

Count Queries​

Story Trend by Priority​

Story Trend by Team​

Test Trend​

Defect Trend​

Custom Measure Queries​

Custom Measure on Tasks​

Custom Measure on Primary Workitems​

Effort Queries​

Effort Trend​

Effort Trend for a Specific Member​

Effort Trend for a Member Group​

Effort Trend for a Sprint​

Effort Trend for a Team​

Current Member Load​

Estimate Queries​

Estimate Trend for Stories​

Estimate by Priority​

Detail Estimate Queries​

Detail Estimate for Primary Workitems​

Active Detail Estimate with Subtotals​

Issue Queries​

Open Issues by Project​

Issues by Priority and Category​

Request Queries​

Requests by Status​

Requests by Priority​

Test Run Queries​

Test Runs by Status​

Test Runs by Build​

ToDo Queries​

ToDo Trend​

ToDo by Team​

Miscellaneous Queries​

Velocity by Iteration​

Workitem Age​

Epic Progress​

Query Optimization Tips​

Use Indexes Effectively​

Performance Best Practices​

Common Patterns​

Related Topics​