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