Common Datasets
- Anastasia Baranouskaya
Overview
The Common Datsets section provides users with access to essential data sources that are frequently used to construct meaningful metrics and charts. These data sources are configured in the Configure Data Source/ Data sources tab.
Available Common Data Sources
Task Tracking System
Jira
Rally
Azure DevOps Board
Version Control
Azure Repos GIT
BitBucket
BitBucket Server (Stash)
GitHub
GitLab
Each data source comprises several datasets (tables) that store various types of data collected during data loads.
Data Source type | Dataset |
|---|---|
Task Tracking System | Ticket |
TicketHistory | |
Worklog | |
Sprints | |
Releases | |
Version Control | Branch |
Commit | |
PullRequest | |
Tag |
Azure Board (TFS/VSTS) has fields with names that differ between Custom Metrics and for metric development, Perf names should be used:
State - Perf: Status
Area - Perf: Components
Iteration - Perf: Sprints
Tag - Perf: Labels
If you need to create metric based on data from different tables you should create PerfQL request (more details Custom Selection (PerfQL) and Joins Between Tables).
To build a custom metric with using historical data you can use not only TicketHistory table but also a function ticket_snapshots.
Ticket_snapshots
A snapshot is a representation of tickets within a defined time interval, allowing for a quick view of the most recent ticket activity over specific periods. Ticket_snapshots is presented in the “Project Settings” section of the PerfQL Selection step.
Examples:
To get historical data of tickets changes with type “Bug” and which have already moved in done status:
select * from ticket_snapshots(
ARRAY(select key from ticket where type = 'Bug'))
sn where is_done(sn);To get list of tickets changes in the specified time interval and date range:
select * from ticket_snapshots(
ARRAY(select key from ticket),
'week',
'2023-02-17 14:24:15.000',
'2023-03-17 14:24:15.000'
);
To get historical data for the specific tickets with interval - 1 month
select * from ticket_snapshots(
ARRAY['EPMTEST-0001', 'EPMTEST-0003'],
'month'
)Join Task Tracking datasets between each other
By default, Custom Metric v2 is created based on one table at a time. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables and create a chart based on them.
PERF tables could be joined with using the following conditions:
Table | Condition |
|---|---|
Ticket JOIN with | |
TicketHistory | Ticket.id=TicketHistory.workitem_id |
Worklog | Ticket.id=Worklog.workitem_id |
Sprint | Sprint.id=any(Ticket.sprints) |
Release | Release.id=any(Ticket.fix_releases) |
TicketHistory JOIN with | |
Ticket | TicketHistory.workitem_id=Ticket.id |
Worklog | TicketHistory.workitem_id=Worklog.workitem_id |
Sprint | Based on situation |
Release | Based on situation |
Worklog JOIN with | |
Ticket | Worklog.workitem_id=Ticket.id |
TicketHistory | Worklog.workitem_id=TicketHistory.workitem_id |
Sprint | Based on situation |
Release | Based on situation |
Sprint JOIN with | |
Ticket | Sprint.id=any(Ticket.sprints) |
TicketHistory | Based on situation |
Worklog | Based on situation |
Mapping Azure DevOps Board with Ticket table
This mapping allows you to effectively use Azure DevOps Board data within the Ticket table for metric development and analysis.
Azure DevOps Board | Custom Metric |
Team Project | Subject |
Id | Id |
Id | Key |
Title | Summary |
Work Item Type | Type |
State | Status |
Priority | Priority |
Resolution | Resolution |
Created_by | Reported_by |
Assigned to | Assigned_to |
Created date | Created |
Changed date | Updated |
Resolved Date | Resolved |
Story points | Story_points |
Completed Work (in hours) | Time_Spent (in minutes) |
Original Estimate | Time_estimate |
Remaining Work | Time_remaining |
Tags | Labels |
Area Path | compoments |
| Sprints |
Child | Sub_tasks |
Parent | Parent_task |
Parent (for Story) | Epic_key |
Custom_fields | Custom_fields |
Closed Date | Done_date |
Due Date | due_date |
Resolved By | resolved_by |
Severity | severity |