nbi_proj_res_rates_and_costs gives results with overlapping dates

[Legacy User]

I'm getting the following results when querying the table nbi_proj_res_rates_and_costs for one specific TEAM_ID's allocation (task: -1)

 

 

As you can see for the SAME team_id i get overlapping dates:

 

2016-12-19  / 2018-07-27

2017-01-01 / 2017-12-31

 

I always thought that the purpose of the Datamart table was to give you only ONE rate for one project / resource / role  for an allocation with non overlapping dates. So i would have expected 3 records with the following dates:

 

2016-12-19  / 2016-12-31

2017-01-01 / 2017-12-31

2018-01-01  / 2018-07-27

 

Is there something I missed or could this be a bug?

 

Any help appreciated,

[Nika Hadzhikidi]

Hi Anthony,

 

There is a known functionality change introduced in CA PPM 14.3, that allowed for those columns to be overlapping in nbi_proj_res_rates_and_costs table.

The reason for that is Rate Matrix Extraction performance improvement. The number of rows was purposefully reduced to bring down the processing time.

 

Aside from the actual overlapping in tables you should not see any issues in PPM UI or Jaspersoft or BO reports as this has been taken care of. The recommendation for any custom queries or jobs would be to adapt them to the new design.

 

Hope this helps -Nika

[Legacy User]

So it is a "feature. So that means that ALL queries / processes using that table should be updated from 14.3 on. That could be quite some work.

 

Also, I believe that by doing that to reduce the # of rows hence to enhance the performance of this table, this will force queries to be more complex and thus reducing the performance again.

 

But OK, nothing we can do about it. So the trick would be to look for the MAX(from_date) of records with overlapping dates.

 

Thanks for your answer.