search cancel

Load DWH Job Fails After Upgrade to 16.0.3 (MSSQL)

book

Article ID: 254473

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

After a successful upgrade to 16.0.3, an initial run of the full Load DWH cannot complete and fails within seconds.

 

STEPS TO REPRODUCE:

1. Restore stock dwh_mssql_base.db

2. Ensure CSA > Data Warehouse connection is available

3. Run Load DWH full job.

 

Actual: Job fails with BG DWH error:

 

ERROR 2022-11-23 09:25:28,439 [ETL_JOB_META_LOAD UUID: a47dadd6-da28-43fb-8e91-6eee9c5e12e7] dwh.event (none:none:none:none) META_LOAD_TABS_SQL - An error occurred executing this job entry : 
Couldn't execute SQL: MERGE INTO DWH_META_TABLES G USING
(
SELECT
  
  
  CASE WHEN UPPER(A.DWH_TABLE_NAME) = 'DWH_LKP_TRANSLATION' THEN 'COP_PORTLET_QUERY_TRANSLATIONS' ELSE B.LOOKUP_TYPE END STATIC_LOOKUP_TYPE,
  UPPER(A.SRC_TABLE_NAME) SRC_TABLE_NAME
, UPPER(A.SRC_KEY_COLUMN) SRC_KEY_COLUMN
, UPPER(A.SRC_DISPLAY_COLUMN) SRC_DISPLAY_COLUMN
, UPPER(A.SRC_DATE_COLUMN) SRC_DATE_COLUMN
, UPPER(A.DWH_TABLE_NAME) DWH_TABLE_NAME
, UPPER(A.DWH_KEY_COLUMN) DWH_KEY_COLUMN
, UPPER(A.DWH_DISPLAY_COLUMN) DWH_DISPLAY_COLUMN
, UPPER(A.DWH_DATE_COLUMN) DWH_DATE_COLUMN
, A.IS_LOOKUP
, A.IS_FACT
, UPPER(A.DWH_DIM_TABLE_NAME) DWH_DIM_TABLE_NAME
, A.MULTI_LANG
, A.LOOKUP_SRC_TYPE LOOKUP_SRC_TYPE
, UPPER(A.DWH_AUDIT_DATE_REF) DWH_AUDIT_DATE_REF
, IS_DELETED DROP_FLAG
, CASE WHEN UPPER(A.DWH_TABLE_NAME) LIKE UPPER('%_LN') THEN 1 ELSE 0 END AS IS_EXTEND
, A.HAS_EXTENSION_TABLE
, A.USE_CREATED_DATE
, A.SRC_DATE_CREATED_COLUMN
, A.DWH_DATE_CREATED_COLUMN
, A.PROCESS_ORDER
FROM 
 [PPMDBLINK].niku.niku.DWH_META_TABLES A LEFT OUTER JOIN
  (SELECT DISTINCT UPPER(LKP_DIM_TABLE_NAME) LKP_DIM_TABLE_NAME, MAX(LOOKUP_TYPE) OVER (PARTITION BY LKP_DIM_TABLE_NAME) LOOKUP_TYPE FROM [PPMDBLINK].niku.niku.DWH_META_COLUMNS
  WHERE LKP_DIM_TABLE_NAME IS NOT NULL) B
ON UPPER(A.DWH_TABLE_NAME) = B.LKP_DIM_TABLE_NAME
)H
ON (G.DWH_TABLE = H.DWH_TABLE_NAME AND G.SRC_TABLE = H.SRC_TABLE_NAME)
WHEN MATCHED THEN UPDATE SET 
        G.IS_LOOKUP = H.IS_LOOKUP,G.IS_FACT = H.IS_FACT,G.DWH_DIM_TABLE_NAME = H.DWH_DIM_TABLE_NAME,G.MULTI_LANG = H.MULTI_LANG,G.DROP_FLAG = H.DROP_FLAG,G.DWH_KEY_COL = H.DWH_KEY_COLUMN,G.DWH_DISPLAY_COL = H.DWH_DISPLAY_COLUMN,
    G.DWH_DATE_COL = H.DWH_DATE_COLUMN,G.SRC_KEY_COL = H.SRC_KEY_COLUMN,G.SRC_DISPLAY_COL = H.SRC_DISPLAY_COLUMN,G.SRC_DATE_COL = H.SRC_DATE_COLUMN,
        G.LOOKUP_TYPE = H.LOOKUP_SRC_TYPE,G.DWH_AUDIT_DATE = H.DWH_AUDIT_DATE_REF,G.STATIC_LOOKUP_TYPE = H.STATIC_LOOKUP_TYPE,G.IS_EXTEND = H.IS_EXTEND, G.HAS_EXTENSION_TABLE = H.HAS_EXTENSION_TABLE,
        G.USE_CREATED_DATE = H.USE_CREATED_DATE, G.SRC_DATE_CREATED_COLUMN = H.SRC_DATE_CREATED_COLUMN, G.DWH_DATE_CREATED_COLUMN = H.DWH_DATE_CREATED_COLUMN,G.PROCESS_ORDER = H.PROCESS_ORDER
WHEN NOT MATCHED THEN INSERT
 (IS_LOOKUP,IS_FACT,DWH_DIM_TABLE_NAME,MULTI_LANG,DROP_FLAG,DWH_KEY_COL,DWH_DISPLAY_COL,DWH_DATE_COL,SRC_KEY_COL,SRC_DISPLAY_COL,SRC_DATE_COL,LOOKUP_TYPE,DWH_AUDIT_DATE,STATIC_LOOKUP_TYPE,IS_EXTEND,
 DWH_TABLE,SRC_TABLE,HAS_EXTENSION_TABLE,USE_CREATED_DATE, SRC_DATE_CREATED_COLUMN, DWH_DATE_CREATED_COLUMN, PROCESS_ORDER)
VALUES 
 (H.IS_LOOKUP,H.IS_FACT,H.DWH_DIM_TABLE_NAME,H.MULTI_LANG,H.DROP_FLAG,H.DWH_KEY_COLUMN,H.DWH_DISPLAY_COLUMN,H.DWH_DATE_COLUMN,H.SRC_KEY_COLUMN,H.SRC_DISPLAY_COLUMN,H.SRC_DATE_COLUMN,H.LOOKUP_SRC_TYPE,
  H.DWH_AUDIT_DATE_REF,H.STATIC_LOOKUP_TYPE,H.IS_EXTEND,H.DWH_TABLE_NAME,H.SRC_TABLE_NAME,H.HAS_EXTENSION_TABLE,H.USE_CREATED_DATE, H.SRC_DATE_CREATED_COLUMN, H.DWH_DATE_CREATED_COLUMN,H.PROCESS_ORDER);

Invalid column name 'USE_CREATED_DATE'.

 

Expected: The job should complete.

 

Environment

Release : 16.0.3

Resolution

In one use case, the restored PPM_DWH db were missing column(s) on the table: DWH_META_TABLES

      ,[PROCESS_ORDER]
      ,[SRC_DATE_CREATED_COLUMN]
      ,[DWH_DATE_CREATED_COLUMN]
      ,[USE_CREATED_DATE]

Request the file to restore from the Support team.

 

Additional Information

Have the DBA

1. Connect as the 'sa'  user to perform user mapping for db user(ex:ClarityAdmin) to ppm_dwh

2. Ran commands:

GRANT ALTER ANY LOGIN TO ex:ClarityAdmin;

GRANT ALTER ANY LINKED SERVER TO ex:ClarityAdmin;

grant view server state to ex:ClarityAdmin;