CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

[Kathryn Ellis]

 

Overview:

Include an attribute that uses a Dynamic Query Lookup in the Data Warehouse.

 

Steps:

1. Create the Dynamic Query Lookup definition with specific coding rules (see below)
2. After creating the Dynamic Query Lookup, create an attribute and check the box to 'Include in the Data Warehouse'
   • NOTE: if you do not define your Dynamic Query Lookup properly you can encounter error messages when saving the attribute with this option checked (see below for error message explanations)
3. Then execute the 'Load Data Warehouse' job for a full run to include this attribute.
   

Dynamic Query Coding Rules for use with DWH:

If you want to include an attribute that uses a Dynamic Query Lookup into the Data Warehouse (DWH), you must first ensure that the definition of the lookup query has specific coding.

 

The Dynamic Query Lookup MUST ALWAYS include a select column named 'LAST_UPDATED_DATE'

• The alias or column field name must be set to 'LAST_UPDATED_DATE'.
• This must be a date field that is populated with a valid date.
• The date can be from any of the tables included in the dynamic query.

•   For example, if you are querying the PRTIMEPERIOD table, the last updated date field has a table column name of 'PRMODTIME' ; your select statement could look like this: 

@SELECT:tp.PRMODTIME:LAST_UPDATED_DATE@

 

Not all tables have language translations associated with the data. Therefore, adding language translations is OPTIONAL and not a requirement for using the query lookup with DWH.

• If you want to include the translations, you can add a select column named 'LANGUAGE_CODE' (required) and 'LANGUAGE_ID' (optional).
• In addition to the select columns, you need to include the @WHERE:PARAM:LANGUAGE@ statement in the WHERE clause for DWH to recognize the language translations exist in this lookup.

 

Here is an example of a dynamic query lookup that includes BOTH RULES for defining the last updated date and the language translations:

 

SELECT  @SELECT:a.name:country_name@,

        @SELECT:c.currency_code:currency_code@,

        @SELECT:c.currency_code || ' (' || c.currency_symbol || ')':currency_name@,

@SELECT:c.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,

@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,

@SELECT:LANG.ID:LANGUAGE_ID@

FROM    cmn_countries b,

        cmn_captions_nls a,

        cmn_currencies c,

CMN_LANGUAGES LANG

WHERE   a.table_name = 'CMN_COUNTRIES'

AND     @FILTER@

AND     a.pk_id = b.id

AND     a.language_code = @WHERE:PARAM:LANGUAGE@

AND     b.currency_id = c.id

AND     LANG.LANGUAGE_CODE=a.LANGUAGE_CODE

 

 

 

 

For more information on creating Dynamic Query Lookups, reference the current release documentation

 

On Premise v14.3 Documentation

https://docops.ca.com/ca-ppm/14-3/en/administration/configure-lookups#ConfigureLookups-DynamicQueryLookups 

 

SaaS v14.3 Documentation

https://docops.ca.com/ca-ppm-saas/14-3/en/administration/configure-lookups#ConfigureLookups-DynamicQueryLookups 

 

 

ERROR MESSAGE Explanations:

 

When I try to check the box 'Include in the Data Warehouse' option for an attribute that is defined using a Dynamic Query Lookup, there are 2 errors that can be generated  ODF-0920 or ODF-0921.

 

(general message)

ODF-0920: Unable to make attribute Data Warehouse enabled. Attribute lookup query returns translations and can not be brought to DWH, correct the lookup query to make it DWH enable

 

(actual message for lookup missing LANGUAGE_CODE)

ODF-0920: Because this custom attribute uses a dynamic lookup that uses a language code parameter WHERE:PARAM:LANGUAGE but does not return a language_code column it cannot be Data Warehouse enabled. To successfully enable a custom attribute that uses a dynamic lookup for the Data Warehouse you must add the following columns to the lookup query select statement LANGUAGE_CODE and LANGUAGE_ID.

 

(message for a lookup that does not have LAST_UPDATED_DATE)

ODF-0921: For <attribute_id> attribute lookup query does not have LAST_UPDATED_DATE. So corresponding records will not be updated as expected in DWH

[System]

Thanks for the tip Kathryn!

Kathryn Ellis wrote:

 

Overview:

Include an attribute that uses a Dynamic Query Lookup in the Data Warehouse.

Steps:

1. Create the Dynamic Query Lookup definition with specific coding rules (see below)
2. After creating the Dynamic Query Lookup, create an attribute and check the box to 'Include in the Data Warehouse'
   • NOTE: if you do not define your Dynamic Query Lookup properly you can encounter error messages when saving the attribute with this option checked (see below for error message explanations)
3. Then execute the 'Load Data Warehouse' job for a full run to include this attribute.
   

Dynamic Query Coding Rules for use with DWH:

If you want to include an attribute that uses a Dynamic Query Lookup into the Data Warehouse (DWH), you must first ensure that the definition of the lookup query has specific coding.

 

The Dynamic Query Lookup MUST ALWAYS include a select column named 'LAST_UPDATED_DATE'

• The alias or column field name must be set to 'LAST_UPDATED_DATE'.
• This must be a date field that is populated with a valid date.
• The date can be from any of the tables included in the dynamic query.

•   For example, if you are querying the PRTIMEPERIOD table, the last updated date field has a table column name of 'PRMODTIME' ; your select statement could look like this: 

@SELECT:tp.PRMODTIME:LAST_UPDATED_DATE@

Not all tables have language translations associated with the data. Therefore, adding language translations is OPTIONAL and not a requirement for using the query lookup with DWH.

