Fact Tables Reference

Fact tables in the Digital.ai Agility Data Mart contain measurement data used in reports. This data can be analyzed or classified by information available in dimension tables.

Overview

In a star schema, fact tables contain the measurement data being used in reports. In the Digital.ai Agility Data Mart, all Fact tables are in a database namespace (schema) known as "Fact".

Fact Table Types

The Data Mart includes the following primary fact tables:

• Fact.Workitem - Metrics for all workitems (primary and secondary)
• Fact.PrimaryWorkitem - Metrics specific to Stories, Defects, and TestSets
• Fact.Effort - Time tracking entries
• Fact.Epic - Epic trend data over time
• Fact.EpicDaysIn - Days an Epic spent in each status
• Fact.EpicTest - Epic test information
• Fact.Issue - Daily changes to Issues
• Fact.PrimaryWorkitemDaysIn - Days a workitem spent in each status
• Fact.ProjectSummary - Project summary trend data
• Fact.Request - Daily changes to Requests
• Fact.Test - Test execution details
• Fact.TestRun - Test run records
• Additional merged fact tables - Created during ETL and merged into Fact.Workitem

note

All numeric columns in fact tables are designed for aggregation. Check the semi-additive versus additive designation to understand how metrics accumulate over time.

Fact.Workitem

The Fact.Workitem table contains metrics available on any workitem, including both primary workitems (Stories, Defects, TestSets) and secondary workitems (Tasks, Tests).

Overview

• Granularity: One row per workitem per day
• Metrics Type: Semi-additive (cumulative only for a given day)
• Use Case: Comprehensive workitem reporting across all types

Available Metrics

• Estimate - The Estimate attribute on Primary Workitems
• Detail Estimate - Detail Estimate on all workitems
• ToDo - Remaining work on all workitems
• Done - Effort recorded against workitems (not always historically accurate)
• Custom Fields - Supported custom fields defined on workitems

note

The Done attribute represents the amount of Effort recorded against the workitem regardless of other attributes. The value is correct but not always historically accurate. For historically accurate effort data, see Fact.Effort.

Table Columns

Column Name	Description	Datatype	Foreign Key To
DateKey	Date of this measure	int	Dim.Date
WorkitemKey	Data Mart identifier for the PrimaryWorkitemKey or SecondaryWorkitemKey	int
AssetTypeKey	Data Mart identifier for the type of workitem	int	Dim.AssetType
PrimaryWorkitemKey	Data Mart identifier for the Story or Defect	int	Dim.PrimaryWorkitem
SplitFromKey	Data Mart identifier for the Primary Workitem this was split from	int	Dim.PrimaryWorkitem
ProjectKey	Data Mart identifier for the Project containing this workitem	int	Dim.Project
IterationKey	Data Mart identifier for the Iteration containing this workitem	int	Dim.Iteration
ThemeKey	Data Mart identifier for the Theme containing this workitem	int	Dim.Theme
TeamKey	Data Mart identifier for the Team assigned to this workitem	int	Dim.Team
CustomerKey	Data Mart identifier for the Member acting as Customer (Stories only)	int	Dim.Member
EpicKey	Data Mart identifier for the Epic (when workitem is part of an Epic)	int	Dim.Epic
WorkitemSourceKey	Data Mart identifier for Source of this workitem	int	Dim.WorkitemSource
WorkitemPriorityKey	Data Mart identifier for the Priority of this workitem	int	Dim.WorkitemPriority
WorkitemStatusKey	Data Mart identifier for the Status of this workitem	int	Dim.WorkitemStatus
StoryRiskKey	Data Mart identifier for Risk (Stories only)	int	Dim.StoryRisk
StoryTypeKey	Data Mart identifier for Type (Stories only)	int	Dim.StoryType
DefectTypeKey	Data Mart identifier for Type (Defects only)	int	Dim.DefectType
DefectResolutionKey	Data Mart identifier for the Defect Resolution	int	Dim.DefectResolution
VerifiedByKey	Data Mart identifier for the Member that verified the Defect	int	Dim.Member
RegressionSuiteKey	Data Mart identifier for the Regression Suite	int	Dim.RegressionSuite
EnvironmentKey	Data Mart identifier for the Environment	int	Dim.Environment
RegressionPlanKey	Data Mart identifier for the Regression Plan	int	Dim.RegressionPlan
PrimaryAssetStateKey	Data Mart identifier for the asset state of the Story or Defect	int	Dim.AssetState
ClassOfService	Data Mart identifier for the Class of Service	int	Dim.ClassOfService
SecondaryWorkitemKey	Data Mart identifier when the workitem is a Task or Test	int	Dim.SecondaryWorkitem
TaskStatusKey	Data Mart identifier for the Task Status	int	Dim.TaskStatus
TaskTypeKey	Data Mart identifier for the Task Type	int	Dim.TaskType
TaskSourceKey	Data Mart identifier for the Task Source	int	Dim.TaskSource
SecondaryAssetStateKey	Data Mart identifier for the asset state of the Task or Test	int	Dim.AssetState
TestTypeKey	Data Mart identifier for the Test Type	int	Dim.TestType
TestStatusKey	Data Mart identifier for the Test Status	int	Dim.TestStatus
RegressionTestKey	Data Mart identifier for the Regression Test	int	Dim.RegressionTest
Estimate	Estimate measure (Stories and Defects only)	real
DetailEstimate	Detail Estimate measure	real
ToDo	ToDo measure	real
Done	Effort measure	real

