Clarity

  • 1.  'Load DataWarehouse' Job Failing

    Posted Jul 29, 2016 10:55 AM

    I included couple of Dynamic Lookups (Project Object Attributes) into Jaspersoft Datawarehouse. But after adding them my 'Load DataWarehouse' Job is failing.



  • 2.  Re: 'Load DataWarehouse' Job Failing

    Posted Jul 29, 2016 10:56 AM

    Here the error that I am getting Error 7/28/16 7:14 AM User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_W_PROJECT_PURPOS_00 at Processor.processRow(Processor.java:65) at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181) at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60) at java.lang.Thread.run(Thread.java:745) Error 7/28/16 7:14 AM dwh_etl_interface_lkp - Errors detected! Error 7/28/16 7:14 AM ClarityDB - isOracle? - An error occurred executing this job entry : Couldn't execute SQL: INSERT INTO DWH_LKP_W_PROJECT_PURPOS_00(LANGUAGE_CODE, LANGUAGE_CODE_KEY, CLARITY_UPDATED_DATE, W_PROJECT_PURPOS_00_KEY, W_PROJECT_PURPOS_00, dw_updated_date) select LANGUAGE_CODE as LANGUAGE_CODE, LANGUAGE_ID as LANGUAGE_CODE_KEY, LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, LOOKUP_CODE as W_PROJECT_PURPOS_00_KEY, NAME as W_PROJECT_PURPOS_00, to_date('2016/07/28 12:14:38', 'yyyy/mm/dd HH24:MI:SS') as dw_updated_date  from DWH_LKP_W_PROJECT_PURPOS_00_V@PPMDBLINK where 1=1 and language_code in (select language_code from CMN_LANGUAGES@PPMDBLINK where is_dw_enabled=1)  AND LAST_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss') [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "LANGUAGE_CODE": invalid identifier Error 7/28/16 7:14 AM User Defined Java Class - Unexpected error Error 7/28/16 7:14 AM ETL Job Failed. Please see log bg-dwh.log for details. Job Completed 7/28/16 7:14 AM NJS-0401: Execution of job failed.



  • 3.  Re: 'Load DataWarehouse' Job Failing

    Posted Jul 29, 2016 11:01 AM

    And here is the query that I used in my Dynamic Lookup. I am able to include the attribute in DWH successfully but Job is failing. SELECT DISTINCT clv.lookup_code,       clv.name,       clv.sort_order,       clv.last_updated_date,       LANG.ID,       LANG.LANGUAGE_CODE       FROM  cmn_lookups_v clv, CMN_LANGUAGES LANG LEFT OUTER JOIN cmn_lookups_v clv1 ON clv1.lookup_code=@WHERE:PARAM:USER_DEF:STRING:W_ACTIVITY_TYPE@ WHERE @FILTER@ AND clv.lookup_type = 'W_PROJECT_PURPOSE_LIST' AND clv.language_code = 'en' AND LANG.LANGUAGE_CODE=clv.LANGUAGE_CODE AND ( ( clv1.lookup_code IN ('ACTIVITY_TYPE_INVEST') AND clv.lookup_code IN ('PRJ_PUR_REGULATORY_LEGAL','PRJ_PUR_SECURITY_RISK','PRJ_PUR_REFRESH_REPLACE', 'PRJ_PUR_ ADDITIONAL_TECH_CAPAC','PRJ_PUR_NEW_FOUNDATIONAL_CAPAB','PRJ_PUR_NEW_BUSINESS_CAPABILIT') ) )



  • 4.  Re: 'Load DataWarehouse' Job Failing

    Broadcom Employee
    Posted Aug 05, 2016 04:17 PM

    Hi Prasanth,

     

    Thank you for raising this : since you also have a Support issue on this, I will work with you on it and we will then update the post with the resolution.

     

    Thanks -Nika



  • 5.  Re: 'Load DataWarehouse' Job Failing

     
    Posted Aug 09, 2016 06:50 PM

    Hi Nika_Hadzhikidi - Any resolution for this one yet? Thanks! Chris



  • 6.  Re: 'Load DataWarehouse' Job Failing

    Broadcom Employee
    Posted Aug 10, 2016 10:42 AM

    Hi Chris:

     

    I tested the lookup query which Prasanth provided and was able to successfully add it to Data Warehouse on the same PPM release. This would mean that the lookup query was not actually what caused the issue.

    I advised Prasanth the NSQL query is correct, and to try recreating the lookup with the same query and see if this lookup gets added correctly.

     

    It seems like the issue was now resolved, I am yet pending Prasanth to confirm what was the resolution.

     

    Thanks -Nika



  • 7.  Re: 'Load DataWarehouse' Job Failing

    Posted Aug 05, 2016 04:44 PM

    When adding dynamic lookups be sure to reference the guides.  Specific attributes need to be included Perhaps, created date and the like.  It might be related.  Support will be able to tell exactly.



  • 8.  Re: 'Load DataWarehouse' Job Failing

    Posted Aug 07, 2016 02:48 AM

    I also agree with Austin. These issues usually occur when you add a Dynamic lookup attribute in the datawarehouse. I am sure our engineer from CA Support will help you further on that.

     

    Cheers,

    Abhisek Dhar



  • 9.  Re: 'Load DataWarehouse' Job Failing
    Best Answer

    Broadcom Employee
    Posted Aug 11, 2016 11:59 AM

    Prasanth managed to resolve the issue by changing the query to include:

    LAST_UPDATED_DATE
    LANGUAGE_CODE
    LANGUAGE_ID

    in uppercase. In his lower environment, the query worked with no issue, but in Production the uppercase was required to make the Load Data Warehouse complete.

     

    Thank you -Nika