Kathryn_Ellis

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

Discussion created by Kathryn_Ellis Employee on Oct 7, 2015
Latest reply on Jan 22, 2016 by paul.maxwell


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

Outcomes