OWB - Mapping Configuration

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

What means the different values configuration of a mapping.

Advertising

3 - General

Properties Description
Deployable If you disable it, scripts for that mapping are not generated.
Language Warehouse Builder sets the correct value depending on the mapping: PL/SQL, SQL*Loader, ABAP (for an SAP source mapping)
Referred Calendar If you want to schedule the mapping to run based on a previously defined schedule

4 - Code Generation Option

ANSI SQL Syntax If you select this option, ANSI SQL syntax is generated. Else, Oracle SQL syntax is generated.

4.1 - Commit Control

Transaction Commit Control parameters on a mapping.

Automatic automatically commits data based on the mapping design. For multiple targets in a single mapping, data is committed based on target by target processing (insert, update, delete).
Automatic Correlated specialized type of automatic commit that applies only to PL/SQL mappings with multiple targets. Warehouse Builder considers all targets collectively and commits or rolls back data uniformly across all targets. The mapping behavior varies according to the operating mode you select. For more information about correlated commit, see Committing Data from a Single Source to Multiple Targets.
Manual Select manual commit control for PL/SQL mappings that you want to interject complex business logic or perform validations before committing data. Options :
* commit logic within the mapping as described in “Embedding Commit Logic into the Mapping
* commit data in a process flow or from a SQL Plus session as described in “Committing Data Independently of Mapping Design”.
No Commit If you set this option, then OWB mapping does not issue a commit while the mapping executes.
Advertising

4.2 - Others

Analyze Table Statements If you select this option, code is generated for analyzing the target table after the target is loaded, if the resultant target table is double or half its original size. If the target table is not in the same schema as the mapping and you wish to analyze the table, then you would need to grant ANALYZE ANY to the schema owning the mapping.
Enable Parallel DML If you select this option, Parallel DML is enabled at runtime. Executing DML statements in parallel improves the response time of data-intensive operations in large databases that are present in a data warehouse.
Optimized Code Select this option to improve performance for mappings that include the OWB - splitter operator and inserts into multiple target tables. When this option is selected and the mapping is executed by Oracle9i or higher, a single SQL statement is generated (multi_table_insert) that inserts data into multiple tables based on the same set of source data.
Authid
Use Target Load Ordering For PL/SQL mappings with multiple targets, you can generate code that defines an order for loading the targets. This is important when a parent-child relationship exists between two or more targets in a mapping. The option is selected by default.
ERROR TRIGGER Specify the name of the error trigger procedure in this field.
Bulk Processing Code If this configuration parameter is selected and the operating mode is set to row based, Warehouse Builder generates PL/SQL Data Processing - (Batch|Bulk) Processing processing code. PL/SQL Data Processing - (Batch|Bulk) Processing processing improves row-based ETL performance by collecting, processing, and writing rows in Data Processing - (Batch|Bulk) Processing , instead of doing it row by row. The size of each bulk is determined by the configuration parameter Bulk Size. Set based mode offers optimal performance, followed by bulk processing, and finally by row based mode. For more information, see the Oracle PL/SQL Reference Guide.
Generation Mode By default, when code is generated for a mapping, the code for all possible operating modes is generated. That is, if you set Default Operating Mode to set based, Warehouse Builder still generates code for all possible operating modes when Generation Mode is set to All Operating Modes. This enables you to switch the operating modes for testing purposes at runtime.

5 - Runtime Parameters

Bulk Use Bulk Size to specify the number of rows in each bulk for PL/SQL Data Processing - (Batch|Bulk) Processing Processing. Warehouse Builder uses the Data Processing - (Batch|Bulk) Processing Size parameter only when Data Processing - (Batch|Bulk) Processing Processing Code option is selected and the operating mode is set to row based.
Analyze Table Sample Percentage When you select the Analyze Table Statements option, Warehouse Builder estimates when gathering statistics on the target tables. After data is loaded into the target tables, statistics used for cost-based optimization are gathered on each target table. You can set this parameter to the percentage of rows in each target table used for this analysis.
Commit Frequency Commit frequency applies only to non-bulk mode mappings. Bulk mode mappings commit according to the bulk size.
Maximum Number of Errors Execution of the package terminates when the number of errors (warning) exceeds the maximum number of errors value
Advertising

5.1 - Default Operating Mode

(DEFAULT_OPERATING_MODE)

Set based A single SQL statement that inserts all data and performs all operations on the data is generated. This increases the speed of Data Manipulation Language (DML) operations. Set based mode offers optimal performance but minimal auditing details
Row based Statements that process data row by row are generated. The select statement is a SQL cursor. All subsequent statements are PL/SQL. Because data is processed row by row, the row based operating mode has the slowest performance but offers exhaustive auditing details
Row based (Target Only) A cursor select statement is generated and attempts are made to include as many operations as possible in the cursor. For each target, Warehouse Builder generates a PL/SQL insert statement and inserts each row into the target separately
Set based fail over row based (SET_BASED_FAIL_OVER_TO_ROW_BASED) The mapping is executed in set based mode. If an error occurs, the execution fails and the mapping is started over again in the row based mode. This mode is recommended for use only in test environments and is not recommended for use in production environments
Set based fail over row based (Target Only) The mapping is first executed in set based mode. If an error occurs, the execution fails over to Row based (Target Only) mode. This mode is recommended for use only in test environments and is not recommended for use in production environments.
SELECT prop.*
FROM cmpstringpropertyvalue_v prop
WHERE logicalname = '8i.MAPPINGS.PLSQLSTEP.RUNTIMEPARAMS.DEFAULTOPERATINGMODE'
ORDER BY prop.creationtimestamp;

5.2 - Default Audit Level

Audit levels dictate the amount of audit information captured in the runtime schema when the package is run.

None No auditing information is recorded in runtime
Statistics Statistical auditing information is recorded in runtime
Error Details Error information and statistical auditing information is recorded in runtime
Complete All auditing information is recorded in runtime. Running a mapping with the audit level set to Complete generates a large amount of diagnostic data which may quickly fill the allocated tablespace

5.3 - Default Purge Group

Default Purge Group is used when executing the package. Each audit record in the runtime schema is assigned to the purge group specified.

6 - Reference