Before I start defining interfaces I first need to define my target datasource which is an Oracle 10g schema.
I will re-organize my models to be able to identify the source- and target-datamodels used for my migration-path.
I create a new Model Folder to hold the different data-models which will be used for my project.
Next I drag-and-drop the Data Model I’ve already created – see previous posts – inside my Model Folder and rename the Model to ‘SRC_Model_Excel’ by double-clicking.
Now I need to define the Physical and Logical Schema for my target-datastore which is my Oracle 10g schema.
Navigate to the Topology Manager and go to Technologies and choose ‘Insert Data Server for the Oracle-technology icon.
Define the connection settings for your Oracle Connection such as username/password and the JDBC configuration settings (JDBC-driver = oracle.jdbc.driver.OracleDriver,Url = jdbc:oracle:thin:@::). Test the connection-settings are defined correcttly via the Test-button and the local agent (depending on the configuration of your ODI). Click OK.
Update the default settings for the Physical Schema in the following way:
- Define the schema to be your personal schema, your user-defined schema and the work schema is the work repository we’ve defined earlier. I’m very glad ‘Cenisis’ on the ODI-OTN forum has helped me figure out the difference between these two schema’s. If you want more information take a look at the following thread: http://forums.oracle.com/forums/thread.jspa?forumID=374&threadID=491550
- Make sure to grant select-privileges ont the work repository schema to your user-schema because otherwise the execution of the interface will fail!
- Define the Context of your physical schema, use the ‘Global’ context and define a new Logical Schema ‘Demo_DB’ and click OK.
Next we will define our data model for the target-datastore in our Models-tab in the Designer-window of ODI.
Choose ‘Insert Model’ and define this as being the target-datastore and choose ‘Oracle’ as ‘Technology’ and the Logical Schema that was defined earlier. Define the ‘Global’ context in the ‘Reverse’-tab and choose which tables you want to reverse engineer in the ‘Selective Reverse’-tab.
The different tables you’ve chosen to reverse-engineer will be shown in the Model as datastore.
Now it’s time to define a new interface to transform our Excel data (source) to our oracle data (target), the different tasks we need to perform:
- Create a New Interface.
- Define the Target Datastore.
- Define the source datastores, filters and joins on these sources.
- Define the mapping between source and target data.
- Define the interface flow.
- Define the flow control.
- Execute the interface for testing.
Go back to the first tab of your Designer-window, the ‘Projects’-tab and right-click on the ‘Interface’-icon in the Folder and choose ‘Insert Interface’.
Make sure to map the fields correctly by clicking on the attribute in the target-datastore and drag-and-drop the attribute from the source datastore to the implementation-tab in the mapping-screen.
Make sure to check the ‘Active Mapping’ Checkbox and set the “execute on” radio button to the target area.
We will import the needed LKM, IKM and CKM to use them in our interface where we are migrating data from Excel to Oracle. Depending on the scenario, or use case you’re working out you need to use other KM’s which is explained in the guides (ODI Documentation Library).
Go to the Knowledge Module-node inside Designer, choose the Loading (LKM) KM and right-click and choose import.
Open up the interface you’ve defined earlier and open up the ‘Flow’-tab and click on the SSO_0 or the source-datastore. Choose the LKM you’ve defined earlier from the LKM Selection Dropdown in the screen below.
In the Controls-tab of your interface you need to select the CKM to be used.
Save your settings and choose to ‘Execute’ your interface … in my case I got the following error ‘Flow Control not possible if no Key is declared in your Target Datastore’. And after a little bit of snooping around I’ve figured out the problem, there was no Primary Key defined for my target datastore.
If you need to define constraints for your datastores you’ve got to go to the Model and drill down into the specific DataStore. Right-click on the Constraints-node and choose to define new constraints, choose the type ‘PK’/’Unique’/’Alternate’ and in the Columns-tab choose the column.
Navigate back to your interface and choose the second tab ‘Diagram’ and choose your Target DataStore, in the screen below define the Primary Key you’ve defined earlier in the ‘Update Key’-dropdown.
Choose to Execute the interface again, choose Yes for the default settings to execute the interface.
To follow the result of your execution in the Operator, click on the operator-icon in your menu bar.
The Operator window that subsequently opens (and can be refreshed using the button if the window is already open) details the execution process tasks.