Job Failing | Load Data Warehouse & Free Training  | Learn with Rego

Document created by navzjoshi00 Champion on Oct 20, 2016Last modified by navzjoshi00 Champion on Feb 3, 2017
Version 2Show Document
  • View in full screen mode

Your Rego Guide, Providing Clarity for CA PPMDear Community Members,


Today we'll help you resolve several issues we've seen crop up from time to time, like job failures for Load Data Warehouse. There's also some free training opportunities right around the corner. Perhaps the most exciting--a Meisterplan demo. It's the Portfolio Management add-on from itdesign that we're really impressed with, and Citrix will be there to tell their story of using the solution. 


We also have a ton of people downloading the Quick Reference Cards that are part of our Countdown to CA World. There's a new QRC released every day on the regoXchange, and this link will get you to the full list. Several additional free trainings and events, like Jaspersoft and regoU, you can find at the bottom of this Q&A. 




1. Why is the Load DWH Jaspersoft job failing?


2. Can you give me a GEL jumpstart for getting the Next Auto Number Sequence ID of an object attribute?


3. What happens to Timesheets after a Project's Financial Hold is opened?


4. What's a good way to remove financial transactions that are already in the "Ready to Post to WIP" stage?


5. Why can't we see the full OBS hierarchy?


Please feel free to comment on any alternative answers you've found.


We love your input (always).



1. I have an On Demand issue with the Load Data Warehouse job. After adding the Portfolio Category 1 to the UI, the DWH job began to fail. Can you help? Here's bit more of the background:

Attribute is already included in the DWH by default
•    Attribute is OOTB
•    I only added values to the lookup (portfolio category 1) and placed on UI (ideas and projects)
•    Load DHW job does not fail in the Development environment
•    We looked at the xml for the Application Management domain and the attribute is already there
•    We could not find an error in the log
•    I combed the CA Support site and couldn’t find any defects that seemed to be related


9/15/16 12:15 PM

Executing ETL Job.

Job Started

9/15/16 12:15 PM


9/15/16 12:20 PM

ETL Job Finished Successfully.


9/15/16 12:20 PM

Starting Jaspersoft domain updates

Job Completed

9/15/16 12:20 PM

NJS-0401: Execution of job failed.


9/15/16 12:20 PM

Error occurred while processing domain attributes. Could not update attribute portfolio_cat1_key of object inv to domain CSK_APP_Management

We believe we've found the cause of the issue and a resolution. As you can see below, there was an issue on CA support we tested by removing the attributes from the DWH, running the DWH full load, adding the attributes back in, and rerunning the job again.

The Load Datawarehouse (ETL) job fails with error in domain update
Document ID:  TEC1450585
Last Modified Date:  7/15/2016
Technical Document Details

The Load Datawarehouse (ETL) job fails with variations of the error, depending on the object.
Error occurred while processing domain attributes. Could not update attribute [xxxxx] of object xxxxx to domain xxxxx

This error is indicative of PPM vs DWH schema synchronization issue where the ETL job completes, but the domain could not get updated. 
To resolve the issue, the attribute must by synched between the PPM and DWH schema.

Step 1: Remove the offending attribute.
1. Go to Administration >  Studio > Objects > [object] > [attribute]
2. Checkmark the attribute. If many attributes are affected, a bulk update can be used.
3. Click the "Exclude from Data Warehouse" button.
4. Run the full Load DWH job.


Step 2: Re-add the attribute.
1. Go to Administration >  Studio > Objects > [object] > [attribute]
2. Checkmark the attribute. If many attributes are affected, a bulk update can be used.
3. Click the "Include in Data Warehouse" button.
4. Run the full Load DWH job.


2. Does anyone have an example in GEL of returning the next auto number sequence ID of an object attribute? I appreciate the jumpstart!



One ideal method, reviewed at last year's regoUniversity, is database independent, and you can use it on SQL server or Oracle. We like it even more than Database procedure calls. 


