Interface mapping Query ODI 11G

Below query will give you complete details of an ODI PROJECT like

PROJECT_NAME,FOLDER_NAME,INTERFACE_NAME,INTERFACE_TYPE,JOIN_CONDITION_OR_FILTER_TEXT, KM_USED,SOURCE_TABLES,COLUMNS and TARGET_TABLES,COLUMNS.

(Without connecting to ODI STUDIO to know the column mappings)

SELECT

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 'PRIMARY_INTEFACE'

ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE,

SNP_TXT_HEADER.FULL_TEXT JOIN_CONDITION_OR_FILTER_TEXT,

DECODE (SNP_POP_CLAUSE.JOIN_TYPE ,'J', 'JOIN','FILTER') EXPRESSION_TYPE,

NVL(STL.TRT_NAME,'NONE') AS LKM_USED ,

NVL(STI.TRT_NAME,'NONE') AS IKM_USED ,

NVL(STC.TRT_NAME,'NONE') AS CKM_USED,

SNP_SOURCE_TAB.SRC_TAB_ALIAS SOURCE_TABLE,

SNP_COL.COL_NAME  AS SOURCE_COLUMN,

SNP_POP.LSCHEMA_NAME        AS TARGET_SCHEMA,

SNP_POP.TABLE_NAME          AS TARGET_TABLE,

SNP_POP_COL.COL_NAME        AS TARGET_COLUMN

FROM SNP_FOLDER

LEFT OUTER JOIN SNP_PROJECT ON SNP_PROJECT.I_PROJECT=SNP_FOLDER.I_PROJECT

LEFT OUTER JOIN SNP_POP ON SNP_FOLDER.I_FOLDER = SNP_POP.I_FOLDER

LEFT OUTER JOIN SNP_DATA_SET ON SNP_DATA_SET.I_POP = SNP_POP.I_POP

LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP_CLAUSE = SNP_DATA_SET.I_POP

LEFT OUTER JOIN SNP_TXT_HEADER  ON SNP_POP_CLAUSE.I_TXT_SQL  = SNP_TXT_HEADER.I_TXT

LEFT OUTER JOIN SNP_SRC_SET ON SNP_POP.I_POP = SNP_SRC_SET.I_POP

LEFT OUTER JOIN SNP_TRT STL ON SNP_SRC_SET.I_TRT_KLM = STL.I_TRT

LEFT OUTER JOIN SNP_TRT STI ON STI.I_TRT = SNP_POP.I_TRT_KIM

LEFT OUTER JOIN SNP_TRT STC ON STC.I_TRT = SNP_POP.I_TRT_KCM

LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_DATA_SET=SNP_DATA_SET.I_DATA_SET

LEFT OUTER JOIN SNP_COL ON SNP_SOURCE_TAB.I_TABLE=SNP_COL.I_TABLE

LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP

WHERE

SNP_PROJECT.PROJECT_NAME='DEVELOPMENT'

--and SNP_TXT_HEADER.FULL_TEXT is not null

--AND SNP_SOURCE_TAB.I_TABLE=SNP_COL.I_TABLE

ORDER BY SNP_POP.POP_NAME;

K@run@

Setting loglevel for a user/report/system OBIEE 11G

For a particular report
Edit report --> go to advanced tab --> Enter the below tag in Prefix section --> Apply SQL and save the report
SET VARIABLE LOGLEVEL=n; (where n=1,2,3,4,5)
SET VARIABLE LOGLEVEL=2,DISABLE_CACHE_HIT=1;

Here disable_cache_hit is to make sure that the report query to hit the database butnot the cache.

For whole system
Open RPD in online/offline. Go to tools --> Options --> Repository
Set the value for System logging level and save the rpd


For a particular user
Open RPD in online/offline. Go to manage --> Identity --> double click on a user
Set the value for Logging level and save the rpd

K@run@

How to check the disk Space in linux for OBIEE 11g

How to check the disk Space in linux for OBIEE 11g:-

Enter the command “df -h” in the putty prompt and check for your directory similar to the below example.

Size Used Avail Use%

99G   74G   21G  79% /app/oracle------------ (Check the free space of the OBIEE and ODI installed directory).

If the space is consumed too much then try to free the space by deleting the unnecessary logs like saw.log, nqquery.log,nqserver.log from instance directory and other server logs from UserProjects 

directory based on the time stamp of the log file. 

K@run@