¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040217 |
| Summary | Is this task missing resources (DS06)? |
| Error message | Task_ID is missing in resources (DS06). |
¶ 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 "Missing Resources" is designed to identify any tasks in the DS04 Schedule table that are missing associated resources in the DS06 Schedule Resources table.
The error message "Task_ID is missing in resources (DS06)" indicates that there is a task in the DS04 Schedule table that does not have a corresponding entry in the DS06 Schedule Resources table. This could be due to an oversight in data entry or a problem with the data import process.
The fields causing the issue are the 'task_ID' and 'schedule_type' fields in both the DS04 Schedule and DS06 Schedule Resources tables. The expected values for these fields should be identical in both tables for each task.
Please note that this check excludes tasks with a subtype of 'SVT' or 'ZBA', and tasks of type 'SM', 'FM', or 'WS'. If a task falls into one of these categories, it is expected not to have a corresponding entry in the DS06 Schedule Resources table.
To resolve this issue, ensure that each task in the DS04 Schedule table has a corresponding entry in the DS06 Schedule Resources table, unless it falls into one of the excluded categories.
¶ Why do we check this?
This test is being performed to ensure that all tasks in the 'DS04 Schedule' table have corresponding resources in the 'DS06' table. The test is checking for any instances where a 'Task_ID' in the 'DS04 Schedule' table does not have a matching entry in the 'DS06' resources table.
The importance of this check is to maintain the integrity and quality of the project management data. In the context of EVMS (Earned Value Management System), each task should have associated resources for proper planning, tracking, and management of the project. If a task is missing resources, it could lead to inaccurate project cost and schedule estimates, and potentially cause problems during the analysis of the project data.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is recommended to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsTaskMissingResources] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Resources as (
SELECT task_ID, schedule_type, ISNULL(subproject_ID,'') SubP
FROM DS06_schedule_resources
WHERE upload_ID = @upload_ID
)
SELECT
S.*
FROM
DS04_schedule S LEFT OUTER JOIN Resources R ON S.schedule_type = R.schedule_type
AND S.task_ID = R.task_ID
AND ISNULL(S.subproject_ID,'') = R.SubP
WHERE
S.upload_id = @upload_ID
AND ISNULL(subtype,'') NOT IN ('SVT', 'ZBA')
AND S.type NOT IN ('SM','FM', 'WS')
AND R.task_ID IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of the 'subproject_id' field. |