Fact.PrimaryWorkitem

The Fact.PrimaryWorkitem table contains metrics available on Primary Workitems (Stories, Defects, and TestSets).

Overview

• Granularity: One row per Primary Workitem per day
• Metrics Type: Semi-additive (cumulative only for a given day)
• Use Case: Reporting focused on Stories, Defects, and TestSets only

Available Metrics

• Estimate - The Estimate attribute on Primary Workitems
• Detail Estimate - Detail Estimate on all workitems
• ToDo - Remaining work on workitems
• Done - Effort recorded against the primary workitem (not always historically accurate)
• Custom Fields - Supported custom fields defined on Primary and Secondary Workitems

tip

Use Fact.PrimaryWorkitem when your reports focus exclusively on Stories, Defects, and TestSets. Use Fact.Workitem when you need to include Tasks and Tests in the same report.

Table Columns

Column Name	Description	Datatype	Foreign Key To
DateKey	Date of this measure	int	Dim.Date
WorkitemKey	The PrimaryWorkitemKey for this measure	int
AssetTypeKey	Data Mart identifier for the type of workitem	int	Dim.AssetType
PrimaryWorkitemKey	Data Mart identifier for the Story or Defect	int	Dim.PrimaryWorkitem
SplitFromKey	Data Mart identifier for the Primary Workitem this was split from	int	Dim.PrimaryWorkitem
ProjectKey	Data Mart identifier for the Project containing this workitem	int	Dim.Project
IterationKey	Identifier for the Theme containing this workitem	int	Dim.Iteration
ThemeKey	Data Mart identifier for the Iteration containing the workitem	int	Dim.Theme
TeamKey	Data Mart identifier for the Team assigned to this workitem	int	Dim.Team
CustomerKey	Data Mart identifier for the Member acting as Customer (Stories only)	int	Dim.Member
EpicKey	Data Mart identifier for the Epic (when Story is part of an Epic)	int	Dim.Epic
WorkitemSourceKey	Data Mart identifier for Source of this workitem	int	Dim.WorkitemSource
WorkitemPriorityKey	Data Mart identifier for the Priority of this workitem	int	Dim.WorkitemPriority
WorkitemStatusKey	Data Mart identifier for the Status of this workitem	int	Dim.WorkitemStatus
StoryRiskKey	Data Mart identifier for Risk (Stories only)	int	Dim.StoryRisk
StoryTypeKey	Data Mart identifier for Type (Stories only)	int	Dim.StoryType
DefectTypeKey	Data Mart identifier for Type (Defects only)	int	Dim.DefectType
DefectResolutionKey	Data Mart identifier for the Defect Resolution	int	Dim.DefectResolution
VerifiedByKey	Data Mart identifier for the Member that verified the Defect	int	Dim.Member
RegressionSuiteKey	Data Mart identifier for the Regression Suite	int	Dim.RegressionSuite
EnvironmentKey	Data Mart identifier for the Environment	int	Dim.Environment
RegressionPlanKey	Data Mart identifier for the Regression Plan	int	Dim.RegressionPlan
PrimaryAssetStateKey	Data Mart identifier for the state this workitem	int	Dim.AssetState
ClassOfServiceKey	Data Mart identifier for the Class Of Service	int	Dim.ClassOfService
Estimate	Estimate measure	real
DetailEstimate	Detail Estimate measure (Note 1)	real
ToDo	ToDo measure (Note 1)	real
Done	Done measure (Note 1)	real

