ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Report of list of KMs in each Interface

This query lists all the KMs used in each Interfaces under a particular Folder or Project with Interface  Name , Interface Type ie. Permanent_Interface (Blue) or Temporary_Interface (Yellow) ,LKM , IKM , CKM and Source Set Number since there can be multiple Sources feeding a single target in an Interface.

SELECT SP.pop_name AS INTERFACE_NAME ,
CASE WHEN SP.WSTAGE=’E’ THEN ‘TEMP_INTERFACE’
ELSE ‘PERM_INTERFACE’
END AS INTERFACE_TYPE,
NVL(STL.trt_name,’None’) AS LKM_USED ,
NVL(STI.trt_name,’None’) AS IKM_USED ,
NVL(SSS.src_set_name,’None’) AS INPUT_SET_NAME ,
NVL(STC.TRT_NAME,’NONE’) AS CKM_USED
FROM snp_pop SP
LEFT OUTER JOIN snp_src_set SSS
ON SP.i_pop = SSS.i_pop
LEFT OUTER JOIN snp_trt STL
ON SSS.i_trt_klm = STL.i_trt
LEFT OUTER JOIN snp_folder SF
ON SP.i_folder = SF.i_folder
LEFT OUTER JOIN snp_trt STI
ON STI.i_trt = SP.i_trt_kim
LEFT OUTER JOIN SNP_TRT STC
ON stc.i_trt = SP.I_TRT_KCM
LEFT OUTER JOIN snp_project SPJ
ON SPJ.i_project = SF.i_project
WHERE SF.folder_name = ‘<FOLDER_NAME>’
AND spj.project_name = ‘<PROJECT_NAME>’
ORDER BY SP.pop_name, SSS.src_set_name;

Sample Output

image

2 Comments

  1. Hello OdiExperts,
    I have used above sql to get a report of list of KMs in each Interface.
    However I cannot see LKM_USED data.( I check “LKM SQL to Oracle” is used on many interfaces at ODI Designer )
    Can you let me know the reason? How can I get LKM List?
    Thanks in advance.

    Example
    INTERFACE_NAME / INTERFACE_TYPE / LKM_USED / IKM_USED / INPUT_SET_NAME /CKM_USED
    IF.ST_INS TEMP_INTERFACE None IKM MDW Oracle Merge None CKM Oracle
    IF.TOT_KPI TEMP_INTERFACE None IKM MDW SQL Control Append None CKM Oracle
    ……

Leave a Reply

Required fields are marked *.