Tuesday, September 14, 2021

ODI - File Reading steps

Usecase: Here, we will read a csv file from a file location and then write to oracle db using copied source file data store.

Summary steps:

  1. Know the name/type of file
  2. Preparing the path that ODI can reach that area/machine/sharing folder
  3. Creating physical and logical schema
  4. Creating model after that we can see our file as datastore
  5. Create a target table(with copy paste from the datastore or you can create manually)
  6. Create a map and drag drop these objects into map.
  7. Choosing knowledge modules if nesessary you may need to import new knowledge modules. Path of knowledge modules(ORACLE_HOME/odi/sdk/xml_reference). 
  8. Run

Implementation Steps:

Step1: create source csv file.

File name: sourcedata.csv
Headers: Name,empid,salary
Provide some values.

Step2: upload the file to the correct location .

Here using localhost location as /home/oracle/Documents

Step3: Define source topology physical architecture

Go to Topology tab
File -- new data server -- File_Generic(name)
Keep same jdbc driver details.
In the definition section provide the connections user and password and save -- ok with local(no agent)

On the created data server (File_Generic)-- new physical schema
In the definition area, privide the directory name for both schema and work schema.

Go to the context area and save it. It will show as empty.

Step4: Define source topology logical architecture

File -- new logical schema -- name(LGC_test)
Provide context values for following

Link the created physical schema

Step5: Define source design model

Go to the design tab and create new model folder as file and then create new model as MDL_File
Provide following definition details:
Technology: file
Logical schema: LGC_test

Do reverse enggineering and save

File -- MDL_FILE -- create new data store
Name : sourcedata
Resourcename: sourcedata.csv
UNDER files:
Format: delimited
Heading(number of lines): 2
Field separator: other: ,

Go to attributes and do reverae engg. It will populate all the csv fields. Save and view data.

Step6: preparing the target table.

Copy the created souredata datastore to another model for exaple HR model.(it has a oracle db connection)

Open that copied datastore and change the details like name ans resourcename s fileone.


Step7: Mapping from source to target

Create a new project as file_projects
Create new mapping as map_file_import
Drag and drop both the datastores in the map area.
Link these 2 tables and map the fields. You can automap or also can do manually.


From mapping area, go to the physical tab and change the loading knowledge module to LKM file to oracle(Builtin) Global

In the Integration knowledge module, make true for create target table and truncate target table

Note: optionally you can import knowledge modules from project knowledge module section.

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...