Posted by : Arjun Lagisetty Thursday, May 12, 2016



Way before the time of Quick Edit tab in ODI. It was painful to understand to get an overview of the Interfaces. Some questions listed below took time to be answered.

  • What Data sources are being used?
  • What are the joins?
  • What are the mapped columns.
  • Generate SQL for extracting required details from the table.
  • Format the SQL and print it out to a reports.

Fumbling through the mapping tab was clumsy and it took may click to get these answers. Quick edit tab did solve a lot these issues. It did not have to render the whole mapping as a diagram thus saving a lot of time.

However, If I want to document an interface it takes awful lot of time and there is no way to export the interface in text readable format. Since ODI stores all the data in the work repository tables. I could easily generate the documentation from this tables.

Below is my attempt to document the interfaces from the interface table. In the next post we will explore how to document procedure, package and variables, export it all to human readable ASCII text files. This will give us ability to do simple search with in the code. Example, where a particular function is used in the interface(s).

I created some python scripts to print out interfaces to text file but your approach can be different. I will attempt to clean up my python scripts before I upload them but here are the queries for  your pleasure.
--Get list of sources for an interface
SELECT DISTINCT SNP_SOURCE_TAB.LSCHEMA_NAME || '.' || SNP_SOURCE_TAB.TABLE_NAME AS SOURCE_TABLE
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_SOURCE_TAB SNP_SOURCE_TAB
ON SNP_SOURCE_TAB.I_POP = SNP_POP.I_POP
WHERE SNP_POP.POP_NAME = "ENTER_INTERFACE_NAME"
AND SNP_FOLDER.FOLDER_NAME = "ENTER_FOLDER_NAME"
AND SNP_PROJECT.PROJECT_NAME = "ENTER_PROJECT_NAME";


-- Get all the filters on an interface
SELECT DISTINCT S_TXT.TXT AS FILTER_TXT
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_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
WHERE SNP_POP_CLAUSE.CLAUSE_TYPE = 3
AND SNP_POP.POP_NAME = "ENTER_INTERFACE_NAME"
AND SNP_FOLDER.FOLDER_NAME = "ENTER_FOLDER_NAME"
AND SNP_PROJECT.PROJECT_NAME = "ENTER_PROJECT_NAME";



-- Get all active mappings on the interface
SELECT SNP_POP.POP_NAME AS INTF_NAME
,SNP_POP.TABLE_NAME AS TARGET_TABLE_NAME
,SNP_DATA_SET.DS_NAME AS DATA_SET
,SNP_POP_COL.COL_NAME AS TARGET_COLUMN
,CASE

WHEN SNP_POP_COL.EXE_DB = 'T'
THEN SNP_TXT_HEADER2.FULL_TEXT
ELSE SNP_TXT_HEADER.FULL_TEXT
END AS MAPPPING
,CASE

WHEN SNP_POP_COL.IND_INS = 1
THEN 'True'
ELSE 'False'
END AS INSERT_MAP
,CASE

WHEN SNP_POP_COL.IND_UPD = 1
THEN 'True'
ELSE 'False'
END AS UPDATE_MAP
FROM SNP_POP
LEFT OUTER JOIN SNP_POP_COL ON SNP_POP.I_POP = SNP_POP_COL.I_POP
LEFT OUTER JOIN SNP_POP_MAPPING ON SNP_POP_COL.I_POP_COL = SNP_POP_MAPPING.I_POP_COL
LEFT OUTER JOIN SNP_TXT_HEADER ON SNP_POP_MAPPING.I_TXT_MAP = SNP_TXT_HEADER.I_TXT
LEFT OUTER JOIN SNP_TXT_HEADER SNP_TXT_HEADER2 ON SNP_TXT_HEADER2.I_TXT = SNP_POP_COL.I_TXT_MAP
LEFT OUTER JOIN SNP_DATA_SET ON SNP_POP.I_POP = SNP_DATA_SET.I_POP
AND SNP_POP_MAPPING.I_DATA_SET = SNP_DATA_SET.I_DATA_SET
WHERE SNP_POP_COL.IND_ENABLE = 1
AND SNP_POP.POP_NAME = 'ENTER_INTERFACE_NAME'
ORDER BY DS_NAME
,SNP_POP_COL.COL_NAME;
-- Get the target model for the interface
SELECT SNP_SOURCE_TAB.LSCHEMA_NAME || '.' || SNP_TABLE.TABLE_NAME AS "TARGET_TABLE_NAME"
FROM SNP_PROJECT
JOIN SNP_FOLDER
ON SNP_FOLDER.I_PROJECT = SNP_PROJECT.I_PROJECT
JOIN SNP_POP
ON SNP_POP.I_FOLDER = SNP_FOLDER.I_FOLDER
JOIN SNP_MODEL
ON SNP_MODEL.I_MOD = SNP_POP.I_MOD
JOIN SNP_TABLE
ON SNP_POP.I_TABLE = SNP_TABLE.I_TABLE
WHERE SNP_POP.POP_NAME = "ENTER_INTERFACE_NAME"
AND SNP_FOLDER.FOLDER_NAME = "ENTER_FOLDER_NAME"
AND SNP_PROJECT.PROJECT_NAME = "ENTER_PROJECT_NAME";

-- Get all the joins on the interface
SELECT DISTINCT PC.I_TXT_SQL AS GRP
,T1.TABLE_NAME AS LEFT_TABLE
,T2.TABLE_NAME AS RIGHT_TABLE
,NVL2(PC.I_TABLE2, DECODE(PC.IND_OUTER1, 1, 'LEFT ') || DECODE(PC.IND_OUTER2, 1, 'RIGHT ') || DECODE(PC.IND_OUTER1 + PC.IND_OUTER2, 0, 'INNER ', 'OUTER ') || 'JOIN', 'FILTER') CLAUSE_TYPE
FROM SNP_POP_CLAUSE PC
,SNP_POP P
,SNP_TABLE T1
,SNP_TABLE T2
,SNP_SOURCE_TAB ST1
,SNP_SOURCE_TAB ST2
,SNP_PROJECT PRJ
,SNP_FOLDER FLD
WHERE 1 = 1
AND P.I_POP = PC.I_POP
AND ST1.I_SOURCE_TAB = PC.I_TABLE1
AND ST2.I_SOURCE_TAB = PC.I_TABLE2
AND ST1.I_TABLE = T1.I_TABLE
AND ST2.I_TABLE = T2.I_TABLE
AND PC.CLAUSE_TYPE = 1
AND P.I_FOLDER = FLD.I_FOLDER
AND FLD.I_PROJECT = PRJ.I_PROJECT
AND SNP_POP.POP_NAME = "ENTER_INTERFACE_NAME"
AND SNP_FOLDER.FOLDER_NAME = "ENTER_FOLDER_NAME"
AND SNP_PROJECT.PROJECT_NAME = "ENTER_PROJECT_NAME";


Here is a comical picture of interface icon just for laughs.





















Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty