Data model for Analytics

Azure DevOps Services | Azure DevOps Server | Azure DevOps Server 2022 | Azure DevOps Server 2020

The Analytics data model for Azure DevOps consists of entity sets, whose members (entities) contain properties that you can filter, aggregate, and summarize. Additionally, they contain navigation properties that relate entities to one other, providing access to other properties for selecting, filtering, and grouping.

Understanding the Analytics data model

The Analytics service provides a structured approach to accessing Azure DevOps data through OData endpoints. This data model enables you to:

• Query work tracking data: Access work items, areas, iterations, and related metadata
• Analyze pipeline information: Query build and release pipeline data
• Report on test results: Access test execution and planning data
• Create custom reports: Build Power BI reports and other analytics solutions

Schema namespaces

The Analytics data model operates on two schema namespaces:

• Microsoft.VisualStudio.Services.Analytics.Model
• Microsoft.VisualStudio.Services.Analytics

These namespaces organize the entities and define their structure, ensuring consistent data access patterns across different Azure DevOps features.

Entity sets and entity types

Entity types are named structured types with a key. They define the named properties and relationships of each entity. The key of an EntityType forms from a subset of the primitive properties—for example, WorkItemId, PipelineId, ReleasePipelineId—and other properties of the entity type.

Entity sets are named collections of entities. For example, WorkItems is an entity set containing WorkItem entities. An entity's key uniquely identifies the entity within an entity set. If multiple entity sets use the same entity type, the same combination of key values can appear in more than one entity set and identifies different entities, one per entity set where this key combination appears. Each of these entities has a different entity-id. Entity sets provide entry points into the data model.

Entity sets are described in OData metadata, and vary by project. You can explore the complete list of entity sets, entity types, and properties by requesting the OData metadata for your project. To learn how, see Construct OData queries for Analytics.

Composite entities

Composite entities support specific scenarios. The system composes them from simpler entities, often requires more computing resources to generate, and may return larger result sets. To achieve the best performance and avoid unnecessary throttling, ensure that you query the correct entity for your scenario.

For example, WorkItemSnapshot combines WorkItemRevisions and Dates such that each date has one revision for each work item. This representation supports OData queries that focus on trend data for a filtered set of work items. However, you shouldn't use this composite entity to query the current state of work items. Instead, you should use the WorkItems entity set to generate a more quickly running query.

Similarly, some entities may contain all historic values, while others may only contain current values. WorkItemRevisions contains all work item history, which you shouldn't use in scenarios where the current values are of interest.

Relationships

To generate more complex query results, you can combine entities using relationships. You can employ relationships to expand, filter, or summarize data.

Some navigation properties result in a single entity, while others result in a collection of entities. The following diagram shows select entities and their navigation properties. For clarity, some composite entities and relationships have been omitted.

Understanding entity relationships

The Analytics data model uses several types of relationships:

• One-to-many: A single parent entity relates to multiple child entities (e.g., one Area to many WorkItems)
• Many-to-one: Multiple entities relate to a single parent entity (e.g., many WorkItems to one Area)
• One-to-one: A single entity relates to exactly one other entity
• Many-to-many: Multiple entities relate to multiple other entities (e.g., WorkItems to Tags)

Relationship keys

Entity relationships are also represented as foreign keys so that external tools can join entities. These properties have the suffix "SK", and are either integer or GUID data types. Date properties have corresponding integer date key properties with the following format: YYYYMMDD.

Work tracking entity types and entity sets

The following entity types and entity sets are supported with the indicated API versions. For a complete reference, see Work tracking metadata reference for Azure Boards Analytics.

Key work tracking entities for reporting

When building reports, consider these primary entity sets:

• Current state reports: Use WorkItems for current work item status
• Historical trend reports: Use WorkItemSnapshot for trend analysis over time
• Detailed change tracking: Use WorkItemRevisions for comprehensive history
• Board-specific reports: Use WorkItemBoardSnapshot for Kanban board analysis

Best practices for using the Analytics data model

Performance optimization

1. Choose the right entity: Use current state entities (WorkItems) for status reports and snapshot entities for trends
2. Filter early: Apply filters at the entity level rather than after data retrieval
3. Limit data ranges: Use date filters to restrict historical data queries
4. Use appropriate aggregations: Leverage built-in aggregation functions when possible

Query design patterns

Current state queries

/WorkItems?$filter=State ne 'Closed'&$select=WorkItemId,Title,State

Historical trend queries

/WorkItemSnapshot?$filter=DateSK ge 20241001&$select=WorkItemId,State,DateSK

Relationship navigation

/WorkItems?$expand=Area($select=AreaPath),AssignedTo($select=UserName)

Common pitfalls to avoid

• Using revision entities for current state: Don't use WorkItemRevisions when WorkItems suffices
• Over-expanding relationships: Only expand necessary navigation properties
• Missing filters: Always filter large entity sets to improve performance
• Ignoring composite entities: Use composite entities like WorkItemSnapshot for trend analysis

Version considerations

Different API versions provide different capabilities:

• v1.0: Basic work tracking entities
• v2.0: Added process and enhanced filtering capabilities
• v3.0-preview: Added pipeline and test entities
• v4.0-preview: Enhanced composite entities and additional pipeline metrics

Choose the appropriate version based on your reporting requirements and the entities you need to access.

Related articles

• OData Analytics query guidelines
• WIT analytics
• Aggregate data
• Exploring Analytics OData metadata
• Work tracking, process, and project limits
• Analytics views dataset design
• Construct OData queries for Analytics
• Analytics security and permissions