Load Data warehouse job failure for Investment object attributes on MSSQL DB

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

We tried adding only one String attribute and we are getting error now running the Load Data Warehouse (DWH) job in Clarity PPM

Excerpt of error from DWH job log: 

dwh_db_check_ - An error occurred executing this job entry: Couldn't execute SQL: MERGE INTO DWH_IN_INVESTMENT  USING 
( 

User Defined Java Class - java.lang.Runtime.Exception: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_INVESTMENT at Process 

dwh_etl_intereface_dim - Errors detected 

In app-ca.logs: 
A SQL exception occurred when creating view ODF_IDEA_V. The view will not be recreated. ...[SQLServer]CREATE VIEW failed because column 'attributeID' in view 'odf_temp_view11111111111111' exceeds the maximum of 1024"columns (repeated for multiple columns in project and idea object.

• Note: You can see this on other investment types including: ODF_PROJECT_V

Environment

Release:
Component: PPMPRD

Cause

This is due to a limitation in the MSSQL database, 1024 is the database limit on the number of columns a database view can have. 

Resolution

Inactivate some of the custom attributes from the view so the total number of attributes (OOTB + Custom) doesn't exceed 1024. We recommend up to 100 custom attributes per object.

• Tip for On Premise: To see which object these attributes are on you can query the ODF_CUSTOM_ATTRIBUTES table.
• For On Demand/On Premise: Here's how to verify if you have exceeded the number of columns allowed:

1. Enable PPM Action trace using the steps in KB KB000009353 
2. Open the SQL trace file and search by the views that couldn't be created. 

• Example: There is a failure creating view ODF_PROJECT_V and the logs show 'odf_temp_view11111111111111', so search by 11111111111111. 

        3. You will find row: create view odf_temp_view11111111111111 as select ... 
        4.  Get the SELECT statement from the trace. 
        5.  Run the query in SQL server management studio. It can take a while to get the results (in the past case it took around 3 minutes to get the results). 
        6.  Copy 1 row of the result with headers and paste in excel file. 
        7.  Remove all data except headers. 
        8.  Click on the row to hi-light the headers and click Copied / Pasted Special and Pasted as Transpose so the columns turned into rows.
        9.  Check to see if there are more then 1024 rows. If yes, because the total number of attributes (OOTB + Custom) on Project Object (which also inherits other abstract object attributes like 'investment', 'financial') is exceeding 1024, the view can not be created.