ODI 12c - loading XML file to SQL table

Here, we will see how to load a XML file to SQL table.

Steps:

Step1: Topology - Physical Architecture - XML

Step2: New data server

Step3: Dataserver name

Step4: JDBC - provide schema path, file directory etc.

Step5: It will create a DTD file for that XML file.

Step6: Created data server - new physical schema.

Step7: provide schema and work schema.

Step8: Logical architecture - XML - create new logical schema.

Step9: provide the physical schemas for each contexts.

Step10: Designer - create new model

Step11: provide name, technology, logical schema etc.

Step12: Reverse engg - select System table

Step13: Selective Reverse engg - select objects and reverse engg.

Step14: datastores created.

Step15: create new project and map and mapp source and target datastores.

Step16: Physical - Choose LKM

Step17: choose IKM and flow control as false.

ODI 12c - Use unique temporary object name

When ODI loads data from source table or datastore to Staging table, it stores to C$_default table then from there it sends the data back to Target table or datastore.

If we want to use unique object name like the unique staging area C$_12unique...default table then we have to go to mapping - physical tab and select option - "Use Unique Temporary object names".

ODI - Import OOTB Knowledge modules

By default, ODI will provide us few of the Global Knkwledge modules. Apart from that, we can also import Oracle provided KMs.

Steps to import:

Step1: Go to your created project and right click on the Knowledge modules object and traverse the following directory and select the required KMs and  press ok 

Path: C:\Oracle\Middleware\Oracle_Home\odi\sdk\xml-reference

ODI 12c - Model creations with constraints

Here, We will create a primary key constraints on dept no. On dept model datastore.

Steps:

Step1: Create a model folder.

Step2: provide folder name

Step3: right click on the created folder and create new model.

Step4: provide model name, technology, logical schema and agent if any and save.

Step5: Go to Reverse engg for masking if any

 

Step6: Go to selective reverse engg and select objects to revers engg and select tables as needed and click reverse engg.

 

Step7: tables will be created.

Step8: Expand dept data store and click on Constraints and new key

Step9:select primary key

Step10: go to attributes and select deptno

Primary key created. This key will be created in ODI level not on db level.

To create db level key, go to control of the constraint and select defined in the database.

ODI 12c - Scheduling ODI load plans

Implementation Steps:

Step1: Locate the ODI load plan you created. And expand your load plan by clicking on the + sign next to the name of the load plan.  This can be found within the “Load Plans and Scenarios” tab. Highlight the “Scheduling” option and right mouse click..  Then select New Scheduling.

Step2: Within the definition tab you, you will need to enter in execution options such as, “Context, Agent, and Log Level.”  Once you have entered that you will need to select a Status. Here in our example we will leave this “Active.”  Once you have selected your status, then you will need to enter you execution options.  Here you will need to select when you want your load plan to run.  Once the following definitions have been set, click the save button. 

Step3: Next you will need to click on the “Topology” tab and expand the “Agents” listed under the “Physical Architecture” sub tab. Select the agent in which you are using to execute your load plan.  Then highlight and right mouse click.  Here a list of options will appear in which you will need to select “Update Schedule.”  This will update the ODI agent to reflect your scheduled changes.

In addition, to make sure your ODI load plan has been added to the ODI scheduler you can view the scheduler by selecting the “View Schedule” when highlighting and right mouse clicking the ODI agent.

ODI - Create Model and Topology objects using Wizard option

 Implementation steps: 

Step1: Go to Design tab and Models Accodian and click the folder sign and New Model and Topology Objects.

Step2: provide Model name, select technology , Logical Schema name and context.

Step3: provide Data server details like Name, User, password, driver, url etc. Next and test connection.

Step4: Provide Physical schema nane, db schema names.

Step5: Model will be created.

Step6: Go to Reverse Enginner and do mask for selecting tables if you need.

Step7: Go to selective Reverse engg a d select objects to reverse engg.

Step8: Datastores tables will be created. Just right click on any table and click view data

ODI - Move file using OdiFileMove command

OdiFileMove: Use this command to move or rename files or a directory into files or a directory.

Here, I have created two referesh variables and one to store source path and another to store archive path. Now connect these two variables with OdiFileMove cmd to move files from source to target.

Once this package created, then create a scenario and run and check in operator tab for execution status.

For more details on cmd and paramters:

https://docs.oracle.com/middleware/1213/odi/reference/opentools.htm#BEIBHCBC