¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 1060260 |
| Summary | Is this BL resource missing among the FC resources? |
| Error message | Combo of task_ID, resource_ID, role_ID, & EOC (where schedule_type = BL) not found in DS06 (where schedule_type = FC). |
¶ 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 "BL Resource Missing Among FC Resources" is designed to identify any discrepancies between the resources listed in the Baseline (BL) schedule and those in the Forecast (FC) schedule in the DS06 Resources table.
The test specifically checks if a combination of task_ID, resource_ID, role_ID, and EOC (End of Contract) from the BL schedule is missing in the FC schedule. If such a combination is not found in the FC schedule, it is flagged as an error.
The likely cause of this error is a mismatch between the resources assigned in the BL and FC schedules. This could be due to a resource being added or removed in the FC schedule without updating the BL schedule, or vice versa.
To resolve this issue, ensure that for every task, the same resources (identified by resource_ID and role_ID) and EOC are assigned in both the BL and FC schedules. If a resource is added or removed in one schedule, the other schedule should be updated accordingly to maintain data integrity.
¶ Why do we check this?
This test is being performed to ensure that the baseline (BL) resources are also included in the forecast (FC) resources. The test checks if the combination of task_ID, resource_ID, role_ID, and EOC (where schedule_type = BL) is found in DS06 (where schedule_type = FC).
The importance of this check is to ensure consistency and completeness of data between the baseline and forecast resources. If a baseline resource is missing in the forecast resources, it could lead to inaccurate forecasting and planning, which could potentially impact the project's success.
The severity of this test is marked as a MAJOR. This means that while the issue does not make the data unreviewable, it is likely to cause problems during analysis. It is recommended to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsBLResourceMissingInFC] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with FCRes as (
SELECT task_ID, ISNULL(subproject_ID,'') SubP, TRIM(ISNULL(resource_ID,'')) ResID, TRIM(ISNULL(role_ID,'')) RoleID, EOC
FROM DS06_schedule_resources
WHERE upload_id = @upload_ID AND schedule_type = 'FC'
)
SELECT BLR.*
FROM DS06_schedule_resources BLR LEFT OUTER JOIN FCRes ON BLR.task_ID = FCRes.task_ID
AND ISNULL(BLR.subproject_ID,'') = FCRes.SubP
AND TRIM(ISNULL(BLR.resource_ID,'')) = FCRes.ResID
AND TRIM(ISNULL(BLR.role_ID,'')) = FCRes.RoleID
AND BLR.EOC = FCRes.EOC
WHERE
upload_id = @upload_ID
AND schedule_type = 'BL'
AND FCRes.task_ID IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of subproject_id field. |