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 database2. 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.
Thanks a lot for sharing your experience with the community, this is very helpful and detailed post, very well researched and explained.
Just as note for everyone reading this - we at Support do not recommend the amount of custom attributes on one object to go beyond 100 as best practice. If you need to have more than that, our recommendation would be to create a separate sub-object. This is the way we have been suggesting to customers to work with CA PPM for better usability. Since there is a MSSQL limitation we cannot do much but actually reducing the attributes as workaround. Having too many can not only affect DWH but also the way the object views are compiled. (May make your ODF_PROJECT_V view invalid).
We have seen the issue more often with MSSQL but it also exists on Oracle. It does seem to be less frequent though.
The only other thing that can be done - you could raise an idea here, something like this: "Add an additional Object view to support a large amount of custom attributes" - i.e. have a second object view such as ODF_PROJECT_V2, V3 etc. Product Management will review and see if that's something they want to do.
Thank you -Nika