Clarity PPM1

Expand all | Collapse all

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

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

    Posted 10-07-2015 05:08 PM

     

    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



  • 2.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 10-07-2015 06:08 PM

    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



  • 3.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 12-09-2015 08:35 PM

    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…



  • 4.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 12-09-2015 09:03 PM

    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.



  • 5.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 12-09-2015 10:54 PM

    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?



  • 6.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 12-15-2015 09:42 PM

    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.

     



  • 7.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 01-04-2016 10:01 AM

    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?



  • 8.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 01-04-2016 05:27 PM

    yes, you can use SYSDATE if you desire.



  • 9.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 01-22-2016 05:48 PM

    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.



  • 10.  Re: CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse

    Posted 10-08-2015 01:05 AM

    Thanks for sharing this useful info, Kathryn

     

    Regards,

    NJ