How to replicate Feature Backlog's Program PI Velocity in Jira Align Enterprise Insights
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The Feature Backlog displays the ‘Program PI Velocity’ value whenever a Program and Program Increment is selected.
Program PI Velocity is defined as: "The average number of level-of-effort points accepted by the program in the previous two PIs."
This article shows how customers can replicate the same Program PI Velocity calculation in Enterprise Insights.
Solution
The Program PI Velocity calculates “points accepted” as those from Stories which are:
Under the Program
NOT in the recycle bin or deleted bin
In the Accepted state (i.e. Accepted Date is populated)
Assigned to at least one of the following:
Directly to one of the previous 2 completed PIs
To a Sprint under one of the previous 2 completed PIs
To a parent Feature under one of the previous 2 completed PIs
The following sample SQL Query can be used in Enterprise Insights to capture this same scope and replicate the Feature Backlog’s Program PI Velocity calculation.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
declare @PI_1 int = 496
declare @PI_2 int = 411
declare @ProgramId int = 196
SELECT
sum (LOE)/2 as Velocity
FROM
(
SELECT /*Query for stories in sprints within a PI*/
ST.[Story ID]
, ST.[Story Name]
, isnull(ST.[level of effort],0) as LOE
, P.[Program ID]
, P.[Program Name]
, ST.[fk program increment id]
FROM current_dw.Story AS ST
INNER JOIN current_dw.Sprint AS SP ON ST.[FK Sprint ID] = SP.[Sprint ID]
INNER JOIN current_dw.Program AS P ON P.[Program Id] = ST.[FK Program ID]
INNER JOIN current_dw.[Program Increment] AS PI ON PI.[Program Increment ID] = SP.[FK Program Increment ID]
WHERE
P.[Program id]= @ProgramId -- Replace Program id
AND PI.[program increment id] in (@PI_1, @PI_2) -- Replace PI id
AND ST.[Date Accepted] is not null
UNION
SELECT /*Query for stories not assigned to a sprint but are assigned to the PI. PI is assigned based on feature PI assignment*/
ST.[Story ID]
, ST.[Story Name]
, isnull(ST.[level of effort],0) as LOE
, P.[Program ID]
, P.[Program Name]
,ST.[fk program increment id]
FROM current_dw.Story AS ST
LEFT JOIN current_dw.Sprint AS SP ON ST.[FK Sprint ID] = SP.[Sprint ID]
INNER JOIN current_dw.Program AS P ON P.[Program ID] = ST.[FK Program ID]
LEFT JOIN current_dw.[feature] f on st.[fk feature id] = f.[feature id]
WHERE
ST.[FK Sprint ID] = 0 /*Return stories that are not assigned to a sprint*/
AND P.[Program id]= @ProgramId -- Replace Program Name
And (ST.[fk program increment id] in (@PI_1, @PI_2) or ((ST.[fk program increment id] = '0') and (f.[fk program increment id] in (@PI_1, @PI_2))))
AND ST.[Date Accepted] is not null
) AS T;Was this helpful?