AnsweredAssumed Answered

Jobs "Load Data Warehouse" and "Load Data Warehouse - Access Rights" fail to execute

Question asked by mcanovas on Sep 22, 2015
Latest reply on Sep 23, 2015 by Suman Pramanik

Hello,

 

I'm configuring our Data Warehouse in 14.2.0.5 version Clarity PPM.

I've created DBLink and it went fine. I see "Available" status in CSA.

I executed "Time Slicing" job and ended OK.

Then both DW loading jobs failed inmediately after its execution, and these are their logs:

 

1) After executing "Load Data Warehouse - Access Rights" job:

 

dwh_db_check_ - An error occurred executing this job entry :

Couldn't execute SQL: DECLARE

  P_DB_LINK VARCHAR2(200);

  P_BATCH_SIZE NUMBER;

BEGIN

  P_DB_LINK := 'PPMDBLINK';

  P_BATCH_SIZE := 50000;

 

  DWH_INV_SECURITY_PRC(

  P_DB_LINK => P_DB_LINK,

  P_BATCH_SIZE => P_BATCH_SIZE

  );

END;

 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "PERMISSION_KEY": invalid identifier

ORA-06512: at "PPM_DWH.DWH_INV_SECURITY_PRC", line 37

ORA-06512: at line 8

 

Then I check DWH_INV_SECURITY_PRC procedure and I see a query like this: SELECT INVESTMENT_KEY, USER_KEY, USER_NAME, INVESTMENT_TYPE_KEY, GLOBAL_VIEW_RIGHT, PERMISSION_KEY FROM DWH_INV_SECURITY_V@'||P_DB_LINK

 

Indeed, if we look for PERMISSION_KEY column in DWH_INV_SECURITY_V view, we can't find it, neither GLOBAL_VIEW_RIGHT column.

 

 

2) After executing "Load Data Warehouse" job:

 

dwh_db_check_ - An error occurred executing this job entry :

Couldn't execute SQL: BEGIN

DWH_CFG_PRE_CONFIG_SP(

  P_DBLINK => 'PPMDBLINK'

);

END;

 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist

ORA-02063: preceding line from PPMDBLINK

ORA-06512: at "PPM_DWH.DWH_CFG_PRE_CONFIG_SP", line 37

ORA-06512: at line 2

 

When I review DWH_CFG_PRE_CONFIG_SP code, I see there are lots of inconsistencies, thus several tables are not placed in our tablespace (CMN_LANGUAGES, ODF_CUSTOM_ATTRIBUTES, CMN_LIST_OF_VALUES, etc).

 

Can anyone give us a hand?

 

Regards,

 

Marc

Outcomes