Validity 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

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:

Open Google Chrome web browser.
Navigate to:

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

Enter following email and password, then click Sign In.

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

Password: Welcome123!

Valid Order dates

Scenario: Ensure OrderDate is never in the future and ShipDate is after OrderDate.

Business Rule Configuration:

Rule Name: Order_Date_Validity
Description: Validates logical date relationships in orders
Data Quality Dimension: Validity
Schedule: Daily
Target: Sales.SalesOrderHeader

SQL Query:

SELECT 
    COUNT(*) AS total_count,
    SUM(CASE 
        WHEN OrderDate <= GETDATE() 
        AND (ShipDate IS NULL OR ShipDate >= OrderDate)
        AND (DueDate >= OrderDate)
        THEN 1 ELSE 0 
    END) AS scopeCount,
    SUM(CASE 
        WHEN OrderDate > GETDATE() 
        OR (ShipDate IS NOT NULL AND ShipDate < OrderDate)
        OR (DueDate < OrderDate)
        THEN 1 ELSE 0 
    END) AS nonCompliant
FROM Sales.SalesOrderHeader

Product Quantity Range

Scenario: Inventory quantities should be within reasonable business ranges.

Business Rule Configuration:

Rule Name: Inventory_Quantity_Validity
Description: Validates inventory quantities are within acceptable ranges
Data Quality Dimension: Validity
Schedule: Daily
Target: Production.ProductInventory

SQL Query:

SELECT 
    COUNT(*) AS total_count,
    SUM(CASE 
        WHEN Quantity >= 0 
        AND Quantity <= 10000 
        AND Bin BETWEEN 1 AND 100
        THEN 1 ELSE 0 
    END) AS scopeCount,
    SUM(CASE 
        WHEN Quantity < 0 
        OR Quantity > 10000 
        OR Bin NOT BETWEEN 1 AND 100
        THEN 1 ELSE 0 
    END) AS nonCompliant
FROM Production.ProductInventory

PreviousConsistency Rules NextTimeliness and Conformity Rules

Last updated 4 months ago

Was this helpful?

Good evening

hashtagValidity Rules

hashtagAccessing your Catalog

hashtagValid Order dates

hashtagProduct Quantity Range

Validity Rules

Accessing your Catalog

Valid Order dates

Product Quantity Range