Clarity PPM1

Expand all | Collapse all

CA PPM Tuesday Tip: How to Set Up the DWH (Data Warehouse) time slices to avoid errors with the 'Update Report Tables' and 'Load DWH' jobs

  • 1.  CA PPM Tuesday Tip: How to Set Up the DWH (Data Warehouse) time slices to avoid errors with the 'Update Report Tables' and 'Load DWH' jobs

    Posted 07-08-2015 05:16 PM

    When setting up the DWH time slices from the
    Administration > Data Adminstration > Time Slices page, please ensure that the start day of the slice ID matches that of the
    Administration > Project Management > Settings > General > First Day of Work Week

    first day of work week.JPG

    Use the following queries to quickly find out what the days are set to:
    --a
    select sr.id, sr.request_name, TO_CHAR(SR.FROM_DATE, 'MM-DD-YYYY HH24:MI:SS AM Dy')
    from PRJ_BLB_SLICEREQUESTS SR
    where SR.IS_DWH_REQUEST = 1
    and SR.PERIOD = 1 --weekly
       order by SR.id

     

    --b
    select prweekstart from PRSITE
    0 - Sunday
    1 - Monday
    2 - Tuesday
    3 - Wednesday
    4 - Thursday
    5 - Friday
    6 - Saturday


    If the starting day does not match,
    reslice the DWH request to start on a day consistent with the
    Administration > Project Management > Settings > General > First Day of Work Week


    The following error can occur:

    Couldn't execute SQL: BEGIN
    RPT_CALENDAR_SP();
    RPT_INV_HIERARCHY_SP();
    END;

    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "RPT_CALENDAR_SP", line 255
    ORA-06512: at line 2

    1. Pause the Time slicing job, only if it is in the Waiting or Scheduled state.

     

    2. Go to Administration > Data Administration > Time Slices page 
    and modify all the Weekly DWH time slice IDs to conform to the
    Administration > Project Management > Settings > General > First Day of Work Week

    A tip (from my colleague wilel06 Liz Williamson) is to sort all the weekly DWH time slices by ID.
    Then open each ID link in a new tab.
    Modify the from date to an agreed date that has the same day of the week.

     

     

     

     

    3. Run the following jobs:
    -Time Slicing - either resume the paused recurring instance or an immediate run and allow it to complete.
    -Update Report Tables job (all parameters checkmarked)
    -Load DWH (full parameter checkmarked)

     


    If the problem still persists,set the monthly DWH slice ids to the 1st of the month.



  • 2.  Re: CA PPM Tuesday Tip: How to Set Up the weekly DWH (Data Warehouse) time slices

    Posted 07-09-2015 12:51 AM

    Thanks for sharing this useful info, Tuan

     

    Regards

    NJ



  • 3.  Re: CA PPM Tuesday Tip: How to Set Up the weekly DWH (Data Warehouse) time slices

    Posted 07-09-2015 11:20 AM

    Thanks for the tip Tuan!

    TUAN TRAN wrote:

     

    When setting up the DWH time slices from the
    Administration > Data Adminstration > Time Slices page, please ensure that the start day of the slice ID matches that of the
    Administration > Project Management > Settings > General > First Day of Work Week

    first day of work week.JPG

    Use the following queries to quickly find out what the days are set to:
    --a
    select sr.id, sr.request_name, TO_CHAR(SR.FROM_DATE, 'MM-DD-YYYY HH24:MI:SS AM Dy')
    from PRJ_BLB_SLICEREQUESTS SR
    where SR.IS_DWH_REQUEST = 1
    and SR.PERIOD = 1 --weekly
       order by SR.id

     

    --b
    select prweekstart from PRSITE
    0 - Sunday
    1 - Monday
    2 - Tuesday
    3 - Wednesday
    4 - Thursday
    5 - Friday
    6 - Saturday

     

    If the starting day does not match,
    reslice the DWH request to start on a day consistent with the first workday of the week.

    Run the Time Slicing job.
    Run the Load Data Warehouse (LDW) job.



  • 4.  Re: CA PPM Tuesday Tip: How to Set Up the weekly DWH (Data Warehouse) time slices

    Posted 07-13-2015 11:04 AM

    Thanks for sharing this Tuan.



  • 5.  Re: CA PPM Tuesday Tip: How to Set Up the weekly DWH (Data Warehouse) time slices

    Posted 07-21-2015 12:23 PM

    A couple of comments

    1) I tried running the query but IS_DWH_REQUEST does not exist in PRJ_BLB_SLICEREQUESTS (Clarity v13.3 anyway)

     

    2) Is it ok to change the First Day of Work Week in Administration > Project Management > Settings > General if Monday is not the start of your work week?



  • 6.  Re: CA PPM Tuesday Tip: How to Set Up the weekly DWH (Data Warehouse) time slices

    Posted 07-21-2015 05:20 PM

    Hi Colum

     

    #1 - Data Warehouse is in v14.2 ; not 13.3

    #2 - The information about the work week is telling you to change the start date of your slice definition , not the start date of your work week.  Therefore, if your company work week starts on Sunday, instead of Monday, change the SLICE definitions to start on Sunday.