note

Note 1: These fields are populated based on the Tracking Level settings in your Digital.ai Agility instance.

Fact.Effort

Effort is a measure of the amount of time spent working on a feature or defect. In the core application, effort is accumulated in the Done field.

Overview

• Granularity: One row per effort entry
• Metrics Type: Additive (does not accumulate over time)
• Use Case: Historically accurate time tracking reporting
• Historical Accuracy: All attributes are correct at the time the Effort was recorded and do not change

Purpose

Each row in the Fact.Effort table represents a single Effort entry and is historically accurate. This means attributes are recorded at the time effort is logged and remain unchanged even if related items move or change.

Example: If effort is recorded against a Defect in Project X and that Defect moves to Project Y, the Effort record does not reflect the move.

tip

For Effort based on current attribute values, use the Done column in Fact.PrimaryWorkitem, Fact.Workitem, or specific workitem fact tables.

Table Columns

Column Name	Description	Datatype	Foreign Key To
EffortKey	Datamart unique identifier for this entry	int
Audit	Identifier for this entry	int
DateKey	Date this entry was reported as Foreign Key relationship	int	Dim.Date
Date	Date this entry was reported	datetime
MemberKey	Member reporting effort	int	Dim.Member
WorkitemKey	Identifies the Workitem where effort was reported	int
ProjectKey	Identifies the Project where effort was reported	int	Dim.Project
IterationKey	Identifies the Iteration where effort was reported	int	Dim.Iteration
TeamKey	Identifies the Team reporting effort	int	Dim.Team
PrimaryWorkitemKey	Identifies the Primary Workitem associated with this entry	int	Dim.PrimaryWorkitem
SecondaryWorkitemKey	Identifies the Task or Test associated with this entry	int	Dim.SecondaryWorkitem
ThemeKey	Identifies the Theme associated with this entry	int	Dim.Theme
EpicKey	Identifies the Epic associated with this entry at the time effort was entered	int	Dim.Epic
Effort	Effort value	decimal(16,2)

Important Notes

1. Historical Accuracy: Attributes are correct at the time Effort is recorded and do not change. If a workitem moves to a different project or iteration after effort is logged, the Effort record continues to reference the original project/iteration.

2. Epic Association: Each Effort record is augmented with EpicKey by the ETL. Epic Key is the unique identifier for the Portfolio Item associated with the Story, Defect, or TestSet at the time the Effort was entered. If a Story moves from Epic A to Epic B, Fact.Effort will contain effort associated to both Epics.

3. Same as Source: Fact.Effort contains the same information as the source table in Digital.ai Agility, with additional Epic association added during ETL.

Fact.Epic

Use Fact.Epic to create reports that trend Epic data over time or report on Epic attribute values for a given day.

Overview

• Granularity: One row per Epic per day
• Metrics Type: Semi-additive (cumulative only for a given day)
• Use Case: Epic trending and historical analysis

