TuanTran

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

Discussion created by TuanTran Employee on Jul 8, 2015

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.

Outcomes