Download Oracle Warehouse Builder User's Guide

Best Practices for Designing SQL*Loader Mappings
Table 9–5
E1 E2
Target Table Containing Master Records
E3 E4
E5
E6
E7
E8
E9
E10
E11
E
003715 4
153 09061987 014000000 "IRENE
HIRSH" 1
08500
1
E
003941 2
165 03111959 016700000 "ANNE
FAHEY" 1
09900
2
E
001939 2
265 09281988 021300000 "EMILY
WELSH" 1
07700
3
Table 9–6 represents the target table containing the detail records from the file in
Example 9–1 on page 9-15. 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 9–5.
Table 9–6
Target Table Containing Detail Records
P1
P2
P3
P4
P5
P6
P7
P
01152000
01162000
00101
000500000
000700000
1
P
02152000
02162000
00102
000300000
000800000
1
P
03152000
03162000
00107
000300000
001000000
2
P
01152000
01162000
00108
000300000
001000000
3
P
02152000
02162000
00109
000300000
001000000
3
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.
These instructions are for conventional path loading. For
instructions on using direct path loading for master-detail records, see
"Using Direct Path Loading to Ensure Referential Integrity in
SQL*Loader Mappings" on page 9-20.
Note:
This procedure outlines general steps for building such a mapping. Additional
detailed instructions are available at:
■
Using the Import Metadata Wizard on page 4-6
■
Flat File Operator in the Warehouse Builder Online Help
■
Adding Operators that Bind to Workspace Objects on page 7-12
■
Sequence Operator in the Warehouse Builder Online Help
■
Configuring Mappings Reference in the Warehouse Builder Online Help
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.
Understanding Performance and Advanced ETL Concepts 9-15