AnsweredAssumed Answered

Is your Load DWH job failing due to too many custom project attributes?

Question asked by philip_smythe on May 12, 2017

I am wondering if there are any other people - especially those with SQL-Server CA PPM instances - who are seeing the same behaviour described. If you are it would be interesting to hear from you.


THE PATTERN:  In Studio, when a custom attribute is newly selected for inclusion in the data warehouse - the "Load Data Warehouse"  ETL job fails with an error which points to a problem with the view "ODF_PROJECT_V". The error relates to this view having too many columns in it. The error mentions a particular attribute name (possibly the one just selected) as being the immediate cause of the overflow. If the attribute is de-selected for DWH then the job will run again.


APPARENT CAUSE: Each time a custom attribute is created on either the project or investment objects, it seems that CA PPM adds some columns to this view. The number of columns added depends on the type of attribute added (numeric lookups add 6 columns, Booleans result in 5 columns being added, money and string lookup attributes add 2 columns and other types add 1). SQL Server has a view column limit of 1,024. After this limit is reached then the error appears for any new column which is flagged for DWH inclusion. (Despite Oracle having a more stringent 1,000 column limit, we have not seen this error pattern with Oracle instances).


Because the number of custom attributes necessary to trigger the issue varies according to their type - the issue could be triggered on instances which have as few as 170 custom attributes on their project object. Adding any more than this - without careful calculations as to their type - is playing with fire.


OFFICIAL PATCHES AND WORKAROUNDS: CA released a partial fix from version 14.3 patch 9. The fix means that attributes on the project object do not cause an error when flagged - even if the column limit in view ODF_PROJECT_V is exceeded. However we have noticed that the problem still presents itself of attributes which belong to the Investment object (the investment object itself does not need to have a high number of custom attributes - the error is generated when the project object has many customisation). CA have not yet provided a fix for this use case. The official fix is to start either deleting or moving custom attributes to other objects - but this can be a major architectural task.



For the record, the current fix we use when we wish to flag a new attribute for DWH inclusion on the investment object is as follows.:-

1. Drop the PPM_DWH database
2. Re-create it from the OOTB instance that came with the current CA PPM install.
3. Apply the more recent CA PPM patch to the application server - 14.3-9 or later.
4. Run the Jaspersoft update command so as to update the JS domains


 - This will allow the ETL job to run properly with the new attribute - but it is a time consuming process to run.


I would be very interested to hear of the experiences of others in this regard.