Clarity PPM

Expand all | Collapse all

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

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

    Posted 01-12-2015 12:03 PM

    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.



  • 2.  Re: Clarity Tuesday Tip: How to check the Datamart (Datamart Extraction and Datamart Rollup jobs)

    Posted 01-13-2015 12:14 AM

    Thanks for sharing this useful info, Tuan

     

    NJ



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

    Posted 08-13-2015 09:39 AM

    Should Update Earned Values run BEFORE Datamart Extraction job? Or does it not make a difference?



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

    Posted 08-13-2015 11:12 AM

    Check this -

     

    Clarity Tuesday Tip: How should the main Clarity jobs be scheduled?

    https://communities.ca.com/message/241722838#241722838

     

     

     

    NJ