Monday, December 10, 2012

[ODI 11g] Creating RDBMS Schema In ODI


       


Creating RDBMS Schema for ODI SourceDatastore                           
You need to create a schema to host the RDBMS table. In this OBE, you use the RDBMS user/schema ODI_STAGE.

To create a new RDBMS schema for the ODI datastore,perform the following steps:

1. Start SQL Developer. You will create the new schema/user by executing the following SQL commands:

create user ODI_STAGE identified by ODI_STAGE

default tablespace users temporary tablespace temp;
grant connect, resource, create trigger, create view to ODI_STAGE;
 




2. If not done before, in SQL Developer create new connection called ODI_STAGE. Enter User name as ODI_STAGE with password ODI_STAGE. For SID enter ORCL. Click Test to verify connection, and then click Connect

 



Creating a New ODI Dataserver, Physical and Logical Schemas for RDBMS table


Note: You may skip this section if you completed one of the following OBEs:

  • Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table
  • Creating an ODI Project and Interface: Exporting an RDBMS Table to a Flat File

After you created the new database target datastore, you need to create a new ODI target data server and the Physical schema. To create the ODI target data server and physical schema, perform the following steps:

1. In ODI, Open ODI Topology Navigator and then select the Physical Architecture tab. Expand Technologies, right-click Oracle and select New Data Server. 




2. In the Data Server: New window, enter the values provided in the table below. Click the JDBC tab.
Parameter
Value
Name
ODI_STAGE
Instance/dblink (Data Server)
ORCL
User
ODI_STAGE
Password
ODI_STAGE















3. select jdbc:oracle:thin:@<host>:<port>:<sid>, and then click OK. Edit the Url to read: jdbc:oracle:thin:@localhost:1521:ORCL for Oracle Database.
 
Note: Do not copy and paste in the JDBC Url field. This may cause problems with entering a valid URL string.








4. Click Test Connection. In the window that opens, click Yes to save your data. In the Information window, click OK. Click Test to verify successful connection. Click OK. 








5. Click Expand Oracle technology node, right-click ODI_STAGE dataserver, and then select New Physical Schema. 



6. In Schema (Schema) and Schema (Work Schema) field enter your ODI_STAGE schema. Click Save button. Close ODI_STAGE.ODI_STAGE physical schema window. In the Information window, click OK. 







7. Open Logical Architecture tab, expand Technologies > Oracle. Right-click Oracle technology and then select New Logical Schema.




 1. Name logical schema ODI_STAGE. In the Global context, connect this logical schema to ODI_STAGE physical schema as shown below. Click Save
      button and then close the tabs.





Creating a New ODI Project

 To create a new project within Oracle Data Integrator, perform the following steps:

1. Start ODI Designer: Start > Programs > Oracle > Oracle Data Integrator > ODI Studio . Select WORKREP1 from the Login Name drop-down list if not already selected. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.




 2. In the designer tab, click the Projects tab, click New Project icon
, and then click New Project.




3. On the screen that appears, set the Name of the project to Procedure-CRT-TBL in the Name field. The Code field is filled automatically. Click Save iconThe newly created Procedure-CRT-TBL project now appears in the Projects tree view. You have now successfully added a new ODI project.




Creating ODI Procedure to Create and Populate RDBMS Table


To create a new ODI procedure to create and populate RDBMS table, perform the following steps:


1. In the Projects tab, expand: Procedure-CRT-TBL > First Folder. Right-click Procedures and select New Procedure.

 
 
 
2. Enter the procedure name as PRD-create-populate-table. Set Target Technology to Oracle. Click the Details tab. Click the iconto add a step in the procedure.



 



3. In the Command: New window, enter the parameters of the procedure and the command on target provided below. You need this command to drop the table in case the table with this name exists. Select the Ignore Errors check box. Select details tab. 


Parameter
   Value
Name
   Drop table
Technology
   Oracle
Schema
   ODI_STAGE


DROP table ODI_STAGE.SRC_SALES_PERSON1 


DROP table ODI_STAGE.SRC_SALES_PERSON1
  


Note: You may find this and following commands here.


DROP table ODI_STAGE.SRC_SALES_PERSON1 
-----------------------------------------------------------------------------------------
CREATE table "SRC_SALES_PERSON1" (
"SALES_PERSON_ID" NUMBER(8,0) NOT NULL,
"FIRST_NAME" VARCHAR2(80),
"LAST_NAME" VARCHAR2(80),
"DATE_HIRED" VARCHAR2(80),
"DATE_UPDATED" DATE NOT NULL,
constraint "SRC_SALES_PERSON1_PK" primary key("SALES_PERSON_ID")
)

-----------------------------------------------------------------------------------------
begin
insert into ODI_STAGE.SRC_SALES_PERSON1 values(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(20,'Jennie','Daumesnil','28/02/1988',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(51,'Cynthia','Nagata','28/02/1994',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values(52,'William','Kudo','28/03/1993',sysdate);
end;




4. Click the iconto add another step in the procedure. In the Command: New window, if not selected, click the “Command on Target” tab. Enter the parameters of the procedure and the command provided below, and then click the Details tab. 

Parameter
Value
Name
Create Table
Technology
Oracle
Schema
ODI_STAGE
 
CREATE table "SRC_SALES_PERSON1" (
"SALES_PERSON_ID" NUMBER(8,0) NOT NULL,
"FIRST_NAME" VARCHAR2(80),
"LAST_NAME" VARCHAR2(80),
"DATE_HIRED" VARCHAR2(80),
"DATE_UPDATED" DATE NOT NULL,
constraint "TRG_SALES_PERSON1_PK" primary key("SALES_PERSON_ID")

)
/
 




 


5. Click the iconto enter the step to populate the SRC_SALES_PERSON table. Enter the parameters of the procedure and the command provided below, and then click the Details tab. 


Parameter
Value
Name
Populate table
Technology
Oracle
Schema
ODI_STAGE
  




begin
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(20,'Jennie','Daumesnil','28/02/1988',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(51,'Cynthia','Nagata','28/02/1994',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values
(52,'William','Kudo','28/03/1993',sysdate);
end
         









6. View your newly created commands and click Click Save iconto save the procedure. Close the tab.




7. Expand Procedure-CRT-TBL > Procedures, select the newly created procedure PRD-create-populate-table, and then select Execute. Click OK. Click OK again. 








8. Open ODI Operator. In ODI Operator, click All executions node, click the
Refresh icon, find your session, and verify that your procedure executed successfully.





9. Open the Oracle SQL developer and expand ODI_STAGE connection. Select Tables node and click the Refresh Double–click the newly created table SRC_SALES_PERSON1, click Data tab, and verify that your table SRC_SALES_PERSON1 was successfully created and populated with records as shown below.









No comments:

Post a Comment