How do I track down data discrepancies in Actuals, Estimates or Allocation?

Document ID : KB000027253
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue:

Often customers see issues with reports and portlet data. This data can be pulled from a number of sources. You'll need to know the source data for custom portlets, but the method below will help track down data discrepancies in Actuals, Estimates or Allocation.

Resolution:

In order to investigate data discrepancies issues, we need a complete picture of a few examples of the problem. We're trying to get an understanding of the nature of the problem. Getting a complete picture of a few examples will help determine the general problem. If you suspect that data is not in the time slices or the Datamart tables and should be, we will need all the related information about the records.

For this analysis, we need to focus a resource, project and time period combination. For example, we are trying to figure out why the hours for "Resource A" on "Project X" do not show correctly on a report for the 1st week of January 2015. If there are multiple records that show a discrepancy, they will commonly have the same issue causing the discrepancy; therefore we can focus on a few specific examples to find a solution. To do this analysis we need to follow the path the data would flow through our system. An example is: Hours on a Timesheet, Posted actuals in Open Workbench (OWB) or Microsoft Project (MSP). These hours are sliced by the Time Slicing job or by internal time slicing activities. Time slice hours are pulled into the Datamart.  Thus you need to ensure the Background Services are up and running to ensure all BLOB data is sliced.

For the suspect records, you will need a screen shot of the resource's timesheet. This will show you what that actuals should be. Note that if time was entered through voucher Transaction Entry, XOG financial transactions or directly in OWB or MSP, then there will not be a corresponding timesheet.

Next, take a screen shot of the resource's actuals, or estimates if your issue is concerning ETC, in Workbench or MSP. This is used to compare the actual hours in workbench to the actual hours on the timesheet. In Workbench or MSP you'll need to create a Daily Timescaled view showing actuals filtering only for the resource in question. If you need assistance in creating a timescale view, please see product documentation or an experienced project manager. Please manually compare the Actual Hours on the timesheet with the Actuals the Show in Workbench. If you see any discrepancies between the actuals on the timesheet and the actuals in Workbench/MSP please let CA Technical Support know, and provide the specific examples.

If the actual data in the timesheet and Workbench match, you will need to run slice record queries and the assignment record query below. Save the results in an XLS spreadsheet in case the results need to be sent to CA Technical Support. Timeslice pulls data from the binary curve data that Workbench and MSP use. The slice query will show if workbench matches the timeslice data. And the Assignment query will supply the supporting details about the assignment record such as status flags, start and finish dates and when it was last modified. For all 3 of these queries you'll need to supply the Resource Unique Name and Project Unique Name. Please note that the slice queries depend on the slice request id's for daily actuals and estimates that were installed with the product. Please manually review the output of the slice data queries by comparing them to what you see in Workbench. If they match there is no issue with Timeslicing. If they do not match, please insure that Time Slicing job is running properly by checking the Last Run Date on the Time Slices management page. If this date is not current, please reschedule your reoccurring timeslice job. In addition, please review the bg-ca.log files for errors. If you see any errors concerning blobcrack, please check the knowledgebase on the CA Technical Support web site for published articles to assist in resolving common error messages.

Useful Queries

Slice records for Actual Hours

Select Slice, slice_date, r.full_name, t.prname Task_name, p.name project from prj_blb_slices s, prassignment a, prtask t, srm_resources r, srm_projects p where s.prj_object_id = a.prid and a.prtaskid = t.prid and t.prprojectid = p.id and a.prresourceid = r.id and p.unique_name like 'PROJECT UNIQUE NAME HERE' and r.unique_name like 'RESOURCE UNIQUE NAME' and slice_request_id = 2

Slice records for Task Estimates

Select Slice, slice_date, r.full_name, t.prname Task_name, p.name project from prj_blb_slices s, prassignment a, prtask t, srm_resources r, srm_projects p where s.prj_object_id = a.prid and a.prtaskid = t.prid and t.prprojectid = p.id and a.prresourceid = r.id and p.unique_name like 'PROJECT UNIQUE NAME HERE' and r.unique_name like 'RESOURCE UNIQUE NAME' and slice_request_id = 3

Slice records for Task Assignments

Select r.full_name, t.prname Task_name, p.name project, a.* from prassignment a, prtask t, srm_resources r, srm_projects p where a.prtaskid = t.prid and t.prprojectid = p.id and a.prresourceid = r.id and p.unique_name like 'PROJECT UNIQUE NAME HERE' and r.unique_name like 'RESOURCE UNIQUE NAME'

If you are concerned with verifying your allocation data for timeslice, the query below will pull the daily records for one project and resource combination. This data can be compared with the resource's availability to a specific project in Workbench or MSP.

Slice records for Team Allocations

Select Slice, slice_date, r.full_name, p.name project from prj_blb_slices s, prteam t, srm_resources r, srm_projects p where s.prj_object_id = t.prid and t.prprojectid = p.id and t.prresourceid = r.id and p.unique_name like 'PROJECT UNIQUE NAME HERE' and r.unique_name like 'RESOURCE UNIQUE NAME' and slice_request_id = 10

Many of our reports pull their data from the Datamart. The query below will pull the core data from the datamart table and show us if the datamart matches timeslice. Once again you'll need to supply the Resource Unique name and Project Unique name. You can compare the output of the query below to the output of the timeslice queries. Please note that due to the fact timeslice and datamart are scheduled jobs, the data may be out of sync until the jobs are rerun.  Reference the CA PPM Solution Pack documentation for associated time slice ids of stock reports.

Datamart records for Actuals, ETC and Allocations

Select ACTUAL_QTY, ETC_QTY, ALLOCATED_QTY, fact_date, r.full_name, t.prname Task_name, p.name project from NBI_PRT_FACTS DM, srm_resources r, srm_projects p, prtask t where dm.project_id = p.id and dm.resource_id = r.id and dm.task_id = t.prid and p.unique_name like 'PROJECT UNIQUE NAME HERE' and r.unique_name like 'RESOURCE UNIQUE NAME'

 

If you are sending any information to CA Support for analysis, please also send copies of the entire background log file (bg-ca.log).