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

10 comments:

  1. I am trying to implement Data Lineage in Oracle BI Apps 11.1.1.8.1. In the Oracle standard document "https://docs.oracle.com/cd/E51479_01/doc.111181/e51483/datalineage.htm#CDEBBIHD" it has been mentioned that webCatExtract, rpdExract, webCatExtract_dashboard script should be run to extract the metadata. Now it says these extract scripts would run in windows where OBIEE might have been installed.

    But in my project we have installed OBIEE in linux and these .bat scripts are for windows. Could you please tell me how would i get the Linux scripts or there is another way of implementing Data Lineage

    ReplyDelete
    Replies
    1. The bat script is in $ORACLE_BI_HOME/biapps/DataLineage in your Linux.

      You should copy these scripts to 1 windows machine where the OBIEE Client is installed, then continue.

      Delete
    2. Thanks. I got to know that u don't even have to run the script files. The required 3 .txt files (webCatExtract, rpdExract, webCatExtract_dashboard script) could be generated for the BI Admin tool and the catalog manager. From both the tool one can generate reports choosig appropriate columns to create these 3 files. Please let me know if i am wrong.

      Delete
  2. Could you please explain how SDE_DL_ODI_MAPPING_LIST_WIDS step causes SDE_DL_ODI_INTERFACE_HIERARCHY_DERIVE to run long?

    ReplyDelete
  3. Hi,

    You can see this issue on Oracle Support https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=347b5udrs_9&_afrLoop=504504615448787

    I think it's not neccessary and no need to enable it.

    Please put your comments here if you can take a look at it.

    ReplyDelete
  4. Nothing is mentioned about why this step is disabled.

    ReplyDelete
  5. ODI-1519: Serial step "DATA_LINEAGE (InternalID:4220174002)" failed because child step "Dimension_ETL (InternalID:4254366002)" is in error.
    ODI-1519: Serial step "Dimension_ETL (InternalID:4254366002)" failed because child step "ODI (InternalID:4254368002)" is in error.
    ODI-1519: Serial step "ODI (InternalID:4254368002)" failed because child step "SDE_DL_ODI_DATASTORECOLUMN_DIMENSION (InternalID:4293347002)" is in error.
    ODI-1217: Session DATALINEAGE_ETL_SDE_DL_ODI_DATASTORECOLUMN_DIMENSION (147866620) fails with return code 7000.
    ODI-1226: Step SDE_DL_ODI_DatastoreColumn_Dimension_MainInterface.W_DATASTORE_COLUMN_D fails after 1 attempt(s).
    ODI-1240: Flow SDE_DL_ODI_DatastoreColumn_Dimension_MainInterface.W_DATASTORE_COLUMN_D fails while performing a Loading operation. This flow loads target table W_DATASTORE_COLUMN_D.
    Caused By: java.lang.NullPointerException
    at oracle.jdbc.driver.OracleDriver.acceptsURL(OracleDriver.java:717)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:406)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:386)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:353)
    at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:332)
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:217)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:662)

    ReplyDelete
    Replies
    1. I am getting this error while trying to set up the data lineage in the test environment. I did the same in the dev it worked but its throwing me the error while running the load plan in ODI.

      Delete
    2. Did you figure this one above out?

      DATALINEAGE_ETL_SDE_DL_ODI_DATASTORECOLUMN_DIMENSION

      Delete