Clarity

Expand all | Collapse all

Data warehouse job getting failed in CA PPM 15.2.2 path

Jump to Best Answer

Santosh-Komeravelli-EMN10-09-2017 04:46 AM

  • 1.  Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 08-23-2017 11:24 AM

    Hi folks,

     

    We are currently on 14.3.8 patch on production and we are planning to move to 15.2.2 version.

     

    We have upgraded one of our lower environment to 15.2.2 patch and running load data warehouse job full load and is getting failed!

     

    Could you please let us know if i can do any thing to get it work!

     

    We have ensured that time on all the app servers, bg servers and database should match!

     

    Any suggestions to get this resolved?

     

    Let us know if you need any of the log files.

     

    Thanks a lot in advance.

     

    Regards,

    Santosh



  • 2.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 08-23-2017 11:31 AM

    Hi Santosh,

     

    Did you check the errors which are noted in the logs, specifically the bg-dwh log ?

     

    Regards,

    Samik



  • 3.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 08-23-2017 11:50 AM

    Yes samik. It says, UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'.

     

    Here is the error:

     

    2017/08/23 09:55:23 - - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5003671, 3005326).'
    2017/08/23 09:55:23 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
    2017/08/23 09:55:23 - - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5003671, 3005326).'
    2017/08/23 09:55:23 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
    2017/08/23 09:55:23 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
    2017/08/23 09:55:23 - - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5003671, 3005326).'
    2017/08/23 09:52:19 - Get Error Variables - Loading transformation from XML file

     

    Do you have any idea on fixing this?



  • 4.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Broadcom Employee
    Posted 08-23-2017 12:29 PM

    Hi Santosh,

     

    Are you running the "Load Data Warehouse"  job  in incremental Load or in Full Load.

     

    If you are running the job in Incremental Load try Running  the Job in Full Load it will truncates all the DWH tables loads the data from PPM Transaction database to DWH.

     



  • 5.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 08-23-2017 12:46 PM

    Hi Ramachandran,

     

    I am running the load data warehouse job as full load.

     

     

    Kindly refer to the attached screenshot.

     

    Thanks.

     

    Regards,

    Santosh



  • 6.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 10-03-2017 01:11 PM

    Hello,

     

    Our organization is upgrading from 14.3 to 15.2 and are currently running into this same issue.  Was there a solution found?

     

    Thank you!

    Matthew Gilbert



  • 7.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Broadcom Employee
    Posted 10-03-2017 01:15 PM

    we need to see the DWH logs in order to provide the solution

     

    Regards

    Suman Pramanik 



  • 8.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 10-03-2017 01:19 PM
      |   view attached

    Hello Suman,

     

    Thank you for the reply!  Here's what our current log looks like (attached) and the seemingly relevant entries:

     

    ...

    INFO 2017-10-03 09:50:31,705 [dwh_mssql_error_handling UUID: 600d38a5-7655-4bbe-a670-2459015cd661] dwh.event - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5054968, 3005569).'

    ERROR 2017-10-03 09:50:32,018 [dwh_mssql_error_handling UUID: 600d38a5-7655-4bbe-a670-2459015cd661] dwh.event Abort job - Aborting job.
    INFO 2017-10-03 09:50:32,075 [Thread-600] dwh.event null - Job execution finished
    ERROR 2017-10-03 09:50:32,122 [Dispatch Load Data Warehouse : bg@phppmtest (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
    2017/10/03 09:40:06 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:40:08 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:40:11 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:40:12 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:40:13 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:40:15 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:40:16 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:46:44 - audit table name - & DWH_CFG_AUDIT
    2017/10/03 09:50:31 - - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5054968, 3005569).'
    2017/10/03 09:50:32 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
    2017/10/03 09:50:31 - - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5054968, 3005569).'
    2017/10/03 09:50:32 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
    2017/10/03 09:50:32 - Abort job - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Aborting job.
    2017/10/03 09:50:31 - - Error for table 'DWH_INV_ASSIGN_PERIOD_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_ASSIGN_PERIOD_FACTS' and the index name 'DWH_INV_ASSIGN_PER_FACTS_U1'. The duplicate key value is (5054968, 3005569).'
    2017/10/03 09:39:30 - Get Error Variables - Loading transformation from XML file [file:///D:/Niku/Clarity/META-INF/datamart/etl_scripts/dwh_cmn_etl_jobs/dwh_mssql_error_occurred.ktr]
    2017/10/03 09:39:30 - dwh_mssql_error_occurred - Dispatching started for transformation [dwh_mssql_error_occurred]

    ...

     

    Thanks again!

    Matthew

    Attachment(s)

    zip
    bg-dwh.log.zip   112 KB 1 version


  • 9.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Broadcom Employee
    Posted 10-03-2017 01:25 PM

    Hi Matthew,

     

    From the logs there seems to some duplicate  Assignment data in PPM which needs to be cleaned up in order to have DWH running . It could be due to incorrect slice settings and the very first thing I would say is to check the new health report in 15.2 to see if you see issues there and log a support case as it needs deeper investigation.

     

    Regards
    Suman Pramanik 



  • 10.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 10-03-2017 01:33 PM

    Sounds good, Suman. 

    A support ticket has been logged so we'll go from there as well.  Thanks for the information!

     

    Regards,

    Matthew



  • 11.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Broadcom Employee
    Posted 10-03-2017 01:40 PM

    Hi Matthew,

     

    If you navigate under Administration --> Security And Diagnostic and under that you will see the health report.

     

    Regards

    Suman Pramanik 



  • 12.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path
    Best Answer

    Broadcom Employee
    Posted 10-05-2017 10:46 AM

    Hello everyone,

     

    I worked on this issue together with Santosh on a Support ticket. We resolved it by doing the following:

     

    1. Recreate the DWH database from a 15.2 backup
    2. Recreate the DBLINK by going to CSA - Data Warehouse and hitting Save couple of times
    3. Once done, run the command:

    admin db dwh-upgrade -Dupgrade.phase=maintenance

    (this is only required if you are having patches applied on top of 15.2 (base), we were 15.2.0.2) If you are on base version, skip this step.

    1. Run Load Data Warehouse - Full Load

     

    I hope this helps -Nika



  • 13.  Re: Data warehouse job getting failed in CA PPM 15.2.2 path

    Posted 10-09-2017 04:46 AM

    Thanks for providing resolution on this issue Nika.