Oracle University and (Oracle Corporation) use only
Practice 12-2: Developing a New Knowledge Module Use Case Scenario:
To develop the integration project, John created the ODI project and the interface to export data from a flat file and load this data into a relational table. Now, he decides to modify the existing KM to create an audit table each time the interface runs.
Background:
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,” by using command syntax provided in text files.
Next, you duplicate an existing interface, INT-Exp-FF-RT, naming the new interface INT-EXP-FF-RT-AUDIT. 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.
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.
1 2
Oracle University and (Oracle Corporation) use only.
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.
Oracle University and (Oracle Corporation) use only.
c. Double-click Copy of 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.
Oracle University and (Oracle Corporation) use only.
d. 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. Name this command Create Audit Table. Select the Ignore Errors check box. In the Command on Target tab, set the Technology 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.
Oracle University and (Oracle Corporation) use only.
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")%>,
AUDIT_DATE DATE,
AUDIT_INDICATOR VARCHAR2(1)
)
Oracle University and (Oracle Corporation) use only.
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. Verify that the Ignore Errors check box is selected for your new Create Audit Table command.
With the Create Audit Table command still selected, click the Add icon again .
Oracle University and (Oracle Corporation) use only.
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 be schema.table (versus “R” for remote: schema.table@server).
• “A”: Automatic. It enables ODI to determine which physical schema to use (the Data schema [“D”] or the Staging schema [“W”]).
Oracle University and (Oracle Corporation) use only.
getColList:
• Notice the “PK” parameter. If it is used, only the columns that are part of the primary key are included.
`
Oracle University and (Oracle Corporation) use only.
g. Click the Details tab. Scroll down and select the Insert Audit Records command.
Click the icon several times to move the command up and place it right after the Create Audit Table step, as shown in the next screen. Click Save, and then close the tab.
Oracle University and (Oracle Corporation) use only.
h. After clicking Save, verify that your new knowledge module, IKM Oracle UI – Audit, appears in the Knowledge Modules tree.
2. You will create a modified copy of the interface INT-EXP-FF-RT, to execute with your newly created knowledge module. You will need to duplicate the existing interface, rename it, and then change the IKM entry to use your new IKM Oracle UI-Audit.
a. On the Projects tab, right-click the interface INT-EXP-FF-RT and select Duplicate Selection. Click Yes on the Confirmation screen.
Oracle University and (Oracle Corporation) use only.
b. Double-click the interface Copy of INT-EXP-FF-RT. Change the name of the interface to INT-EXP-FF-RT-Audit, as shown in the screenshot. Click the Flow tab.
Oracle University and (Oracle Corporation) use only.
c. On the Flow tab, click Target (ODI_STAGE) in the diagram to open the Target Area – Property Inspector below the diagram. In the IKM Selector drop box, select IKM Oracle UI - Audit. Ensure that in the Options list, FLOW_CONTROL and STATIC_CONTROL options are set to “false”, as shown in the screenshot. Click the Save button, and then close the tab.
Oracle University and (Oracle Corporation) use only.
To execute your new interface, select the interface and click the Execute button . Click OK. Click OK again in the Information window to start the execution.
Oracle University and (Oracle Corporation) use only.
d. Click the Operator tab to open ODI Operator. Click to open the All Executions node. If necessary, click the Refresh icon , find your new interface session, and view the session steps.
Oracle University and (Oracle Corporation) use only.
e. Scroll down and double-click the Create Audit Table step. View the content of the Definition and Code tabs for this step. Close the tab.
Oracle University and (Oracle Corporation) use only.
f. Similarly, view the Insert Audit Records step information. Refer to the following screens. Click Close.
Oracle University and (Oracle Corporation) use only.
g. Open Oracle SQL Developer and expand the ODI_STAGE connection (for the
password, enter ODI_STAGE). Select the Tables (Filtered) node and if necessary click the Refresh button . Click the Audit table TRG_SALES_PERSON_H, click the Data tab, and verify that your Audit table was created and populated with audit records, as shown next.
Oracle University and (Oracle Corporation) use only.
3. In addition, you create an option for your new knowledge module to make your KM more user friendly. This option will enable an end user to choose when to generate audits.
a. In Designer, click the Projects tab, right-click IKM Oracle UI – Audit, and select New Option. Name this option AUDIT_CHANGES, set Type to Check Box, and Default Value to “true”. Click Save and close the AUDIT_CHANGES option tab.
Oracle University and (Oracle Corporation) use only.
b. Now, you need to link this option to your steps in the KM. Double-click IKM Oracle UI - Audit, and click the Details tab. Double-click your new step, Create Audit Table, to edit it. Scroll to the bottom and click the Options plus sign [+] to show its contents.
Oracle University and (Oracle Corporation) use only.
Oracle University and (Oracle Corporation) use only.
c. Deselect the Always Execute check box. Select AUDIT_CHANGES. This will condition the execution of these steps with the value set by the end user. Click Save.
Click the Details tab.
d. Repeat the previous step for the Insert Audit Records command, as shown in the following screens. Click Save to save your KM and close the tab.
Oracle University and (Oracle Corporation) use only.
Oracle University and (Oracle Corporation) use only.
e. Double-click INT-Exp-FF-RT-Audit to edit it. Click the Flow tab, click Target (ODI_STAGE), and verify that the option AUDIT_CHANGES is set to “true”.
Execute the interface .
Oracle University and (Oracle Corporation) use only.
f. If not opened, open ODI Operator, select All Executions, and then click the Refresh button and view the execution results. Both Create Audit Table and Insert Audit Records steps should be present.
Oracle University and (Oracle Corporation) use only.
g. In Designer, edit the INT-Exp-FF-RT-Audit interface again. In the Flow tab, click Target (ODI_STAGE) and set the option AUDIT_CHANGES to “false”. Save your interface and then execute your interface again .
Oracle University and (Oracle Corporation) use only.
Oracle University and (Oracle Corporation) use only.
h. In ODI Operator, click the Refresh button and view the execution results. The new KM steps to create and populate an audit table are not executed.