10 Understanding Performance and Advanced ETL Concepts

Set-Based Mode

In set-based mode, Oracle Warehouse Builder generates a single SQL statement that processes all data and performs all operations. Although processing data as a set improves performance, the auditing information available is limited. Runtime auditing is limited to reporting of the execution error only. With set-based mode, you cannot identify the rows that contain errors.

Figure 10-4 shows a simple mapping and the associated logic that Oracle Warehouse Builder uses to generate code for the mapping when run in set-based operating mode. TAB1, FLTR, and TAB2 are processed as a set using SQL.

Figure 10-4 Simple Mapping Run in Set-Based Mode

Description of "Figure 10-4 Simple Mapping Run in Set-Based Mode"

To correctly design a mapping for the set-based mode, avoid operators that require row- by-row processing such as Match Merge and Name and Address operators. If you include an operator in the data flow that cannot be performed in SQL, then Oracle Warehouse Builder does not generate set-based code and displays an error when you run the package in set-based mode.

For target operators in a mapping, the loading types INSERT/UPDATE and UPDATE/INSERT are always valid for set-based mode. Oracle Warehouse Builder supports UPDATE loading in set-based mode only with Oracle Database is 10g or later. Oracle Warehouse Builder also supports the loading type DELETE in set-based mode. For a complete listing of how Oracle Warehouse Builder handles operators in set-based mappings, see Table 10-2.

Row-Based Mode

In row-based mode, Oracle Warehouse Builder generates statements that process data row by row. The select statement is in a SQL cursor. All subsequent statements are PL/SQL. You can access full run time auditing information for all operators performed in PL/SQL and only limited information for operations performed in the cursor.

Figure 10-5 shows a simple mapping and the associated logic that Oracle Warehouse Builder uses to generate code for the mapping when run in row-based operating mode. TAB1 is included in the cursor and processed as a set using SQL. FLTR and TAB2 are processed row by row using PL/SQL.

Figure 10-5 Simple Mapping Run in Row-Based Mode

Description of "Figure 10-5 Simple Mapping Run in Row-Based Mode"

If the mapping includes any SQL-based operators that cannot be performed in PL/SQL, Oracle Warehouse Builder attempts to generate code with those operations in the cursor. To generate valid row-based code, design your mapping such that if you include any of the following SQL-based operators, Oracle Warehouse Builder can include the operations in the cursor:

• Aggregation

• Deduplicator

• Joiner

• Lookup

• Sequence

• Set

• Sorter

For the preceding operators to be included in the cursor, do not directly precede it by an operator that generates PL/SQL code. In other words, you cannot run the mapping in row-based mode if it contains a Transformation implemented as a procedure, a Flat File used as a source, a Match Merge, or Name and Address operator directly followed by any of the seven SQL-based operators. For the design to be valid, include a staging table between the PL/SQL generating operator and the SQL-based operator.

Row-Based (Target Only) Mode

In row-based (target only) mode, Oracle Warehouse Builder generates a cursor select statement and attempts to include as many operations as possible in the cursor. For each target, Oracle Warehouse Builder inserts each row into the target separately. You can access full run time auditing information for all operators performed in PL/SQL and only limited information for operations performed in the cursor. Use this mode when you expect fast set-based operations to extract and transform the data but need extended auditing for loading the data, which is where errors are likely to occur.

Figure 10-6 shows a simple mapping and the associated logic that Oracle Warehouse Builder uses to generate code for the mapping when run in row-based (target only) operating mode. TAB1 and FLTR are included in the cursor and processed as a set using SQL. TAB2 is processed row by row.

Figure 10-6 Simple Mapping Run in Row-Based (Target Only) Mode

Description of "Figure 10-6 Simple Mapping Run in Row-Based (Target Only) Mode"

Row-based (target only) mode places the same restrictions on SQL-based operators as the row-based operating mode. Additionally, for mappings with multiple targets, Oracle Warehouse Builder generates code with a cursor for each target.

Committing Data from a Single Source to Multiple Targets

To populate multiple targets based on a common source, you may also want to ensure that every row from the source affects all targets uniformly.

