Clarity

 View Only
Expand all | Collapse all

Load Data Warehouse job fails

Jump to Best Answer
  • 1.  Load Data Warehouse job fails

    Posted Jul 15, 2015 12:25 PM

    We have upgraded my clients Development environment to v14.2 and installed Jaspersoft.  Everytime we have tried running the Load Data Warehouse job in Clarity to populate the DWH Database, the job fails with the following error:

     

    ClarityDB - isOracle? - An error occurred executing this job entry :
    Couldn't execute SQL: EXEC DWH_CFG_ADDIN_EXTRAS_SP 'PPMDBLINK.IPT_DEV.niku'

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_INV_STATUS_REPORT'.

     

    This is an OOTB stored procedure that runs and the object it is looking for is hardcode.

     

    Environment Information:

    - Clarity 14.2 Patch 1 Installed on Windows (this was upgraded from 13.2 patch 4)
    - Clarity Database is on MS SQL Server 2008 under the Schema IPT_Dev owned by the user niku.
    - Jaspersoft Installed on Windows with Tomcat 7.0.55
    - Jaspersoft Database is on MS SQL Server 2008 under the Schema PPM_JASPERSOFT owned by the user ppmjaspersoft.
    - Clarity Dataware Database is on MS SQL Server 2008 under the Schema PPM_DWH owned by the user ppm_dwh.
    - DB Link created under the user ppm_dwh to point to the Clarity database on IPT_Dev.

     

    Has anyone seen this before?

     

    Thanks.



  • 2.  Re: Load Data Warehouse job fails

    Posted Jul 16, 2015 03:00 AM

    Hi Michael,

     

    Did you run Time slice job and Load Data Warehouse Access Right job before running this job ?



  • 3.  Re: Load Data Warehouse job fails

    Posted Jul 16, 2015 01:52 PM

    Yes we did run all of the jobs in order per the installation document.  We have been in contact with CA Support and it seems like there are a couple of clients with the same issue we are running into, so they are now looking into it.  But if anyone else has seen this and has a solution, I am all ears.



  • 4.  Re: Load Data Warehouse job fails

    Posted Nov 25, 2015 04:23 PM

    Michael, did your get a resolution to the DWH_INV_STATUS_REPORT table issue ?



  • 5.  Re: Load Data Warehouse job fails

    Posted Mar 31, 2016 04:04 AM

    Dear all,

     

    I stumbled over this post after getting an SQL exception during run of Load Data Wareouse in procedure DWH_CFG_ADDIN_EXTRAS_SP:

    As I have PMO accelerator installed on 14.3, it assumes to be able to use DWH_INV_STATUS_REPORT, but this table is not there and will also not be created after adding/removing the object to/from the DWH.

     

    Any hints on how to get this table in my DWH?

     

    Kind regards

    Georg



  • 6.  Re: Load Data Warehouse job fails

    Posted Apr 22, 2016 11:01 AM

    Just curious if this was ever resolved? If so, how? 



  • 7.  Re: Load Data Warehouse job fails
    Best Answer

    Broadcom Employee
    Posted Apr 26, 2016 11:52 AM

    Hi All,

     

    This error usually happens due to an issue with fields on the Status Report object. It fails when creating the view, here is the code from DWH_CFG_ADDIN_EXTRAS_SP stored procedure, specifically this part:

    'CREATE VIEW DWH_INV_STATUS_REPORT_LATEST_V AS SELECT I.INVESTMENT_KEY, R.CLARITY_STATUS_REPORT_KEY STATUS_REPORT_KEY, ' +
                                      'ROW_NUMBER() OVER (PARTITION BY R.INVESTMENT_KEY ORDER BY R.REPORT_DATE DESC, R.CLARITY_STATUS_REPORT_KEY DESC) REPORT_ORDER ' +
                                      'FROM DWH_INV_INVESTMENT I INNER JOIN DWH_INV_STATUS_REPORT R ON I.INVESTMENT_KEY = R.INVESTMENT_KEY ' +
                                      'WHERE  ISNULL(R.SR_REPORT_STATUS_KEY,''FINAL'') = ''FINAL'''

    Basically at all times we have faced this problem it was related to incorrectly enabled fields on Status Report object (Report status field unchecked or similar).

     

    To solve this issue or similar issue with DWH_CFG_ADDIN_EXTRAS_SP and Status Report, try the following solution:

     

    1. Go to UI - Administration - Objects - Status Report - Attributes
    2. Ensure all the User defined attributes apart from cop_phase are checked for Data Warehouse, most importantly Report Status
    3. If they're not, check them, and run Load Data Warehouse - Full Load
    4. If they already were checked, or the above doesn't help, go to UI - Administration - Objects - Status Report Properties  and uncheck the object from Data Warehouse. Save
    5. Run Load Data Warehouse - Full Load until completion
    6. Now go back in the Status Report properties and check it back for Data Warehouse
    7. Go to Attributes page : select all User Defined attributes apart from cop_phase to be included in Data Warehouse
    8. Run Load Data Warehouse - Full Load until completion

     

    This should resolve the issue.

     

    Please let me know how it goes.

     

    Kind Regards -Nika



  • 8.  Re: Load Data Warehouse job fails

    Posted Apr 26, 2016 12:26 PM

    Thanks, I did that, but maybe I missed a step, so I can try it again. If we don't use the OOTB status report, can we leave the object as unchecked for the DWH or will that cause issues too?



  • 9.  Re: Load Data Warehouse job fails

    Posted Apr 28, 2016 09:46 AM

    These steps are not working. We are getting it on 14.4. Is there any other published solution?   If not, we will contact support.



  • 10.  Re: Load Data Warehouse job fails

    Broadcom Employee
    Posted Apr 28, 2016 09:57 AM

    Hi Lynn,

     

    This solution will work on 14.2 and 14.3 only. On 14.4 it's no longer possible to uncheck the object in step 4, also most of the attributes are not available for unchecking.
    Please feel free to go ahead and raise a Support case with us. Thank you -Nika



  • 11.  Re: Load Data Warehouse job fails

    Posted Apr 28, 2016 10:00 AM

    Thanks yes this is on 14.3 where it does not work- sorry for the confusion.   We just happen to get the same error on 14.4

     

    Could you please still answer my other question? Since we don't use the OOTB status report, can we leave the object unchecked? We then, naturally do not run any OOTB reports that read the OOTB status report.



  • 12.  Re: Load Data Warehouse job fails

    Broadcom Employee
    Posted Apr 28, 2016 02:21 PM

    Hi Lynn,

     

    If you don't use the Status Report object, and the OOTB reports that are based on it, then yes, on 14.3 you can leave it unchecked. The impact of not including it will break some of the OOTB reports such as Project Status Detail or Project Status Summary.
    Please keep in mind that on 14.4. onwards, this object is mandatorily checked for DWH, so it will be greyed out for unchecking.

     

    Hope this helps -Nika



  • 13.  Re: Load Data Warehouse job fails

    Posted May 03, 2016 03:37 PM

    The Checkbox for DWH Enabled is greyed out once you apply the PMO Accelerator Service Pack for 14.4. 



  • 14.  Re: Load Data Warehouse job fails

    Posted Feb 21, 2017 10:17 AM

    I'm following the steps your suggestion, but in step 6 I can not put the check  at checkbox "Include in the store
    I have CA PPM 15.1 sp # 3 and I have the same problem ... but I am locked



  • 15.  Re: Load Data Warehouse job fails

    Posted Feb 21, 2017 10:18 AM

    Include in the Data Warehouse



  • 16.  Re: Load Data Warehouse job fails

    Broadcom Employee
    Posted Feb 21, 2017 10:26 AM

    Hi Ricardo

     

    In your version you will not be able to uncheck anything. If you have the same issue please do the following:

    1. Go to Administration - Content Addins - PMO - Items

    2. Browse for Status Report Object

    3. Select it and click Apply

    4. Once it's applied, check app-ca log for any errors.

    5. If no errors, run a Full Load of Data Warehouse

     

    If you have errors, or the job still fails, raise a ticket with CA Support, we may have to get this corrected manually.

     

    Hope this helps -Nika



  • 17.  RE: Re: Load Data Warehouse job fails

    Posted Jun 20, 2021 10:45 PM
    Hi Nika,

    I have run into a similar issue now after upgrading a customer to 15.9.2. I get the below error. Logged a ticket with support:32747364. I have followed your steps of installing the content from the PMO adding still didnt help. Any suggesstions?

    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at java.base/java.lang.Thread.run(Thread.java:834)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - Couldn't execute SQL: ALTER TABLE DWH_INV_STATUS_REPORT ADD COST_EFFORT_EXPLANATION VARCHAR2(4000)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
    
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at org.pentaho.di.core.database.Database.execStatement(Database.java:1432)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at org.pentaho.di.core.database.Database.execStatements(Database.java:1538)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:225)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	... 2 more
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - Caused by: java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
    
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.ddcj.a(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.ddcj.b(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.ddcj.a(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.ddcj.a(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oracle.ddam.u(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.dder.y(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.dder.u(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at com.ca.clarity.jdbc.oraclebase.dder.execute(Unknown Source)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	at org.pentaho.di.core.database.Database.execStatement(Database.java:1406)
    2021/06/21 12:32:22 - APPEND_COL_SQL.0 - 	... 4 more
    2021/06/21 12:32:22 - DW_META_COLS_MOD_TRN - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Errors detected!
    2021/06/21 12:32:22 - DW_META_COLS_MOD_TRN - DW_META_COLS_MOD_TRN
    2021/06/21 12:32:22 - DW_META_COLS_MOD_TRN - DW_META_COLS_MOD_TRN
    2021/06/21 12:32:22 - DW_META_COLS_MOD_TRN - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Errors detected!
    


    Thanks,
    Vasanth

    ------------------------------
    Thanks,
    Vasanth
    ------------------------------



  • 18.  RE: Re: Load Data Warehouse job fails

    Broadcom Employee
    Posted Jun 21, 2021 09:42 AM
    Hi Vasanth
    What is the full statement that fails? I would run it directly on the DWH database and see if a table or view is actually missing. Hope this helps! -Nika

    ------------------------------
    Nika Hadzhikidi
    Sr Principal Support Engineer
    Broadcom
    ------------------------------



  • 19.  RE: Re: Load Data Warehouse job fails

    Posted Jun 23, 2021 02:59 AM
    Hi Nika,

    Thank you for your response. I did some further investigation and found the below tables and views missing. After i have recreated them in the dwh schema the issue was resolved.

     It was due to 3 DWH tables and few DWH views missing the dwh schema.
    This time the upgrade was from v13.3 to 15.9.2. It did not have issues when I did the upgrade earlier from v13.3 to 15.9.1. However this time the path to upgrade was v13.3 -->14.2-->15.4.0-->15.6.1-->15.7.1-->15.9.0-->15.9.2 when compared to earlier v13.3 -->14.2-->15.4.0-->15.6.1-->15.7.1-->15.9.0-->15.9.1

     

    It appears that the 15.9.0 to 15.9.2 csk install is missing few tables and views. I have manually created them in the dwh schema and was able to have the load dwh full load run successfully.

     

    Below list:

     

    DWH_INV_STATUS_REPORT  TABLE

    DWH_RDM_ITEM_POSITION TABLE

    DWH_INV_STATUS_REPORT_LN              TABLE

    DWH_INV_STATUS_REPORT_LATEST_V                   VIEW

    DWH_INV_STATUS_RPT_LAST_LN_V     VIEW

    DWH_INV_STATUS_RPT_LAST_V             VIEW

    DWH_RDM_ROADMAP_ITEM_FACTS_V                  VIEW

    DWH_RIM_CHANGE_REQ_NOTES_V     VIEW

    DWH_RIM_ISSUE_ACTIONS_V                  VIEW

    DWH_RIM_ISSUE_AI_ASSIGNEES_V       VIEW

    DWH_RIM_ISSUE_NOTES_V   VIEW

    DWH_RIM_ISSUE_TASKS_V    VIEW

    DWH_RIM_RISK_ACTIONS_V VIEW

    DWH_RIM_RISK_AI_ASSIGNEES_V          VIEW

    DWH_RIM_RISK_NOTES_V     VIEW

    DWH_RIM_RISK_TASKS_V      VIEW

    DWH_TME_AVAIL_BY_TP_FACTS_V       VIEW

    DWH_TME_INCIDENT_V         VIEW

    DWH_TME_PERIOD_FILTER_V                  VIEW

    DWH_CMN_SCENARIO_TGT_FACTS_V  VIEW

    DWH_TME_SHEET_FACTS_V VIEW

    DWH_CMN_SCENARIO_FACTS_V            VIEW

    DWH_CMN_SCENARIO_GOAL_FACTS_V                  VIEW



    ------------------------------
    Thanks,
    Vasanth
    ------------------------------



  • 20.  RE: Re: Load Data Warehouse job fails

    Broadcom Employee
    Posted Jun 23, 2021 12:00 PM
    Glad to hear it, thank you for letting us know Vasanth!

    ------------------------------
    Nika Hadzhikidi
    Sr Principal Support Engineer
    Broadcom
    ------------------------------