Clarity

If using data from Datamart Rollup Tables, you may not get the same amount of data records unless all 5 of the Datamart Time Slices have the same 'MAX' date.
Both Datamart jobs use Time Slices 1,2,3,10,11. It determines data based on each slice definition. We recommend that for consistency between all datamart tables, that the minimum start date be at least 3 full months prior to the current month and the 'maximum' date is the SAME for all 5 slices. To determine the 'maximum' date, use the slice From Date and add the number of periods value (number of days).


/*
GET MAX DATE OF THE 5 SLICE DEFINITIONS
*/
SELECT MAX(FROM_DATE + NUM_PERIODS)
FROM PRJ_BLB_SLICEREQUESTS
WHERE ID IN (1,2,3,10,11)

/*
SHOW DETAILS OF EACH SLICE DEFINITION
WITH A CALCULATED END DATE AND COMPARE
*/
SELECT ID,
REQUEST_NAME,

FROM_DATE,
NUM_PERIODS,

(FROM_DATE + NUM_PERIODS) END_DATE
FROM PRJ_BLB_SLICEREQUESTS
WHERE ID IN (1,2,3,10,11)


If the dates need adjustment, go to Administration > Time Slices and update the slice definitions.
If multiple slices need to be changed, do one at a time, let the Time Slicing job catch up and proceed to the next slice definiton.

For more details on how Time Slices and Datamart interact, refer to additional knowledge articles on the Answer Bar

Thanks for sharing this useful bit, Kathryn :happy

Quick question - I ran the below query

/*
SHOW DETAILS OF EACH SLICE DEFINITION
WITH A CALCULATED END DATE AND COMPARE
*/
SELECT ID,
REQUEST_NAME,
FROM_DATE,
NUM_PERIODS,
(FROM_DATE + NUM_PERIODS) END_DATE
FROM PRJ_BLB_SLICEREQUESTS
WHERE ID IN (1,2,3,10,11)

Below is the output

ID Request_Name From_Date Num_Periods End_Date
1
DAILYRESOURCEAVAILCURVE
13-OCT-08
1825
12-OCT-13
2
DAILYRESOURCEACTCURVE
13-OCT-08
1825
12-OCT-13
3
DAILYRESOURCEESTCURVE
13-OCT-08
1825
12-OCT-13
10
DAILYRESOURCEALLOCCURVE
13-JUN-12
735
18-JUN-14
11
DAILYRESOURCEBASECURVE
13-OCT-08
1825
12-OCT-13

You mentioned -
"If the dates need adjustment, go to Administration > Time Slices and update the slice definitions.
If multiple slices need to be changed, do one at a time, let the Time Slicing job catch up and proceed to the next slice definiton."

The end_date for the row in bold is different. Is this something we should look into (changing the slice definition) ?

NJ

If you are using the data from the Datamart in reporting and you are looking at data that extends beyond 12-OCT-2013, you may see that not all data is there. So it really depends on how you use the data.
Based on the data you have currently configured, the maximum date will be for 18-JUN-2014, for the slice data in the Datamart tables.
The job will attempt to fill in data for all 5 slices up to that maximum date, but if all 5 slices do not have data up to that date, it won't be in the tables.
Changing the slices to all have the same maximum date will help to ensure that if you want to compare and use the data in the Datamart tables, data from all 5 slices will be present in the tables.

Thanks Kathryn :smile

NJ

Thanks for the tip Kathryn! :grin:

Another important dependency on these time slices is the 'Resource Finder' functionality.
The Resource Finder page depends on the date ranges of the DAILYRESOURCEAVAILCURVE (Slice #1) and the DAILYRESOURCEALLOCCURVE (Slice #10)
When the user has a date range defined in the filter section, the application uses slice data for both 'Availability' and 'Allocations' to 'find' a resource.
If the DAILYRESOURCEAVAILCURVE is not in the same date range as the DAILYRESOURCEALLOCCURVE date range, the resource finder will not generate any results.

Thanks for sharing this, Kathryn :happy

NJ