Key | Value |
---|---|
Table | DS03 Cost |
Severity | CRITICAL |
Unique ID | 1030072 |
Summary | Does this WP or PP have more than one EVT group? |
Error message | WP or PP where EVT group is not uniform (EVTs are not all LOE, Discrete, Apportioned, or Planning Package for this WP or PP data). |
The Data Integrity and Quality (DIQ) check titled "WP or PP with Multiple EVT Groups" is designed to ensure that each Work Package (WP) or Planning Package (PP) in the DS03 Cost table is associated with a single, uniform EVT group.
EVT groups are categorized as 'Discrete', 'LOE' (Level of Effort), 'Apportioned', or 'PP' (Planning Package). The DIQ check identifies any WP or PP where the EVTs within a given WP or PP are not of the same group.
The fields involved in this check are 'WBS_ID_WP' and 'EVT'. The 'WBS_ID_WP' field identifies the WP or PP, and the 'EVT' field indicates the earned value technique.
If the DIQ check fails, it means that a WP or PP has been associated with more than one EVT group. This could be due to a data entry error or a misunderstanding of EVT categorization.
To resolve this issue, review the EVT assignments for each WP or PP in the DS03 Cost table. Ensure that each WP or PP is associated with a single, uniform EVT group.
This test is being performed on the 'DS03 Cost' table to check for Work Packages (WP) or Planning Packages (PP) that have more than one Earned Value Technique (EVT) group. The test is checking for uniformity in the EVT group, meaning all EVTs should be either LOE (Level of Effort), Discrete, Apportioned, or Planning Package for each particular WP or PP.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if this issue is not resolved, the data cannot be reviewed or analyzed further.
The importance of this check lies in ensuring the consistency and accuracy of the data. WPs support accurate performance measurement by assigning the appropriate EVT. EVTs are to be consistent with how the resource budgets (all elements of cost) are planned to be performed and progress measured. Having multiple EVT groups for a single WP or PP can lead to confusion, inaccuracies, and distortion in measuring performance and lead to incorrect decision-making. Therefore, it is crucial to fix this error to maintain the integrity and quality of the project management data.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_AreWPOrPPEVTsComingled] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with EVTGroups as (
-- WP WBS & EOC by EVTs by group
SELECT
WBS_ID_WP,
CASE
WHEN EVT IN ('B', 'C', 'D', 'E', 'F', 'G', 'H', 'L', 'N', 'O', 'P') THEN 'Discrete'
WHEN EVT = 'A' THEN 'LOE'
WHEN EVT IN ('J', 'M') THEN 'Apportioned'
WHEN EVT = 'K' THEN 'PP'
ELSE ''
END as EVT
FROM
DS03_cost
WHERE
upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) <> '' AND TRIM(ISNULL(EVT,'')) <> ''
), Flags AS (
--Compare. Group by WBS ID to return only distinct WBS IDs
--that fail the test.
SELECT G1.WBS_ID_WP
FROM EVTGroups G1 INNER JOIN EVTGroups G2 ON G1.WBS_ID_WP = G2.WBS_ID_WP
AND G1.EVT <> G2.EVT
GROUP BY G1.WBS_ID_WP
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Flags F ON C.WBS_ID_WP = F.WBS_ID_WP
WHERE
upload_ID = @upload_ID
)