Load Data Warehouse job fails

[pokmi01]

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.

[Anon Anon]

Hi Michael,

 

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

[pokmi01]

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.

[Kimmm]

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

[Georg Schmid]

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

[vtleogal]

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

[Nika Hadzhikidi]

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

[vtleogal]

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?

[vtleogal]

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

[Nika Hadzhikidi]

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

[vtleogal]

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.

[Nika Hadzhikidi]

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

[Anon Anon]

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

[Ricardo Fernandez]

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

[Ricardo Fernandez]

Include in the Data Warehouse

[Nika Hadzhikidi]

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

[Vasanth Geddam]

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?



Thanks,
Vasanth

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

[Nika Hadzhikidi]

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
------------------------------

[Vasanth Geddam]

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
------------------------------

[Nika Hadzhikidi]

Glad to hear it, thank you for letting us know Vasanth!

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