Clarity

We are running Load Data Warehouse job for the first time in 14.3 environment, but, the job is failing with below errors :

User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_FIN_TRANSACTION      at Processor.processRow(Processor.java:64)      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) User Defined Java Class - Unexpected error
dwh_etl_interface_dim - Errors detected!
dwh_db_check_ - An error occurred executing this job entry :
Couldn't execute SQL: DECLARE
 TYPE DIM_REC IS RECORD (BATCH_NUMBER NUMBER(32), CHARGE_CODE VARCHAR2(96), CHARGE_CODE_KEY INTEGER, CLARITY_CREATED_DATE DATE, CLARITY_TRANSACTION_KEY NUMBER(32), LAST_UPDATED_DATE DATE, COMPANY_KEY INTEGER, COMPANY_NAME VARCHAR2(128), COST_TYPE_KEY VARCHAR2(30), DOCUMENT_TYPE_KEY NUMBER(32), ENTITY VARCHAR2(120), ENTITY_KEY INTEGER, EXTERNAL_BATCH_ID VARCHAR2(20), EXTERNAL_TRANSACTION_ID NUMBER(32), INCURRED_BY VARCHAR2(300), INCURRED_BY_KEY INTEGER, INPUT_TYPE_CODE VARCHAR2(96), INPUT_TYPE_CODE_KEY NUMBER(32), INVESTMENT_CLASS VARCHAR2(90), INVESTMENT_CLASS_KEY INTEGER, INVESTMENT_DEPARTMENT VARCHAR2(360), INVESTMENT_DEPARTMENT_KEY INTEGER, INVESTMENT_ID VARCHAR2(60), INVESTMENT_KEY INTEGER, INVESTMENT_LOCATION VARCHAR2(360), INVESTMENT_LOCATION_KEY INTEGER, INVESTMENT_NAME VARCHAR2(240), INVOICE_NUMBER VARCHAR2(16), NOTES VARCHAR2(255), PO_NUMBER VARCHAR2(30), RESOURCE_CLASS VARCHAR2(360), RESOURCE_CLASS_KEY INT

Cant see any related error in bg-ca logs.

The errors in details will be at bg-dwh.logs

Regards

Suman Pramanik 

Hi Suman,

I am not sure, but bg-dwh is of 0 KB

Log gets appeneded every 5MB so you should look at bg-dwh 1

I am attaching bg-dwh logs, the errors are similar to what is shown in job->logs

There are bunch of errors 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

2017/11/02 09:25:52 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01653: unable to extend table PPM_DWH.DWH_FIN_TRANSACTION by 8192 in tablespace DWH_PPM_DATA_DIM

I guess you need to sort of the DB issues before you run the DWH job 

Yes, but ORA12514 is old error 11/02, and this is already resolved  after setting global_names = false

Hi Piyush

This is the latest error:

2017/11/03 02:27:40 - User Defined Java Class.0 - 2017/11/02 09:25:52 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01653: unable to extend table PPM_DWH.DWH_FIN_TRANSACTION by 8192 in tablespace DWH_PPM_DATA_DIM

This will not be affected by setting the global names, please ask your DBA to extend your tablespace DWH_PPM_DATA_DIM and try again. They could add a new file and set the tablespace to autoextend.

Hope this helps -Nika

Hi,

This is the latest error we are getting -

dwh_db_check_ - An error occurred executing this job entry :  Couldn't execute SQL: DECLARE      TYPE DIM_REC IS RECORD (ACTUALS_THRU_DATE DATE, BASE_FINISH_DATE DATE, BASE_START_DATE DATE, BOOKING_STATUS_KEY NUMBER(32), CLARITY_CREATED_DATE DATE, CLARITY_TEAM_KEY NUMBER(30), CLARITY_UPDATED_DATE DATE, FINISH_DATE DATE, HAS_REQUISITION NUMBER(32), INVESTMENT_KEY NUMBER(32), IS_OPEN_FOR_TE NUMBER(32), IS_ROLE NUMBER(32), OPT_DEPT_DIM_TOT NUMBER(32, 6), OPT_DIM_BLEND_RATE NUMBER(32, 6), REQUEST_STATUS_KEY NUMBER(32), REQUIREMENT_NAME VARCHAR2(300), RESOURCE_ID VARCHAR2(60), RESOURCE_KEY NUMBER(32), PRRESOURCEID_CAPTION VARCHAR2(2100), ROLE_ID VARCHAR2(96), ROLE_KEY NUMBER(32), PRROLEID_CAPTION VARCHAR2(2100), STAFF_OBS_TYPE VARCHAR2(80), RSF_OBSUNIT_ID_CAPTION VARCHAR2(2100), STAFF_OBS_UNIT_KEY NUMBER(32), START_DATE DATE, TEAM_KEY NUMBER(30), dw_updated_date date);      TYPE ARRAY IS TABLE OF DIM_REC;      l_data ARRAY;      l_cursor SYS_REFCURSOR;      V_SQL_TEXT VARCHAR2(32000);  BEGIN            CMN_SAVEDROP_CREATE_INDEXES_S
User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_TEAM      at Processor.processRow(Processor.java:64)      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)
dwh_etl_interface_dim - Errors detected!

Based on the error, there is probably a duplicate key for the DWH_INV_TEAM table. Can you run the query and check 

select team_key, count(team_key) from dwh_inv_team
group by team_key
having count(team_key) > 1

Yes I can some entries in the table, do we need to delete the duplicate ones?

I guess you need to open a support case, we need to find out why duplicates are coming. May be some issues with baseline data. 

Thanks Suman, I will follow up with CA for duplicate issue.

Thanks for understanding, without looking at data its very tough to suggest any delete statements and hence requested you for case. 

Hi,

We were able to resolve above issues, but, dwh job still failing with below error:

Could not login to Jaspersoft, domains could not get updated for customizations.

Please note, we do not want to do Jasper set up for reporting, but, why system is forcing for Jasper Soft set up ?

Is there a way to skip it ?

The load dataware house at the end updates the domains provided out of box dynamically. But that can be ignored if you don't want to integrate Japersoft and you will have your data populated in DWH schema.

Is there a way to ignore the integration ?

Nope, unless you go ahead and customize the DWH job.