Load DWH error: "Invalid column name" on custom field (SQL Server)

book

Article ID: 111744

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Running the Load Data Warehouse - Full on Clarity with SQL server fails with the below error message:
 
2018/08/18 22:04:58 - User Defined Java Class.0 - 2018/08/18 22:04:58 - Oracle? - [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid column name 'TEST_FIELD'.

Cause

This usually happens due to a mismatch in the PPM and DWH databases on a custom field. If you check the object referenced by the bg-dwh.log, you will be able to find a custom field "TEST_FIELD" in the object attributes

Environment

Release: Any
Database: SQL Server

Resolution

  1. In Clarity go to Objects -> <OBJECT NAME> -> Attributes and uncheck TEST_FIELD from being included in Data Warehouse. 
    Unchecking the field forces the DWH_<OBJECT>_V to be rebuilt and it should no longer throw the above error message
  2. Run a Full Load of the DWH job
  3. Once it runs fine, we recommend enabling the field again out of peak hours and see if it goes through fine to the DWH database.