Clarity PPM

Expand all | Collapse all

LOAD DATAWAREHOUSE JOB FAILS

Jump to Best Answer
  • 1.  LOAD DATAWAREHOUSE JOB FAILS

    Posted 10-14-2017 02:07 PM

    Hi, LOAD DATAWAREHOUSE JOB fails with the following error on the logs:

     

    2017/10/13 23:01:19 - User Defined Java Class.0 - *****************************************************************
    2017/10/13 23:01:19 - User Defined Java Class.0 -  
    2017/10/13 23:01:19 - User Defined Java Class.0 -  STARTING EXECUTION FOR THE DIMENSION TABLE - DWH_CMN_PERIOD
    2017/10/13 23:01:19 - User Defined Java Class.0 -  
    2017/10/13 23:01:19 - User Defined Java Class.0 - *****************************************************************
    2017/10/13 23:01:22 - User Defined Java Class.0 - DWH_CMN_PERIOD - Job Log: 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [MSSQL?]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [Get Error Variables]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Get Error Variables - Loading transformation from XML file [file:///G:/CA/PPM/META-INF/datamart/etl_scripts/dwh_cmn_etl_jobs/dwh_mssql_error_occurred.ktr]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_occurred - Dispatching started for transformation [dwh_mssql_error_occurred]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count.0 - Finished reading query, closing connection.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Setting environment variables...
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Set variable ERROR_COUNT to value [0]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message.0 - Finished reading query, closing connection.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Value.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message Variables.0 - We didn't receive a row while setting the default values.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message Variables.0 - Set variable ERROR_NUMBER to value [0]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message Variables.0 - Finished after 0 rows.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Finished after 1 rows.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [No Errors?]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [Success 2]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [Success 2] (result=[true])
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [No Errors?] (result=[true])
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [Get Error Variables] (result=[true])
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [MSSQL?] (result=[true])
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - dwh_mssql_error_handling - Starting entry [MSSQL?]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - dwh_mssql_error_handling - Starting entry [Get Error Variables]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Get Error Variables - Loading transformation from XML file [file:///G:/CA/PPM/META-INF/datamart/etl_scripts/dwh_cmn_etl_jobs/dwh_mssql_error_occurred.ktr]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - dwh_mssql_error_occurred - Dispatching started for transformation [dwh_mssql_error_occurred]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count.0 - Finished reading query, closing connection.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count Variable.0 - Setting environment variables...
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message.0 - Finished reading query, closing connection.
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count Variable.0 - Set variable ERROR_COUNT to value [1]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message Variables.0 - Setting environment variables...
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message Variables.0 - Set variable TABLE_NAME to value [DWH_CMN_PERIOD]
    2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message Variables.0 - Set variable ERROR_MESSAGE to value [Violation of PRIMARY KEY constraint 'DWH_CMN_PERIOD_PK'. Cannot insert duplicate key in object 'ppm_dwh.DWH_CMN_PERIOD'. The duplicate key value is (1006210).]

     

    I've been working with CA Support (Argentina) on this case for over a week, but their lack of an accurate and timely response is becoming a problem.

     

    Any ideas on how can I fix this? I've been working on my timeslices configuration since there was a problem related to that on the health report.

    The weird thing is that I tried a new timeslices configuration on our development environment (this is a production db from september 25) and the job works fine, but when I applied the same configuration in our production environment it still fails with the error attached.

     

    Any input will be highly appreciated.

     

    Regarads,

    Juan.



  • 2.  Re: LOAD DATAWAREHOUSE JOB FAILS

    Posted 10-16-2017 02:58 AM

    Hi Juan,

     

    Will it be possible to BG-DWH all logs, we can take a look and suggest.

     

    Regards

    Suman Pramanik 



  • 3.  Re: LOAD DATAWAREHOUSE JOB FAILS

    Posted 10-16-2017 11:34 AM

    Hi Suman, thanks for your interest and response. I'm inluding those logs so you can check the out along with app and bg logs.

     

    Thanks,

     

    Juan.



  • 4.  Re: LOAD DATAWAREHOUSE JOB FAILS
    Best Answer

    Posted 10-16-2017 05:59 PM

    Hi Juan

     

    Looks like you have a duplicate period in DWH_CMN_PERIOD, most likely copied over from PPM. Please try the following solution:

     

    1. Backup and truncate the table RPT_CALENDAR on PPM database
    2. Run the job Update Report Tables until completion.
    3. Run Load Data Warehouse - Full Load

     

    See if you still have the issue.

     

    Hope this helps -Nika



  • 5.  Re: LOAD DATAWAREHOUSE JOB FAILS

    Posted 10-16-2017 06:57 PM

    Nika, thanks so much, this seemed to be the case. Finally my LDW job is working!

     

    Thanks again.

     

    Juan.



  • 6.  Re: LOAD DATAWAREHOUSE JOB FAILS

    Posted 10-17-2017 02:15 AM

    Thank you Juan and Nika