As the datamart tables provide data to portlets and customer extract data from the datamart tables, it is critical that the Datamart is up-to-date.
The folllowing time slice ids feed the datamart:
id=1 DAILYRESOURCEAVAILCURVE Availability - hours per day that a resource is available to work on a projectPRJ_BLB_SLICES.prj_object_id = PRJ_RESOURCES.prid id=2 DAILYRESOURCEACTCURVEActuals - hourly time posted for an assignment on a projectPRJ_BLB_SLICES.prj_object_id = PRASSIGNMENT.prid
id=3 DAILYRESOURCEESTCURVEEstimates - hourly estimate for an assignment on a projectPRJ_BLB_SLICES.prj_object_id = PRASSIGNMENT.prid
id=10 DAILYRESOURCEALLOCCURVEAllocation - percentage time that a resource is allocated to a projectPRJ_BLB_SLICES.prj_object_id = PRTEAM.prid id=11 DAILYRESOURCEBASECURVEBaseline (in hours) for a project assignmentPRJ_BLB_SLICES.prj_object_id =PRASSIGNMENT.prid
Be sure the daily resource time slice ids are within the requested date ranges. The recommendation is
http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec435572.aspxHow should I configure my timeslices in Clarity
If the data is the daily time slices, it will be in the datamart.
To monitor the overall work the Datamart Extraction (DME) has to process, run the following query:
select status, count(1)
group by status
To monitor the tables populated by the DME and DMR, the following general query can be used:
select '(01 DME) NBI_DIM_OBS', COUNT(*) FROM NBI_DIM_OBS union
select '(02 DME) NBI_DIM_FISCAL_TIME', COUNT(*) from NBI_DIM_FISCAL_TIME union
select '(03 DME) NBI_DIM_CALENDAR_TIME', COUNT(*) FROM NBI_DIM_CALENDAR_TIME UNION
select '(04 DME) NBI_DIM_OBS_FLAT', COUNT(*) FROM NBI_DIM_OBS_FLAT UNION
select '(05 DME) NBI_PRT_FACTS', COUNT(*) from NBI_PRT_FACTS UNION
select '(06 DME) NBI_PROJECT_CURRENT_FACTS', COUNT(*) FROM NBI_PROJECT_CURRENT_FACTS union
select '(07 DME) NBI_R_FACTS', count(*) from nbi_r_facts union
select '(08 DMR) NBI_ROLLUP_SQL', COUNT(*) FROM NBI_ROLLUP_SQL UNION
select '(09 DMR) NBI_PM_PT_FACTS', COUNT(*) FROM NBI_PM_PT_FACTS UNION
select '(10 DMR) NBI_FM_PT_FACTS', COUNT(*) FROM NBI_FM_PT_FACTS UNION
select '(11 DMR) NBI_RT_FACTS', count(*) from NBI_RT_FACTS union
select '(12 DMR) NBI_PM_PROJECT_TIME_SUMMARY', count(*) from NBI_PM_PROJECT_TIME_SUMMARY union
select '(13 DMR) NBI_FM_PROJECT_TIME_SUMMARY', count(*) from NBI_FM_PROJECT_TIME_SUMMARY union
select '(14 DMR) NBI_RESOURCE_TIME_SUMMARY', count(*) from NBI_RESOURCE_TIME_SUMMARY
If there is any issue with the aforementioned tables being populated, please open a CA PPM issue.
Thanks for sharing this useful info, Tuan
Should Update Earned Values run BEFORE Datamart Extraction job? Or does it not make a difference?
Check this -
Clarity Tuesday Tip: How should the main Clarity jobs be scheduled?