Figure 10-7 shows a PL/SQL mapping that illustrates this case. The target tables all depend upon the source table. If a row from SOURCE causes changes in multiple targets (for instance TARGET_1 and TARGET_2), then Oracle Warehouse Builder should commit the appropriate data to both affected targets at the same time. If this relationship is not maintained when you run the mapping again, then the data can become inaccurate and possibly unusable.

Figure 10-7 Mapping with Multiple Targets Dependent on One Source

Description of "Figure 10-7 Mapping with Multiple Targets Dependent on One Source"

If the number of rows from the source table is relatively small, maintaining the three targets may not be difficult. Manually maintaining targets dependent on a common source, however, becomes more tedious as you increase the number of rows from the source, or as you design more complex mappings with more targets and transformations.

To ensure that every row in the source properly affects every target, configure the mapping to use the correlated commit strategy.

Using the Automatic Correlated Commit Strategy

In set-based mode, correlated commit may impact the size of your rollback segments. Space for rollback segments may be a concern when you merge data (insert/update or update/insert).

Correlated commit operates transparently with PL/SQL bulk processing code.

The correlated commit strategy is not available for mappings run in any mode that are configured for Partition Exchange Loading or that include a Queue, Match Merge, or Table Function operator.

Automatic Commit versus Automatic Correlated Commit

The combination of the commit strategy and operating mode determines mapping behavior. Table 10-3 shows the valid combinations that you can select.

Correlated commit is not applicable for row-based (target only). By definition, this operating mode places the cursor as close to the target as possible. In most cases, this results in only one target for each select statement and negates the purpose of committing data to multiple targets. If you design a mapping with the row-based (target only) and correlated commit combination, then Oracle Warehouse Builder runs the mapping but does not perform the correlated commit.

To understand the effects each operating mode and commit strategy combination has on a mapping, consider the mapping from Figure 10-7. Assume the data from source table equates to 1,000 new rows. When the mapping runs successfully, Oracle Warehouse Builder loads 1,000 rows to each of the targets. If the mapping fails to load the 100th new row to Target_2, then you can expect the following results, ignoring the influence from other configuration settings such as Commit Frequency and Number of Maximum Errors:

• Set-based/ Correlated Commit: A single error anywhere in the mapping triggers the rollback of all data. When Oracle Warehouse Builder encounters the error inserting into Target_2, it reports an error for the table and does not load the row. Oracle Warehouse Builder rolls back all the rows inserted into Target_1 and does not attempt to load rows to Target_3. No rows are added to any of the target tables. For error details, Oracle Warehouse Builder reports only that it encountered an error loading to Target_2.

• Row-based/ Correlated Commit: Beginning with the first row, Oracle Warehouse Builder evaluates each row separately and loads it to all three targets. Loading continues in this way until Oracle Warehouse Builder encounters an error loading row 100 to Target_2. Oracle Warehouse Builder reports the error and does not load the row. It rolls back the row 100 previously inserted into Target_1 and does not attempt to load row 100 to Target_3. Next, Oracle Warehouse Builder continues loading the remaining rows, resuming with loading row 101 to Target_1. Assuming Oracle Warehouse Builder encounters no other errors, the mapping completes with 999 new rows inserted into each target. The source rows are accurately represented in the targets.

• Set-based/ Automatic Commit: When Oracle Warehouse Builder encounters the error inserting into Target_2, it does not load any rows and reports an error for the table. It does, however, continue to insert rows into Target_3 and does not roll back the rows from Target_1. Assuming Oracle Warehouse Builder encounters no other errors, the mapping completes with one error message for Target_2, no rows inserted into Target_2, and 1,000 rows inserted into Target_1 and Target_3. The source rows are not accurately represented in the targets.

• Row-based/Automatic Commit: Beginning with the first row, Oracle Warehouse Builder evaluates each row separately for loading into the targets. Loading continues in this way until Oracle Warehouse Builder encounters an error loading row 100 to Target_2 and reports the error. Oracle Warehouse Builder does not roll back row 100 from Target_1, does insert it into Target_3, and continues to load the remaining rows. Assuming Oracle Warehouse Builder encounters no other errors, the mapping completes with 999 rows inserted into Target_2 and 1,000 rows inserted into each of the other targets. The source rows are not accurately represented in the targets.

