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

[Tuan Tran]

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

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.

[navzjoshi00]

Thanks for sharing this useful info, Tuan

 

Regards

NJ

[System]

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

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.

[wteixeira]

Thanks for sharing this Tuan.

[Colum Creed]

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?

[Kathryn Ellis]

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.