DWH_INV_TEAM_PER_FACTS_V alloc_cost discrepancies

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

DWH_INV_TEAM_PER_FACTS_V will return some records with alloc_hours >0 and alloc_cost =0

We will not be able to change the view code-wise as the expectation is for some projects not to use allocation costs but still use hours so it is ok if the view is showing 0 if no cost is expected in multiple use cases.

 

Cause

Root cause of view showing 0s for Allocation cost when there is Allocation hours.

1. May be caused by Time Slices not correctly covering the same periods. 
2. Secondly, Rate/Cost not set on project level. For Open Financially Projects in the system that do not have Labor Matrix set up on the project in Settings – there is no matching cost/rate in NBI_PROJ_RES_RATES_AND_COSTS and cost slices are not updated because of this for the specific project/team/resource

Resolution

For Time Slicing discrepancies

1. Reset all the DWH slices to follow best practices and cover the same exact periods. Do not have Cost for 1 years and Allocation Hours for 3 years
2. After this, the Time Slicing job will run for some time to recalculate everything – recommend to do this after hours

For Rate Matrix issues:

1. Find a discrepant record within the correct coverage
2. Query the Rates table with the team_id to see if you are able to see costs covering that period for -1.

select * from NBI_PROJ_RES_RATES_AND_COSTS where project_id =<xxxx> and team_id =<xxxx> and task_id ='-1' ---Replace the IDs accordingly

There would not be a record – which indicates there is no rate therefore there cannot be a cost calculated

3. Open the project in UI – in Settings add a Labor Matrix, Save
4. Run Full RME job (first 3 options selected)
5. Run Time Slicing job
6. Run Update EV Costs Job
7. Run Time Slicing again
8. Once this is done, the costs will start displaying correctly in DWH_INV_TEAM_PER_FACTS_V

 

You can use this query to determine projects that are Open and not having matrix set up at project level:

select count(*) from pac_mnt_projects where status ='O' and (transratesourcelabor is null or transcostsourcelabor is null)

This would have to be filled for each, and then you have to run the RME job which will take some time. You can then run the steps 5-8 and it should fix the remaining discrepancies.

You can then use the below query to be able to identify any discrepancies, this would account for rates that are NOT in the table.

SELECT
    c.period_start_date,
    c.period_key,
    c.period_type_key,
    t.team_key
    --COUNT(t.team_key) as team_count
FROM
    DWH_INV_TEAM_PER_FACTS_V t
JOIN
    DWH_CMN_PERIOD_V c ON c.period_key = t.period_key
JOIN PRTEAM p ON p.prid = t.team_key   
WHERE
    t.alloc_hours > 0
    AND t.alloc_cost = 0
    AND c.period_start_date > TO_DATE('01-JAN-2020','dd-mm-yyyy') ----REPLACE THE DATE ACCORDINGLY
    AND c.period_end_date < TO_DATE('01-JAN-2030','dd-mm-yyyy') ----REPLACE THE DATE ACCORDINGLY
    AND NOT EXISTS (
        SELECT 1
        FROM NBI_PROJ_RES_RATES_AND_COSTS n
        WHERE n.team_id = t.team_key
        AND n.from_date <= c.period_start_date
        AND n.to_date > c.period_start_date
        AND p.prresourceid = n.resource_id
        and n.task_id ='-1'
    )
GROUP BY
    c.period_start_date,
    c.period_key,
    c.period_type_key,
    t.team_key
ORDER BY
    c.period_start_date;