Embedding Commit Logic into the Mapping

For PL/SQL mappings only, you can embed commit logic into the mapping design by adding a Pre-Mapping Process or Post-Mapping Process operator with SQL statements to commit and rollback data. When you run the mapping, Oracle Warehouse Builder commits or rollback data based solely on the SQL statements you provide in the Pre-Mapping Process or Post-Mapping Process operator.

Use these instructions to implement a business rule that is tedious or impossible to design given existing Oracle Warehouse Builder mapping operators. For example, you may want to verify the existence of a single row in a target. Write the required logic in SQL and introduce that logic to the mapping through a pre or post mapping operator.

To include commit logic in the mapping design:

1. Design the mapping to include a Pre-Mapping Process or Post-Mapping Process operator. Use one of these operators to introduce commit and rollback SQL statements.

2. Configure the mapping with Commit Control set to Manual.

   In the Projects Navigator, right-click the mapping and select Configure. Under Code Generation Options, select Commit Control to Manual.

   To understand the implications of selecting to commit data manually, see "About Manual Commit Control".

3. Deploy the mapping.

4. Run the mapping.

   Oracle Warehouse Builder runs the mapping but does not commit data until processing the commit logic you wrote in the Pre-Mapping Process or Post-Mapping Process operator.

Committing Data at Runtime

For PL/SQL mappings alone, you can run mappings and issue commit and rollback commands from the SQL*Plus session. Based on your knowledge of SQL*Plus and the "Main Procedure", you can manually run and validate multiple mappings before committing data.

To commit data manually at runtime:

1. Design the PL/SQL mappings. For instance, create one mapping to load dimensions and a separate mapping to load cubes.

   These instructions are not valid for SQL*Loader and ABAP mappings.

2. Configure both mappings with the Commit Control parameter set to Manual.

   In the Projects Navigator, right-click the mapping and select Configure. Under the Code Generation Options, set the Commit Control parameter to Manual.

3. Generate each mapping.

4. From a SQL*Plus session, issue the following command to run the first mapping called map1 in this example:

   var status VARCHAR2(30);
   execute map1.main(:status);
   

   The first line declares the predefined status variable described in Table 10-4. In the second line, p_status is set to the status variable. When map1 completes, SQL*Plus displays the mapping status such as OK.

5. Run the second mapping, in this example, the cubes mapping called map2.

   You can run the second in the same way you ran the previous map. Or, you can supply additional parameters listed in Table 10-4 to dictate how to run the map2 in this example:

   map2.main (p_status => :status,               \
              p_operating_mode => 'SET_BASED',   \
              p_audit_level => 'COMPLETE');
   

6. Verify the results from the execution of the two mappings and send either the commit or rollback command.

7. Automate your commit strategy as described in "Committing Mappings through the Process Flow Editor".

Committing Mappings through the Process Flow Editor

For PL/SQL mappings alone, you can commit or rollback mappings. Based on your knowledge of the SQL*PLUS activity, the Main Procedure, and writing PL/SQL scripts, you can use process flows to automate logic that commits data after all mappings complete successfully or rollback the data if any mapping fails.

To commit multiple mappings through a process flow:

1. Design the PL/SQL mappings.

   These instructions are not valid for SQL*Loader and ABAP mappings.

2. Ensure each mapping is deployed to the same schema.

   All mappings must have their locations pointing to the same schema. You can achieve this by designing the mappings under the same target module. Or, for multiple target modules, ensure that the locations point to the same schema.

3. Configure each mapping with the Commit Control parameter set to Manual.

   In the Projects Navigator, right-click the mapping and select Configure. Under Code Generation Options, set the Commit Control parameter to Manual.

4. Design a process flow using a SQL*PLUS activity instead of multiple mapping activities.

   In typical process flows, you add a Mapping activity for each mapping and the process flow runs an implicit commit after each Mapping activity. However, in this design, do not add mapping activities. Instead, add a single SQL*PLUS activity.

5. Write a PL/SQL script that uses the main procedure to run each mapping. The following script demonstrates how to run the next mapping only if the initial mapping succeeds.

   declare
       status VARCHAR2(30);
   begin
       map1.main(status);
       if status != 'OK' then
          rollback;
       else
          map2.main(status);
          if status != 'OK' then
              rollback;
          else
              commit;
          end if;
       end if;
   end;
   

