Data warehouse job getting failed in CA PPM 15.2.2 path

[Santosh-Komeravelli-EMN]

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

[bassa10]

Hi Santosh,

 

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

 

Regards,

Samik

[Santosh-Komeravelli-EMN]

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?

[Arunprasath Ramachandran]

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.

 

[Santosh-Komeravelli-EMN]

Hi Ramachandran,

 

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

 

 

Kindly refer to the attached screenshot.

 

Thanks.

 

Regards,

Santosh

[Mgilbert87]

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

[Suman Pramanik]

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

 

Regards

Suman Pramanik 

[Mgilbert87]

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)

bg-dwh.log.zip   112 KB 1 version

[Suman Pramanik]

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 

[Mgilbert87]

Sounds good, Suman. 

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

 

Regards,

Matthew

[Suman Pramanik]

Hi Matthew,

 

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

 

Regards

Suman Pramanik 

[Nika Hadzhikidi]

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.

4. Run Load Data Warehouse - Full Load

 

I hope this helps -Nika

[Santosh-Komeravelli-EMN]

Thanks for providing resolution on this issue Nika.