This post is about how to do a simple Minus query in ODI without creating any view or other process .

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)

This query provides all the detail of the Variables created under various project and their associated  default properties.

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

This query will show the interface  mapping  in the following order

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 

This query provide  all the necessary connection detail   in  ODI Topology.

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

Versioning is a smart method of implementing a certain condition or multiple condition , objects for a particular interface , package or scenarios without required to have multiple such objects.

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.