TuanTran

CA PPM Tuesday Tip: How to check the Datamart (Datamart Extraction and Datamart Rollup jobs)

Discussion created by TuanTran Employee on Jan 12, 2015
Latest reply on Aug 13, 2015 by navzjoshi00

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 project
PRJ_BLB_SLICES.prj_object_id = PRJ_RESOURCES.prid
   
id=2  DAILYRESOURCEACTCURVE
Actuals - hourly time posted for an assignment on a project
PRJ_BLB_SLICES.prj_object_id = PRASSIGNMENT.prid

 

id=3  DAILYRESOURCEESTCURVE
Estimates - hourly estimate for an assignment on a project
PRJ_BLB_SLICES.prj_object_id = PRASSIGNMENT.prid

 

id=10 DAILYRESOURCEALLOCCURVE
Allocation - percentage time that a resource is allocated to a project
PRJ_BLB_SLICES.prj_object_id = PRTEAM.prid
  
id=11 DAILYRESOURCEBASECURVE
Baseline (in hours) for a project assignment
PRJ_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.aspx
How 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)

from nbi_events

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.

Outcomes