¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 9040134 |
| Summary | Does this SVT have logic? |
| Error message | SVT (subtype = SVT) task_id found in DS05 task_ID list. |
¶ 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 "SVT With Logic" is designed to verify the logical consistency of the Schedule Visibility Task (SVT) in the DS04 Schedule table. This check is particularly important for tasks that are classified as SVT or ZBA subtypes.
The DIQ check operates by cross-referencing the DS04 Schedule table with the DS05 Schedule Logic table. It specifically looks for instances where the task_ID of an SVT or ZBA subtype in the DS04 Schedule table matches a task_ID in the DS05 Schedule Logic table.
If the DIQ check identifies a match, it indicates that the SVT task has associated logic in the DS05 Schedule Logic table. This is an error because SVT tasks should not have associated logic.
The fields causing this issue are the 'subtype' field in the DS04 Schedule table and the 'task_ID' field in both the DS04 Schedule and DS05 Schedule Logic tables. The expected value for the 'subtype' field is 'SVT' or 'ZBA', and the 'task_ID' fields should not have matching values in both tables.
To resolve this issue, review the tasks in the DS04 Schedule table and ensure that SVT and ZBA tasks do not have associated logic in the DS05 Schedule Logic table.
¶ Why do we check this?
This test is being performed to check if the Schedule Visibility Task (SVT) in the DS04 Schedule table has logic. The test is checking if the task_id of the SVT (with subtype = SVT or ZBA) is found in the DS05 task_ID list. This is important because it ensures that the SVT is logically connected to the tasks in the project, which is crucial for accurate project management and forecasting.
The severity of this test is marked as an MINOR. This means that while it may not immediately prevent data from being reviewed or analyzed, it could potentially cause minor problems or indicate that the data does not adhere to all best practices. It's a signal to the data management team to review this aspect of the data and correct it if necessary, to ensure the overall integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_DoesSVTHaveLogic] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Logic as (
SELECT schedule_type, task_ID, ISNULL(subproject_ID,'') SubP
FROM DS05_schedule_logic
WHERE upload_ID = @upload_ID
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN Logic L ON S.schedule_type = L.schedule_type
AND S.task_ID = L.task_ID
AND ISNULL(S.subproject_ID,'') = L.SubP
WHERE
upload_id = @upload_ID
AND subtype = 'SVT'
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for addition of 'subproject_ID' field and for improved filtering of subtypes. |