Tuesday, July 22, 2014

Oracle Business Intelligence Applications 11.1.1.8.1 - Notes when setting up the Data Lineage

When setting up Data Lineage on Oracle Business Intelligence Applications 11.1.1.8.1 as document (http://docs.oracle.com/cd/E51479_01/doc.111181/e51483/datalineage.htm#CDEDDIGA), you may get some errors like below:

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_OBIEE_BMM_HIERARCHY (44440500) fails with return code 7000. 
IOError: (2, 'ENOENT', 'E:\\software\\obiee\\Oracle_BI1\x08iapps\\DataLineage/rpd_text.txt') 

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_OBIEE_SQL_PARSER (44447500) fails with return code 7000. 
IOError: (2, 'ENOENT', 'E:\\software\\obiee\\Oracle_BI1\x08iapps\\DataLineage/sql_query_list.txt') 

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_FUSION_METADATA_EXTRACT (44543500) fails with return code 7000. 
OSError: [Errno 0] No such directory: 'E:\\software\\obiee\\Oracle_BI1\x08iapps\\DataLineage' 

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_FUSION_TEMPORARY_LOAD (44703500) fails with return code 7000. 
ODI-1226: Step SDE_DL_FUSION_AM_Extract_Temporary.W_FUSION_PILLAR_AM_TMP fails after 1 attempt(s). 
ODI-1240: Flow SDE_DL_FUSION_AM_Extract_Temporary.W_FUSION_PILLAR_AM_TMP fails while performing a Loading operation. This flow loads target table W_FUSION_PILLAR_AM_TMP. 
ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection BIAPPS_DW_FILE. 
Caused By: java.sql.SQLException: ODI-40438: File not found: E:\software\obiee\Oracle_BI1\biapps\etl\data_files\src_files\BIA_11/AM_List.dsv 

ODI-1217: Session DATALINEAGE_ETL_SDE_DL_ODI_INTERFACE_HIERARCHY_DERIVE (44798500) fails with return code 7000. 
ODI-1226: Step INTERFACE_HIERARCHY fails after 1 attempt(s). 
ODI-1232: Procedure INTERFACE_HIERARCHY execution fails. 
Caused By: org.apache.bsf.BSFException: exception from Jython: 
Traceback (most recent call last): 
File "<string>", line 1, in <module> 
File "<string>", line 158, in process_interface_hierarchy 
IOError: (2, 'ENOENT', '$ORACLE_BI_HOME/biapps/DataLineage/adaptor_list.txt') 

ODI-1217: Session DATALINEAGE_ETL_SIL_DL_COMMON_INTERFACE_HIERARCHY_FACT (44799500) fails with return code 7000. 
ODI-1226: Step SIL_DL_COMMON_Interface_Hierarchy_Fact.W_INTERFACE_HIERARCHY_F fails after 1 attempt(s). 
ODI-1240: Flow SIL_DL_COMMON_Interface_Hierarchy_Fact.W_INTERFACE_HIERARCHY_F fails while performing a Loading operation. This flow loads target table W_INTERFACE_HIERARCHY_F. 
ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection BIAPPS_DW_FILE. 
Caused By: java.sql.SQLException: ODI-40438: File not found: E:\software\obiee\Oracle_BI1\biapps\etl\data_files\src_files\BIA_11/interface_hierarchy_output.txt 

ODI-1217: Session DATALINEAGE_ETL_SIL_DL_COMMON_ETL_SUMMARY (44804500) fails with return code 7000. 
ODI-1226: Step SIL_DL_COMMON_ETL_SUMMARY.W_ETL_SUMMARY_F fails after 1 attempt(s). 
ODI-1240: Flow SIL_DL_COMMON_ETL_SUMMARY.W_ETL_SUMMARY_F fails while performing a Loading operation. This flow loads target table W_ETL_SUMMARY_F. 
ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection BIAPPS_DW_FILE. 
Caused By: java.sql.SQLException: ODI-40438: File not found: E:\software\obiee\Oracle_BI1\biapps\etl\data_files\src_files\BIA_11/lineage_summary.txt 



If you have got one of those, please ensure that some following configurations had been done (I tried and the 'Data Lineage Extract and Load' load plan ran successfully):

- Use / instead of \ when setting DL_HOME variable on the Data Lineage Exract and Load load plan
- Copy APP-INF from $ORACLE_BI_HOME/fsm/modules/oracle.setup/SetupLite.ear to $ORACLE_BI_HOME/biapps/DataLineage
- Update config of BIAPPS_DW_FILE, change directory from $ORACLE_BI_HOME/biapps/etl/data_files/src_files/BIA_11 to $ORACLE_BI_HOME/biapps/DataLineage (same with the value set for the DL_HOME variable on the Data Lineage Exract and Load load plan.
- Copy all source files from $ORACLE_BI_HOME/biapps/etl/data_files/src_files/BIA_11 to $ORACLE_BI_HOME/biapps/DataLineage
- Enable SDE_DL_OBIEE_BMM_HIERARCHY & SDE_DL_FUSION_METADATA_EXTRACT step in the Data Lineage Exract and Load load plan (they are disabled by default). Disable SDE_DL_ODI_MAPPING_LIST_WIDS step if it is enabled (disabled by default, enabling it will caused SDE_DL_ODI_INTERFACE_HIERARCHY_DERIVE step runs for a long time)
Tin Dang