Analytics Software

Dear all,

I installed and configured biapps 7.9.6.3, then I run the full load of Subject CRM - Loyalty in DAC. And I got the error of task fail, I check the Session log files in Informatica server.

$ view SEBL_VERT_811.DATAWAREHOUSE.SDE_SBL_Vert_811_Adaptor.SDE_GeographyDimension_Business.log

DIRECTOR> VAR_27028 Use override value [0] for user-defined workflow/worklet variable:[$$passInStatus].
DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].
DIRECTOR> VAR_27028 Use override value [SEBL_VERT_811] for session parameter:[$DBConnection_OLTP].
DIRECTOR> VAR_27028 Use override value [SEBL_VERT_811.DATAWAREHOUSE.SDE_SBL_Vert_811_Adaptor.SDE_GeographyDimension_Business.log] for session parameter:[$PMSessionLogFile].
DIRECTOR> VAR_27028 Use override value [1] for mapping parameter:[MPLT_LOAD_W_GEO_DS.$$DATASOURCE_NUM_ID].
DIRECTOR> VAR_27028 Use override value [] for mapping parameter:[$$Hint1].
DIRECTOR> VAR_27028 Use override value [] for mapping parameter:[$$Hint2].
DIRECTOR> TM_6014 Initializing session [SDE_GeographyDimension_Business] at [Mon Jul 25 17:29:47 2011].
DIRECTOR> TM_6683 Repository Name: [Oracle_BI_DW_Base]
DIRECTOR> TM_6684 Server Name: [Oracle_BI_DW_Server]
DIRECTOR> TM_6686 Folder: [SDE_SBL_Vert_811_Adaptor]
DIRECTOR> TM_6685 Workflow: [SDE_GeographyDimension_Business] Run Instance Name: [] Run Id: [260]
DIRECTOR> TM_6101 Mapping name: SDE_GeographyDimension_Business [version 1].
DIRECTOR> TM_6963 Pre 85 Timestamp Compatibility is Enabled
DIRECTOR> TM_6964 Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
DIRECTOR> TM_6827 [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Storage] will be used as storage directory for session [SDE_GeographyDimension_Business].
DIRECTOR> CMN_1802 Session recovery cache initialization is complete.
DIRECTOR> TM_6708 Using configuration property [DisableDB2BulkMode,Yes]
DIRECTOR> TM_6708 Using configuration property [ServerPort,6325]
DIRECTOR> TM_6708 Using configuration property [overrideMpltVarWithMapVar,Yes]
DIRECTOR> TM_6708 Using configuration property [SiebelUnicodeDB,SIEBEL@ANSDEV dwhadmin@ANBDEV]

DIRECTOR> TM_6703 Session [SDE_GeographyDimension_Business] is run by 64-bit Integration Service [node01_hkhgc01dvapp01], version [9.0.1 HotFix2], build [1111].
MANAGER> PETL_24058 Running Partition Group [1].
MANAGER> PETL_24000 Parallel Pipeline Engine initializing.
MANAGER> PETL_24001 Parallel Pipeline Engine running.
MANAGER> PETL_24003 Initializing session run.
MAPPING> CMN_1569 Server Mode: [UNICODE]
MAPPING> CMN_1570 Server Code page: [UTF-8 encoding of Unicode]
MAPPING> TM_6151 The session sort order is [Binary].
MAPPING> TM_6185 Warning. Code page validation is disabled in this session.
MAPPING> TM_6156 Using low precision processing.
MAPPING> TM_6180 Deadlock retry logic will not be implemented.
MAPPING> TM_6187 Session target-based commit interval is [10000].
MAPPING> TM_6307 DTM error log disabled.
MAPPING> TE_7022 TShmWriter: Initialized
MAPPING> DBG_21075 Connecting to database [ANBDEV], user [dwhadmin]
MAPPING> CMN_1716 Lookup [MPLT_LOAD_W_GEO_DS.LKP_W_LST_OF_VAL_G] uses database connection [Relational:DataWarehouse] in code page [UTF-8 encoding of Unicode]
MAPPING> CMN_1716 Lookup [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS] uses database connection [Relational:DataWarehouse] in code page [UTF-8 encoding of Unicode]
MAPPING> DBG_21694 AGG_COUNTRY_CITY_ZIPCODE Partition [0]: Index cache size = [1048576], Data cache size = [2097152]
MAPPING> TE_7212 Increasing [Index Cache] size for transformation [AGG_COUNTRY_CITY_ZIPCODE] from [1048576] to [2402304].
MAPPING> TE_7212 Increasing [Data Cache] size for transformation [AGG_COUNTRY_CITY_ZIPCODE] from [2097152] to [2097528].
MAPPING> TE_7029 Aggregate Information: Creating New Index and Data Files
MAPPING> TE_7034 Aggregate Information: Index file is [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.idx]
MAPPING> TE_7035 Aggregate Information: Data file is [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.dat]
MAPPING> TM_6007 DTM initialized successfully for session [SDE_GeographyDimension_Business]
DIRECTOR> PETL_24033 All DTM Connection Info: [<NONE>].
MANAGER> PETL_24004 Starting pre-session tasks. : (Mon Jul 25 17:29:47 2011)
MANAGER> PETL_24027 Pre-session task completed successfully. : (Mon Jul 25 17:29:47 2011)
DIRECTOR> PETL_24006 Starting data movement.
MAPPING> TM_6660 Total Buffer Pool size is 36000000 bytes and Block size is 128000 bytes.
LKPDP_2> DBG_21097 Lookup Transformation [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS]: Default sql to create lookup cache: SELECT CITY,COUNTRY,ZIPCODE,STATE_PROV FROM W_GEO_DS ORDER BY CITY,COUNTRY,ZIPCODE,STATE_PROV