• If you want to include the translations, you can add a select column named 'LANGUAGE_CODE' (required) and 'LANGUAGE_ID' (optional).
• In addition to the select columns, you need to include the @WHERE:PARAM:LANGUAGE@ statement in the WHERE clause for DWH to recognize the language translations exist in this lookup.

 

Here is an example of a dynamic query lookup that includes BOTH RULES for defining the last updated date and the language translations:

 

SELECT  @SELECT:a.name:country_name@,

        @SELECT:c.currency_code:currency_code@,

        @SELECT:c.currency_code || ' (' || c.currency_symbol || ')':currency_name@,

@SELECT:c.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,

@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,

@SELECT:LANG.ID:LANGUAGE_ID@

FROM    cmn_countries b,

        cmn_captions_nls a,

        cmn_currencies c,

CMN_LANGUAGES LANG

WHERE   a.table_name = 'CMN_COUNTRIES'

AND     @FILTER@

AND     a.pk_id = b.id

AND     a.language_code = @WHERE:PARAM:LANGUAGE@

AND     b.currency_id = c.id

AND     LANG.LANGUAGE_CODE=a.LANGUAGE_CODE

 

 

 

 

For more information on creating Dynamic Query Lookups, reference the current release documentation

 

On Premise v14.3 Documentation

https://wiki.ca.com/display/CCPPMOP143/Create+Lookups#CreateLookups-DynamicQueryLookups

 

SaaS v14.3 Documentation

https://wiki.ca.com/display/CCPPMOD143/Create+Lookups#CreateLookups-DynamicQueryLookups

 

 

ERROR MESSAGE Explanations:

 

When I try to check the box 'Include in the Data Warehouse' option for an attribute that is defined using a Dynamic Query Lookup, there are 2 errors that can be generated  ODF-0920 or ODF-0921.

 

(general message)

ODF-0920: Unable to make attribute Data Warehouse enabled. Attribute lookup query returns translations and can not be brought to DWH, correct the lookup query to make it DWH enable

 

(actual message for lookup missing LANGUAGE_CODE)

ODF-0920: Because this custom attribute uses a dynamic lookup that uses a language code parameter WHERE:PARAM:LANGUAGE but does not return a language_code column it cannot be Data Warehouse enabled. To successfully enable a custom attribute that uses a dynamic lookup for the Data Warehouse you must add the following columns to the lookup query select statement LANGUAGE_CODE and LANGUAGE_ID.

 

(message for a lookup that does not have LAST_UPDATED_DATE)

ODF-0921: For <attribute_id> attribute lookup query does not have LAST_UPDATED_DATE. So corresponding records will not be updated as expected in DWH

[Anon Anon]

In version 14.3 of Clarity, Dynamic SQL in Look-ups is no longer able to be updated.  Has there been any consideration on how these additional attributes are added to existing Dynamic SQL Look-ups going forward?

 

From the Administration Notes for V14.3:

CA PPM SaaS administrators create dynamic query lookups with NSQL queries to produce drop-down lists on the user interface. You associate the lookup with an object attribute. With Release 14.3, you cannot modify the NSQL query for a dynamic query lookup if the lookup is associated with an object attribute.

https://docops.ca.com/ca-ppm-saas/14-3/release-information/change-impact-and-upgrade-saas/release-14-3-updates-saas#Rele…

[Kathryn Ellis]

I see what you are talking about. Even if it is a user-defined lookup, will not allow updates to the query when it is associated with the attribute.

I think you would have to create a new lookup/attribute pair and migrate the data over to the new attribute and deactivate/remove the old one.

[Anon Anon]

This is an CA clouded environment, so the only way I can achieve is:

1. Create new attribute - no look-up, just needs to be same type as original attribute.  this will just be a temporary attribute to store the original attribute
2. Write process to update every record to copy over original attribute to new attribute
3. Pull out every portlet, process, view and potentially Jasper reports that is using original attribute (anyone else think of anything else that needs to be considered?)
4. Delete original attribute
5. Modify Dynamic look-up which hopefully is now able to be edited as it is now not being used by an Object attribute
6. Recreate original attribute
7. Write process to update every record to copy over new attribute to recreated original attribute
8. Push back all portlets, processes, views and potentially jasper reports that were using original attributes (ie those items from Step 3)
9. Delete new attribute created in Step 1

Am I missing any other steps?

 

Shouldn't this have been documented and agreed before apply this restriction on Look-ups for V14.3?

[Anon Anon]

I was able to:

XOG out the lookup

Edit the XML to change the SQL

XOG back the lookup

 

Lot clearner than doing the 9 steps documented above, but the reason we are now prevented from directly editing the SQL from the look-up is due to 'CLRT-77011 (S2) - BPM-0519: Internal Process Engine Error', so worth considering when you do change the SQL.

 

[MLR_VZW]

Re the rules for the mandatory LAST_UPDATED_DATE column:

It is stated that any date field from the SQL can be used in the return set.

IS IT possible to then just use SYSDATE?

[Kathryn Ellis]

yes, you can use SYSDATE if you desire.

[Paul Maxwell]

Mmmmm, any valid date can be used, so sysdate is technically ok. But my guess is that the inherent logic for having the LAST_UPDATED_DATE column would be to help ensure the DWH is updated, hence if you use sysdate you may be updating more frequently than is strictly necessary. Just a thought.

[navzjoshi00]

Thanks for sharing this useful info, Kathryn

 

Regards,

NJ