Clarity

Could you help us with this?
We are available to provide you with the information you need.

After running the job he displayed the following errors:

File: bg-ca.log

ERROR 2018-04-03 15:54:57,451 [Dispatch Load Data Warehouse : bg@NXCAPPM01 (tenant=clarity)] niku.njs (clarity:lvilla:6443497__5BCD6754-6B24-46F1-B28C-C6286D616B76:Load Data Warehouse) Error executing job: 5033032
java.lang.Exception: ETL Job Failed. Please see log bg-dwh.log for details.
____________________________________________________

File: bg-dwh.log

ERROR 2018-04-03 15:54:57,422 [Dispatch Load Data Warehouse : bg@NXCAPPM01 (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2018/04/03 15:54:57 - MSSQL? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2018/04/03 15:54:57 - MSSQL? - Couldn't execute SQL: EXEC DWH_CFG_ADDIN_EXTRAS_SP 'PPMDBLINK.niku.niku'
2018/04/03 15:54:57 - MSSQL? -
2018/04/03 15:54:57 - MSSQL? - [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_INV_STATUS_REPORT'.
____________________________________________________

After running the "Load Data Warehouse" job, the ppm_dwh.DWH INV_STATUS_REPORT and ppm_dwh.DWH_INV_STATUS_REPORT_LN tables was deleted

I guess you are hitting a known defect DE32643 if you are using version lesser than 15.3. Please do log a support case and we can work with you to fix or also you can look into When saving the Status Report object, the read-onl - CA Knowledge  

Hi Suman, our version is CA PPM 15.2

Our status report objet is in dwh

Our restriction to be able to migrate to CA PPM 15.4 is for the job load data warehouse to be completed.

In sequence, I tell you what we did:

1. We are in version 15.2 of CA PPM and we want to migrate to version 15.4. When doing so, checkinstall gave the following error: check-dwh-load-job-success.xml | ERROR | Load Data Warehouse job has failed last time it was run. Make sure to run it successfully before proceeding the upgrade.

2. So what we did was re create the database ppm_dwh

3. And then we ran the jobs datamart extration again, load data warehouse create user, and load data warehouse

Hi Suman, 

we followed your suggestion (When saving the Status Report object, the read-onl - CA Knowledge) and after going back to running the job Load data warehouse we got the following errors.

Could you help us with this?

Job Type: Load Data Warehouse - Job Properties

...

Executing ETL Job.

Table input 2 - Unexpected error

Table input 2 - org.pentaho.di.core.exception.KettleDatabaseException:  An error occurred executing SQL:  SELECT CASE WHEN a.record_count > 0 THEN 1 ELSE 0 END record_exists FROM  (SELECT COUNT(1) record_count        FROM   DWH_COP_PRJ_STATUSRPT_V src         WHERE (CLARITY_UPDATED_DATE IS NULL OR CLARITY_UPDATED_DATE >= CONVERT(DATETIME,'1910/01/01 00:00:00'))) a [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_COP_PRJ_STATUSRPT_V'.       at org.pentaho.di.core.database.Database.openQuery(Database.java:1641)      at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:233)      at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)      at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)      at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLSyntaxErrorException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_COP_PRJ_STATUSRPT_V'.      at com.ca.clarity.jdbc.sqlserverbase.ddcw.b(Unknown Source)      at com.

DWH_DIM_FIELD_MAPPING - Errors detected!

DWH_DIM_FIELD_MAPPING - Errors detected!

null - Unexpected error

null - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_STATUS_REPORT      at Processor.processRow(Processor.java:64)      at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)      at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)      at java.lang.Thread.run(Thread.java:745)

null - Errors detected!

null - Errors detected!

ETL Job Failed. Please see log bg-dwh.log for details.

NJS-0401: Execution of job failed.

Hi Suman, the case in CA support is:

2018/04/05 17:19:19 - Table input 2.0 - WHERE (CLARITY_UPDATED_DATE IS NULL OR CLARITY_UPDATED_DATE >= CONVERT(DATETIME,'1910/01/01 00:00:00'))) a
2018/04/05 17:19:19 - Table input 2.0 - [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_COP_PRJ_STATUSRPT_V'.
2018/04/05 17:19:19 - Table input 2.0 -
2018/04/05 17:19:19 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1641)
2018/04/05 17:19:19 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:233)
2018/04/05 17:19:19 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
2018/04/05 17:19:19 - Table input 2.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2018/04/05 17:19:19 - Table input 2.0 - at java.lang.Thread.run(Thread.java:745)
2018/04/05 17:19:19 - Table input 2.0 - Caused by: java.sql.SQLSyntaxErrorException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_COP_PRJ_STATUSRPT_V'.
2018/04/05 17:19:19 - Table input 2.0 - at com.ca.clarity.jdbc.sqlserverbase.ddcw.b(Unknown Source)
...

2018/04/05 17:19:19 - DWH_DIM_FIELD_MAPPING - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Errors detected!
null
ERROR 2018-04-05 17:19:19,846 [dwh_etl_interface_dim - User Defined Java Class] dwh.event null - Unexpected error
ERROR 2018-04-05 17:19:19,851 [dwh_etl_interface_dim - User Defined Java Class] dwh.event null - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_STATUS_REPORT