This is an automated email from the ASF dual-hosted git repository.
yumeng pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-devlake-website.git
The following commit(s) were added to refs/heads/main by this push:
new d81976123 docs: add SQL for issue and commit metrics (#329)
d81976123 is described below
commit d8197612368e1c9c99b6e67ea0f5f52ae79863c0
Author: Louis.z <[email protected]>
AuthorDate: Thu Nov 17 22:37:46 2022 +0800
docs: add SQL for issue and commit metrics (#329)
* docs: add jira demo
* docs: add SQL for issue and commit metrics
Co-authored-by: Startrekzky <[email protected]>
---
docs/Metrics/AddedLinesOfCode.md | 54 ++++++++++++++--
docs/Metrics/BugAge.md | 60 +++++++++++++++---
docs/Metrics/BugCountPer1kLinesOfCode.md | 70 +++++++++++++++++----
docs/Metrics/CommitAuthorCount.md | 24 ++++++-
docs/Metrics/CommitCount.md | 66 +++++++++++++------
docs/Metrics/DeletedLinesOfCode.md | 51 ++++++++++++++-
docs/Metrics/IncidentAge.md | 58 ++++++++++++++---
docs/Metrics/IncidentCountPer1kLinesOfCode.md | 67 +++++++++++++++++---
docs/Metrics/RequirementCount.md | 66 ++++++++++---------
docs/Metrics/RequirementDeliveryRate.md | 62 ++++++++++++++++--
docs/Metrics/RequirementGranularity.md | 14 +++--
docs/Metrics/RequirementLeadTime.md | 59 ++++++++++++++---
livedemo/DataSources/Jira.md | 19 ++++++
static/img/Metrics/added-loc-monthly.png | Bin 0 -> 67687 bytes
static/img/Metrics/bug-age-monthly.png | Bin 0 -> 68696 bytes
static/img/Metrics/bug-per-1k-loc-monthly.png | Bin 0 -> 68819 bytes
static/img/Metrics/commit-count-monthly.png | Bin 0 -> 22871 bytes
static/img/Metrics/deleted-loc-monthly.png | Bin 0 -> 66374 bytes
static/img/Metrics/incident-age-monthly.png | Bin 0 -> 27797 bytes
static/img/Metrics/incident-per-1k-loc-monthly.png | Bin 0 -> 28980 bytes
static/img/Metrics/requirement-count-monthly.png | Bin 0 -> 84006 bytes
.../Metrics/requirement-delivery-rate-monthly.png | Bin 0 -> 26182 bytes
.../img/Metrics/requirement-delivery-rate-text.png | Bin 0 -> 13685 bytes
.../img/Metrics/requirement-lead-time-monthly.png | Bin 0 -> 29869 bytes
24 files changed, 555 insertions(+), 115 deletions(-)
diff --git a/docs/Metrics/AddedLinesOfCode.md b/docs/Metrics/AddedLinesOfCode.md
index 2921ea65b..2d9443e58 100644
--- a/docs/Metrics/AddedLinesOfCode.md
+++ b/docs/Metrics/AddedLinesOfCode.md
@@ -2,7 +2,7 @@
title: "Added Lines of Code"
description: >
Added Lines of Code
-sidebar_position: 7
+sidebar_position: 11
---
## What is this metric?
@@ -20,14 +20,60 @@ This metric is calculated by summing the additions of
commits in the given data
<b>Data Sources Required</b>
-This metric relies on commits collected from GitHub, GitLab or BitBucket.
+This metric relies on `commits` collected from GitHub, GitLab or BitBucket.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
+<b>SQL Queries</b>
+
+The following SQL shows how to find the added lines of code in specific
repositories, eg. 'repo-1' and 'repo-2'.
+
+```
+SELECT
+ sum(c.additions) as added_lines_of_code
+FROM
+ commits c
+ LEFT JOIN repo_commits rc ON c.sha = rc.commit_sha
+ LEFT JOIN repos r ON r.id = rc.repo_id
+WHERE
+ -- please replace the repo ids with your own, or create a '$repo_id'
variable in Grafana
+ r.id in ('repo-1','repo-2')
+ and message not like '%Merge%'
+ and $__timeFilter(c.authored_date)
+ -- the following condition will remove the month with incomplete data
+ and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+```
+
+
+If you want to measure the monthly trend of `added lines of code` in the
screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+WITH _commits as(
+ SELECT
+ DATE_ADD(date(authored_date), INTERVAL -DAY(date(authored_date))+1 DAY) as
time,
+ sum(additions) as added_lines_of_code
+ FROM commits
+ WHERE
+ message not like '%Merge%'
+ and $__timeFilter(authored_date)
+ -- the following condition will remove the month with incomplete data
+ and authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ group by 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ added_lines_of_code
+FROM _commits
+ORDER BY time
+```
+
## How to improve?
-1. From the project/team dimension, observe the accumulated change in Added
lines to assess the team activity and code growth rate
+1. From the project/team dimension, observe the accumulated change in added
lines to assess the team activity and code growth rate
2. From version cycle dimension, observe the active time distribution of code
changes, and evaluate the effectiveness of project development model.
3. From the member dimension, observe the trend and stability of code output
of each member, and identify the key points that affect code output by
comparison.
diff --git a/docs/Metrics/BugAge.md b/docs/Metrics/BugAge.md
index 66cdcbad5..dddced271 100644
--- a/docs/Metrics/BugAge.md
+++ b/docs/Metrics/BugAge.md
@@ -2,7 +2,7 @@
title: "Bug Age"
description: >
Bug Age
-sidebar_position: 9
+sidebar_position: 5
---
## What is this metric?
@@ -13,23 +13,65 @@ The amount of time it takes a bug to fix.
2. Improve team's and individual's bug fixing efficiency. Identify
good/to-be-improved practices that affect bug age age
## Which dashboard(s) does it exist in
-- Jira
-- GitHub
-- Weekly Bug Retro
+- [Jira](https://devlake.apache.org/livedemo/DataSources/Jira)
+- [GitHub](https://devlake.apache.org/livedemo/DataSources/GitHub)
+- [Weekly Bug
Retro](https://devlake.apache.org/livedemo/QAEngineers/WeeklyBugRetro)
## How is it calculated?
-This metric equals to `resolution_date` - `created_date` of issues in type
"BUG".
+Similar to [requirement lead time](./RequirementLeadTime.md), this metric
equals `resolution_date - created_date` of issues in type "BUG".
<b>Data Sources Required</b>
-This metric relies on issues collected from Jira, GitHub, or TAPD.
+This metric relies on `issues` collected from Jira, GitHub, or TAPD.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on the 'type-bug' configuration in Jira, GitHub or TAPD
transformation rules to let DevLake know what CI builds/jobs can be regarded as
`Bugs`.
+This metric relies on the 'type-bug' configuration in Jira, GitHub or TAPD's
transformation rules while adding/editing a blueprint. This configuration tells
DevLake what issues are `bugs`.
+<b>SQL Queries</b>
+
+The following SQL shows how to find the bug age of a specific `bug`.
+```
+-- lead_time_minutes is a pre-calculated field whose value equals
'resolution_date - created_date'
+SELECT
+ lead_time_minutes/1440 as bug_age_in_days
+FROM
+ issues
+WHERE
+ type = 'BUG'
+```
+
+If you want to measure the `mean bug age` in the screenshot below, please run
the following SQL in Grafana.
+
+
+
+```
+with _bugs as(
+ SELECT
+ DATE_ADD(date(i.resolution_date), INTERVAL -DAY(date(i.resolution_date))+1
DAY) as time,
+ AVG(i.lead_time_minutes/1440) as issue_lead_time
+ FROM issues i
+ join board_issues bi on i.id = bi.issue_id
+ join boards b on bi.board_id = b.id
+ WHERE
+ -- $board_id is a variable defined in Grafana's dashboard settings to
filter out issues by boards
+ b.id in ($board_id)
+ and i.status = "DONE"
+ and i.type = 'BUG'
+ and $__timeFilter(i.resolution_date)
+ -- the following condition will remove the month with incomplete data
+ and i.resolution_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ group by 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ issue_lead_time as "Mean Bug Age in Days"
+FROM _bugs
+ORDER BY time
+```
## How to improve?
1. Observe the trend of bug age and locate the key reasons.
-2. According to the severity level, type (business, functional
classification), affected module, source of bugs, count and observe the length
of bug age.
\ No newline at end of file
+2. Compare the age of bugs by severity levels, types (business, functional
classification), affected components, etc.
\ No newline at end of file
diff --git a/docs/Metrics/BugCountPer1kLinesOfCode.md
b/docs/Metrics/BugCountPer1kLinesOfCode.md
index 0c252e530..99e912890 100644
--- a/docs/Metrics/BugCountPer1kLinesOfCode.md
+++ b/docs/Metrics/BugCountPer1kLinesOfCode.md
@@ -2,7 +2,7 @@
title: "Bug Count per 1k Lines of Code"
description: >
Bug Count per 1k Lines of Code
-sidebar_position: 12
+sidebar_position: 6
---
## What is this metric?
@@ -11,27 +11,75 @@ Amount of bugs per 1,000 lines of code.
## Why is it important?
1. Defect drill-down analysis to inform the development of design and code
review strategies and to improve the internal QA process
2. Assist teams to locate projects/modules with higher defect severity and
density, and clean up technical debts
-3. Analyze critical points, identify good/to-be-improved practices that affect
defect count or defect rate, to reduce the amount of future defects
+3. Identify good/to-be-improved practices that affect defect count or defect
rate, to reduce the number of future defects
## Which dashboard(s) does it exist in
N/A
## How is it calculated?
-The number of bugs divided by total accumulated lines of code (additions +
deletions) in the given data range.
+The number of bugs divided by the total accumulated lines of code (additions +
deletions) in the given data range.
-<b>Data Sources Required</b>
+<b>Data sources required</b>
-This metric relies on
-- issues collected from Jira, GitHub or TAPD.
-- commits collected from GitHub, GitLab or BitBucket.
+- `issues` collected from Jira, GitHub or TAPD.
+- `commits` collected from GitHub, GitLab or BitBucket.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on
-- "Issue type mapping" in Jira, GitHub or TAPD's transformation rules page to
let DevLake know what type(s) of issues can be regarded as bugs.
-- "PR-Issue Mapping" in GitHub, GitLab's transformation rules page to let
DevLake know the bugs are fixed by which PR/MRs.
+This metric relies on the 'type-bug' configuration in Jira, GitHub or TAPD's
transformation rules while adding/editing a blueprint. This configuration tells
DevLake what issues are `bugs`.
+<b>SQL Queries</b>
+
+If you want to measure the monthly trend of `Bugs per 1k lines of code` in the
screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+with _line_of_code as (
+ select
+ DATE_ADD(date(authored_date), INTERVAL -DAY(date(authored_date))+1
DAY) as time,
+ sum(additions + deletions) as line_count
+ from
+ commits
+ where
+ message not like 'Merge%'
+ and $__timeFilter(authored_date)
+ group by 1
+),
+
+
+_bug_count as(
+ select
+ DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
+ count(*) as bug_count
+ from issues i
+ where
+ type = 'Bug'
+ and $__timeFilter(created_date)
+ group by 1
+),
+
+
+_bug_count_per_1k_loc as(
+ select
+ loc.time,
+ 1.0 * bc.bug_count / loc.line_count * 1000 as bug_count_per_1k_loc
+ from
+ _line_of_code loc
+ left join _bug_count bc on bc.time = loc.time
+ where
+ bc.bug_count is not null
+ and loc.line_count is not null
+ and loc.line_count != 0
+)
+
+select
+ date_format(time,'%M %Y') as month,
+ bug_count_per_1k_loc as 'Bug Count per 1000 Lines of Code'
+from _bug_count_per_1k_loc
+order by time;
+```
## How to improve?
1. From the project or team dimension, observe the statistics on the total
number of defects, the distribution of the number of defects in each severity
level/type/owner, the cumulative trend of defects, and the change trend of the
defect rate in thousands of lines, etc.
diff --git a/docs/Metrics/CommitAuthorCount.md
b/docs/Metrics/CommitAuthorCount.md
index 3be4ad206..46bf55934 100644
--- a/docs/Metrics/CommitAuthorCount.md
+++ b/docs/Metrics/CommitAuthorCount.md
@@ -2,7 +2,7 @@
title: "Commit Author Count"
description: >
Commit Author Count
-sidebar_position: 14
+sidebar_position: 10
---
## What is this metric?
@@ -23,10 +23,30 @@ This metric is calculated by counting the number of commit
authors in the given
This metric relies on commits collected from GitHub, GitLab or BitBucket.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
+<b>SQL Queries</b>
+
+The following SQL shows how to find the `commit author count` in specific
repositories, eg. 'repo-1' and 'repo-2'.
+
+```
+SELECT
+ count(distinct c.author_id)
+FROM
+ commits c
+ LEFT JOIN repo_commits rc ON c.sha = rc.commit_sha
+ LEFT JOIN repos r ON r.id = rc.repo_id
+WHERE
+ -- please replace the repo ids with your own, or create a '$repo_id'
variable in Grafana
+ r.id in ('repo-1', 'repo-2')
+ and message not like '%Merge%'
+ and $__timeFilter(c.authored_date)
+ -- the following condition will remove the month with incomplete data
+ and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+```
+
## How to improve?
As a secondary indicator, this helps assess the labor cost of participating in
coding.
diff --git a/docs/Metrics/CommitCount.md b/docs/Metrics/CommitCount.md
index ae85af8d2..336accb72 100644
--- a/docs/Metrics/CommitCount.md
+++ b/docs/Metrics/CommitCount.md
@@ -2,7 +2,7 @@
title: "Commit Count"
description: >
Commit Count
-sidebar_position: 6
+sidebar_position: 9
---
## What is this metric?
@@ -24,30 +24,58 @@ This metric is calculated by counting the number of commits
in the given data ra
This metric relies on commits collected from GitHub, GitLab or BitBucket.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
<b>SQL Queries</b>
-If you want to see the monthly trend, run the following SQL
+The following SQL shows how to find commits in specific repositories, eg.
'repo-1' and 'repo-2'.
```
- with _commits as(
- SELECT
- DATE_ADD(date(authored_date), INTERVAL -DAY(date(authored_date))+1 DAY)
as time,
- count(*) as commit_count
- FROM commits
- WHERE
- message not like '%Merge%'
- and $__timeFilter(authored_date)
- group by 1
- )
-
- SELECT
- date_format(time,'%M %Y') as month,
- commit_count as "Commit Count"
- FROM _commits
- ORDER BY time
+SELECT
+ r.id,
+ c.*
+FROM
+ commits c
+ LEFT JOIN repo_commits rc ON c.sha = rc.commit_sha
+ LEFT JOIN repos r ON r.id = rc.repo_id
+WHERE
+ -- please replace the repo ids with your own, or create a '$repo_id'
variable in Grafana
+ r.id in ('repo-1','repo-2')
+ and message not like '%Merge%'
+ and $__timeFilter(c.authored_date)
+ -- the following condition will remove the month with incomplete data
+ and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+```
+
+If you want to measure the monthly trend of `commit count` in the screenshot
below, please run the following SQL in Grafana.
+
+
+
+```
+with _commits as(
+ SELECT
+ DATE_ADD(date(c.authored_date), INTERVAL -DAY(date(c.authored_date))+1
DAY) as time,
+ count(c.sha) as commit_count
+ FROM
+ commits c
+ LEFT JOIN repo_commits rc ON c.sha = rc.commit_sha
+ LEFT JOIN repos r ON r.id = rc.repo_id
+ WHERE
+ -- please replace the repo ids with your own, or create a '$repo_id'
variable in Grafana
+ r.id in ($repo_id)
+ and message not like '%Merge%'
+ and $__timeFilter(c.authored_date)
+ -- the following condition will remove the month with incomplete data
+ and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ group by 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ commit_count as "Commit Count"
+FROM _commits
+ORDER BY time
```
## How to improve?
diff --git a/docs/Metrics/DeletedLinesOfCode.md
b/docs/Metrics/DeletedLinesOfCode.md
index 218ceae0c..963834af1 100644
--- a/docs/Metrics/DeletedLinesOfCode.md
+++ b/docs/Metrics/DeletedLinesOfCode.md
@@ -2,7 +2,7 @@
title: "Deleted Lines of Code"
description: >
Deleted Lines of Code
-sidebar_position: 8
+sidebar_position: 12
---
## What is this metric?
@@ -20,12 +20,57 @@ This metric is calculated by summing the deletions of
commits in the given data
<b>Data Sources Required</b>
-This metric relies on commits collected from GitHub, GitLab or BitBucket.
+This metric relies on `commits` collected from GitHub, GitLab or BitBucket.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
+<b>SQL Queries</b>
+
+The following SQL shows how to find the `deleted lines of code` in specific
repositories, eg. 'repo-1' and 'repo-2'.
+
+```
+SELECT
+ sum(c.deletions) as added_lines_of_code
+FROM
+ commits c
+ LEFT JOIN repo_commits rc ON c.sha = rc.commit_sha
+ LEFT JOIN repos r ON r.id = rc.repo_id
+WHERE
+ -- please replace the repo ids with your own, or create a '$repo_id'
variable in Grafana
+ r.id in ('repo-1','repo-2')
+ and message not like '%Merge%'
+ and $__timeFilter(c.authored_date)
+ -- the following condition will remove the month with incomplete data
+ and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+```
+
+If you want to measure the monthly trend of `deleted lines of code` in the
screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+with _commits as(
+ SELECT
+ DATE_ADD(date(authored_date), INTERVAL -DAY(date(authored_date))+1 DAY) as
time,
+ sum(deletions) as deleted_lines_of_code
+ FROM commits
+ WHERE
+ message not like '%Merge%'
+ and $__timeFilter(authored_date)
+ -- the following condition will remove the month with incomplete data
+ and authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ group by 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ deleted_lines_of_code
+FROM _commits
+ORDER BY time
+```
+
## How to improve?
1. From the project/team dimension, observe the accumulated change in Added
lines to assess the team activity and code growth rate
2. From version cycle dimension, observe the active time distribution of code
changes, and evaluate the effectiveness of project development model.
diff --git a/docs/Metrics/IncidentAge.md b/docs/Metrics/IncidentAge.md
index 4cd5e60cb..edf030772 100644
--- a/docs/Metrics/IncidentAge.md
+++ b/docs/Metrics/IncidentAge.md
@@ -2,33 +2,75 @@
title: "Incident Age"
description: >
Incident Age
-sidebar_position: 10
+sidebar_position: 7
---
## What is this metric?
-The amount of time it takes a incident to fix.
+The amount of time it takes an incident to fix.
## Why is it important?
1. Help the team to establish an effective hierarchical response mechanism for
incidents. Focus on the resolution of important problems in the backlog.
2. Improve team's and individual's incident fixing efficiency. Identify
good/to-be-improved practices that affect incident age
## Which dashboard(s) does it exist in
-- Jira
-- GitHub
+- [Jira](https://devlake.apache.org/livedemo/DataSources/Jira)
+- [GitHub](https://devlake.apache.org/livedemo/DataSources/GitHub)
## How is it calculated?
-This metric equals to `resolution_date` - `created_date` of issues in type
"INCIDENT".
+Similar to [requirement lead time](./RequirementLeadTime.md), this metric
equals `resolution_date - created_date` of issues in type "INCIDENT".
<b>Data Sources Required</b>
-This metric relies on issues collected from Jira, GitHub, or TAPD.
+This metric relies on `issues` collected from Jira, GitHub, TAPD, or PagerDuty.
<b>Transformation Rules Required</b>
-This metric relies on the 'type-incident' configuration in Jira, GitHub or
TAPD transformation rules to let DevLake know what CI builds/jobs can be
regarded as `Incidents`.
+This metric relies on the 'type-incident' configuration in Jira, GitHub or
TAPD's transformation rules while adding/editing a blueprint. This
configuration tells DevLake what issues are `incidents`.
+<b>SQL Queries</b>
+
+The following SQL shows how to find the incident age of a specific `incident`.
+```
+-- lead_time_minutes is a pre-calculated field whose value equals
'resolution_date - created_date'
+SELECT
+ lead_time_minutes/1440 as incident_age_in_days
+FROM
+ issues
+WHERE
+ type = 'INCIDENT'
+```
+
+If you want to measure the `mean incident age` in the screenshot below, please
run the following SQL in Grafana.
+
+
+
+```
+with _incidents as(
+ SELECT
+ DATE_ADD(date(i.resolution_date), INTERVAL -DAY(date(i.resolution_date))+1
DAY) as time,
+ AVG(i.lead_time_minutes/1440) as issue_lead_time
+ FROM issues i
+ join board_issues bi on i.id = bi.issue_id
+ join boards b on bi.board_id = b.id
+ WHERE
+ -- $board_id is a variable defined in Grafana's dashboard settings to
filter out issues by boards
+ b.id in ($board_id)
+ and i.status = "DONE"
+ and i.type = 'INCIDENT'
+ and $__timeFilter(i.resolution_date)
+ -- the following condition will remove the month with incomplete data
+ and i.resolution_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ group by 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ issue_lead_time as "Mean Incident Age in Days"
+FROM _incidents
+ORDER BY time
+```
## How to improve?
1. Observe the trend of incident age and locate the key reasons.
-2. According to the severity level, type (business, functional
classification), affected module, source of bugs, count and observe the length
of incident age.
\ No newline at end of file
+2. Compare the age of incidents by severity levels, types (business,
functional classification), affected components, etc.
\ No newline at end of file
diff --git a/docs/Metrics/IncidentCountPer1kLinesOfCode.md
b/docs/Metrics/IncidentCountPer1kLinesOfCode.md
index 9ad927877..c789c0667 100644
--- a/docs/Metrics/IncidentCountPer1kLinesOfCode.md
+++ b/docs/Metrics/IncidentCountPer1kLinesOfCode.md
@@ -2,7 +2,7 @@
title: "Incident Count per 1k Lines of Code"
description: >
Incident Count per 1k Lines of Code
-sidebar_position: 13
+sidebar_position: 8
---
## What is this metric?
@@ -11,7 +11,7 @@ Amount of incidents per 1,000 lines of code.
## Why is it important?
1. Defect drill-down analysis to inform the development of design and code
review strategies and to improve the internal QA process
2. Assist teams to locate projects/modules with higher defect severity and
density, and clean up technical debts
-3. Analyze critical points, identify good/to-be-improved practices that affect
defect count or defect rate, to reduce the amount of future defects
+3. Identify good/to-be-improved practices that affect defect count or defect
rate, to reduce the number of future defects
## Which dashboard(s) does it exist in
N/A
@@ -22,15 +22,64 @@ The number of incidents divided by total accumulated lines
of code (additions +
<b>Data Sources Required</b>
-This metric relies on
-- issues collected from Jira, GitHub or TAPD.
-- commits collected from GitHub, GitLab or BitBucket.
+- `issues` collected from Jira, GitHub, TAPD or PagerDuty.
+- `commits` collected from GitHub, GitLab or BitBucket.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on
-- "Issue type mapping" in Jira, GitHub or TAPD's transformation rules page to
let DevLake know what type(s) of issues can be regarded as incidents.
-- "PR-Issue Mapping" in GitHub, GitLab's transformation rules page to let
DevLake know the bugs are fixed by which PR/MRs.
+This metric relies on the 'type-incident' configuration in Jira, GitHub or
TAPD's transformation rules while adding/editing a blueprint. This
configuration tells DevLake what issues are `incidents`.
+
+<b>SQL Queries</b>
+
+If you want to measure the monthly trend of `Incidents per 1k lines of code`
in the screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+with _line_of_code as (
+ select
+ DATE_ADD(date(authored_date), INTERVAL -DAY(date(authored_date))+1
DAY) as time,
+ sum(additions + deletions) as line_count
+ from
+ commits
+ where
+ message not like 'Merge%'
+ and $__timeFilter(authored_date)
+ group by 1
+),
+
+
+_incident_count as(
+ select
+ DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as
time,
+ count(*) as incident_count
+ from issues i
+ where
+ type = 'INCIDENT'
+ and $__timeFilter(created_date)
+ group by 1
+),
+
+
+_incident_count_per_1k_loc as(
+ select
+ loc.time,
+ 1.0 * ic.incident_count / loc.line_count * 1000 as
incident_count_per_1k_loc
+ from
+ _line_of_code loc
+ left join _incident_count ic on ic.time = loc.time
+ where
+ ic.incident_count is not null
+ and loc.line_count is not null
+ and loc.line_count != 0
+)
+
+select
+ date_format(time,'%M %Y') as month,
+ incident_count_per_1k_loc as 'Incident Count per 1000 Lines of Code'
+from _incident_count_per_1k_loc
+order by time;
+```
## How to improve?
1. From the project or team dimension, observe the statistics on the total
number of defects, the distribution of the number of defects in each severity
level/type/owner, the cumulative trend of defects, and the change trend of the
defect rate in thousands of lines, etc.
diff --git a/docs/Metrics/RequirementCount.md b/docs/Metrics/RequirementCount.md
index e9a6bd329..f8ea39865 100644
--- a/docs/Metrics/RequirementCount.md
+++ b/docs/Metrics/RequirementCount.md
@@ -2,7 +2,7 @@
title: "Requirement Count"
description: >
Requirement Count
-sidebar_position: 2
+sidebar_position: 1
---
## What is this metric?
@@ -13,8 +13,8 @@ The number of delivered requirements or features.
2. Evaluate whether the delivery capacity matches the business phase and
demand scale. Identify key bottlenecks and reasonably allocate resources.
## Which dashboard(s) does it exist in
-- Jira
-- GitHub
+- [Jira](https://devlake.apache.org/livedemo/DataSources/Jira)
+- [GitHub](https://devlake.apache.org/livedemo/DataSources/GitHub)
## How is it calculated?
@@ -22,43 +22,47 @@ This metric is calculated by counting the number of
delivered issues in type "RE
<b>Data Sources Required</b>
-This metric relies on the issues collected from Jira, GitHub, or TAPD.
+This metric relies on the `issues` collected from Jira, GitHub, or TAPD.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD transformation rules to let DevLake know what CI builds/jobs can be
regarded as `Requirements`.
+This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD's transformation rules while adding/editing a blueprint. This
configuration tells DevLake what issues are `requirements`.
<b>SQL Queries</b>
-If you want to see a single count, run the following SQL in Grafana
+The following SQL shows how to find the total count of requirements in
specific boards, eg. 'board-1' and 'board-2'.
+
```
- select
- count(*) as "Requirement Count"
- from issues i
- join board_issues bi on i.id = bi.issue_id
- where
- i.type = 'REQUIREMENT'
- and i.status = 'DONE'
- -- this is the default variable in Grafana
- and $__timeFilter(i.created_date)
- and bi.board_id in ($board_id)
+select
+ count(*) as "Requirement Count"
+from issues i
+ join board_issues bi on i.id = bi.issue_id
+where
+ i.type = 'REQUIREMENT'
+ and i.status = 'DONE'
+ -- please replace the board ids with your own, or create a '$board_id'
variable in Grafana
+ and bi.board_id in ('board-1','board-2')
+ and $__timeFilter(i.created_date)
```
-If you want to see the monthly trend, run the following SQL
+If you want to see the monthly trend of `requirement count` in the screenshot
below, please run the following SQL
+
+
+
```
- SELECT
- DATE_ADD(date(i.created_date), INTERVAL
-DAYOFMONTH(date(i.created_date))+1 DAY) as time,
- count(distinct case when status != 'DONE' then i.id else null end) as
"Number of Open Issues",
- count(distinct case when status = 'DONE' then i.id else null end) as
"Number of Delivered Issues"
- FROM issues i
- join board_issues bi on i.id = bi.issue_id
- join boards b on bi.board_id = b.id
- WHERE
- i.type = 'REQUIREMENT'
- and i.status = 'DONE'
- and $__timeFilter(i.created_date)
- and bi.board_id in ($board_id)
- GROUP by 1
+SELECT
+ DATE_ADD(date(i.created_date), INTERVAL -DAYOFMONTH(date(i.created_date))+1
DAY) as time,
+ count(distinct case when status != 'DONE' then i.id else null end) as
"Number of Open Requirements",
+ count(distinct case when status = 'DONE' then i.id else null end) as "Number
of Delivered Requirements"
+FROM issues i
+ join board_issues bi on i.id = bi.issue_id
+ join boards b on bi.board_id = b.id
+where
+ i.type = 'REQUIREMENT'
+ and $__timeFilter(i.created_date)
+ -- please replace the board ids with your own, or create a '$board_id'
variable in Grafana
+ and bi.board_id in ($board_id)
+group by 1
```
## How to improve?
diff --git a/docs/Metrics/RequirementDeliveryRate.md
b/docs/Metrics/RequirementDeliveryRate.md
index eb0a03133..1c1b245eb 100644
--- a/docs/Metrics/RequirementDeliveryRate.md
+++ b/docs/Metrics/RequirementDeliveryRate.md
@@ -13,8 +13,8 @@ The ratio of delivered requirements to all requirements.
2. Evaluate whether the delivery capacity matches the business phase and
demand scale. Identify key bottlenecks and reasonably allocate resources.
## Which dashboard(s) does it exist in
-- Jira
-- GitHub
+- [Jira](https://devlake.apache.org/livedemo/DataSources/Jira)
+- [GitHub](https://devlake.apache.org/livedemo/DataSources/GitHub)
## How is it calculated?
@@ -22,11 +22,63 @@ The number of delivered requirements divided by the total
number of requirements
<b>Data Sources Required</b>
-This metric relies on the issues collected from Jira, GitHub, or TAPD.
+This metric relies on the `issues` collected from Jira, GitHub, or TAPD.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD transformation rules to let DevLake know what CI builds/jobs can be
regarded as `Requirements`.
+This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD's transformation rules while adding/editing a blueprint. This
configuration tells DevLake what issues are `requirements`.
+
+<b>SQL Queries</b>
+
+The following SQL shows how to find the `requirement delivery rate` in
specific boards, eg. 'board-1' and 'board-2'.
+
+
+
+```
+WITH _requirements as(
+ SELECT
+ count(distinct i.id) as total_count,
+ count(distinct case when i.status = 'DONE' then i.id else null end) as
delivered_count
+ FROM issues i
+ join board_issues bi on i.id = bi.issue_id
+ WHERE
+ i.type = 'REQUIREMENT'
+ and $__timeFilter(i.created_date)
+ -- please replace the board ids with your own, or create a '$board_id'
variable in Grafana
+ and bi.board_id in ('board_1', 'board_2')
+)
+
+SELECT
+ now() as time,
+ 1.0 * delivered_count/total_count as requirement_delivery_rate
+FROM _requirements
+```
+
+If you want to measure the monthly trend of `requirement delivery rate` in the
screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+WITH _requirements as(
+ SELECT
+ DATE_ADD(date(i.created_date), INTERVAL
-DAYOFMONTH(date(i.created_date))+1 DAY) as time,
+ 1.0 * count(distinct case when i.status = 'DONE' then i.id else null
end)/count(distinct i.id) as delivered_rate
+ FROM issues i
+ join board_issues bi on i.id = bi.issue_id
+ WHERE
+ i.type = 'REQUIREMENT'
+ and $__timeFilter(i.created_date)
+ -- please replace the board ids with your own, or create a '$board_id'
variable in Grafana
+ and bi.board_id in ($board_id)
+ GROUP BY 1
+)
+
+SELECT
+ time,
+ delivered_rate
+FROM _requirements
+ORDER BY time
+```
## How to improve?
diff --git a/docs/Metrics/RequirementGranularity.md
b/docs/Metrics/RequirementGranularity.md
index 03bb91767..974766021 100644
--- a/docs/Metrics/RequirementGranularity.md
+++ b/docs/Metrics/RequirementGranularity.md
@@ -2,7 +2,7 @@
title: "Requirement Granularity"
description: >
Requirement Granularity
-sidebar_position: 5
+sidebar_position: 4
---
## What is this metric?
@@ -13,8 +13,8 @@ The average number of story points per requirement.
2. Establish a data-supported workload estimation model to help R&D teams
calibrate their estimation methods and more accurately assess the granularity
of requirements, which is useful to achieve better issue planning in project
management.
## Which dashboard(s) does it exist in
-- Jira
-- GitHub
+- [Jira](https://devlake.apache.org/livedemo/DataSources/Jira)
+- [GitHub](https://devlake.apache.org/livedemo/DataSources/GitHub)
## How is it calculated?
@@ -22,11 +22,13 @@ The average story points of issues in type "REQUIREMENT" in
the given data range
<b>Data Sources Required</b>
-This metric relies on issues collected from Jira, GitHub, or TAPD.
+This metric relies on `issues` collected from Jira, GitHub, or TAPD.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD transformation rules to let DevLake know what CI builds/jobs can be
regarded as `Requirements`.
+This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD's transformation rules while adding/editing a blueprint. This
configuration tells DevLake what issues are `requirements`.
+
+Besides, if you are importing Jira issues, you also need to configure the
field of 'story_points' in the transformation.
## How to improve?
diff --git a/docs/Metrics/RequirementLeadTime.md
b/docs/Metrics/RequirementLeadTime.md
index 74061d63d..96c64dd6a 100644
--- a/docs/Metrics/RequirementLeadTime.md
+++ b/docs/Metrics/RequirementLeadTime.md
@@ -2,7 +2,7 @@
title: "Requirement Lead Time"
description: >
Requirement Lead Time
-sidebar_position: 4
+sidebar_position: 2
---
## What is this metric?
@@ -13,24 +13,67 @@ The amount of time it takes a requirement to deliver.
2. Focus on the end-to-end velocity of value delivery process; coordinate
different parts of R&D to avoid efficiency shafts; make targeted improvements
to bottlenecks.
## Which dashboard(s) does it exist in
-- Jira
-- GitHub
-- Community Experience
+- [Jira](https://devlake.apache.org/livedemo/DataSources/Jira)
+- [GitHub](https://devlake.apache.org/livedemo/DataSources/GitHub)
+- [Community
Experience](https://devlake.apache.org/livedemo/OSSMaintainers/CommunityExperience)
## How is it calculated?
-This metric equals to `resolution_date` - `created_date` of issues in type
"REQUIREMENT".
+This metric equals `resolution_date - created_date` of issues in type
"REQUIREMENT".
<b>Data Sources Required</b>
This metric relies on issues collected from Jira, GitHub, or TAPD.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
-This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD transformation rules to let DevLake know what CI builds/jobs can be
regarded as `Requirements`.
+This metric relies on the 'type-requirement' configuration in Jira, GitHub or
TAPD's transformation rules while adding/editing a blueprint. This
configuration tells DevLake what issues are `requirements`.
+<b>SQL Queries</b>
+
+The following SQL shows how to find the lead time of a specific `requirement`.
+```
+-- lead_time_minutes is a pre-calculated field whose value equals
'resolution_date - created_date'
+SELECT
+ lead_time_minutes/1440 as requirement_lead_time_in_days
+FROM
+ issues
+WHERE
+ type = 'REQUIREMENT'
+```
+
+
+If you want to measure the `mean requirement lead time` in the screenshot
below, please run the following SQL in Grafana.
+
+
+
+```
+with _issues as(
+ SELECT
+ DATE_ADD(date(i.resolution_date), INTERVAL -DAY(date(i.resolution_date))+1
DAY) as time,
+ AVG(i.lead_time_minutes/1440) as issue_lead_time
+ FROM issues i
+ join board_issues bi on i.id = bi.issue_id
+ join boards b on bi.board_id = b.id
+ WHERE
+ -- $board_id is a variable defined in Grafana's dashboard settings to
filter out issues by boards
+ b.id in ($board_id)
+ and i.type = 'REQUIREMENT'
+ and i.status = "DONE"
+ and $__timeFilter(i.resolution_date)
+ -- the following condition will remove the month with incomplete data
+ and i.resolution_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ group by 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ issue_lead_time as "Mean Requirement Lead Time in Days"
+FROM _issues
+ORDER BY time
+```
## How to improve?
1. Analyze the trend of requirement lead time to observe if it has improved
over time.
-2. Analyze and compare the requirement lead time of each project/team to
identify key projects with abnormal lead time.
+2. Compare the requirement lead time of each project/team to identify key
projects with abnormal lead time.
3. Drill down to analyze a requirement's staying time in different phases of
SDLC. Analyze the bottleneck of delivery velocity and improve the workflow.
\ No newline at end of file
diff --git a/livedemo/DataSources/Jira.md b/livedemo/DataSources/Jira.md
new file mode 100644
index 000000000..b0a2f3edb
--- /dev/null
+++ b/livedemo/DataSources/Jira.md
@@ -0,0 +1,19 @@
+---
+title: "Jira"
+description: >
+ Jira Live Demo
+---
+
+# Jira
+
+<div className="info">
+ <h5>
+ <img
+
src="https://user-images.githubusercontent.com/84442212/197146839-c2d116e6-e0b8-40a0-bb29-e51fb4805a81.png";
+ alt=""
+ width="3%"
+ /> For best viewing experience, please view the dashboard on a desktop
+ </h5>
+</div>
+
+<iframe
src="https://grafana-lake.demo.devlake.io/grafana/d/F5vqBQl7z/jira?orgId=1&from=now-6M&to=now";
width="135%" height="1240px"></iframe>
diff --git a/static/img/Metrics/added-loc-monthly.png
b/static/img/Metrics/added-loc-monthly.png
new file mode 100644
index 000000000..2e7398d44
Binary files /dev/null and b/static/img/Metrics/added-loc-monthly.png differ
diff --git a/static/img/Metrics/bug-age-monthly.png
b/static/img/Metrics/bug-age-monthly.png
new file mode 100644
index 000000000..c097909c6
Binary files /dev/null and b/static/img/Metrics/bug-age-monthly.png differ
diff --git a/static/img/Metrics/bug-per-1k-loc-monthly.png
b/static/img/Metrics/bug-per-1k-loc-monthly.png
new file mode 100644
index 000000000..7ebfb5a6c
Binary files /dev/null and b/static/img/Metrics/bug-per-1k-loc-monthly.png
differ
diff --git a/static/img/Metrics/commit-count-monthly.png
b/static/img/Metrics/commit-count-monthly.png
new file mode 100644
index 000000000..b0293c000
Binary files /dev/null and b/static/img/Metrics/commit-count-monthly.png differ
diff --git a/static/img/Metrics/deleted-loc-monthly.png
b/static/img/Metrics/deleted-loc-monthly.png
new file mode 100644
index 000000000..27ea78329
Binary files /dev/null and b/static/img/Metrics/deleted-loc-monthly.png differ
diff --git a/static/img/Metrics/incident-age-monthly.png
b/static/img/Metrics/incident-age-monthly.png
new file mode 100644
index 000000000..7a83343fd
Binary files /dev/null and b/static/img/Metrics/incident-age-monthly.png differ
diff --git a/static/img/Metrics/incident-per-1k-loc-monthly.png
b/static/img/Metrics/incident-per-1k-loc-monthly.png
new file mode 100644
index 000000000..ab1fcd68a
Binary files /dev/null and b/static/img/Metrics/incident-per-1k-loc-monthly.png
differ
diff --git a/static/img/Metrics/requirement-count-monthly.png
b/static/img/Metrics/requirement-count-monthly.png
new file mode 100644
index 000000000..aea3d61b5
Binary files /dev/null and b/static/img/Metrics/requirement-count-monthly.png
differ
diff --git a/static/img/Metrics/requirement-delivery-rate-monthly.png
b/static/img/Metrics/requirement-delivery-rate-monthly.png
new file mode 100644
index 000000000..563c08380
Binary files /dev/null and
b/static/img/Metrics/requirement-delivery-rate-monthly.png differ
diff --git a/static/img/Metrics/requirement-delivery-rate-text.png
b/static/img/Metrics/requirement-delivery-rate-text.png
new file mode 100644
index 000000000..b87313897
Binary files /dev/null and
b/static/img/Metrics/requirement-delivery-rate-text.png differ
diff --git a/static/img/Metrics/requirement-lead-time-monthly.png
b/static/img/Metrics/requirement-lead-time-monthly.png
new file mode 100644
index 000000000..6873772ca
Binary files /dev/null and
b/static/img/Metrics/requirement-lead-time-monthly.png differ
![https://user-images.githubusercontent.com/84442212/197146839-c2d116e6-e0b8-40a0-bb29-e51fb4805a81.png"](https://user-images.githubusercontent.com/84442212/197146839-c2d116e6-e0b8-40a0-bb29-e51fb4805a81.png"){kind=link}