note

To report on Epic attributes for a specific day, specify a Date filter that identifies the date of interest.

Table Columns

Key columns include:

• DateKey, EpicKey, ProjectKey, ProgramKey
• Epic status, priority, type dimensions
• Custom fields configured for Epics
• Estimate and effort metrics specific to Epics

For complete column details, query the Fact.Epic table schema in your Data Mart instance.

Fact.EpicDaysIn

The Fact.EpicDaysIn table contains information about how many days an Epic was in a given status value.

Overview

• Granularity: One row per Epic per status value
• Metrics Type: Count of days
• Use Case: Cycle time analysis for Epics
• DateKey: Always set to the maximum date value from Fact.Epic

caution

Oscillation between status values is not reflected in this table. Therefore, the DaysInStatus value may be greater than the value calculated by subtracting FirstDateInStatus from LastDateInStatus.

Table Columns

Column Name	Description	Datatype	Foreign Key To
DateKey	Maximum Date in Fact.Epic	int	Dim.Date
EpicKey	Data Mart identifier for the Epic	int	Dim.Epic
EpicStatusKey	Data Mart identifier for the Epic Status value	int	Dim.EpicStatus
DaysInStatus	The number of days this Epic has been in this status value	int
FirstDateInStatus	The first date this Epic was seen in this status value	datetime
LastDateInStatus	The last date this Epic was seen in this status value	datetime

Fact.EpicTest

The Fact.EpicTest table contains test information related to Epics.

Overview

• Granularity: One row per Epic test per day
• Metrics Type: Semi-additive
• Use Case: Epic test tracking and quality metrics

For complete column details, query the Fact.EpicTest table schema in your Data Mart instance.

Fact.Issue

The Fact.Issue table captures information about daily changes to Issues.

Overview

• Granularity: One row per Issue per day
• Metrics Type: Semi-additive (cumulative only for a given day)
• Use Case: Issue tracking and trend analysis
• Custom Fields: Supported custom fields are available

Table Columns

Column Name	Description	Datatype	Foreign Key To
DateKey	Date of this measure	int	Dim.Date
IssueKey	Datamart identifier for the Issue	int	Dim.Issue
ProjectKey	Datamart identifier for the project containing this issue	int	Dim.Project
TeamKey	Datamart identifier for the team assigned to this issue	int	Dim.Team
OwnerKey	Datamart identifier for the member responsible for this issue	int	Dim.Member
ResolutionKey	Datamart identifier for the issue resolution	int	Dim.IssueResolution
SourceKey	Datamart identifier for the source of this issue	int	Dim.WorkitemSource
PriorityKey	Datamart identifier for the issue priority	int	Dim.IssuePriority
CategoryKey	Datamart identifier for the issue category	int	Dim.IssueCategory
AssetStateKey	Datamart identifier for the asset state of this issue	int	Dim.AssetState

Fact.PrimaryWorkitemDaysIn

The Fact.PrimaryWorkitemDaysIn table tracks the number of days a workitem (Story, Defect, TestSet) spent in a given status value.

Overview

• Granularity: One row per Primary Workitem per status value
• Metrics Type: Count of days
• Use Case: Cycle time and lead time analysis for workitems

note

Similar to Fact.EpicDaysIn, oscillation between statuses is not reflected. The DaysInStatus value may exceed the calculated difference between FirstDateInStatus and LastDateInStatus.

Table Columns

Key columns include:

• DateKey, PrimaryWorkitemKey, WorkitemStatusKey
• DaysInStatus, FirstDateInStatus, LastDateInStatus
• Project, Team, and other dimension keys

For complete column details, query the Fact.PrimaryWorkitemDaysIn table schema in your Data Mart instance.

Fact.ProjectSummary

Use Fact.ProjectSummary to create reports that trend Project Summary data over time.

Overview

• Granularity: One row per Project per day
• Metrics Type: Semi-additive (cumulative only for a given day)
• Use Case: Project-level rollup metrics and trending

