Handling Errors and Recovering from Failure

Designing Advanced ETL Scenarios Using SAP Data Services Integrator Transforms

Optimizing Performance

Auditing Data Flows

Objective

After completing this lesson, you will be able to implement the SAP Data Services auditing feature to check data quality.

Auditing

Building robust Extract, Transform, Load (ETL) processes is essential for mastering SAP Data Services. While designing efficient data movement is important, production success also depends on how the system handles errors and unexpected behavior.

High-quality data integration requires a safety net to manage exceptions and anticipate failures. To resolve errors, you must first identify them.

Auditing is the primary tool for monitoring data flow and ensuring quality during job execution. You can define audit points, set up rules, and track statistics to identify where data might be lost or corrupted.

Statistics can be collected from any Data Services object—such as a source, transform, or target—to verify that the data flow performed as intended.

The image illustrates a data set analysis where a source data set with 6 rows is divided into two subsets: North Sales with 3 rows and South Sales with 2 rows, highlighting that the combined number of rows in the subsets (5 rows) does not equal the number of rows in the source data set (6 rows).

While auditing alerts you to issues, you must investigate the root cause. Potential issues include erroneous source data, incorrect transform configurations, or loading errors.

Setting Up Audit

To use auditing, follow these steps:

Define audit points: These points collect and store runtime statistics for data flowing out of objects.
Define rules: Use statistics to verify that data at specific points meets your expectations. Verification occurs when data is:
- Extracted from sources.
- Processed by transforms.
- Loaded into targets.
Generate runtime notifications: Notifications include a list of failed audit rules and the statistical values at the time of failure.
Display audit statistics: After a job executes, use these statistics to identify which objects in the data flow produced incorrect data.

Audit Points and Labels

An audit point is the object in a data flow where statistics are collected. You can audit a source, a transform, or a target.

When defining audit points, you must specify an audit function. This function represents the specific statistic (such as a count or sum) that Data Services collects for a table, output schema, or column.

The available audit functions depend on the type of audit point selected.

Audit Functions

Choose from these audit functions:

Data Object	Function	Description
Table or output schema	Count	This function collects two statistics: Good count: Rows successfully processed. Error count: Rows that generated errors (if error handling is enabled). The data type for this function is integer.
Column	Sum	Calculates the sum of numeric values in the column for "good" rows only. This applies to integer, decimal, double, and real data types.
Column	Average	Calculates the average of numeric values in the column for "good" rows only. This applies to integer, decimal, double, and real data types.
Column	Checksum	Used to detect involuntary changes in column values using a Checksum value (The checksum of "London" will be different from the checksum of "london" or " London"). This applies only to varchar data types.

An audit label is a unique name generated by Data Services for each audit statistic in the data flow. Labels are created for every defined audit function at each audit point.

The image displays two interface screenshots from a data auditing tool, with the top section showing Audit Points and Labels where users can select audit functions and labels for specific data points like ods_customer and cust_temp, and the bottom section titled Audit Functions illustrating the Schema Properties window where users can define audit functions such as count, error count, and data type for a selected data point.

Audit Labels

If the audit point is on a table or output schema, the Count function generates these two labels:

$Count_objectname
$CountError_objectname

If the audit point is on a column, the label uses the following format:

$auditfunction_objectname.

Note

An audit label becomes invalid if the associated object is deleted or renamed. Invalid labels appear under a separate node on the Labels tab. To fix this, recreate the labels and delete the invalid entries.

Defining Audit Rules and Actions

The Rule tab of the Audit dialog shows the different audit rules created on the audit labels.

The image depicts a software interface for configuring auditing rules, featuring sections for defining rules, specifying actions on failure (such as raising exceptions or emailing a list), and custom functions, with options to add or delete rules and a close button at the bottom.

Audit rules compare statistics between objects. For example, you can use a rule to verify that the row count in a source table matches the row count in the target table.

An audit rule is a Boolean expression consisting of a left-hand-side (LHS), a Boolean operator, and a right-hand-side (RHS). The LHS can be a single label, an expression using multiple labels and mathematical operators, or a function. The RHS can also be a constant.

Examples of Audit Rules

$Count_CUSTOMER = $Count_CUSTDW.
$Sum_ORDER_US + $Sum_ORDER_EUROPE = $Sum_ORDER_DW.
round($Avg_ORDER_TOTAL) >= 10000.

Audit Actions

You can choose any combination of the following actions to trigger upon an audit failure:

E-mail to List: Sends a notification identifying the failed rule to the specified email addresses. Separate multiple addresses with commas or use a variable.
Script: Executes a custom script.
Raise exception: Logs the failed rule in the Error Log and stops the job at the first failure.
This is the default action. If this is disabled, the job will complete successfully even if a rule fails, and no messages will be written to the job log.

If multiple actions are selected, Data Services executes them in the order listed above.

Note

The E-mail to List option uses the smtp_to function. You must configure the SMTP server and sender details in the Data Services Server Manager.

Audit Status

Audit status can be viewed in these locations:

Action on Failure	Where to View Audit Information
Raise an exception	Job Error Log, Metadata Reports
E-mail to list	E-mail message, Metadata Reports
Script	Custom script destination, Metadata Reports

Tracing Audit

You can enable or disable auditing in the Job Execution Properties dialog box.

To view audit results directly in the log, set Trace Audit Data to Yes in the Job Execution Properties dialog box.

The image shows a computer screen displaying a configuration window for tracing various functions in a software application, with Trace Audit Data selected and set to Yes, alongside a log output window showing multiple audit entries and data flow messages, indicating successful data processing and some errors.

Auditing a Data Flow

Follow this simulation to learn how to implement the Audit feature.

ExerciseStart Exercise

Summary

In this lesson, you have learned how to implement the audit feature.

Define audit points to collect runtime statistics from sources, transforms, or targets during data flow execution.
Create audit rules using Boolean expressions to compare statistics and verify data integrity across objects.
Configure audit actions including email notifications, custom scripts, or exceptions when rules fail during execution.
Use audit functions like Count, Sum, Average, and Checksum to monitor data quality and track processing.
Enable trace auditing in Job Execution Properties to view real-time audit results and troubleshoot issues.

Next lesson