Kathryn_Ellis

CA Clarity Tuesday Tip: How Timeslices impact Datamart Data

Discussion created by Kathryn_Ellis Employee on Jun 20, 2013
Latest reply on Jun 18, 2018 by CandiceWong
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

Outcomes