Message Image  

Clarity

Expand all | Collapse all

Create Jaspersoft Job failing

Jump to Best Answer
  • 1.  Create Jaspersoft Job failing

    Posted 06-10-2019 03:15 AM
    Edited by Chris Hackett 06-13-2019 11:18 AM
    HI,


    Recently we did a Refresh of our DEV system with PROD Data.
    And we see that Create Update Jaspersoft Job is failing with message

    Failed to get an instance of ClarityJasperAdmin :: Exception getting ClarityJasperAdmin : Unauthorized access to http://<url>/reportservice with username ppmjasperadmin.

    i tried this command "admin jaspersoft syncPPMContext -userName superuser -password superuser",
    but No luck. Do I need to regenerate Keystore file? Please help me to know  what is wrong here and how to solve this.

    Thanks,
    Divya

    #jaspersoft​


  • 2.  RE: Create Jaspersoft Job failing
    Best Answer

    Posted 06-10-2019 05:27 AM
    Hi Divya

    As you refresh you would need to do the following steps


    1. Generate the keystore and copy to Jaspersoft server
    2. Login to Jaspersoft console and Search for user jasperadmin under the tenant which is linked to clarity instance
    3. Edit the user and look for tab Attributes on the right-hand panel
    4. Delete all the attributes except domain Whitelist as that is read only
    5. Now login to Clarity application server
    6. Navigate to CLARITY_HOME\bin and execute update jasperParameters
    7. Run the Job Now. 

    Hope this helps


    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 3.  RE: Create Jaspersoft Job failing

    Posted 06-11-2019 07:00 AM
    Thanks Suman. That worked.


  • 4.  RE: Create Jaspersoft Job failing

    Posted 06-18-2019 05:20 AM
    Hi Suman,

    After I executed the command also, DWH jib is failing.
    Here is the log from bg-dwh.log

    Couldn't execute SQL: DECLARE
    V_SQL_TEXT CLOB;
    V_SQLCODE NUMBER;
    V_SQLERRM VARCHAR2(4000);
    TYPE DIM_REC IS RECORD (AS_OF_DATE DATE, OBJ_STAKEHOLDER1_CAPTION VARCHAR2(2100), BUSINESS_OWNER_KEY NUMBER(32), ESTIMATING_FACTOR_1 NUMBER(32), ESTIMATING_FACTOR_2 NUMBER(32), EV_CALC_METHOD_KEY NUMBER(32), EV_PERIOD_ID_CAPTION VARCHAR2(2100), EV_RPT_PERIOD_KEY NUMBER(32), OBJ_STAKEHOLDER3_CAPTION VARCHAR2(2100), FINANCE_GOVERNANCE_KEY NUMBER(32), IDEA_PRJ_CATEGORY_KEY VARCHAR2(255), IDEA_PRJ_TYPE_KEY VARCHAR2(255), INVESTMENT_KEY NUMBER(32), IS_FINISH_IMPOSED NUMBER(32), IS_PROGRAM NUMBER(32), IS_REQ_APPROVAL_REQUIRED NUMBER(32), IS_START_IMPOSED NUMBER(32), OBJECTIVE VARCHAR2(4000), OBJ_STAKEHOLDER2_CAPTION VARCHAR2(2100), PROJECT_MGMT_OFFICE_KEY NUMBER(32), RISK_FLEXIBILITY_KEY NUMBER(32), RISK_FUNDING_KEY NUMBER(32), RISK_HUMAN_INTERFACE_KEY NUMBER(32), RISK_IMPLEMENTATION_KEY NUMBER(32), RISK_INTERDEPENDENCY_KEY NUMBER(32), RISK_OBJECTIVES_KEY NUMBER(32), RISK_ORG_CULTURE_KEY NUMBER(32), RISK_RESOURCE_AVAIL_KEY NUMBER(32), RISK_SPONSORSHIP_KEY NUMBER(32), RISK_SUPPORTABILITY_KEY NUMBER(32), RISK_TECHNICAL_KEY NUMBER(32), SPONSORED_BY VARCHAR2(96), STATUS_REPORTING_KEY VARCHAR2(255), Z_ACHIVED_DELIVERAB VARCHAR2(4000), Z_ANNUAL_SALES_VOL NUMBER(32), Z_ANNUAL_SALES_VOLY1 NUMBER(32), Z_ANNUAL_SALES_VOLY2 NUMBER(32), Z_ANNUAL_SALES_VOLY3 NUMBER(32), Z_ANN_TOV_CALC NUMBER(32, 6), Z_ANN_VOL NUMBER(32), Z_AUT_ITEM_NUMBER VARCHAR2(60), Z_BALANCE_ATTACK_KEY VARCHAR2(255), Z_BOOKED_BUSINESS_KEY VARCHAR2(255), Z_COMMENT VARCHAR2(1200), Z_COMPOUND_KEY VARCHAR2(255), Z_CONGLOMERATE VARCHAR2(90), Z_CUSTOMER_NAME_IN VARCHAR2(150), Z_CUST_CLASSIFICAT VARCHAR2(90), Z_CUST_NAME_NEW VARCHAR2(60), Z_CUST_PART_NO VARCHAR2(120), Z_ENG_ACTION_REQUEST VARCHAR2(4000), Z_FIRST_SUPPLY_DATE DATE, Z_GOODSCOST NUMBER(32, 6), Z_MULTIPART NUMBER(32), Z_PARTS_APPLICATION_KEY VARCHAR2(255), Z_PRJ_ESTCOMPLDATE DATE, Z_PROJECT_TYPE_KEY VARCHAR2(255), Z_PROPOSAL_DATE_INDI DATE, Z_TF_ACTIVITIES_NEXT VARCHAR2(4000), Z_TF_ASSUMP_CONS_RI VARCHAR2(4000), Z_TF_BDG_CMT VARCHAR2(4000), Z_TF_BDG_PL NUMBER(32, 6), Z_TF_BDG_PL_COM VARCHAR2(4000), Z_TF_BUSINESS_MGR_CAPTION VARCHAR2(2100), Z_TF_BUSINESS_MGR_KEY NUMBER(32), Z_TF_CUST_DRWG_NO VARCHAR2(120), Z_TF_EXE_SUMM VARCHAR2(4000), Z_TF_FEB VARCHAR2(4000), Z_TF_FLEX_COMMENTS VARCHAR2(690), Z_TF_HIGH_LOW VARCHAR2(4000), Z_TF_IMPL_COMMENTS VARCHAR2(690), Z_TF_JAN VARCHAR2(4000), Z_TF_KEY_LEARN VARCHAR2(4000), Z_TF_OBJ_COMMENTS VARCHAR2(690), Z_TF_PRJ_BOUNDARIS VARCHAR2(4000), Z_TF_PRJ_DELIV VARCHAR2(4000), Z_TF_PRJ_HEALTH_STAT_KEY VARCHAR2(255), Z_TF_PRJ_LVL_KEY VARCHAR2(255), Z_TF_PRJ_OBJECTIVE VARCHAR2(4000), Z_TF_PRJ_PHASE_KEY VARCHAR2(255), Z_TF_PRJ_STATUS_KEY VARCHAR2(255), Z_TF_PROD_GRP_KEY VARCHAR2(255), Z_TF_PROJ_SPONS_CAPTION VARCHAR2(2100), Z_TF_PROJ_SPONS_KEY NUMBER(32), Z_TF_STAKEHOLDER_AD VARCHAR2(4000), Z_TF_TIM_PL NUMBER(32, 6), Z_TF_TSS_DRWG_NO VARCHAR2(120), Z_TF_TSS_PART_NO VARCHAR2(600), Z_TSS_QUOTATION_NO VARCHAR2(90), dw_updated_date date);
    TYPE ARRAY IS TABLE OF DIM_REC;
    l_data ARRAY;
    l_cursor SYS_REFCURSOR;
    BEGIN
    CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_INV_PROJECT','SAVE_DROP');
    V_SQL_TEXT := 'select AS_OF_DATE, OBJ_STAKEHOLDER1_CAPTION, BUSINESS_OWNER_KEY, ESTIMATING_FACTOR_1, ESTIMATING_FACTOR_2, EV_CALC_METHOD_KEY, EV_PERIOD_ID_CAPTION, EV_RPT_PERIOD_KEY, OBJ_STAKEHOLDER3_CAPTION, FINANCE_GOVERNANCE_KEY, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, INVESTMENT_KEY, IS_FINISH_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, IS_START_IMPOSED, OBJECTIVE, OBJ_STAKEHOLDER2_CAPTION, PROJECT_MGMT_OFFICE_KEY, RISK_FLEXIBILITY_KEY, RISK_FUNDING_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_IMPLEMENTATION_KEY, RISK_INTERDEPENDENCY_KEY, RISK_OBJECTIVES_KEY, RISK_ORG_CULTURE_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_SPONSORSHIP_KEY, RISK_SUPPORTABILITY_KEY, RISK_TECHNICAL_KEY, SPONSORED_BY, STATUS_REPORTING_KEY, Z_ACHIVED_DELIVERAB, Z_ANNUAL_SALES_VOL, Z_ANNUAL_SALES_VOLY1, Z_ANNUAL_SALES_VOLY2, Z_ANNUAL_SALES_VOLY3, Z_ANN_TOV_CALC, Z_ANN_VOL, Z_AUT_ITEM_NUMBER, Z_BALANCE_ATTACK_KEY, Z_BOOKED_BUSINESS_KEY, Z_COMMENT, Z_COMPOUND_KEY, Z_CONGLOMERATE, Z_CUSTOMER_NAME_IN, Z_CUST_CLASSIFICAT, Z_CUST_NAME_NEW, Z_CUST_PART_NO, Z_ENG_ACTION_REQUEST, Z_FIRST_SUPPLY_DATE, Z_GOODSCOST, Z_MULTIPART, Z_PARTS_APPLICATION_KEY, Z_PRJ_ESTCOMPLDATE, Z_PROJECT_TYPE_KEY, Z_PROPOSAL_DATE_INDI, Z_TF_ACTIVITIES_NEXT, Z_TF_ASSUMP_CONS_RI, Z_TF_BDG_CMT, Z_TF_BDG_PL, Z_TF_BDG_PL_COM, Z_TF_BUSINESS_MGR_CAPTION, Z_TF_BUSINESS_MGR_KEY, Z_TF_CUST_DRWG_NO, Z_TF_EXE_SUMM, Z_TF_FEB, Z_TF_FLEX_COMMENTS, Z_TF_HIGH_LOW, Z_TF_IMPL_COMMENTS, Z_TF_JAN, Z_TF_KEY_LEARN, Z_TF_OBJ_COMMENTS, Z_TF_PRJ_BOUNDARIS, Z_TF_PRJ_DELIV, Z_TF_PRJ_HEALTH_STAT_KEY, Z_TF_PRJ_LVL_KEY, Z_TF_PRJ_OBJECTIVE, Z_TF_PRJ_PHASE_KEY, Z_TF_PRJ_STATUS_KEY, Z_TF_PROD_GRP_KEY, Z_TF_PROJ_SPONS_CAPTION, Z_TF_PROJ_SPONS_KEY, Z_TF_STAKEHOLDER_AD, Z_TF_TIM_PL, Z_TF_TSS_DRWG_NO, Z_TF_TSS_PART_NO, Z_TSS_QUOTATION_NO, to_date(''2019/06/18 10:39:39'', ''yyyy/mm/dd HH24:MI:SS'') as dw_updated_date from DWH_PROJECT_V@CLARITYDBLINK A where LAST_UPDATED_DATE >= to_date(''1910/01/01 00:00:00'', ''yyyy/MM/dd HH24:mi:ss'') AND LAST_UPDATED_DATE <= (SELECT DISTINCT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS) AND A.LANGUAGE_CODE =''en''';
    OPEN l_cursor FOR V_SQL_TEXT;
    LOOP
    FETCH l_cursor BULK COLLECT INTO l_data LIMIT 50000;
    FORALL i IN 1..l_data.COUNT
    INSERT INTO DWH_INV_PROJECT (AS_OF_DATE, BUSINESS_OWNER, BUSINESS_OWNER_KEY, ESTIMATING_FACTOR_1, ESTIMATING_FACTOR_2, EV_CALC_METHOD_KEY, EV_RPT_PERIOD, EV_RPT_PERIOD_KEY, FINANCE_GOVERNANCE, FINANCE_GOVERNANCE_KEY, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, INVESTMENT_KEY, IS_FINISH_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, IS_START_IMPOSED, OBJECTIVE, PROJECT_MGMT_OFFICE, PROJECT_MGMT_OFFICE_KEY, RISK_FLEXIBILITY_KEY, RISK_FUNDING_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_IMPLEMENTATION_KEY, RISK_INTERDEPENDENCY_KEY, RISK_OBJECTIVES_KEY, RISK_ORG_CULTURE_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_SPONSORSHIP_KEY, RISK_SUPPORTABILITY_KEY, RISK_TECHNICAL_KEY, SPONSORED_BY, STATUS_REPORTING_KEY, Z_ACHIVED_DELIVERAB, Z_ANNUAL_SALES_VOL, Z_ANNUAL_SALES_VOLY1, Z_ANNUAL_SALES_VOLY2, Z_ANNUAL_SALES_VOLY3, Z_ANN_TOV_CALC, Z_ANN_VOL, Z_AUT_ITEM_NUMBER, Z_BALANCE_ATTACK_KEY, Z_BOOKED_BUSINESS_KEY, Z_COMMENT, Z_COMPOUND_KEY, Z_CONGLOMERATE, Z_CUSTOMER_NAME_IN, Z_CUST_CLASSIFICAT, Z_CUST_NAME_NEW, Z_CUST_PART_NO, Z_ENG_ACTION_REQUEST, Z_FIRST_SUPPLY_DATE, Z_GOODSCOST, Z_MULTIPART, Z_PARTS_APPLICATION_KEY, Z_PRJ_ESTCOMPLDATE, Z_PROJECT_TYPE_KEY, Z_PROPOSAL_DATE_INDI, Z_TF_ACTIVITIES_NEXT, Z_TF_ASSUMP_CONS_RI, Z_TF_BDG_CMT, Z_TF_BDG_PL, Z_TF_BDG_PL_COM, Z_TF_BUSINESS_MGR, Z_TF_BUSINESS_MGR_KEY, Z_TF_CUST_DRWG_NO, Z_TF_EXE_SUMM, Z_TF_FEB, Z_TF_FLEX_COMMENTS, Z_TF_HIGH_LOW, Z_TF_IMPL_COMMENTS, Z_TF_JAN, Z_TF_KEY_LEARN, Z_TF_OBJ_COMMENTS, Z_TF_PRJ_BOUNDARIS, Z_TF_PRJ_DELIV, Z_TF_PRJ_HEALTH_STAT_KEY, Z_TF_PRJ_LVL_KEY, Z_TF_PRJ_OBJECTIVE, Z_TF_PRJ_PHASE_KEY, Z_TF_PRJ_STATUS_KEY, Z_TF_PROD_GRP_KEY, Z_TF_PROJ_SPONS, Z_TF_PROJ_SPONS_KEY, Z_TF_STAKEHOLDER_AD, Z_TF_TIM_PL, Z_TF_TSS_DRWG_NO, Z_TF_TSS_PART_NO, Z_TSS_QUOTATION_NO, dw_updated_date) VALUES (l_data(i).AS_OF_DATE, l_data(i).OBJ_STAKEHOLDER1_CAPTION, l_data(i).BUSINESS_OWNER_KEY, l_data(i).ESTIMATING_FACTOR_1, l_data(i).ESTIMATING_FACTOR_2, l_data(i).EV_CALC_METHOD_KEY, l_data(i).EV_PERIOD_ID_CAPTION, l_data(i).EV_RPT_PERIOD_KEY, l_data(i).OBJ_STAKEHOLDER3_CAPTION, l_data(i).FINANCE_GOVERNANCE_KEY, l_data(i).IDEA_PRJ_CATEGORY_KEY, l_data(i).IDEA_PRJ_TYPE_KEY, l_data(i).INVESTMENT_KEY, l_data(i).IS_FINISH_IMPOSED, l_data(i).IS_PROGRAM, l_data(i).IS_REQ_APPROVAL_REQUIRED, l_data(i).IS_START_IMPOSED, l_data(i).OBJECTIVE, l_data(i).OBJ_STAKEHOLDER2_CAPTION, l_data(i).PROJECT_MGMT_OFFICE_KEY, l_data(i).RISK_FLEXIBILITY_KEY, l_data(i).RISK_FUNDING_KEY, l_data(i).RISK_HUMAN_INTERFACE_KEY, l_data(i).RISK_IMPLEMENTATION_KEY, l_data(i).RISK_INTERDEPENDENCY_KEY, l_data(i).RISK_OBJECTIVES_KEY, l_data(i).RISK_ORG_CULTURE_KEY, l_data(i).RISK_RESOURCE_AVAIL_KEY, l_data(i).RISK_SPONSORSHIP_KEY, l_data(i).RISK_SUPPORTABILITY_KEY, l_data(i).RISK_TECHNICAL_KEY, l_data(i).SPONSORED_BY, l_data(i).STATUS_REPORTING_KEY, l_data(i).Z_ACHIVED_DELIVERAB, l_data(i).Z_ANNUAL_SALES_VOL, l_data(i).Z_ANNUAL_SALES_VOLY1, l_data(i).Z_ANNUAL_SALES_VOLY2, l_data(i).Z_ANNUAL_SALES_VOLY3, l_data(i).Z_ANN_TOV_CALC, l_data(i).Z_ANN_VOL, l_data(i).Z_AUT_ITEM_NUMBER, l_data(i).Z_BALANCE_ATTACK_KEY, l_data(i).Z_BOOKED_BUSINESS_KEY, l_data(i).Z_COMMENT, l_data(i).Z_COMPOUND_KEY, l_data(i).Z_CONGLOMERATE, l_data(i).Z_CUSTOMER_NAME_IN, l_data(i).Z_CUST_CLASSIFICAT, l_data(i).Z_CUST_NAME_NEW, l_data(i).Z_CUST_PART_NO, l_data(i).Z_ENG_ACTION_REQUEST, l_data(i).Z_FIRST_SUPPLY_DATE, l_data(i).Z_GOODSCOST, l_data(i).Z_MULTIPART, l_data(i).Z_PARTS_APPLICATION_KEY, l_data(i).Z_PRJ_ESTCOMPLDATE, l_data(i).Z_PROJECT_TYPE_KEY, l_data(i).Z_PROPOSAL_DATE_INDI, l_data(i).Z_TF_ACTIVITIES_NEXT, l_data(i).Z_TF_ASSUMP_CONS_RI, l_data(i).Z_TF_BDG_CMT, l_data(i).Z_TF_BDG_PL, l_data(i).Z_TF_BDG_PL_COM, l_data(i).Z_TF_BUSINESS_MGR_CAPTION, l_data(i).Z_TF_BUSINESS_MGR_KEY, l_data(i).Z_TF_CUST_DRWG_NO, l_data(i).Z_TF_EXE_SUMM, l_data(i).Z_TF_FEB, l_data(i).Z_TF_FLEX_COMMENTS, l_data(i).Z_TF_HIGH_LOW, l_data(i).Z_TF_IMPL_COMMENTS, l_data(i).Z_TF_JAN, l_data(i).Z_TF_KEY_LEARN, l_data(i).Z_TF_OBJ_COMMENTS, l_data(i).Z_TF_PRJ_BOUNDARIS, l_data(i).Z_TF_PRJ_DELIV, l_data(i).Z_TF_PRJ_HEALTH_STAT_KEY, l_data(i).Z_TF_PRJ_LVL_KEY, l_data(i).Z_TF_PRJ_OBJECTIVE, l_data(i).Z_TF_PRJ_PHASE_KEY, l_data(i).Z_TF_PRJ_STATUS_KEY, l_data(i).Z_TF_PROD_GRP_KEY, l_data(i).Z_TF_PROJ_SPONS_CAPTION, l_data(i).Z_TF_PROJ_SPONS_KEY, l_data(i).Z_TF_STAKEHOLDER_AD, l_data(i).Z_TF_TIM_PL, l_data(i).Z_TF_TSS_DRWG_NO, l_data(i).Z_TF_TSS_PART_NO, l_data(i).Z_TSS_QUOTATION_NO, l_data(i).dw_updated_date);
    COMMIT;
    EXIT WHEN l_cursor%NOTFOUND;
    END LOOP;
    CLOSE l_cursor;
    CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_INV_PROJECT','CREATE');
    EXCEPTION
    WHEN OTHERS THEN
    BEGIN
    V_SQLCODE := SQLCODE;
    V_SQLERRM := SQLERRM;
    V_SQL_TEXT := 'INSERT INTO DWH_INV_PROJECT (AS_OF_DATE, BUSINESS_OWNER, BUSINESS_OWNER_KEY, ESTIMATING_FACTOR_1, ESTIMATING_FACTOR_2, EV_CALC_METHOD_KEY, EV_RPT_PERIOD, EV_RPT_PERIOD_KEY, FINANCE_GOVERNANCE, FINANCE_GOVERNANCE_KEY, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, INVESTMENT_KEY, IS_FINISH_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, IS_START_IMPOSED, OBJECTIVE, PROJECT_MGMT_OFFICE, PROJECT_MGMT_OFFICE_KEY, RISK_FLEXIBILITY_KEY, RISK_FUNDING_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_IMPLEMENTATION_KEY, RISK_INTERDEPENDENCY_KEY, RISK_OBJECTIVES_KEY, RISK_ORG_CULTURE_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_SPONSORSHIP_KEY, RISK_SUPPORTABILITY_KEY, RISK_TECHNICAL_KEY, SPONSORED_BY, STATUS_REPORTING_KEY, Z_ACHIVED_DELIVERAB, Z_ANNUAL_SALES_VOL, Z_ANNUAL_SALES_VOLY1, Z_ANNUAL_SALES_VOLY2, Z_ANNUAL_SALES_VOLY3, Z_ANN_TOV_CALC, Z_ANN_VOL, Z_AUT_ITEM_NUMBER, Z_BALANCE_ATTACK_KEY, Z_BOOKED_BUSINESS_KEY, Z_COMMENT, Z_COMPOUND_KEY, Z_CONGLOMERATE, Z_CUSTOMER_NAME_IN, Z_CUST_CLASSIFICAT, Z_CUST_NAME_NEW, Z_CUST_PART_NO, Z_ENG_ACTION_REQUEST, Z_FIRST_SUPPLY_DATE, Z_GOODSCOST, Z_MULTIPART, Z_PARTS_APPLICATION_KEY, Z_PRJ_ESTCOMPLDATE, Z_PROJECT_TYPE_KEY, Z_PROPOSAL_DATE_INDI, Z_TF_ACTIVITIES_NEXT, Z_TF_ASSUMP_CONS_RI, Z_TF_BDG_CMT, Z_TF_BDG_PL, Z_TF_BDG_PL_COM, Z_TF_BUSINESS_MGR, Z_TF_BUSINESS_MGR_KEY, Z_TF_CUST_DRWG_NO, Z_TF_EXE_SUMM, Z_TF_FEB, Z_TF_FLEX_COMMENTS, Z_TF_HIGH_LOW, Z_TF_IMPL_COMMENTS, Z_TF_JAN, Z_TF_KEY_LEARN, Z_TF_OBJ_COMMENTS, Z_TF_PRJ_BOUNDARIS, Z_TF_PRJ_DELIV, Z_TF_PRJ_HEALTH_STAT_KEY, Z_TF_PRJ_LVL_KEY, Z_TF_PRJ_OBJECTIVE, Z_TF_PRJ_PHASE_KEY, Z_TF_PRJ_STATUS_KEY, Z_TF_PROD_GRP_KEY, Z_TF_PROJ_SPONS, Z_TF_PROJ_SPONS_KEY, Z_TF_STAKEHOLDER_AD, Z_TF_TIM_PL, Z_TF_TSS_DRWG_NO, Z_TF_TSS_PART_NO, Z_TSS_QUOTATION_NO, dw_updated_date) VALUES (l_data(i).AS_OF_DATE, l_data(i).OBJ_STAKEHOLDER1_CAPTION, l_data(i).BUSINESS_OWNER_KEY, l_data(i).ESTIMATING_FACTOR_1, l_data(i).ESTIMATING_FACTOR_2, l_data(i).EV_CALC_METHOD_KEY, l_data(i).EV_PERIOD_ID_CAPTION, l_data(i).EV_RPT_PERIOD_KEY, l_data(i).OBJ_STAKEHOLDER3_CAPTION, l_data(i).FINANCE_GOVERNANCE_KEY, l_data(i).IDEA_PRJ_CATEGORY_KEY, l_data(i).IDEA_PRJ_TYPE_KEY, l_data(i).INVESTMENT_KEY, l_data(i).IS_FINISH_IMPOSED, l_data(i).IS_PROGRAM, l_data(i).IS_REQ_APPROVAL_REQUIRED, l_data(i).IS_START_IMPOSED, l_data(i).OBJECTIVE, l_data(i).OBJ_STAKEHOLDER2_CAPTION, l_data(i).PROJECT_MGMT_OFFICE_KEY, l_data(i).RISK_FLEXIBILITY_KEY, l_data(i).RISK_FUNDING_KEY, l_data(i).RISK_HUMAN_INTERFACE_KEY, l_data(i).RISK_IMPLEMENTATION_KEY, l_data(i).RISK_INTERDEPENDENCY_KEY, l_data(i).RISK_OBJECTIVES_KEY, l_data(i).RISK_ORG_CULTURE_KEY, l_data(i).RISK_RESOURCE_AVAIL_KEY, l_data(i).RISK_SPONSORSHIP_KEY, l_data(i).RISK_SUPPORTABILITY_KEY, l_data(i).RISK_TECHNICAL_KEY, l_data(i).SPONSORED_BY, l_data(i).STATUS_REPORTING_KEY, l_data(i).Z_ACHIVED_DELIVERAB, l_data(i).Z_ANNUAL_SALES_VOL, l_data(i).Z_ANNUAL_SALES_VOLY1, l_data(i).Z_ANNUAL_SALES_VOLY2, l_data(i).Z_ANNUAL_SALES_VOLY3, l_data(i).Z_ANN_TOV_CALC, l_data(i).Z_ANN_VOL, l_data(i).Z_AUT_ITEM_NUMBER, l_data(i).Z_BALANCE_ATTACK_KEY, l_data(i).Z_BOOKED_BUSINESS_KEY, l_data(i).Z_COMMENT, l_data(i).Z_COMPOUND_KEY, l_data(i).Z_CONGLOMERATE, l_data(i).Z_CUSTOMER_NAME_IN, l_data(i).Z_CUST_CLASSIFICAT, l_data(i).Z_CUST_NAME_NEW, l_data(i).Z_CUST_PART_NO, l_data(i).Z_ENG_ACTION_REQUEST, l_data(i).Z_FIRST_SUPPLY_DATE, l_data(i).Z_GOODSCOST, l_data(i).Z_MULTIPART, l_data(i).Z_PARTS_APPLICATION_KEY, l_data(i).Z_PRJ_ESTCOMPLDATE, l_data(i).Z_PROJECT_TYPE_KEY, l_data(i).Z_PROPOSAL_DATE_INDI, l_data(i).Z_TF_ACTIVITIES_NEXT, l_data(i).Z_TF_ASSUMP_CONS_RI, l_data(i).Z_TF_BDG_CMT, l_data(i).Z_TF_BDG_PL, l_data(i).Z_TF_BDG_PL_COM, l_data(i).Z_TF_BUSINESS_MGR_CAPTION, l_data(i).Z_TF_BUSINESS_MGR_KEY, l_data(i).Z_TF_CUST_DRWG_NO, l_data(i).Z_TF_EXE_SUMM, l_data(i).Z_TF_FEB, l_data(i).Z_TF_FLEX_COMMENTS, l_data(i).Z_TF_HIGH_LOW, l_data(i).Z_TF_IMPL_COMMENTS, l_data(i).Z_TF_JAN, l_data(i).Z_TF_KEY_LEARN, l_data(i).Z_TF_OBJ_COMMENTS, l_data(i).Z_TF_PRJ_BOUNDARIS, l_data(i).Z_TF_PRJ_DELIV, l_data(i).Z_TF_PRJ_HEALTH_STAT_KEY, l_data(i).Z_TF_PRJ_LVL_KEY, l_data(i).Z_TF_PRJ_OBJECTIVE, l_data(i).Z_TF_PRJ_PHASE_KEY, l_data(i).Z_TF_PRJ_STATUS_KEY, l_data(i).Z_TF_PROD_GRP_KEY, l_data(i).Z_TF_PROJ_SPONS_CAPTION, l_data(i).Z_TF_PROJ_SPONS_KEY, l_data(i).Z_TF_STAKEHOLDER_AD, l_data(i).Z_TF_TIM_PL, l_data(i).Z_TF_TSS_DRWG_NO, l_data(i).Z_TF_TSS_PART_NO, l_data(i).Z_TSS_QUOTATION_NO, l_data(i).dw_updated_date)';
    ROLLBACK;
    INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES('DWH_INV_PROJECT',V_SQLERRM,V_SQLCODE,REPLACE(V_SQL_TEXT,'**',''''));
    COMMIT;
    RAISE_APPLICATION_ERROR(-20100,'ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ' || V_SQLERRM);
    END;

    END;

    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ORA-00904: "Z_TF_TIM_PL": invalid identifier
    ORA-06512: at line 31


    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Full Insert-orcl] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [clarity - isOracle? 2] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Delete all records] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [DWH_DIM_FULL_LD_MAPPING] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [is Full Refresh?] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [no Records got Updated?] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [DWH_DIM_HAS_RECORDS] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Use DataPump?] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Full Load(O)?] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [set variables - oracle] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [clarity - isOracle?] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [DWH_DIM_FIELD_MAPPING] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Finished job entry [Pre execution] (result=[false])
    INFO 2019-06-18 10:39:41,211 [Thread-39175] dwh.event DWH_DIM_LOAD - Job execution finished
    ERROR 2019-06-18 10:39:41,212 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - Unexpected error
    ERROR 2019-06-18 10:39:41,230 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_PROJECT


  • 5.  RE: Create Jaspersoft Job failing

    Posted 06-18-2019 05:20 AM
    Hi Suman,

    DHW job is still failing after I executed the command.

    Here is the log from bg-dwh.log




  • 6.  RE: Create Jaspersoft Job failing

    Posted 06-18-2019 05:40 AM
    Those commands were not given to fix DWH. The error is below
    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ORA-00904: "Z_TF_TIM_PL": invalid identifier

    So it looks like you have custom attribute, go to that attribute and uncheck Include in DWH and run the full load



    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 7.  RE: Create Jaspersoft Job failing

    Posted 06-18-2019 07:50 AM
    Hi Suman,

    That attribute is not part of 'Include in the Data warehouse".

    Regards,
    Divya


  • 8.  RE: Create Jaspersoft Job failing

    Posted 06-18-2019 11:22 AM
    Did you refresh clarity as well as data ware house both or it was just one?

    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 9.  RE: Create Jaspersoft Job failing

    Posted 06-19-2019 01:22 AM
    Hi Suman,

    It was only Clarity refresh.

    Regards,
    Divya


  • 10.  RE: Create Jaspersoft Job failing

    Posted 06-19-2019 06:02 AM
    Its advisable to refresh both clarity as well as DWH schema. Have a look at the technical document https://ca-broadcom.wolkenservicedesk.com/kb/ca-ppm-when-to-rebuild-the-data-warehouse-dwh-database/kb000004413

    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------



  • 11.  RE: Create Jaspersoft Job failing

    Posted 06-19-2019 08:06 AM
    Hi Suman,

    I checked the DB versions for  niku and dwh. Everything seems to be fine.

    So if I ask our DB Administrator to refresh DWH similar to clarity DB, would that work?

    Regards,
    Divya