6. Paste your PL/SQL script into the SQL*PLUS activity.

   In the editor explorer, select SCRIPT under the SQL*PLUS activity and then double-click Value in the object inspector.

7. Optionally apply a schedule to the process flow as described in "Defining Schedules".

8. Deploy the mappings, process flow, and schedule if you defined one.

Maintaining Relationships Between Master and Detail Records

You can maintain the relationship between master and detail records if both types of records share a common field. If Example 10-1 contains a field Employee ID in both Employee and Payroll records, then you can use it as the primary key for the Employee table and as the foreign key in the Payroll table, thus associating Payroll records to the correct Employee record.

However, if your file does not have a common field that is required to join master and detail records, you must add a sequence column to both the master and detail targets (see Table 10-5 and Table 10-6) to maintain the relationship between the master and detail records. Use the Sequence operator to generate this additional value.

Table 10-5 represents the target table containing the master records from the file in Example 10-1. The target table for the master records in this case contains employee information. Columns E1-E10 contain data extracted from the flat file. Column E11 is the additional column added to store the master sequence number. Notice that the number increments by one for each employee.

Table 10-6 represents the target table containing the detail records from the file in Example 10-1. The target table for the detail records in this case contains payroll information, with one or more payroll records for each employee. Columns P1-P6 contain data extracted from the flat file. Column P7 is the additional column added to store the detail sequence number. Notice that the number for each payroll record matches the corresponding employee record in Table 10-5.

Extracting and Loading Master-Detail Records

This section contains instructions on creating a mapping that extracts records from a master-detail flat file and loads those records into two different tables. One target table stores master records and the other target table stores detail records from the flat file. The Mapping Sequence is used to maintain the master-detail relationship between the two tables.

This procedure outlines general steps for building such a mapping. Additional detailed instructions are available at:

• "Flat File Operator"

• "Using the Add Operator Dialog Box to Add Operators"

• "Sequence Operator"

• "Configuring Mappings Reference"

• Oracle Warehouse Builder Sources and Targets Guide

To extract from a master-detail flat file and maintain master-detail relationships, use the following steps:

1. Import and sample the flat file source that consists of master and detail records.

   When naming the record types as you sample the file, assign descriptive names to the master and detail records. This makes it easier to identify those records in the future.

   In this example, for multi-record-type flat files, the Flat File Sample Wizard contains department and employee information. The master record type (for employee records) is called EmployeeMaster, while the detail record type (for payroll information) is called PayrollDetail.

2. Drop a Flat File operator onto the Mapping Editor canvas and specify the master-detail file from which you want to extract data.

3. Drop a Sequence operator onto the mapping canvas.

4. Drop a Table operator for the master records onto the mapping canvas.

   You can either select an existing workspace table that you created earlier or create a new unbound Table operator with no attributes. You can then map or copy all required fields from the master record of the Flat File operator to the master Table operator (creating columns) and perform an outbound reconciliation to define the table later.

   The table must contain all the columns required for the master fields you want to load plus an additional numeric column for loading sequence values.

5. Drop a Table operator for the detail records onto the mapping canvas.

   You can either select an existing workspace table that you created earlier or create a new unbound Table operator with no attributes. You can then map or copy all required fields from the master record of the Flat File operator to the master Table operator (creating columns) and perform an outbound synchronize to define the table later.

   The table must contain all the columns required for the detail fields you want to load plus an additional numeric column for loading sequence values.

6. Map all of the necessary flat file master fields to the master table and detail fields to the detail table.

   Figure 10-8 displays the mapping of the fields.

7. Map the Sequence NEXTVAL attribute to the additional sequence column in the master table.

   Figure 10-8 displays the mapping from the NEXTVAL attribute of the Sequence operator to the master table.

