Overlapping rows in rate table

Document ID : KB000094133
Last Modified Date : 02/05/2018
Show Technical Document Details
Issue:
We're getting a Duplicate Dimension Data error due to two rate records getting pulled when only one matches the criteria in the Rate Matrix. 

Portlets are showing duplicate dimensions and it is difficult to determine which record contains the correct rate.
 
Environment:
PAS 14.3 and newer
Oracle and SQL Server
Cause:
In 14.3 changes were made to the Rate Matrix Extraction job to improve performance.

There is now additional java code which is used to extract the correct rate/date.  Now there is not a 1 to 1 correlation between the rate matrix and the nbi_proj_res_rates_and_facts table and the additional java code is used to extract the correct rate.

The changes now produce overlapping rows in the nbi_proj_res_rates_and_costs table.

You will now see a row covering the whole date range of the matrix  plus rows for the specific project and/or task dates.

 
Resolution:
Any custom queries should use   MAX from_date when multiple matching rows are returned/matched for a given input date.


Here is an example:

Consider the following rate matrix rows
 
1. 1/1/2017 - 2/28/2017 - 10$
2. 3/1/2017 - 3/31/2017 - 20$
3. 4/1/2017 - 12/31/2017 - 10$
 
When the rate matrix extraction job is run this data would get consolidated into 2 rows in nbi_proj_res_rates_and_costs as below
 
1. 1/1/2017 - 12/31/2017 - 10$
2. 3/1/2017 - 3/31/3017 - 20$
 
This is an optimization that the RME job does when storing the data in nbi_proj_res_rates_and_costs table.
 
When CA PPM is using the nbi_proj_res_rates_and_costs table to fetch the rates, it doesn't directly join with the nbi_proj_res_rates_and_costs to get a unique row with the required rate. CA PPM also would get both the rows from nbi_proj_res_rates_and_costs and this data is converted to a NkCurve in java which would handle the rate shifts when consolidated and create a curve with 3 segments with the following data
 
1. 1/1/2017 - 2/28/2017 - 10$
2. 3/1/2017 - 3/31/2017 - 20$
3. 4/1/2017 - 12/31/2017 - 10$
 
The consolidation of nbi_proj_res_rates_and_costs rows into single curve is handling the rate shifts and getting the non-overlapping segments with correct rates. Then the application code in java would fetch the correct rate from the matching segment.
 
 
However, this curve consolidation is not possible when directly joining with the nbi_proj_res_rates_and_costs table which is causing the duplicate dimensions in NSQL portlets.
 
Resolution Explanation : Use the MAX from_date when multiple matching rows are returned/matched for a given input date.
 
Always use the row with MAX from_date among the possible candidate rate rows. i.e. if the query fetching the rows is returning 2 records now as below,
 
1. 1/1/2017 - 12/31/2017 - 10$
2. 3/1/2017 - 3/31/3017 - 20$
 
then modifying the query to fetch the row with MAX from_date would only fetch
 
3/1/2017 - 3/31/3017 - 20$
 
The point to remember here is that when creating the Rate Matrices from UI, we  do not allow overlapping rows. The data in nbi_proj_res_rates_and_costs is not a one-to-one mapping between the rate matrix rows but a consolidated/optimized way of storing.



To summarize:

There is now additional java code which is used to extract the correct rate/date.
You should be able to use  MAX from_date for the rows to get the rates needed.


 
Additional Information:
https://communities.ca.com/thread/241779198-nbiprojresratesandcosts-gives-results-with-overlapping-dates