Clarity

Expand all | Collapse all

CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

  • 1.  CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 01:11 PM

    Hello all,

                   

    We are trying to investigate an error that we are seeing on our CA-PPM instance. The "Load Data warehouse" ETL job is creating an error which we believe could be related to the maximum number of columns a database view (1024 for our SQL Server DB - and view ODF_PROJECT_V) being exceeded.

                   

    We are looking for anybody who is using CA-PPM 14.3, who has an instance with at least 400 custom attributes on it - and for whom this job is working OK. It does not matter how many of these attributes are flagged for inclusion in the DWH.

     

    If your CA-PPM instance matches this pattern then it would be useful for us to know roughly how many custom attributes you do have.

     

    Any response would be very gratefully received.

     

    Phil Smythe



  • 2.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 01:47 PM

    Hi Philip,

     

    This is not a PPM limitation rather a Database limitations see the below link

    https://msdn.microsoft.com/en-us/library/ms143432.aspx

     

    Important! While CA Clarity PPM supports up to 500 custom attributes per object, CA strongly advises against assigning more than 100 custom attributes to a single stock or custom object. High numbers of custom attributes for an object can cause performance issues and can cause subsequent upgrades to fail. If you need a large number of custom attributes, consider creating and using a subobject rather than assigning a large number of custom attributes to a single object.

     

    So the best way is to break down the attributes into multiple sub objects rather putting all in one object.

     

    Regards

    Suman Pramanik



  • 3.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 01:59 PM

    Hi Philip,

     

    As Suman said above, this is a database limitation for the maximum number of columns, that's why we recommend ensuring the number of columns you create is smaller.

     

    However ODF_PROJECT_V is not used in Data Warehouse, and will not fail the Data Warehouse job if it's not compiled. Please can you paste the exact error message you are facing from bg-dwh log.

     

    Also, please go to  Objects - Project - Attributes and try unchecking/checking any custom attribute for Data Warehouse. Then please go to app-ca log and provide all the entries for the same time/minute.

     

    Based on this we will be able to see what exactly causes the job to fail.

    Thanks -Nika



  • 4.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 03:40 PM

    Say you have this high number of custom attributes and are in an earlier version with BO, that is kinda hard requirement to reduce the number of attributes just like that to 100 or less.

    It would be a major redesign and would require some time for analysis, testing, development of a revised design, migration and further testing.

     

    As the initial post it ain't any better if the attributes already hit the fan. It is a show stopper.

     

    Is there any option like modifying the DWH structure so that all the attributes are not put into the same table and making each attribute to use only one column in the view under any circumstances.



  • 5.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 03:53 PM

    Hi Martti,

     

    As I said earlier this is not a PPM limitation and database, so if the data is available in PPM, you can still build reports using PPM schema.

     

    Regards

    Suman Pramanik



  • 6.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 03:58 PM

    Hi Martti :

     

    At this point we don't think this is the issue that failed the Load Data Warehouse job. ODF_PROJECT_V is not used for this job, so it might be a completely different issue. We need customer logs to look through.

     

    Thanks -Nika



  • 7.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-04-2016 04:15 PM

    Thanks   SumanPramanik that is and option for custom reports which would be mostly the case when you talk about consider custom attributes.

    Just wondering what kind of hours are you working in Hyperabad to answer this quickly.

     

    Thanks for your input Nika_Hadzhikidi  yes it is always possible that the conclusion is not  correct, hopefully it is not as there are more implementations where there are more than 100 custom attributes.

    If this is the same case as in

    https://www.linkedin.com/groups/2076955/2076955-6122046051928076289

    the lads seem to have already digged into it



  • 8.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-05-2016 03:11 AM

    Many thanks for your replies so far on this. The issue I am talking about is the same one mentioned by Paul at the LinkedIn URL above - and it is also the subject of a CA Support call. As Paul explains in that link, we are seeing an error when trying to add an additional attribute for inclusion in the DWH. The error seen in the log file tells us that the view ODF_PROJECT_V can't be rebuilt as it has exceeded the maximum number of columns (1024 for SQL server).

     

    Once we have reached the limit in terms of DWH selected attributes, any run attempt of the job "Load Data Warehouse" gives us the error:-

    Couldn't execute SQL: MERGE INTO DWH_INV_PROJECT TGT

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid column name 'ZZ_IBS_BFA7'.

     

    Looking at the contents of ODF_PROJECT_V, it would appear that the number of columns in the view rises in proportion to the number of attributes on the project object. For an OOTB 14.3 instance there are 230 project object attributes - and 483 columns in the table. The number of columns in the table then increases according to the type of each new custom attribute added - for example each Boolean attribute adds 5 view columns - others create just one or two. Any DWH-synced attribute that is added to the tables in INV_INVESTMENT, INV_PROJECT, FIN_FINANCIALS, PAC_MNT_PROJECTS, ODF_CA_PROJECT or ODF_CA_INV - seems to get added  as columns on this view. If this assumption is correct then - as Suman points out -  this would seem to indicate that there is only room to add in about 100 custom attributes on the project and/or investment objects before we hit problems. That is only about a 10th of the number of custom attributes which we could have prior to v 14 and could be a major architectural problem to anybody upgrading such a system.

     

    It may be, as Nika points out, that the ODF_PROJECT_V view does not directly affect the Load Data Warehouse job - but it would seem that it could have secondary effects once a given number of synced project/investment attributes have been created.

     

    So my question here is all about trying to disprove our theory by looking for anybody who was in a similar position to us - but who had not ran into problems. Still waiting for any replies on that score.

     



  • 9.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-05-2016 09:46 AM

    Hi Philip

     

    Thanks for clarifying. On your last question:

    I personally have a sandbox with 475 custom enabled attributes on Project Object and have no issue to run Load Data Warehouse job.


    So whilst we are recommending to limit the custom attributes to 100, going over that limit is not failing the Load Data Warehouse job.

     

    The amount of attributes can certainly affect the job, but I would not think that ODF_PROJECT_V is related. From your description and error message it looks more like your DWH_PROJECT_V does not get created/compiled properly. This view will only contain the project attributes, not the investment ones.

    To troubleshoot this would you mind raising a Support case : and upload your bg-dwh logs. Also please go Objects - Project - Attributes and try unchecking/checking any custom attribute for Data Warehouse, and provide your app-ca.log  for review.

     

    Many thanks -Nika



  • 10.  Re: CA-PPM 14.3 users with at least 400 custom attributes. Is DWH job working OK?

    Posted 04-06-2016 04:31 AM

    Thanks for your helpful reply, Nika.

    We have done some further analysis, as you requested, and uploaded the details to case "00343571: Problems with the creation of DWH mapped attributes". If you had a chance to look at the results then that would be very useful. There are a lot of details we have provided in the analysis and in the attached logs.

     

    Once we have a solution, I will update here for the benefit of the community.

     

    regards

     

    Phil