Clarity PPM1

Tech Tip: Data Warehouse job failing on DWH_LKP_TRANSLATION if other language than English is enabled (i.e. Spanish) after patch install

  • 1.  Tech Tip: Data Warehouse job failing on DWH_LKP_TRANSLATION if other language than English is enabled (i.e. Spanish) after patch install

    Posted 02-14-2018 12:50 PM

    Hello, fellow communities members!

     

     


    Wanted to share with you an issue we have hit with Data Warehouse, turned out to be a bug.

     

    Affected versions: CA PPM 15.1.0

    Fix for 15.1.0.10

    Not reproducible on other versions.
    Oracle and MSSQL


    STR:
    1. Connect to CA PPM 15.1.0
    2. Go to Administration - System Options - add Spanish language to Data Warehouse
    3. Save
    4. Run DWH job - Full, note it runs well
    5. Now upgrade the system to 15.1.0.8
    6. Run Load Data Warehouse - Full

    Expected Results: The job to run correctly
    Actual Results: Load Data Warehouse fails with error message:
    2018/01/19 15:00:39 - User Defined Java Class.0 - 2018/01/19 15:00:39 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "PPM1510_DWH"."DWH_LKP_TRANSLATION"."TRANSLATION" (actual: 1428, maximum: 255)

    Alternate MSSQL message:
    Error message:
    ERROR 2017-12-29 10:20:06,317 [Thread-53050] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
    Couldn't execute SQL: INSERT INTO DWH_LKP_TRANSLATION(CLARITY_UPDATED_DATE, IS_ACTIVE, LANGUAGE_CODE, LANGUAGE_CODE_KEY, SORT_ORDER, TRANSLATION, TRANSLATION_KEY, dw_updated_date)
    select LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, IS_ACTIVE as IS_ACTIVE, LANGUAGE_CODE as LANGUAGE_CODE, LANGUAGE_ID as LANGUAGE_CODE_KEY, SORT_ORDER as SORT_ORDER, NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, CONVERT(DATETIME ,'2017-12-29 10:20:05') as dw_updated_date from [PPMDBLINK].niku.niku.DWH_LOOKUPS_V where 1=1 AND LAST_UPDATED_DATE >= CONVERT(DATETIME,'1910/01/01 00:00:00')

    [CA Clarity][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.





    When checking we can see that when running this query on PPM:
    select
    NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, length(name) from DWH_LOOKUPS_V
    order by length(name) desc

    There is the following value on an OOTB attribute that gets corrupted:
    Lookup Type PAC_GL_RPTS
    Lookup value GL_RPT_POST_MATRIX_UNDEFINED


    Lookup Type: GL_RPT_POST_MATRIX_UNDEFINED 
    Length 1411 (max DWH allowed is 255)


    Workaround:
    1. Go to Administration - Lookups
    2. Open the lookup PAC_GL_RPTS
    3. Go Values
    4. Find in the list the Value GL_RPT_POST_MATRIX_UNDEFINED
    5. Click on the little Globe icon (Translate)
    6. Reset the Translation (NAME field) in Spanish (and any other included in DWH language) to be under 255 characters, i.e. " No se ha definido la matriz del libro mayor"
    7. Save
    8. Run Load Data Warehouse - Full

    If after the above there is still an issue query:
    select
    NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, length(name) from DWH_LOOKUPS_V
    order by length(name) desc
    Any lookup translations over 255 will have to be manually modified as per above.

     

     

    Hope this helps! -Nika