Dimension Load to Reporting Layer Failed Due to Dimension Name Greater Than 100 (Doc ID 2981726.1)

Last updated on DECEMBER 27, 2023

Applies to:

Symptoms

Dimension load batch to reporting layer failed at SCD task. In this case, Batch BT-L2-DIM batch for GL_ACCOUNT dimension load executed via Scheduler failed with below error.

 
Error:

Error executing query : INSERT INTO DIM_CS_GL_ACCOUNT ( DIM_CS_GL_ACCOUNT.LANGUAGE_ID,DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_ID,DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_CD,DIM_CS_GL_ACCOUNT.COMMON_COA_ID,DIM_CS_GL_ACCOUNT.COMMON_COA_CD,DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_ID,DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_CD,DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_ID,DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_CD,DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_ID,DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_CD,DIM_CS_GL_ACCOUNT.GL_ACCOUNT_SK,DIM_CS_GL_ACCOUNT.EFFECTIVE_DATE,DIM_CS_GL_ACCOUNT.GL_ACCOUNT_CD,DIM_CS_GL_ACCOUNT.GL_ACCOUNT_NAME,DIM_CS_GL_ACCOUNT.DESCRIPTION,DIM_CS_GL_ACCOUNT.LEAF_ONLY_FLAG,DIM_CS_GL_ACCOUNT.ENABLED_FLAG,DIM_CS_GL_ACCOUNT.GL_ACCOUNT_ID,DIM_CS_GL_ACCOUNT.DISPLAY_ORDER_NUM,DIM_CS_GL_ACCOUNT.RECORD_INDICATOR,DIM_CS_GL_ACCOUNT.RECORD_START_DATE,DIM_CS_GL_ACCOUNT.RECORD_END_DATE,DIM_CS_GL_ACCOUNT.LANGUAGE_CD) SELECT M.LANGUAGE_ID,M.ACCRUAL_BASIS_ID,M.ACCRUAL_BASIS_CD,M.COMMON_COA_ID,M.COMMON_COA_CD,M.ROLLUP_SIGNAGE_ID,M.ROLLUP_SIGNAGE_CD,M.ACCOUNT_TYPE_ID,M.ACCOUNT_TYPE_CD,M.RECONCILIATION_PRODUCT_ID,M.RECONCILIATION_PRODUCT_CD,SEQ_GL_ACCOUNT.nextval,M.EFFECTIVE_DATE,M.GL_ACCOUNT_CD,M.GL_ACCOUNT_NAME,M.DESCRIPTION,M.LEAF_ONLY_FLAG,M.ENABLED_FLAG,M.GL_ACCOUNT_ID,M.DISPLAY_ORDER_NUM,'Y','13-OCT-2023 00:00:00','31-DEC-99',M.LANGUAGE_CD FROM ( SELECT DIM_CS_GL_ACCOUNT.ROWID ROW_ID,VW_DIM_CS_GL_ACCOUNT.LANGUAGE_ID,VW_DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_ID,VW_DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_CD,VW_DIM_CS_GL_ACCOUNT.COMMON_COA_ID,VW_DIM_CS_GL_ACCOUNT.COMMON_COA_CD,VW_DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_ID,VW_DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_CD,VW_DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_ID,VW_DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_CD,VW_DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_ID,VW_DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_CD,VW_DIM_CS_GL_ACCOUNT.EFFECTIVE_DATE,VW_DIM_CS_GL_ACCOUNT.GL_ACCOUNT_CD,VW_DIM_CS_GL_ACCOUNT.GL_ACCOUNT_NAME,VW_DIM_CS_GL_ACCOUNT.DESCRIPTION,VW_DIM_CS_GL_ACCOUNT.LEAF_ONLY_FLAG,VW_DIM_CS_GL_ACCOUNT.ENABLED_FLAG,VW_DIM_CS_GL_ACCOUNT.GL_ACCOUNT_ID,VW_DIM_CS_GL_ACCOUNT.DISPLAY_ORDER_NUM,VW_DIM_CS_GL_ACCOUNT.LANGUAGE_CD,CASE WHEN (DIM_CS_GL_ACCOUNT.ROWID IS NULL) THEN 1 WHEN (COALESCE(DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_ID,-0.00001) != COALESCE(VW_DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_ID,-0.00001) OR COALESCE(DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_CD,'*') != COALESCE(VW_DIM_CS_GL_ACCOUNT.ACCRUAL_BASIS_CD,'*') OR COALESCE(DIM_CS_GL_ACCOUNT.COMMON_COA_ID,-0.00001) != COALESCE(VW_DIM_CS_GL_ACCOUNT.COMMON_COA_ID,-0.00001) OR COALESCE(DIM_CS_GL_ACCOUNT.COMMON_COA_CD,'*') != COALESCE(VW_DIM_CS_GL_ACCOUNT.COMMON_COA_CD,'*') OR COALESCE(DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_ID,-0.00001) != COALESCE(VW_DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_ID,-0.00001) OR COALESCE(DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_CD,'*') != COALESCE(VW_DIM_CS_GL_ACCOUNT.ROLLUP_SIGNAGE_CD,'*') OR COALESCE(DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_ID,-0.00001) != COALESCE(VW_DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_ID,-0.00001) OR COALESCE(DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_CD,'*') != COALESCE(VW_DIM_CS_GL_ACCOUNT.ACCOUNT_TYPE_CD,'*') OR COALESCE(DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_ID,-0.00001) != COALESCE(VW_DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_ID,-0.00001) OR COALESCE(DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_CD,'*') != COALESCE(VW_DIM_CS_GL_ACCOUNT.RECONCILIATION_PRODUCT_CD,'*') ) THEN 2 END AS SCD_ROW_TYPE_ID FROM VW_DIM_CS_GL_ACCOUNT LEFT JOIN DIM_CS_GL_ACCOUNT ON (DIM_CS_GL_ACCOUNT.GL_ACCOUNT_ID=VW_DIM_CS_GL_ACCOUNT.GL_ACCOUNT_ID AND DIM_CS_GL_ACCOUNT.RECORD_INDICATOR='Y') WHERE VW_DIM_CS_GL_ACCOUNT.EFFECTIVE_DATE = TO_DATE('20231013','YYYYMMDD') )M WHERE SCD_ROW_TYPE_ID = '2' OR ROW_ID IS NULL

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.