8. Map the Sequence CURRVAL attribute to the additional sequence column in the detail table.

   Figure 10-8 shows a completed mapping with the flat file master fields mapped to the master target table, the detail fields mapped to the detail target table, and the NEXTVAL and CURRVAL attributes from the Mapping Sequence mapped to the master and detail target tables, respectively.

   Figure 10-8 Completed Mapping from Master-Detail Flat File to Two Target Tables

   
   Description of "Figure 10-8 Completed Mapping from Master-Detail Flat File to Two Target Tables"
   
   

9. Configure the mapping that loads the source data into the target tables with the following parameters:

   Direct Mode: Not selected

   Errors Allowed: 0

   Row: 1

   Trailing Nullcols: True (for all tables)

Error Handling Suggestions

This section contains error handling recommendations for files with varying numbers of errors.

If your data file almost never contains errors:

1. Create a mapping with a "Sequence Operator".

2. Configure a mapping with the following parameters:

   Direct Mode= Not selected

   ROW=1

   ERROR ALLOWED = 0

3. Generate the code and run an SQL*Loader script.

   If the data file has errors, then the loading stops when the first error occurs.

4. Fix the data file and run the control file again with the following configuration values:

   CONTINUE_LOAD=TRUE

   SKIP=number of records loaded

If your data file is likely to contain a moderate number of errors:

1. Create a primary key (PK) for the master record based on the seq_nextval column.

2. Create a foreign key (FK) for the detail record based on the seq_currval column which references the master table PK.

   In this case, master records with errors is rejected with all their detail records. You can recover these records by following these steps.

3. Delete all failed detail records that have no master records.

4. Fix the errors in the bad file and reload only those records.

5. If there are very few errors, you may choose to load the remaining records and manually update the table with correct sequence numbers.

6. In the log file, you can identify records that failed with errors because those errors violate the integrity constraint. The following is an example of a log file record with errors:

   Record 9: Rejected - Error on table "MASTER_T", column "C3". 
   ORA-01722: invalid number 
   Record 10: Rejected - Error on table "DETAIL1_T". 
   ORA-02291: integrity constraint (SCOTT.FK_SEQ) violated - parent key not found
   Record 11: Rejected - Error on table "DETAIL1_T". 
   ORA-02291: integrity constraint (SCOTT.FK_SEQ) violated - parent key not found 
   Record 21: Rejected - Error on table "DETAIL2_T". 
   ORA-02291: invalid number  
   

If your data file always contains many errors:

1. Load all records without using the Sequence operator.

   Load the records into independent tables. You can load the data in Direct Mode, with the following parameters that increase loading speed:

   ROW>1

   ERRORS ALLOWED=MAX

2. Correct all rejected records.

3. Reload the file again with a "Sequence Operator".

Subsequent Operations

After the initial loading of the master and detail tables, you can use the loaded sequence values to further transform, update, or merge master table data with detail table data. For example, if your master records have a column that acts as a unique identifier, such as an Employee ID, and you want to use it as the key to join master and detail rows (instead of the sequence field you added for that purpose), you can update the detail tables to use this unique column. You can then drop the sequence column you created for the initial load. Operators such as the Aggregator, Filter, or Match Merge operator can help you with these subsequent transformations.

Using Indirect PEL

If you design a mapping using PEL and it includes remote sources or a join of multiple sources, Oracle Warehouse Builder must perform source processing and stage the data before partition exchange can proceed. Therefore, configure such mappings with Direct PEL set to False. Oracle Warehouse Builder transparently creates and maintains a temporary table that stores the results from source processing. After performing the PEL, Oracle Warehouse Builder drops the table.

Figure 10-11 shows a mapping that joins two sources and performs an aggregation. If all new data loaded into the ORDER_SUMMARY table is always loaded into same partition, then you can use Indirect PEL on this mapping to improve load performance. In this case, Oracle Warehouse Builder transparently creates a temporary table after the Aggregator and before ORDER_SUMMARY.

Figure 10-11 Mapping with Multiple Sources

Description of "Figure 10-11 Mapping with Multiple Sources"

Oracle Warehouse Builder creates the temporary table using the same structure as the target table with the same columns, indexes, and constraints. For the fastest performance, Oracle Warehouse Builder loads the temporary table using parallel direct-path loading INSERT. After the INSERT, Oracle Warehouse Builder indexes and constrains the temporary table in parallel.

Example: Using Direct PEL to Publish Fact Tables

