| Table of Contents | ||||
|---|---|---|---|---|
|
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
...
Ticket
...
TicketHistory
...
Worklog
...
Sprints
...
Releases
...
Version Control
...
Branch
...
Commit
...
PullRequest
...
Tag
...
Overview
The "Datasets" section is a fundamental part of custom metrics, where users can access and manage various data essential for creating metrics, charts, and reports. This section serves as the foundation for data-driven decision-making and analysis within your system.
Within the "Datasets" section, users will find several main sections, each catering to a specific type of dataset:
Common Datasets: This section contains data sources that have been pre-configured in Perf, simplifying data access for users. Common datasets typically include Task Tracking Systems (TTS) and Version Control Systems. Users can seamlessly integrate these data sources into their metric-building processes.
Uploaded Datasets: In this section, users can access datasets that were manually uploaded in CSV format. These datasets provide flexibility for users to bring in custom data for analysis and reporting.
Custom Datasets: Custom datasets are data sources that are connected via API using custom data loaders. Users can tap into external systems and databases to bring in specific data relevant to their metrics. Custom datasets offer extensive flexibility and customization options.
Catalog Metric Data: This section houses data retrieved from the catalog metrics. Users can access pre-defined metrics and incorporate them into their analyses, streamlining the process of metric creation.
Structure and Features
Here are some important structural features of the "Datasets" section. Each of the main sections listed above is further divided into sub-sections, providing users with organized access to their datasets.
Tables: Within each sub-section, users will find tables that represent specific datasets. These tables display key information and options for each dataset.
Columns: Tables within the sub-sections include various columns, each designed to display specific data attributes or properties. These columns can include data types such as Array, Boolean, Date, JSON, Numeric, and Text, providing users with diverse data-handling capabilities.
| Info |
|---|
If Datasets section displayes “No Data” it’s mean that the data source is not configured or there are no any uploaded file. |
| Info |
|---|
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.
| Info |
|---|
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:
| Code Block |
|---|
| 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:
| Code Block |
|---|
| 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
| Code Block |
|---|
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
...
Data Types
Each filed has an icon to display type of a data in them:
 | Text |
 | Date |
 | Numeric |
 | Array |
 | Boolean |
 | JSON |
| Info |
|---|
Note:
|
| Anchor | ||||
|---|---|---|---|---|
|
...
| Code Block | ||
|---|---|---|
| ||
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
...
| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|