Data Sources

Custom metrics can be created using the following data sources:

Task Tracking System
- Jira
- Rally
- Azure DevOps Board
Version Control
- Azure Repos GIT
- BitBucket
- BitBucket Server (Stash)
- GitHub
- GitLab

Data Source Tables

Each data source has several datasets (tables) with different types of data collected during data loads.

Data Source type	Table
Task Tracking System	Ticket
	TicketHistory
	Worklog
	Sprints
	Releases
Version Control	Branch
	Commit
	PullRequest
	Tag

If project has no configured data sources, then related tables will be empty.

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).

If data source is configured, then each table has a list of columns, which are added on the Axis X and Y.

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.

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'
)

Mapping Azure DevOps Board with Ticket table

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

Data Types

Each filed has an icon to display type of a data in them:

	Text
	Date
	Numeric
	Array
	Boolean
	JSON

Note:

Ability to add fields on the Axes and use the different Settings on the chart depend on fields type.
Azure Board (TFS/VSTS) has fields which names are differ in the Custom Metrics and for metric development should be used Perf names:
- State - Status
- Area - Components
- Iteration - Sprints
- Tag - Labels

Joins Between Tables

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.

SELECT * FROM table_A <JOIN_TYPE> table_B ON join_condition

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

Copyright © 2024 EPAM Systems, Inc.
All Rights Reserved. All information contained herein is, and remains the property of EPAM Systems, Inc. and/or its suppliers and is protected by international intellectual property law. Dissemination of this information or reproduction of this material is strictly forbidden, unless prior written permission is obtained from EPAM Systems, Inc.

PERF Datasets