| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | CRITICAL |
| Unique ID | 1030108 |
| Summary | Is this row duplicated by period date, CA WBS ID, WP WBS ID, EOC, EVT and is_indirect? |
| Error message | Count of period_date, WBS_ID_CA, WBS_ID_WP, EOC, EVT, and is_indirect combo > 1. |
The Data Integrity and Quality (DIQ) check titled "Non-Unique Cost Row" is designed to identify any potential duplicate entries in the DS03 Cost table. This check is important to ensure the accuracy and reliability of the data in the table.
The check operates by examining the combination of the following fields: period date, CA WBS ID, WP WBS ID, EOC, EVT, and is_indirect. If the same combination of these fields appears more than once in the table, it is flagged as a potential duplicate entry.
The likely cause of this error is the entry of identical data in multiple rows. This could be due to a data entry error, or it could indicate a problem with the data source or import process.
To resolve this issue, each row in the DS03 Cost table should have a unique combination of period date, CA WBS ID, WP WBS ID, EOC, EVT, and is_indirect. If duplicates are found, they should be investigated and corrected.
This test is being performed to ensure that there are no duplicate entries in the DS03 Cost table for the combination of period date, CA WBS ID, WP WBS ID, EOC, EVT, and is_indirect. Duplicate entries can lead to incorrect calculations and misinterpretation of data, which can negatively impact the decision-making process.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if any duplicate entries are found, they must be corrected before the data can be further reviewed or analyzed. This is crucial because maintaining data integrity and quality is paramount in ensuring accurate and reliable project management. Any discrepancies in the data can lead to significant errors in project planning, forecasting, execution, and evaluation.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Dupes as (
SELECT period_date, WBS_ID_CA, ISNULL(WBS_ID_WP,'') WBS_ID_WP, EOC, ISNULL(EVT,'') EVT, ISNULL(is_indirect,'') IsInd
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY period_date, WBS_ID_CA, ISNULL(WBS_ID_WP,''), EOC, ISNULL(EVT,''), ISNULL(is_indirect,'')
HAVING COUNT(*) > 1
)
SELECT C.*
FROM DS03_Cost C INNER JOIN Dupes D ON C.period_date = D.period_date
AND C.WBS_ID_CA = D.WBS_ID_CA
AND ISNULL(C.WBS_ID_WP,'') = D.WBS_ID_WP
AND C.EOC = D.EOC
AND ISNULL(C.EVT,'') = D.EVT
AND ISNULL(C.is_indirect,'') = D.IsInd
WHERE upload_ID = @upload_ID
)
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of the 'is_indirect' field. All cases where 'is_indirect' is missing or null are treated as empty strings. |