In general, for some Data Warehouse (DWH) enabled attributes, the user can encounter an alert error message when attempting to save changes to the attribute properties. Another general symptom could be encountered when executing the Load Data Warehouse job indicating that an attribute is invalid.
Below is one specific attribute example of how this issue is encountered, but it could occur for other attributes within the system.
Example Use Case:
In Studio, on the Project Object, when trying to update and save the 'Business Owner' attribute (Attribute ID = obj_stakholder1), a yellow Alert message appears. This is a PMO Accelerator attribute.
ODF_0921: For obj_stakeholder1 attribute lookup query does not have LAST_UPDATED_DATE. So corresponding records will not be updated as expected in DWH
Steps to Reproduce:
1. Login as an administrator
2. Navigate to Administration, Studio: Objects, Project Object
3. Go to Attributes page, click into 'Business Owner' attribute (attribute id = obj_stakeholder1)
Note: the 'Include in the Data Warehouse' option is checked
4. Click 'Save' or 'Save and Return' button (no changes needed to generate message)
Expected Result: Save is successful
Actual Result: ODF-0921 error message appears
Also, if the Load Data Warehouse job is executed, it could fail with a message ORA-00904: "BUSINESS_OWNER": invalid identifier
or similar message with the specific attribute ID.
Applies to all supported PAS environments for specified releases.
Caused by CLRT-78675
In the 'Example Use Case', the associated Dynamic Query Lookup 'Project Stakeholder' (Lookup ID = OBJ_PROJECT_STAKEHOLDER) is missing a requirement for including the attribute in the Data Warehouse. The Dynamic Query Lookup definition must have a select column defined for 'LAST_UPDATED_DATE'.
In general, the root cause for any attribute exhibiting these symptoms are:
- The 'last_updated_date' is not included within the dynamic query lookup definition.
- The 'last_updated_date' is required for the attribute to be used properly within the Data Warehouse.
- Not all installed PMO Accelerator attributes associated with a dynamic query lookup definition has the 'last_updated_date' defined, therefore we have raised CLRT-78675 with SE for review of all lookups that may be used within the Data Warehouse.
This issue CLRT-78675, related to stock Dynamic Query Lookups, is open with development for review.
If this occurs for a user-defined Dynamic Query Lookup, edit your query definition so that you add a date column with the alias 'LAST_UPDATED_DATE'.
For an example of a query that has this extra column LAST_UPDATED_DATE, refer to one of the stock Dynamic Lookups such as the Currencies Lookup (ID = LOOKUP_CURRENCIES).
Contact CA Technical Support for advice regarding a workaround with stock attributes.
For more information on creating Dynamic Query Lookups, reference the current release documentation
On Premise v14.3 Documentation
SaaS v14.3 Documentation
CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in Data Warehouse