Uniqueness Rules

Uniqueness Rules

In this series of detailed workshops we're going to:

• Understand the business value and ROI of implementing business rules

• Master the 7 Data Quality Dimensions in PDC

• Create and configure business rules with detailed step-by-step guidance

• Implement rule actions (Status, Tags, Webhooks) and understand their purpose

• Organize rules using rule groups for operational efficiency

• Monitor and interpret rule execution results for continuous improvement

x

---

Accessing your Catalog

To get started using the Data Catalog, log in using the address and credentials provided by your Data Catalog service user or administrator.

To access your catalog, please follow these steps:

1. Open Google Chrome web browser.

2. Navigate to:

https://pdc.pentaho.labpdc.pentaho.lab

1. Enter following email and password, then click Sign In.

Username: [email protected] (mapped to Business Steward role)

Password: Welcome123!

---

x

x

Duplicate Customer Detection

Duplicate Customer Detection

Scenario: Identify potential duplicate customer records based on name and contact info.

Business Rule Configuration:

Rule Name: Customer_Duplicate_Detection
Description: Identifies potential duplicate customer records
Data Quality Dimension: Uniqueness
Schedule: Weekly
Target: Person.Person

SQL Query:

WITH DuplicateCandidates AS (
    SELECT 
        FirstName, 
        LastName, 
        EmailAddress,
        COUNT(*) AS DupCount
    FROM Person.Person p
    JOIN Person.EmailAddress e ON p.BusinessEntityID = e.BusinessEntityID
    GROUP BY FirstName, LastName, EmailAddress
    HAVING COUNT(*) > 1
)
SELECT 
    (SELECT COUNT(DISTINCT BusinessEntityID) FROM Person.Person) AS total_count,
    (SELECT COUNT(DISTINCT BusinessEntityID) FROM Person.Person) - 
        (SELECT SUM(DupCount - 1) FROM DuplicateCandidates) AS scopeCount,
    (SELECT SUM(DupCount - 1) FROM DuplicateCandidates) AS nonCompliant

---

x

Unique Product Numbers

Incomplete Product Information

Scenario: Ensure ProductNumber is truly unique across all products.

Business Rule Configuration:

Rule Name: Product_Number_Uniqueness
Description: Validates ProductNumber uniqueness constraint
Data Quality Dimension: Uniqueness
Schedule: Daily
Target: Production.Product

SQL Query:

WITH DuplicateProducts AS (
    SELECT ProductNumber, COUNT(*) AS CountDuplicates
    FROM Production.Product
    GROUP BY ProductNumber
    HAVING COUNT(*) > 1
)
SELECT 
    COUNT(*) AS total_count,
    COUNT(*) - COALESCE((SELECT SUM(CountDuplicates - 1) FROM DuplicateProducts), 0) AS scopeCount,
    COALESCE((SELECT SUM(CountDuplicates - 1) FROM DuplicateProducts), 0) AS nonCompliant
FROM Production.Product

---

x

x

x

First Tab

x

Second Tab

x