Clarity

Expand all | Collapse all

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

Jump to Best Answer
  • 1.  Jobs "Load Data Warehouse" and "Load Data Warehouse - Access Rights" fail to execute

    Posted 09-22-2015 08:52 AM

    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



  • 2.  Re: Jobs "Load Data Warehouse" and "Load Data Warehouse - Access Rights" fail to execute
    Best Answer

    Posted 09-22-2015 10:22 AM

    The problem looks like the import of Dataware house template is not correct as its unable to find the columns, one thing you can try is to drop the datawarehouse database and reimport, create the database link and run the job again.

     

    Regards

    Suman Pramanik



  • 3.  Re: Jobs "Load Data Warehouse" and "Load Data Warehouse - Access Rights" fail to execute

    Posted 09-23-2015 12:20 PM

    Hi Suman,

     

    We had a problem with the DBLink, it was pointing to the wrong database.

     

    Thank you for your answer and apologise for wasting your time.

     

    Regards,

     

    Marc



  • 4.  Re: Jobs "Load Data Warehouse" and "Load Data Warehouse - Access Rights" fail to execute

    Posted 09-23-2015 12:27 PM

    Hey Marc,

     

    Glad that you fixed it. Have a good one.

     

    Regards

    Suman Pramanik