LKPDP_1> DBG_21312 Lookup Transformation [MPLT_LOAD_W_GEO_DS.LKP_W_LST_OF_VAL_G]: Lookup override sql to create cache: SELECT W_LST_OF_VAL_G.VAL AS VAL, W_LST_OF_VAL_G.R_TYPE AS R_TYPE FROM W_LST_OF_VAL_G
WHERE
W_LST_OF_VAL_G.R_TYPE LIKE 'ETL%' ORDER BY R_TYPE,VAL

LKPDP_1> TE_7212 Increasing [Index Cache] size for transformation [MPLT_LOAD_W_GEO_DS.LKP_W_LST_OF_VAL_G] from [1048576] to [1050000].
LKPDP_2> TE_7212 Increasing [Index Cache] size for transformation [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS] from [20000000] to [20006400].
LKPDP_2> TE_7212 Increasing [Data Cache] size for transformation [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS] from [20000000] to [20004864].
READER_1_1_1> DBG_21438 Reader: Source is [ANSDEV], user [SIEBEL]
READER_1_1_1> BLKR_16051 Source database connection [SEBL_VERT_811] code page: [UTF-8 encoding of Unicode]
READER_1_1_1> BLKR_16003 Initialization completed successfully.
WRITER_1_*_1> WRT_8146 Writer: Target is database [ANBDEV], user [dwhadmin], bulk mode [ON]
WRITER_1_*_1> WRT_8106 Warning! Bulk Mode session - recovery is not guaranteed.
WRITER_1_*_1> WRT_8221 Target database connection [DataWarehouse] code page: [UTF-8 encoding of Unicode]
WRITER_1_*_1> WRT_8124 Target Table W_GEO_DS :SQL INSERT statement:
INSERT INTO W_GEO_DS(CITY,CONTINENT,COUNTRY,COUNTY,STATE_PROV,ZIPCODE,DATASOURCE_NUM_ID,X_CUSTOM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)
WRITER_1_*_1> WRT_8020 No column marked as primary key for table [W_GEO_DS]. UPDATEs Not Supported.
WRITER_1_*_1> WRT_8270 Target connection group #1 consists of target(s) [W_GEO_DS]
WRITER_1_*_1> WRT_8003 Writer initialization complete.
READER_1_1_1> BLKR_16007 Reader run started.
WRITER_1_*_1> WRT_8005 Writer run started.
WRITER_1_*_1> WRT_8158

*****START LOAD SESSION*****

Load Start Time: Mon Jul 25 17:29:47 2011

Target tables:

W_GEO_DS


READER_1_1_1> RR_4029 SQ Instance [SQ_S_ADDR_ORG] User specified SQL Query [SELECT DISTINCT
S_ADDR_ORG.CITY,
S_ADDR_ORG.COUNTRY,
S_ADDR_ORG.COUNTY,
S_ADDR_ORG.PROVINCE,
S_ADDR_ORG.STATE,
S_ADDR_ORG.ZIPCODE,
'0' AS X_CUSTOM
FROM
V_ADDR_ORG S_ADDR_ORG
]
READER_1_1_1> RR_4049 SQL Query issued to database : (Mon Jul 25 17:29:47 2011)
READER_1_1_1> CMN_1761 Timestamp Event: [Mon Jul 25 17:29:47 2011]
READER_1_1_1> RR_4035 SQL Error [
ORA-00942: table or view does not exist

Database driver error...
Function Name : Execute
SQL Stmt : SELECT DISTINCT
S_ADDR_ORG.CITY,
S_ADDR_ORG.COUNTRY,
S_ADDR_ORG.COUNTY,
S_ADDR_ORG.PROVINCE,
S_ADDR_ORG.STATE,
S_ADDR_ORG.ZIPCODE,
'0' AS X_CUSTOM
FROM
V_ADDR_ORG S_ADDR_ORG

Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt : SELECT DISTINCT
S_ADDR_ORG.CITY,
S_ADDR_ORG.COUNTRY,
S_ADDR_ORG.COUNTY,
S_ADDR_ORG.PROVINCE,
S_ADDR_ORG.STATE,
S_ADDR_ORG.ZIPCODE,
'0' AS X_CUSTOM
FROM
V_ADDR_ORG S_ADDR_ORG

Oracle Fatal Error].
READER_1_1_1> CMN_1761 Timestamp Event: [Mon Jul 25 17:29:47 2011]
READER_1_1_1> BLKR_16004 ERROR: Prepare failed.
WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.
WRITER_1_*_1> WRT_8325 Final rollback executed for the target [W_GEO_DS] at end of load
WRITER_1_*_1> WRT_8035 Load complete time: Mon Jul 25 17:29:47 2011

