Join the two source  and apply a LEFT OUTER JOIN  and in the join query box delete the condition and write the query in this form namely.

NOT EXISTS ( SELECT ‘X’ FROM  SCHEMA_NAME.SOURCE_1 ( TABLE TO BE MINUS WITH ) WHERE JOINING CONDITION.

So for my Example it is 

NOT EXISTS ( SELECT ‘X’ FROM ODI_TEMP.SOURCE_1 WHERE SOURCE_2.EMP_ID=SOURCE_1.EMP_ID)

Also enable Distinct Rows option as there a cross join occurs and thus getting the right record .

comparing the Data between the minus query and odi interface result reveals that they are similar.

Lets look at another example where the table columns are mismatched.

Similarly LEFT OUTER JOIN  on the Source with to be compared and writing the condition and also enabling the distinct records option.

NOT EXITS( SELECT ‘X’ FROM ODI_TEMP.DUP_CHANNELS ( TO BE MINUS WITH) WHERE CHANNELS.CHANNEL_ID=DUP_CHANNELS.CHANNEL_ID( JOINING CONDITION)

SELECT DISTINCT
CASE
WHEN SNP_VAR.I_PROJECT IS NULL
THEN 'GLOBAL_VARIABLE'
ELSE SNP_PROJECT.PROJECT_NAME
END                 AS PROJECT_NAME,
SNP_VAR.VAR_NAME    AS VARIABLE_NAME,
CASE
WHEN SNP_VAR.VAR_DATATYPE='A' THEN 'ALPHANUMERIC'
WHEN SNP_VAR.VAR_DATATYPE='N' THEN 'NUMERIC'
WHEN SNP_VAR.VAR_DATATYPE='D' THEN 'DATE'
ELSE 'TEXT' END     AS DATATYPE,
CASE WHEN SNP_VAR.VAR_DATATYPE='T' THEN V_TXT.TXT
ELSE SNP_VAR.DEF_v  END      AS DEFAULT_VALUE,
SNP_VAR.lschema_name AS VARIABLE_QUERY_SCHEMA,
SNP_TXT.TXT         AS VARIABLE_QUERY,
S_TXT.TXT           AS DESCRIPTION
FROM SNP_VAR
LEFT OUTER JOIN SNP_PROJECT ON SNP_VAR.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT=SNP_VAR.I_TXT_VAR_IN
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_VAR.I_TXT_VAR
LEFT OUTER JOIN SNP_TXT V_TXT ON V_TXT.I_TXT=SNP_VAR.i_txt_var_val_txt
ORDER BY project_name

PROJECT_NAME  , FOLDER_NAME  ,  INTERFACE_NAME  , INTERFACE_TYPE , TARGET_SCHEMA , TARGET_TABLE , TARGET_COLUMN , TARGET_DATATYPE , SOURCE_SCHEMA , SOURCE_TABLE , SOURCE_COLUMN , SOURCE_DATATYPE , MAPPING  filtered by Interface , Folder  or  Project Name. I have commented out other option of Interface & Folder..

SELECT DISTINCT
SNP_PROJECT.PROJECT_NAME    AS PROJECT_NAME,
SNP_FOLDER.FOLDER_NAME      AS FOLDER_NAME,
SNP_POP.POP_NAME            AS INTERFACE_NAME,
CASE
WHEN SNP_POP.WSTAGE='E' THEN 'TABLE_TO_TABLE_INF'
ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE,
SNP_POP.LSCHEMA_NAME        AS TARGET_SCHEMA,
SNP_POP.TABLE_NAME          AS TARGET_TABLE,
SNP_POP_COL.COL_NAME        AS TARGET_COLUMN,
CASE
WHEN SNP_POP.WSTAGE='E' THEN T_COL.SOURCE_DT||' ('||T_COL.LONGC||')'
ELSE SNP_POP_COL.SOURCE_DT||' ('||SNP_POP_COL.LONGC||')'
END AS TRG_DATATYPE,
SNP_SOURCE_TAB.LSCHEMA_NAME AS SOURCE_SCHEMA,
SNP_TABLE.TABLE_NAME        AS SOURCE_TABLE,
SNP_COL.COL_NAME            AS SOURCE_COLUMN ,
SNP_COL.SOURCE_DT||' ('||SNP_COL.LONGC||')' AS SRC_DATATYPE,
rowtocol('SELECT DISTINCT TXT FROM SNP_TXT WHERE I_TXT='||i_txt ) AS COLUMN_MAPPING
-- to use without rowtocol use SNP_TXT.TXT AS COLUMN_MAPPING
FROM SNP_PROJECT
LEFT OUTER JOIN SNP_FOLDER ON SNP_FOLDER.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_POP ON SNP_POP.I_FOLDER=SNP_FOLDER.I_FOLDER
LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_POP_CLAUSE.I_TXT_SQL
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT= SNP_POP_COL.I_TXT_MAP
LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_TXT_CROSSR ON SNP_TXT_CROSSR.I_TXT=SNP_TXT.I_TXT
LEFT OUTER JOIN SNP_COL ON SNP_COL.I_COL=SNP_TXT_CROSSR.I_COL
LEFT OUTER JOIN SNP_TABLE ON SNP_TABLE.I_TABLE= SNP_COL.I_TABLE
LEFT OUTER JOIN SNP_COL T_COL ON T_COL.I_COL=SNP_POP_COL.I_COL
WHERE
-- SNP_POP.POP_NAME='&TAB'
-- SNP_FOLDER.FOLDER_NAME ='&TAB'
SNP_PROJECT.PROJECT_NAME='DEVELOPMENT'
ORDER BY SNP_POP.POP_NAME

I have used the rowtocol function  for SNP_TXT.TXT  ,as when  mapping length  is more than  256 character ODI stores in separate line with different i_txt number causing  mapping to come into two or multiple rows . In order to avoid such scenario and in order to get all the Mapping field information into a single column , i have used this function rowtocol.

Source code for  rowtocol function is available here – > http://www.oracle.com/technology/oramag/code/tips2004/050304.html 

SELECT DISTINCT
SNP_TECHNO.TECHNO_NAME    AS TECHNOLOGY,
SNP_CONNECT.CON_NAME      AS DATA_SERVER,
SNP_PSCHEMA.EXT_NAME      AS PHYSICAL_SCHEMA,
SNP_PSCHEMA.SCHEMA_NAME   AS SCHEMA_NAME,
SNP_PSCHEMA.WSCHEMA_NAME  AS WORK_SCHEMA,
SNP_CONTEXT.CONTEXT_NAME  AS CONTEXT_NAME,
SNP_LSCHEMA.LSCHEMA_NAME  AS LOGICAL_SCHEMA,
SNP_CONNECT.JAVA_DRIVER   AS DRIVER_INFO,
SNP_MTXT_PART.TXT         AS URL
FROM SNP_TECHNO
LEFT OUTER JOIN SNP_CONNECT ON SNP_CONNECT.I_TECHNO=SNP_TECHNO.I_TECHNO
LEFT OUTER JOIN SNP_PSCHEMA ON SNP_PSCHEMA.I_CONNECT=SNP_CONNECT.I_CONNECT
LEFT OUTER JOIN SNP_PSCHEMA_CONT ON SNP_PSCHEMA_CONT.I_PSCHEMA=SNP_PSCHEMA.I_PSCHEMA
LEFT OUTER JOIN SNP_LSCHEMA ON SNP_LSCHEMA.I_LSCHEMA=SNP_PSCHEMA_CONT.I_LSCHEMA
LEFT OUTER JOIN SNP_CONTEXT ON SNP_CONTEXT.I_CONTEXT=SNP_PSCHEMA_CONT.I_CONTEXT
LEFT OUTER JOIN SNP_MTXT_PART ON SNP_MTXT_PART.I_TXT=SNP_CONNECT.I_TXT_JAVA_URL
WHERE SNP_CONNECT.CON_NAME IS NOT NULL
ORDER BY SNP_TECHNO.TECHNO_NAME

Creating Version

To create a version , right click on the object to be versioned and click ok Version-> Create and a popup will be appear as shown below and automatically version number appears , you can also change the version number if required and also provide the description as in the long term and in future understanding would help us to understand the purpose of the version .

Restoring Version

To restore any object version , just right click and select Version -> Restore .. , and select the version to be restored.

Don’t worry about the warning , click ok .

Interface Versioning

VERSION – 1.0.0.0

VERSION – 1.0.0.1

Package Versioning

VERSION – 1.0.0.0

VERSION – 1.0.0.1

Scenario Versioning

Regenerate scenario

Comparing Versions

To compare any two version of the objects , just right click and select version -> Compare with versions..

Here I am comparing 1.0.0.1 with 1.0.0.0

As you can see that in version 1.0.0.0 all the columns where mapped where’s in the COLUMN- C3 was not mapped .

Here in this Example I am comparing the Packages created above .

And as you can see that VAR_JOIN1 is added in the var_join1 and it even shows the different in descriptions , for example

Description in Version 1.0.0.0

Version 1.0.0.1 Description with addition of VAR_JOIN1 being added to it .

Comparing Scenarios

Report Generation

You can create a report by click on the print button found on the top of the compare version

And select the path and location of the PDF Folder.

The biggest drawback I see is that I can compare between two version only , not multiple. Hope Oracle implements multiple comparison in future versions.

All the versioning information is stored in SNP_VERSION table of Master Repository and are connected to Work Respository objects linked to External_version and Object_name.

To solve this issue, I have written a small program that shows the File Length and Header or First Line of the file in the operator . Why First line so that I know whether the first line is an Header row or not .

JYTHON PROCEDURE CODE

# AUTHOR – KSHITIZ DEVENDRA
# LANG – JYTHON
# DESCRIPTION
# ——————————————————————————–
# THIS PROGRAM IS USED TO GET THE NUMBER OF RECORDS IN FILE AND
# ALSO DISPLAYS THE HEADER OF THE FILE
# THE CORRECT NO OF RECORDS ARE CALCULATED BY SUBTRACTING TOTAL LENGTH – HEADER
# ——————————————————————————–
filesrc = open(‘<— SPECIFY FILE PATH AND NAME HERE —-> ‘,’r')
first=filesrc.readline()
lines = 0
while first:
#get the no of lines in the file
lines += 1
first=filesrc.readline()
#print lines
## THE ABOVE PART OF THE PROGRAM IS TO COUNT THE NUMBER OF LINES
## AND STORE IT INTO THE VARIABLE ‘ LINES ‘
def intWithCommas(x):
if type(x) not in [type(0), type(0L)]:
raise TypeError(“Parameter must be an integer.”)
if x < 0:
return ‘-’ + intWithCommas(-x)
result = ”
while x >= 1000:
x, r = divmod(x, 1000)
result = “,%03d%s” % (r, result)
return “%d%s” % (x, result)
## THE ABOVE PROGRAM IS TO DISPLAY THE NUMBERS
## 1234567890 AS 1,234,567,890
s1=str(intWithCommas(lines))
s2=’ \n\n and the First Line of the File is —>> \n\n ‘
filesrc.seek(0)
s3=str(filesrc.readline())
final=s1 + s2 + s3
raise ‘ \n\n The Number of Lines in the File are —>> ‘ , final
## SINCE IN OPERATOR I CAN THROW ONLY SINGLE RAISE COMMAND I CONCATENATED ALL
## THE REQUIRED MESSAGE AND DISPLAYED IN THE OPERATOR AS ONE RAISE COMMAND

SAMPLE OUTPUT

The total execution time for 1.4 million records was 22 so i am expecting it to be fast

Please note i have attached the codes in two formats one for Procedure and another for LKM’s as i have done small changes so that when used in LKM it read the source file automatically , and also does the correct length calculation.

JYTHON CODE FOR LKM

# AUTHOR – KSHITIZ DEVENDRA
# LANG – JYTHON
# DESCRIPTION
# ——————————————————————————–
# THIS PROGRAM IS USED TO GET THE NUMBER OF RECORDS IN FILE AND
# ALSO DISPLAYS THE HEADER OF THE FILE
# THE CORRECT NO OF RECORDS ARE CALCULATED BY SUBTRACTING TOTAL LENGTH – HEADER
# ——————————————————————————–
filesrc = open(‘<%=odiRef.getSrcTablesList(“”, “[SCHEMA]/[RES_NAME]“, “”, “”)%>’,'r’)
first=filesrc.readline()
lines = 0
while first:
#get the no of lines in the file
lines += 1
first=filesrc.readline()
#print lines
## THE ABOVE PART OF THE PROGRAM IS TO COUNT THE NUMBER OF LINES
## AND STORE IT INTO THE VARIABLE ‘ LINES ‘
def intWithCommas(x):
if type(x) not in [type(0), type(0L)]:
raise TypeError(“Parameter must be an integer.”)
if x < 0:
return ‘-’ + intWithCommas(-x)
result = ”
while x >= 1000:
x, r = divmod(x, 1000)
result = “,%03d%s” % (r, result)
return “%d%s” % (x, result)
## THE ABOVE PROGRAM IS TO DISPLAY THE NUMBERS
## 1234567890 AS 1,234,567,890
s1=str(intWithCommas(lines-<%=odiRef.getSrcTablesList(“”, “[FILE_FIRST_ROW]“, “, “, “”)%>))
s2=’ \n\n and the First Line of the File is —>> \n\n ‘
filesrc.seek(0)
s3=str(filesrc.readline())
final=s1 + s2 + s3
raise ‘ \n\n The Number of Lines in the File <%=odiRef.getSrcTablesList(“”, “[SCHEMA]/[RES_NAME]“, “”, “”)%> are —>> ‘ , final
## SINCE IN OPERATOR I CAN THROW ONLY SINGLE RAISE COMMAND I CONCATENATED ALL
## THE REQUIRED MESSAGE AND DISPLAYED IN THE OPERATOR AS ONE RAISE COMMAND

Comparing the above Procedure , LKM codes have small changes to get the File and the correct Length calculated smartly.

filesrc = open(‘<%=odiRef.getSrcTablesList(“”, “[SCHEMA]/[RES_NAME]“, “”, “”)%>’,'r’)

This code will be the correct File name and would open it for reading .

s1=str(intWithCommas(lines-<%=odiRef.getSrcTablesList(“”, “[FILE_FIRST_ROW]“, “, “, “”)%>))

This code will subtract the Total lines – Heading(No of Lines) so that in operator it shows the correct value.

raise ‘ \n\n The Number of Lines in the File <%=odiRef.getSrcTablesList(“”, “[SCHEMA]/[RES_NAME]“, “”, “”)%> are —>> ‘ , final

This code will display the File path and Name in the operator Message box.

As you can see it displayed the File Path and Name with Total Number of Lines , Comparing to the above procedure where it displayed 1,453,389 , here smartly it read from the Data store definition and found out that the Heading (no of lines ) is one and while displaying showing the correct output.

Please find the codes below in XML format and import in INSERT_UPDATE mode and also enable ” IGNORE ERRORS ” option as Jython raise command is used to throw warning message in the operator and I have used raise to display the output required.

To use in any LKM File to KM , just copy paste the code from -PROCEDURE FILE_LENGTH_HEADER TO BE USED IN LKM’s and enable the option -IGNORE ERRORS and move it to the first order so that as soon the load starts you would get the required details in the operator.

PROCEDURE FILE_LENGTH_HEADER

PROCEDURE FILE_LENGTH_HEADER TO BE USED IN LKM’s

Please find this ODI procedure using Jython to show the value of the Variable.

Step 1

Create an ODI Procedure and Insert an option called  “VAR” and give the default as

<%=odiRef.getPrevStepLog("STEP_NAME")%> , reason being so that by default when using this procedure after the variable, ODI  automatically  takes the name and throws the variable value in Operator .

Create another Option called “PROJECT” and give the default value as your project code. The reason i have created an option for Project code is that so that you can modify in the option without any need for change in code also you can use for global variables too.

Step 2 .

Add an option  in the ODI procedure  and give the technology – Jython  and check “ Ignore Errors “ and paste the following code

raise  ‘  \n THE VALUE OF THE VARIABLE <%=odiRef.getOption("VAR")%>  IS  –  <%=#odiRef.getOption("PROJECT")%>.<%=odiRef.getOption("VAR")%> ‘

make sure that everything inside the raise command  have single quotes before and after .

Lets test this Procedure.

and for Jython is

This above example shows that i want to find the value of  variable of  variable other than the previous step , just change the var value to that name of the required variable.

hope this helps who are looking for a way to know the value of the variable at run time.

For global variables you don’t need to mention the project code. Just type ‘GLOBAL’ instead of the project code.

Please find below the codes ,download and all you would need to do is to change your default project codes in the option from XMT to whatever desired and you can make any kind of changes desired.

Just import in an Insert_Update mode and change the project code to your default.

VALUE OF VARIABLE – JYTHON

Such a scenario can exist while doing lookup of staging table with other Lookup or join tables and so the joining condition can keep getting changed so there way I can have only one interface rather than two or multiple interfaces.

As you can see there are two joins but they are not connected.  Looking at each join as you can see I have declared the Table_Name. Variable = Join_Table_name.Variable

TO do so ,just connect any two columns of the two tables and then change the joining condition column with variables.

similarly for the second one two.

I have specified the join columns in the variable ,as for this example i am making the join between S_COL.I_TABLE.SNP_COL.I_TABLE  and S_COL.I_TABLE=SNP_TABLE.I_TABLE

Specify your variable with the required column names  as i did with I_TABLE and run your interface. You can have different column conditions with a same variables and one interface.

Short cut for keys for ODI

 Shortcuts for ODI Designer

Shortcuts of ODI Operator

DWR – Development Work Repository

EWR – Execution Work Repository

Step 1 – Export the scenarios

Right click and export the scenario to the required folder

Step 2 – Select the Folder to export the scenarios

Step 3 – Repeat the above steps for all the scenarios to be exported.

Login into other EWR environment and go the Operator – scenarios tab ,as the EWR don’t have designer enabled.

Step 1 – Right click on the Scenario and select ‘ Import Scenario .. ‘

Step 2 – Select the right Import Type ” INSERT_UPDATE ” and the directory to be imported and you would find the list of scenarios to be imported.

The best practice is to use import type ” INSERT_UPDATE ” for the reason being that if the scenarios are already present they would be updated else inserted.

Click ok to the warning

And all the scenarios imported will be visible.

Run the test for one or two for successful scenario import and it works.