Table Columns

Key columns include:

• DateKey, ProjectKey
• Aggregate metrics (total estimate, total done, total to-do)
• Team and iteration associations
• Project status and health indicators

For complete column details, query the Fact.ProjectSummary table schema in your Data Mart instance.

Fact.Request

The Fact.Request table captures daily changes to Requests.

Overview

• Granularity: One row per Request per day
• Metrics Type: Semi-additive (cumulative only for a given day)
• Use Case: Request tracking and trend analysis
• Custom Fields: Supported custom fields are available

Table Columns

Key columns include:

• DateKey, RequestKey
• ProjectKey, TeamKey, OwnerKey
• Request status, priority, category dimensions
• Resolution tracking

For complete column details, query the Fact.Request table schema in your Data Mart instance.

Fact.Test

The Fact.Test table provides details about tests run in your environment.

Overview

• Granularity: One row per Test per day
• Metrics Type: Semi-additive
• Use Case: Test execution tracking and quality metrics

Table Columns

Key columns include:

• DateKey, TestKey
• Test status, type dimensions
• Associated workitem and regression test keys
• Custom fields configured for Tests

For complete column details, query the Fact.Test table schema in your Data Mart instance.

Fact.TestRun

The Fact.TestRun table records test runs in your environment.

Overview

• Granularity: One row per test run
• Metrics Type: Additive
• Use Case: Test execution history and quality trending

Table Columns

Key columns include:

• TestRunKey, DateKey
• Test and build associations
• Status and result tracking
• Elapsed time metrics

For complete column details, query the Fact.TestRun table schema in your Data Mart instance.

Using Fact Tables Effectively

Choosing the Right Fact Table

Use Fact.Workitem when:

• You need comprehensive reporting across all workitem types
• Reports include both primary workitems (Stories/Defects) and secondary workitems (Tasks/Tests)
• You want a single unified view of work

Use Fact.PrimaryWorkitem when:

• Reports focus exclusively on Stories, Defects, and TestSets
• You don't need Task or Test data
• You want simpler joins with fewer columns

Use Fact.Effort when:

• You need historically accurate time tracking
• Analysis requires knowing the context when effort was logged
• Reports track effort by the original project/iteration/Epic association

Use Fact.Epic when:

• Reporting on Epic-level metrics and trends
• Tracking Epic progress over time
• Portfolio-level analysis

Use DaysIn fact tables when:

• Calculating cycle time or lead time metrics
• Analyzing time spent in specific statuses
• Understanding workflow bottlenecks

Semi-Additive vs. Additive Metrics

Semi-Additive Metrics (Fact.Workitem, Fact.PrimaryWorkitem, Fact.Epic, Fact.Issue, Fact.Request):

• Metrics are cumulative only for a given day
• To report on a specific date, filter by that DateKey
• Aggregating across dates without proper filtering gives incorrect results
• Example: Summing Estimate across multiple days would count the same workitem multiple times

Additive Metrics (Fact.Effort, Fact.TestRun):

• Metrics can be summed across all dimensions including time
• Each row represents a distinct event
• Example: Summing Effort values across dates gives total effort for the period

Performance Optimization

Index Usage:

• All DateKey columns are indexed for efficient date filtering
• Dimension foreign keys are indexed for join performance
• Always include DateKey in your WHERE clause when querying semi-additive facts

Best Practices:

• Filter by DateKey early in queries to reduce result set size
• Use appropriate fact table granularity (don't use Fact.Workitem if Fact.PrimaryWorkitem suffices)
• Join to dimension tables only when you need their attributes
• Consider using views for complex frequently-used join patterns

Related Topics

• Dimension Tables Reference - Complete dimension table documentation
• Data Mart Query Examples - Sample queries using fact tables
• Data Mart - Data Mart overview and architecture
• Build Custom Reports - Create reports using fact tables
• Bridge Tables - Many-to-many relationship tables