Tuesday Jun 14, 2011

Complex File Processing - 0 to 60

The Complex File technology adds a powerful new driver into the ODI capabilities. It leverages the Fusion Middleware capabilities for supporting complex files, you can then build ODI interfaces just as you are used to!

Using an example from the Oracle┬« Fusion Middleware User's Guide for Technology Adapters 11g Release 1 (see here), the viewlet quickly builds up the model based on the XSD for the complex file (use Native Format Builder to construct).

 Have a look at the viewlet right here

http://blogs.oracle.com/dataintegration/resource/viewlets/odi_11_complexfile_viewlet_swf.html

You can pause it to catch your breath! Its condensed into 60 seconds to keep your attention.

Sunday Sep 13, 2009

How to Define Multi Record Format Files in ODI

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Datastore, Model and Logical Schema are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

It is not unusual to have to load files containing various record formats. For example a company might store orders and order lines using distinct record formats in the same flat file or you might have one single file containing a header, some records and a footer.
In this post, we'll use the following source file as an example:


1,101,2009/09/01,Computer Parts
2,234,101,Motherboard, Asus P6T,239.99
2,235,101,CPU,Intel Celeron 430,40
1,102,2009/09/02,Computer Parts
2,301,102,CPU,AMD Phenom II X4,170
1,103,2009/09/05,Printers
2,401,103,Inkjet Printer,Canon iP4600,69.99
2,402,103,Inkjet Printer,Epson WF30,39.99
2,403,103,Inkjet Printer,HP Deskjet D2660,49.99

As we can see the Order and Order Lines records have different formats (one has 4 fields, the other has 6 fields), they could also have a different field separator.

Identifying the Record Codes

The first step in order to handle such a file in ODI is to identify a record code, this record code should be unique for a particular record type. In our example the record code will be used by ODI to identify if the record is an Order or an Order Line. All the Order records should have the same record code, this also applies to the Order Lines records.
In our example the first field indicates the record code:
- 1 for Orders records.
- 2 for Order Lines records.

Define the Datastores

We assume that you have already created a Model using a Logical Schema that points to the directory containing your source file.

We will start by defining a datastore for the Order records.

Right-click on the File model and select Insert Datastore.
In the Definition tab, enter a name and specify the flat file resource name in the Resource Name field. 

Order_Datastore.jpg

 

 

In the Files tab, specify your flat file settings (delimiter, field separator etc.)

Refer to the ODI documentation for additional information regarding how to define a flat file datastore.

 

 

In our example the Order records have 4 fields:
- RECORD_TYPE
- ORDER_ID
- ORDER_DATE
- ORDER_TYPE

Go to the columns tab and add those 4 columns to your datastore.

Now specify the Record Code in the 'Rec. Code' field of the RECORD_CODE column.

 

 

Order_Datastore_Columns.jpg

Click OK.

 

 

In the Models view, right-click on the datastore and select View Data to display the file content and make sure it is defined correctly. 

 

Order_Datastore_Data.jpg

The data is filtered based on the record code value, we only see the Order records.

 

We will now apply the same approach to the Order Lines record.

Right-click on the File model and select Insert Datastore to add a second datastore for the Order Lines record.

In the Definition tab, enter a name and specify the flat file resource name in the Resource Name field. We are pointing this datastore to the same file we used for the Order records. 

 

Order_Line_Datastore.jpg

In the Files tab, specify your flat file settings (delimiter, field separator etc.).
Refer to the ODI documentation for additional information regarding how to define a flat file datastore.

 

 

In our example the Order Lines records have 6 fields:
- RECORD_TYPE
- LORDER_ID
- ORDER_ID
- LINE_ORDER_TYPE
- ITEM
- PRICE

Go to the columns tab and add those 6 columns to your datastore.

Now specify the Record Code in the 'Rec. Code' field of the RECORD_CODE column.

 

Order_Line_Datastore_Columns.jpg

Click OK.

 

Right-click on the datastore and select View Data to display the file content and make sure it is defined correctly.

 

Order_Line_Datastore_Data.jpg

The data is filtered based on the record code value, we only see the Order Lines records.

You can now use those 2 datastores in your interfaces.

 

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
23
24
25
26
27
28
29
30
   
       
Today