Developing a New Knowledge Module 1

After the interface is created and tested, you may need to create a new knowledge module to
use with the interface.
In this practice, you duplicate an existing knowledge module, IKM SQL Incremental Update,
naming the new knowledge module IKM Oracle UI - Audit. You add two commands to the knowledge module, “Create Audit Table” and “Insert Audit Records,” using command syntax provided in text files.
Next, you duplicate an existing interface, INT-Exp-FF-RT, naming the new interface INT-EXPFF-RT. You change the new interface’s IKM selection to use the new knowledge module you just created, IKM Oracle UI - Audit.
Finally, you execute interface INT-EXP-FF-RT and examine the audit records inserted into the audit table created by your knowledge module.
Duplicate the knowledge module, IKM SQL Incremental Update, naming the new KM IKM Oracle UI – Audit. You insert into the KM two new commands: “Create Audit Table”
and “Insert Audit Records.”
Duplicate the interface INT-EXP-FF-RT, naming the new interface INT-EXP-FF-RT-Audit.
You change the interface’s IKM entry to use your new KM: IKM Oracle UI – Audit.
Your Tasks:
1. Create the new knowledge module with new functionality to create an audit table and insert audit records.

a. If not connected, connect to the DEV_ODI_REPO work repository (User:SUPERVISOR, Password: SUNOPSIS). Click the Designer tab.



b. Open ODI Designer and click the Projects tab. Select Projects > Export-FF-RT >Knowledge Modules > Integration (IKM). Right-click IKM SQL Incremental Update and select Duplicate Selection. In the window that follows, click Yes.


c. Double-click Copy IKM SQL Incremental Update to edit it. Rename this KM IKM Oracle UI – Audit. Click the Details tab.
Note: If the message for Object Locking/Unlocking is displayed, click OK.


 
 
d.Click the Details tab. On the Details screen, review the commands in the current KM.Select Insert new rows and then click the Add icon to add the new command.




e. In the Command New window, name this command Create Audit Table. Select the Ignore Errors check box. Set the Technology on Target to Oracle. Enter the following command to create the audit table. Verify that the check boxes in the Journalizing section are not selected. If necessary, deselect them.
Note: You can find this command in the file 12-2.txt, provided in the following location: c:\Labs\Text.


create table <%=odiRef.getTable("L", "TARG_NAME", "A")%>_H
(
<%=odiRef.getColList("", "[COL_NAME]\t[DEST_CRE_DT]NULL",
",\n\t", "", "PK")%>,
DATE,
VARCHAR2(1)
AUDIT_DATE
AUDIT_INDICATOR
)


f. Click the Details tab. Scroll down and select the Create Audit Table command.Click the icon several times to move the command up and place it right after the Insert new rows step, as shown below. Select the Ignore Errors check box, as shown in the following below. Click the Add icon again




Similarly, create the command that inserts audit records in the audit table, as shown in the screen. Name this command Insert Audit Records. Select the Ignore Errors check box. Set the Technology on Target to Oracle. Enter the command provided below.
Note: You can find this command in the file 12-2.txt, provided in the following location: c:\Labs\Text.

Insert into <%=odiRef.getTable("L", "TARG_NAME", "A")%>_H
(
<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "PK")%>,
AUDIT_DATE,
AUDIT_INDICATOR
)
select <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "",
"PK")%>,
sysdate,
IND_UPDATE
from <%=odiRef.getTable("L","INT_NAME","W")%>

Note: In these substitution methods, you use the following parameters: GetTable:
  • “L”: Local naming convention. For example, in Oracle that would beschema.table (versus “R” for remote: schema.table@server).
  •  “A”: Automatic. It enables ODI to determine which physical schema to use (theData schema [“D”] or the Staging schema [“W”]).
getColList:

  • Notice the “PK” parameter. If it is used, only the columns that are part of the
primary key are included.

No comments:

Post a Comment