LOAD SUMMARY
============

WRT_8036 Target: W_GEO_DS (Instance Name: [W_GEO_DS])
WRT_8044 No data loaded for this target



WRITER_1__1> WRT_8043 ****END LOAD SESSION*****
MANAGER> PETL_24031
***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_S_ADDR_ORG] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_S_ADDR_ORG] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_2_1] created for [the transformation stage] of partition point [AGG_COUNTRY_CITY_ZIPCODE] has completed. The total run time was insufficient for any meaningful statistics.
Thread [WRITER_1_*_1] created for [the write stage] of partition point [W_GEO_DS] has completed. The total run time was insufficient for any meaningful statistics.

MAPPING> CMN_1791 The index cache size that would hold [0] aggregate groups of input rows for [AGG_COUNTRY_CITY_ZIPCODE], in memory, is [0] bytes
MAPPING> CMN_1790 The data cache size that would hold [0] aggregate groups of input rows for [AGG_COUNTRY_CITY_ZIPCODE], in memory, is [0] bytes
MAPPING> CMN_1793 The index cache size that would hold [0] rows in the lookup table for [MPLT_LOAD_W_GEO_DS.LKP_W_LST_OF_VAL_G], in memory, is [0] bytes
MAPPING> CMN_1792 The data cache size that would hold [0] rows in the lookup table for [MPLT_LOAD_W_GEO_DS.LKP_W_LST_OF_VAL_G], in memory, is [0] bytes
MAPPING> CMN_1793 The index cache size that would hold [0] rows in the lookup table for [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS], in memory, is [0] bytes
MAPPING> CMN_1792 The data cache size that would hold [0] rows in the lookup table for [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS], in memory, is [0] bytes
MANAGER> PETL_24005 Starting post-session tasks. : (Mon Jul 25 17:29:47 2011)
MANAGER> PETL_24029 Post-session task completed successfully. : (Mon Jul 25 17:29:47 2011)
MAPPING> TE_7216 Deleting cache files [PMLKUP14527_524289_0_260L64] for transformation [MPLT_LOAD_W_GEO_DS.LKP_W_LST_OF_VAL_G].
MAPPING> TE_7216 Deleting cache files [PMLKUP14527_524293_0_260L64] for transformation [MPLT_LOAD_W_GEO_DS.LKP_W_GEO_DS].
MAPPING> TM_6018 The session completed with [0] row transformation errors.
MANAGER> TE_7216 Deleting cache files [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.idx] for transformation [AGG_COUNTRY_CITY_ZIPCODE].
MANAGER> TE_7216 Deleting cache files [u01/app/oracle/biapps/dev/Informatica/9.0.1/server/infa_shared/Cache/PMAGG14527_3_0_260.dat] for transformation [AGG_COUNTRY_CITY_ZIPCODE].
MANAGER> PETL_24002 Parallel Pipeline Engine finished.
DIRECTOR> PETL_24013 Session run completed with failure.
DIRECTOR> TM_6022

SESSION LOAD SUMMARY
================================================

DIRECTOR> TM_6252 Source Load Summary.
DIRECTOR> CMN_1740 Table: [SQ_S_ADDR_ORG] (Instance Name: [SQ_S_ADDR_ORG])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
DIRECTOR> TM_6253 Target Load Summary.
DIRECTOR> CMN_1740 Table: [W_GEO_DS] (Instance Name: [W_GEO_DS])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
DIRECTOR> TM_6023
===================================================

DIRECTOR> TM_6020 Session [SDE_GeographyDimension_Business] completed at [Mon Jul 25 17:29:48 2011].


After review the log, I found the select statement was fail, the SQL below was wrong:

SELECT DISTINCT
S_ADDR_ORG.CITY,
S_ADDR_ORG.COUNTRY,
S_ADDR_ORG.COUNTY,
S_ADDR_ORG.PROVINCE,
S_ADDR_ORG.STATE,
S_ADDR_ORG.ZIPCODE,
'0' AS X_CUSTOM
FROM
V_ADDR_ORG S_ADDR_ORG

There is no table V_ADDR_ORG but S_ADDR_ORG in the Siebel transaction database. So I don't know why it generate this sql when transfering data to OBAW. This is the out of box bi application.

Experts! How could I fix this problem?? Please help, thank you very much!!


Best regards,
Ryan