GEL Script code is given below:

            <core:invokeStatic className="com.niku.odf.object.autonumbering.AutoNumberGenerator" method="getNextNumber" var="nextCode" >
              <core:arg type="java.lang.String" value="het_prj_gl_actuals"/>
              <core:arg type="java.lang.String" value="code"/>
              <core:arg type="java.lang.String" value="NIKU.ROOT"/>
              <core:arg type="java.lang.Number" value="${null}"/>
              <core:arg type="" value="${null}"/>
              <core:arg type="java.sql.Connection" value="${null}"/>

            <core:invokeStatic className="com.niku.odf.object.autonumbering.AutoNumberGenerator" method="getNextNumber" var="nextName" >
              <core:arg type="java.lang.String" value="het_prj_gl_actuals"/>
              <core:arg type="java.lang.String" value="name"/>
              <core:arg type="java.lang.String" value="NIKU.ROOT"/>
              <core:arg type="java.lang.Number" value="${null}"/>
              <core:arg type="" value="${null}"/>
              <core:arg type="java.sql.Connection" value="${null}"/>


The parameters are:


-    Object ID
-    Attribute ID
-    Partition ID
-    Parent PKID (can be null)
-    Security Identifier (can be null)
-    Connection (can be null)



3. Our project has been on financial hold. When we flip the status to open, will the system retroactively look at all posted timesheets?



If the resources were enabled, there should have been entries created in the Invalid Transactions log when the timesheet were posted. If those entries are present, then you need do nothing more than set the project to open (assuming that the other minimum attributes for enablement have been set), and the transactions will post through the next time you run Post Transactions to Financials and Post to WIP.


4. We entered several duplicated transactions and ran the Post Transactions to Financials Job, so we can't remove the bad transactions from the “invalid transactions” screen. Does anyone have a script or good method to get rid of several transactions from the “Ready to Post to WIP” step?

Posting and reversing is not a good option since there are thousands of transactions already, and identifying the ones we need to remove won’t be easy.


Delete the transactions from the following tables:


It is pretty much same structure as the PPA_WIP tables.




5. When we browse from the OBS on a resource or project record, the full hierarchy won't display. However, if we use FILTERS for OBS or bring OBS into the list view, we see the full tree. Any Ideas?


In this case, Parent Level DEPTS didn’t have a Location, which apparently stopped this browse (even though the lowest level had a location).


THANK YOU------------------------------  

A special thanks to Patrick FinklerBen Rimmasch, Ram Iyer, John Fechenbach, Jerry Dolak, Atul Kunkulol, David Matzdorf, Dave Wachsmann and the Rego Team for this Q/A material.


Rego is releasing 50 CA PPM training items from the regoXchange to celebrate the Countdown to CA World! This week you can download Quick Reference Cards (QRCs) explaining Project Actions and Options Menus, Projects vs Programs vs Portfolios, Risks Issues and Change Requests, Setting the Baseline, and Using the Risk Subpage. Choose the materials you want by clicking here.


Go on a Journey with a Rego Guide. Attendance is Free and Limited. See Available Times at the Links Below. 

Learn Jaspersoft.

Learn Portfolio Management.


Jaspersoft 6.2.1 has new features! See what's new. 


JOIN US------------------------------  

Meisterplan Webinar: Oct 26 @ 12:00PM EST.

Presented by IT Design, Citrix, & Rego. Meisterplan is the CA PPM portfolio add-on from itdesign for prioritizing, selecting, and governing Portfolios based on strong metrics. See a live demo, and hear how Citrix has used Meisterplan to overcome challenges and streamline work processes. Save a Seat (limited to the first 100 registrants).


CampIT in Dallas, TX: Nov 2-3, 2016. 


CA World in Las Vegas, NV: Nov 14-18, 2016. 

Rego Main Booth: 6007 /// regoUniversity Booth: 6002 /// Agile with Icon Booth: 6005.


regoUniversity 2017 at La Cantera Resort & Spa - San Antonio, TX: April 23-27, 2017. 

Receive hands-on CA PPM Training with hundreds of companies who use the tool daily. Click here for Early Bird Pricing.