2. Manage Calendars and Load Cycles¶
The calendar logic defines when a delivery is expected for each TSA table. RED compares the actual loads with these load cycles to determine the object status, e.g. TSA_WAITING or TSA_MISSING.
The building blocks are:
- Calendar (
REDADM.RED_CALENDAR_DEFINITIONS) A named calendar with its holidays, e.g.
AT(Austria),DE(Germany), orCH(Switzerland).- Calendar key (
REDADM.RED_CALENDAR_KEYS) A rule within a calendar that selects days, e.g.
BD(every business day) orCD(every calendar day). The resulting days are listed inREDADM.STA_CALENDAR_DAYS.- Load cycle (
REDADM.RED_TSA_LOAD_CYCLES) Assigns a calendar key to a TSA table for a validity period.
2.1. Add a Calendar Key¶
Add a calendar key with API_REDADM.ADD_CALENDAR_KEY. The rule (p_cal_rule) is an SQL condition on the columns of the calendar base table REDADM.RED_CALENDAR_BASE:
COLUMN |
MEANING |
|---|---|
DOW |
day of the week (1 = Monday, 7 = Sunday) |
DOM |
day of the month (1 - 31) |
DOY |
day of the year |
IS_BD |
business day (weekday that is neither a public nor a stock holiday) |
IS_WD / IS_WE |
weekday / weekend |
IS_H |
public holiday |
IS_SH |
stock holiday, e.g. 31.12. or Good Friday in Austria |
IS_BH |
bank holiday, e.g. 24.12. in Austria |
IS_CU |
calendar ultimo (last day of the month) |
IS_BU |
business ultimo (last business day of the month) |
The optional p_cal_preferring_clause restricts the selected days further with an Exasol PREFERRING clause.
Example: Business Ultimo and Every Monday of the Austrian Calendar
1EXECUTE SCRIPT API_REDADM.ADD_CALENDAR_KEY(
2 'AT' -- p_cal_name
3 ,'BU' -- p_cal_key
4 ,'business ultimo' -- p_description
5 ,'is_bu' -- p_cal_rule
6 ,null -- p_cal_preferring_clause
7);
8
9EXECUTE SCRIPT API_REDADM.ADD_CALENDAR_KEY(
10 'AT' -- p_cal_name
11 ,'MON' -- p_cal_key
12 ,'every Monday' -- p_description
13 ,'dow = 1' -- p_cal_rule
14 ,null -- p_cal_preferring_clause
15);
The days of a new or changed key are calculated by the calendar refresh (see below). Calendar keys are changed with API_REDADM.MODIFY_CALENDAR_KEY and removed with API_REDADM.REMOVE_CALENDAR_KEY.
2.2. Add a Load Cycle¶
Assign a calendar key to a TSA table with API_REDADM.ADD_TSA_LOAD_CYCLE:
- p_load_cycle_from and p_load_cycle_to
Validity period of the load cycle. A table can have several load cycles for different periods, e.g. when the delivery schedule changes.
- p_delivery_offset
Delay of the delivery in days, if the source provides the data of a business date later.
- p_alert_1_offset and p_alert_2_offset
Time in hours after which a missing delivery raises a warning (alert 1) or an error (alert 2).
Example: Month-End Data, Delivered One Day Later
1EXECUTE SCRIPT API_REDADM.ADD_TSA_LOAD_CYCLE(
2 'CFITSA_TEST' -- p_tsa_schema
3 ,'TESTING_PERSON' -- p_tsa_table
4 ,'AT' -- p_load_cycle_cal_name
5 ,'BU' -- p_load_cycle_cal_key
6 ,'2024-08-01' -- p_load_cycle_from
7 ,'9999-12-31' -- p_load_cycle_to
8 ,1 -- p_delivery_offset
9 ,8 -- p_alert_1_offset
10 ,12 -- p_alert_2_offset
11);
2.3. Remove a Load Cycle¶
Remove a load cycle with API_REDADM.REMOVE_TSA_LOAD_CYCLE; it is identified by the TSA table, the calendar key, and the start of its validity period.
Example: Remove the Load Cycle
1EXECUTE SCRIPT API_REDADM.REMOVE_TSA_LOAD_CYCLE(
2 'CFITSA_TEST' -- p_tsa_schema
3 ,'TESTING_PERSON' -- p_tsa_table
4 ,'AT' -- p_load_cycle_cal_name
5 ,'BU' -- p_load_cycle_cal_key
6 ,'2024-08-01' -- p_load_cycle_from
7);
2.4. Refresh the Calendar Information¶
After changing calendar keys or load cycles, refresh the calendar information with API_REDADM.REFRESH_TIME, either for selected TSA tables or, with an empty array, for all.
Example: Refresh the Calendar Information of One Table
1EXECUTE SCRIPT API_REDADM.REFRESH_TIME(
2 true -- p_full_refresh
3 ,ARRAY('CFITSA_TEST.TESTING_PERSON') -- p_tsa_objects
4);
2.5. Check the Load Cycles¶
REDADM.RED_TSA_LOAD_CYCLE_STATUSshows mismatches, e.g. overlapping load cycles, and, after some time in operation, actual loads without a matching load cycle.REDADM.STA_OBJECTS_CAL_MAPshows the expected deliveries per object with their alert limits.REDADM.STA_CALENDAR_BASEandREDADM.STA_CALENDAR_DAYSshow the calendar days, holidays, and the days of each calendar key; RED Vision shows them on its calendar page.
2.6. Suspend a BDomain Temporarily¶
If a BDomain delivers no data for a known period, e.g. during a migration of the source system, define an exception time with API_REDADM.ADD_BDOMAIN_EXCEPT_TIME. During this period the BDomain is skipped.
Example: Skip the BDomain ‘TESTING’ for a Weekend
1EXECUTE SCRIPT API_REDADM.ADD_BDOMAIN_EXCEPT_TIME(
2 'TESTING' -- p_bdomain
3 ,'2024-09-06 18:00:00' -- p_except_from
4 ,'2024-09-09 06:00:00' -- p_except_to
5);