Use Direct PEL when the source table is local and the data is of good quality. You must design the mapping such that the source and target are in the same database and have the same structure. The source and target must have the same indexes and constraints, the same number of columns, and the same column types and lengths.

For example, assume that you have the same mapping from Figure 10-11 but would like greater control on when data is loaded into the target. Depending on the amount of data, it could take hours to load and you would not know precisely when the target table would be updated.

To instantly load data to a target using Direct PEL:

1. Design one mapping to join source data, if necessary, transform data, ensure data validity, and load it to a staging table. Do not configure this mapping to use PEL.

   Design the staging table to exactly match the structure of the final target that you load in a separate mapping.

   For example, the staging table in Figure 10-11 is ORDER_SUMMARY and should be of the same structure as the final target, ORDER_CUBE in Figure 10-12.

2. Create a second mapping that loads data from the staging table to the final target. Configure this mapping to use Direct PEL.

   Figure 10-12 displays the mapping that loads data from the staging table to the final target.

   Figure 10-12 Publish_Sales_Summary Mapping

   
   Description of "Figure 10-12 Publish_Sales_Summary Mapping"
   
   

3. Use either Oracle Warehouse Builder Process Flow Editor or Oracle Workflow to start the second mapping after the completion of the first.

Step 1: Create All Partitions

Oracle Warehouse Builder does not automatically create partitions during run time. Before you can use PEL, you must create all partitions as described in "Defining Partitions".

For example, if you select Month as the frequency of new data collection, you must create all the required partitions for each month of new data. Use the object editors to create partitions for a table, dimension, or cube.

To use PEL, all partition names must follow a naming convention. For example, for a partition that holds data for May 2002, the partition name must be in the format Y2002_Q2_M05.

For PEL to recognize a partition, its name must fit one of the following formats:

Ydddd

Ydddd_Qd

Ydddd_Qd_Mdd

Ydddd_Qd_Mdd_Ddd

Ydddd_Qd_Mdd_Ddd_Hdd

Ydddd_Qd_Mdd_Ddd_Hdd_Mdd

Where d represents a decimal digit. All the letters must be in upper case. Lower case is not recognized.

If you correctly name each partition, Oracle Warehouse Builder automatically computes the Value Less Than property for each partition. Otherwise, you must manually configure Value Less Than for each partition for Oracle Warehouse Builder to generate a DDL statement. The following is an example of a DDL statement generated by Oracle Warehouse Builder:

. . .
PARTITION A_PARTITION_NAME 
      VALUES LESS THAN (TO_DATE('01-06-2002','DD-MM-YYYY')),
. . .

Step 2: Create All Indexes Using the LOCAL Option

Add an index (ORDER_SUMMARY_PK_IDX) to the ORDER_SUMMARY table. This index has two columns, ORDER_DATE and ITEM_ID. Set the following on the Indexes tab of the Table Editor:

• Select UNIQUE in the Type column.

• Select LOCAL in the Scope column.

Now Oracle Warehouse Builder can generate a DDL statement for a unique local index on table ORDER_SUMMARY.

Using local indexes provides the most important PEL performance benefit. Local indexes require all indexes to be partitioned in the same way as the table. When the temporary table is swapped into the target table using PEL, so are the identities of the index segments.

If an index is created as a local index, the Oracle server requires that the partition key column must be the leading column of the index. In the preceding example, the partition key is ORDER_DATE and it is the leading column in the index ORDER_SUMMARY_PK_IDX.

Step 3: Primary/Unique Keys Use "USING INDEX" Option

In this step you must specify that all primary key and unique key constraints are created with the USING INDEX option. In the Projects Navigator, right-click the table and select Configure. The Configuration tab for the table is displayed. Select the primary or unique key in the left panel and select Using Index in the right panel.

With the USING INDEX option, a constraint does not triggers automatic index creation when it is added to the table. The server searches existing indexes for an index with same column list as that of the constraint. Thus, each primary or unique key constraint must be backed by a user-defined unique local index. The index required by the constraint ORDER_SUMMARY_PK is ORDER_SUMMARY_PK_IDX which was created in "Step 2: Create All Indexes Using the LOCAL Option".