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.

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.

Release: ESPCLA99000-14.3-Clarity-Extended Support Plus

This issue CLRT-78675, related to stock Dynamic Query Lookups, is fixed in Clarity PPM 14.4 and higher

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

If you are still seeing this issue in  Clarity PPM 14.4 or  higher for an out of the box lookup, check to ensure the out of the box lookups have been upgraded using the following steps:

1. In Clarity go to Administration->Studio->Content Add-Ins
2. Click on the link to Accelerator: Program Management Office
3. Click on the Items tab
4. Filter on Status of Upgrade Ready and Type: Lookup
5. Check the check box next to the impacted lookups and click Install

Once this is done, the related lookup's query should get updated to include the following line item:

@SELECT:RESOURCES.LAST_UPDATED_DATE:[email protected]