¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030314 |
| Summary | Is a root cause narrative missing for this CA where the CV percent is tripping the favorable percent threshold? |
| Error message | DS03.CVc % abs((BCWP - ACWP) / BCWP) > abs(DS07.threshold_cost_cum_pct_fav) & DS11.narrative_RC_CVc is missing or blank (by DS03.WBS_ID_CA & DS11.WBS_ID). |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "CV Percent without Root Cause Narrative (Favorable)" is designed to identify any instances in the DS03 Cost table where a root cause narrative is missing for a Control Account (CA) that has a Cost Variance (CV) percentage exceeding the favorable threshold.
The CV percentage is calculated as the absolute value of the difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP), divided by the BCWP. This is represented in the test as DS03.CVc % (|(BCWP - ACWP) / BCWP|).
The favorable threshold is defined in the DS07 IPMR Header table as DS07.threshold_cost_cum_pct_fav. If the CV percentage for a CA exceeds this threshold, a root cause narrative should be provided in the DS11 Variance table (DS11.narrative_RC_CVc).
The DIQ check groups the results by the Control Account Work Breakdown Structure ID (WBS_ID_CA) and identifies any instances where the CV percentage exceeds the favorable threshold and the root cause narrative is missing or blank.
If this DIQ check returns any results, it indicates that there are CAs with a favorable CV percentage that do not have a corresponding root cause narrative in the DS11 Variance table. This could be due to an oversight in data entry or a lack of communication between project management and the team responsible for entering the data. To resolve this issue, a root cause narrative should be provided for each CA that has a CV percentage exceeding the favorable threshold.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative for each Cost Variance (CV) where the CV percent is exceeding the favorable percent threshold. The test is checking the 'DS03 Cost' table to see if there are any instances where the CV percent is greater than the threshold and the root cause narrative is missing or blank.
The importance of this check is to ensure that all significant cost variances have an explanation. This is crucial for understanding why the cost variance occurred and for making informed decisions about project management. If a root cause narrative is missing, it could lead to misunderstandings or misinterpretations of the data, which could in turn lead to poor decision making.
The severity of this check is 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. It is therefore important to address this issue to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCVPctMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_cum_pct_fav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CACV as (
SELECT
WBS_ID_CA CAWBS,
ABS((SUM(BCWPi_dollars) - SUM(ACWPi_dollars)) / NULLIF(SUM(BCWPi_dollars),0)) CV
FROM DS03_cost C
WHERE upload_ID = @upload_ID
AND WBS_ID_CA NOT IN (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_RC_CVc,'')) <> ''
)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CACV CV ON C.WBS_ID_CA = CV.CAWBS
WHERE
upload_ID = @upload_ID
AND CV.CV > (SELECT TOP 1 thrshld FROM threshold)
)