하나의 파일에 2개이상의 FORMAT을 가진 데이터 들이 섞여있을 경우에 사용하는 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.
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.
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.
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.
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.
Click OK.
Right-click on the datastore and select View Data to display the file content and make sure it is defined correctly.
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.