Data Quality Functions

The Data Quality Functions module manages system and custom functions designed for various data objects, including Tables, Table Columns, Files, and File Columns. Users can navigate and manage these functions using a Tree View structure.

Selecting a Data Object Type

1. From the left menu, click on a specific Data Object Type.

2. Two options will appear:

   1. System

   2. Custom

Viewing System Functions

• Selecting the System option displays all predefined system functions associated with the chosen data object type.

• These functions are read-only and cannot be edited.

Viewing Custom Functions

• Selecting the Custom option displays all user-defined custom functions linked to the selected data object type.

• Custom functions are editable, allowing users to modify existing functions or create new ones under the selected data object type.

Adding Custom Functions

Users can add custom functions for the required object type along with the default dimension.

• Object Type: Displays the default object type. It can be one of the following: Tables, Table Columns, Files, or File Columns.

• Function Name: Enter the name of the function.

• Function Description: Describe the function’s purpose, how it operates, and the type of output it generates.

• Dimension: Select a dimension from the dropdown, such as Uniqueness, Completeness, or others.

Function Details

• When a Data Object Type is selected from the left menu, the corresponding system and custom functions will appear on the right side of the page. The displayed information includes Function Name, Description, Type, Status, etc.

Detailed Function Information

• Selecting a specific function name will provide detailed information, including Input and Success Criteria, Queries, Function Help, and History Details.

Function Details

The Function Details section provides information about each data quality function, helping users assess its capabilities and relevance effectively.

Function Details Includes:

1. Object Type: Displays whether it relates to Tables, Table Columns, Files, or File Columns.

2. Function Name: Displays the name of the function.

3. Function Description: It explains what the function does, how it operates, and the type of output it generates, helping users understand its purpose.

4. Dimension: Indicates aspects of the function, such as Uniqueness, Completeness, etc.

5. Execution Metrics: Indicates how the result values are presented, such as Count or Percentage

6. Remediation Center Support: Specifies whether the function supports a remediation center.

7. Data Filter Support: Indicates if the function supports data filtering.

Input and Success Criteria Details

The Input and Success Criteria Details section outlines the functionality and options available for data quality functions:

Input Values: Indicates whether the function supports user-provided input values. If supported, users can:

• Enter specific input values.

• Select operators such as Equal to, Not equal to, etc.

• Choose the input value data type, including Number, Date, or Text.

Success Criteria: Specifies if the function supports success criteria. If supported, users can:

• Select success criteria validation types, such as Percent or Count.

• Define labels for success criteria both before and after function validation.

• Choose operators and specify data types for the success criteria.

Note: These settings are editable only for custom-defined functions. For system-defined functions, the details are displayed as read-only.

Queries

The Query Tab displays queries associated with a specific connector, including the supported data types for the function. These queries are used to execute data quality rules.

Query Types

• Rule Query: Fetches results after executing the data quality rule.

• Statistics Query: Retrieves the passed row count, failed row count, and total row count for the table or file where the data quality rule is applied.

• Failed Values Query: Fetches the failed values after executing the data quality rule.

Custom Functions: For custom functions, users can add or edit queries for the required connector along with the supported data types.

To add a query for a custom function:

1. Click the + Icon. This opens the Add Connector Queries pop-up window.

   
2. Select the Connector

• Use the dropdown to select the connector. The dropdown lists all connectors configured in OvalEdge.

• Additionally, the user can enter the Connector Name in the Select the Connector field.

1. Enter Queries

• Rule Query: Define the query to fetch results after executing the data quality rule.

Example: SELECT SUM(CASE WHEN ($INPUTQUERY) THEN 1 ELSE 0 END) AS result

FROM $TABLE WHERE 1=1 $FILTER $ORDERBY

• Rule Query Return Type: Specify the return type for the Rule Query.

• Statistics Query: Enter the query to retrieve counts for passed rows, failed rows, and total rows.

Example: SELECT COUNT(*) AS Totalcount,

SUM(CASE WHEN ($INPUTQUERY) THEN 1 ELSE 0 END) AS Passedcount,

SUM(CASE WHEN ($INPUTQUERY) THEN 0 ELSE 1 END) AS Failedcount

FROM $TABLE

WHERE 1=1 $FILTER $ORDERBY

• Failed Values Query: Define the query to fetch failed values after rule execution.

SELECT $SELECTCOLUMNS

FROM $TABLE

WHERE (CASE WHEN ($INPUTQUERY) THEN 0 ELSE 1 END) = 1 $FILTER $ORDERBY

1. Specify Supported Data Types

• Enter the data types supported by the function.

• Example: INT, BIGINT, SMALLINT, DECIMAL, FLOAT, DOUBLE, TINYINT, MEDIUMINT, YEAR, BIT, BINARY

Once all fields are completed, save the configuration to associate the queries with the selected connector.

Edit Query

To edit the connector queries

1. Click on the Connector Name column

1. Click the Edit icon. This will open the Edit Connector Queries pop-up window

   
2. In Edit Connectors Queries users can modify the Rule Query, Rule Query Return Type, Statistics Query, Failed Values Query, and specify the Supported Data Types.

Note: The Connector Name is not editable and only the functions which are in Draft status can be edited. Once the function is published, users can no longer add or edit the queries.

1. After making the necessary changes, save them to apply the updates.

Functional Help

The Function Help section provides a detailed explanation of the function that helps users understand the function's purpose and how to apply it effectively.

It includes:

• The formula used for calculations.

• A step-by-step example with sample data to demonstrate the function's usage and application.

• Examples of Success Criteria and Failed Criteria to illustrate how the function meets or does not meet the required conditions.

Users can define Function Help for custom functions to explain their usage, functionality, and behavior. This includes:

• How to use the function.

• How the function works, with examples and formulas used for calculations.

The content provided in the Function Help tab is displayed when users add a rule and select the function. Clicking on the Function Help shows these details, enabling users to understand and apply the function effectively to data quality rules.

History

All changes made to Function Details, Input & Success Criteria Details, Queries, and Function Help are tracked and displayed in the History tab. The tab logs the username, date, and timestamp of each activity, such as status changes or updates.

• System Functions:

  • The History tab tracks function status changes and query updates.

  • Each entry includes the username and the date and time of the change.

• Custom Functions:

  • The History tab displays all updates related to Function Name, Description, Example, Input Conditions, Success Criteria Conditions, Queries, Function Help etc.

  • Each entry includes the username and the date and time of the change.