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
April 12, 2018 at 7:00 PM
Thanks Cezar , with this query folder_name and sub_folder_name getting under same column, can i get SUB_FOLDER_NAME separate column ?
February 23, 2018 at 9:47 PM
How to run and I have a requirement to get the Target Tables and fields for the respective source tables and cols mapped in ODI dimensions or Facts.
February 23, 2018 at 9:58 PM
Hi,
You should run it at ODI Development Work Repository…
March 7, 2018 at 1:10 PM
SELECT
P.POP_NAME INTERFACE_NAME,
T.TABLE_NAME TARGET_TABLE,
M.COD_MOD MODEL_CODE
FROM [DEV_ODI_REPO].[SNP_POP] P INNER JOIN [DEV_ODI_REPO].[SNP_TABLE] T ON P.I_TABLE = T.I_TABLE
INNER JOIN [DEV_ODI_REPO].[SNP_MODEL] M ON T.I_MOD = M.I_MOD;
February 23, 2018 at 9:45 PM
HI
where to run this query
August 10, 2016 at 4:39 AM
Hi all.
How are you Cezar. All good mate!
Well. I needed this SQL for some reason. Got your initial query Cezar and did some changes to work with ODI 11g 1.1.7.1
Here follows:
select distinct
SNP_PROJECT.I_PROJECT,
SNP_PROJECT.PROJECT_CODE as PROJECT_CODE,
SNP_PROJECT.PROJECT_NAME as PROJECT_NAME,
SNP_FOLDER.I_FOLDER,
SNP_FOLDER.FOLDER_NAME as FOLDER_NAME,
SNP_POP.I_POP,
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,
SNP_POP_COL.POS,
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,
T9.DS_NAME,
T9.DS_ORDER,
T9.DS_OPERATOR,
T10.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
–,BIM.rowtocol(‘SELECT DISTINCT TXT FROM SNP_TXT T1 WHERE T1.I_TXT=’||S_TXT.i_txt ) AS COLUMN_MAPPING
— to use without rowtocol use SNP_TXT.TXT AS COLUMN_MAPPING
,SNP_TXT_CROSSR.STRING_ELT 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_POP_MAPPING SNP_POP_MAP on SNP_POP_MAP.I_POP_COL=SNP_POP_COL.I_POP_COL
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_POP_MAP.I_TXT_MAP
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
left outer join SNP_DATA_SET T9 on T9.I_POP = SNP_POP.I_POP
and T9.I_DATA_SET = SNP_POP_MAP.I_DATA_SET
LEFT OUTER JOIN SNP_MODEL T10 ON T10.I_MOD = SNP_TABLE.I_MOD
where 1=1
–and snp_pop.pop_name = ‘Interface Name’
order by SNP_POP.POP_NAME
,T9.DS_ORDER
,SNP_POP_COL.POS;
Cheers.
Fernando Ponte
November 18, 2015 at 2:16 PM
Can you publish same sql for ODI 12c repository?
July 10, 2015 at 10:54 AM
Hi,
This is excellent work done by odi experts.
But i want source table and column which is mapping to that column in a particular interface.
and filter condition,join condition,lookups
April 6, 2015 at 7:46 PM
Hello Cezar Santos,
Where you able to rewrite this query for ODI 11.1.1.7.1 ? If so can you please share it. Thanks in advance
March 26, 2015 at 12:28 PM
Hi Kshitiz
I am required to do a similar thing for 12c,since the repo tables have a changed structure now . I am unable to establish a relation between a source col and target col . Pls help
December 9, 2014 at 1:17 PM
Hi
It’s not working anymore with ODI 11.1.1.1.7. Source information is always empty.
Regards
December 9, 2014 at 1:23 PM
Thank you Dominic, I’ll find the new query and publish an update!
September 18, 2014 at 6:44 AM
Hi,
I want to know which tables get populated when i create lookup interface in ODI 11g..
December 18, 2013 at 10:17 AM
Please Update the Code ….. I dont think it will work anymore
January 20, 2010 at 12:17 PM
Hi Kshitiz,
Why you are not using ODI Metadata Navigator to get the interface mapping easily? In that case we don’t need to write such a big and complex query.
Regards,
Hasi…
January 24, 2010 at 11:49 AM
Well , i got a request from someone to design such a query and moreover once development is done , i can use the same query and update my ETL mapping specs too ,which might be useful to some people .
Moreover not all environment have Metadata Navigator Installed.