Completeness Rule

Completeness Rule

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

Completeness rules are the easiest to understand and often have the highest immediate business impact. Missing data is obvious and actionable.

---

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!

---

Title

Title

Problem: AdventureWorks needs customer titles (Mr., Ms., Dr., etc.) for:

• Personalized communication and correspondence

• Formal business letters and documents

• Professional customer relationship management

• Marketing segmentation by demographics

• Cultural and social protocol adherence

Business Impact: If 40% of customers lack title information:

• Inability to personalize communications properly (generic "Dear Customer" messages)

• Risk of offending customers with incorrect forms of address

• Reduced effectiveness of targeted marketing campaigns

• Poor customer experience in formal communications

• Missed opportunity to demonstrate attention to detail and professionalism

Goal: Ensure at least 70% of active customers have title information on file.

Business Rule Configuration:

BUSINESS RULE NAME: Customer Title Completeness

DESCRIPTION: Goal: 70% completeness for customer titles (Mr., Ms., Dr., etc.)

NOTE: This rule checks Person.Person table for missing Title field. Titles are important for personalized communications and professional correspondence.

CUSTOM TAGS: completeness, customer-data, personalization (optional)

BUSINESS RULE TYPE: Internal Data Quality

DATA QUALITY DIMENSION: Completeness

SELECTED TARGET: Person.Person

SQL REFERENCES: Person.Person

SQL Query:

-- =============================================================================
-- TITLE COMPLETENESS CHECK
-- Purpose: Find persons who are missing title information (Mr., Ms., Dr., etc.)
-- Target Table: Person.Person
-- Referenced Tables: Person.Person
-- =============================================================================

SELECT
    -- total_count: Total number of persons in scope (denominator for percentage)
    -- COUNT(*) counts all rows that match the WHERE clause
    -- This represents our universe of persons we expect to have titles
    COUNT(*) AS total_count,
    
    -- scopeCount: Number of persons who HAVE title information (passes quality check)
    -- COUNT(p.Title) only counts non-NULL values
    -- SQL's COUNT() function ignores NULL values by design
    -- This is the "compliant" count - persons with complete title data
    COUNT(p.Title) AS scopeCount,
    
    -- nonCompliant: Number of persons WITHOUT title information (fails quality check)
    -- Uses CASE statement to count NULLs explicitly
    -- WHEN Title IS NULL THEN 1: adds 1 for each missing title
    -- ELSE 0: adds 0 for each existing title
    -- SUM totals all the 1s to get count of missing titles
    SUM(CASE WHEN p.Title IS NULL THEN 1 ELSE 0 END) AS nonCompliant

FROM Person.Person p

WHERE
    -- Filter: Only check specific person types that require titles
    -- SC = Store Contact, IN = Individual Customer, SP = Sales Person, EM = Employee
    -- These are customer-facing or professional roles where titles are important
    -- Excludes types like 'VC' (Vendor Contact) who may not need titles tracked
    p.PersonType IN ('SC', 'IN', 'SP', 'EM')

-- =============================================================================
-- QUERY EXPLANATION FOR PDC:
-- - PDC requires exactly 3 columns: total_count, scopeCount, nonCompliant
-- - All column names must match exactly (case-sensitive in some databases)
-- - COUNT(column) automatically excludes NULLs - this is SQL standard behavior
-- - SUM(CASE...) pattern explicitly counts NULL values for nonCompliant
--
-- RESULTS INTERPRETATION:
-- - If total_count=10000, scopeCount=7000, nonCompliant=3000
-- - Then: 70% have titles (7000/10000), 30% missing (3000/10000)
-- - This would PASS if threshold is ≥70%, FAIL if threshold is ≥75%
--
-- WHY TITLE COMPLETENESS MATTERS:
-- - Personalization drives engagement (studies show 20-30% lift in response)
-- - Proper forms of address show respect and professionalism
-- - Titles indicate professional status (Dr., Prof.) important for B2B
-- - Cultural sensitivity: some cultures place high importance on titles
--
-- TROUBLESHOOTING:
-- - If nonCompliant=0: Check PersonType filter values
-- - If total_count seems low: Verify ModifiedDate filter isn't too restrictive
-- - If total_count != scopeCount + nonCompliant: Check for data type issues
-- - Common titles in data: Mr., Mrs., Ms., Dr., Prof., Sr., Sra.
-- =============================================================================

Query Explanation:

• LEFT JOIN finds people WITHOUT emails (that's what we're looking for)

• PersonType filter focuses on customers/employees who need emails

• Date filter focuses on active records

---

1. In left menu, Click on: 'Data Operations' option -> Business Rules

Create a Business Rule

1. In the Business Rules Tile, Click on: 'Business Rules'

Add Business Rule

1. Click on: Add Business Rule.

2. Enter the following details:

Setting	Value
BUSINESS RULE NAME	Customer Title Completeness
DESCRIPTION	Goal: 70% completeness for customer titles (Mr., Ms., Dr., etc.)
NOTE	This rule checks Person.Person table for missing Title field. Titles are important for personalized communications and professional correspondence.
CUSTOM TAGS	completeness, customer-data, personalization
RULE ENABLED	☑ (checked)
RULE APPROVED	☑ (checked)

Create Title Completeness Rule

1. Click: 'CREATE BUSINESS RULE'.

2. Click: 'CONFIGURE'.

Configure Rule

x

x

x

x

Email Addresses

Product Information

x

First Tab

x

Second Tab

x