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), or CH (Switzerland).

Calendar key (REDADM.RED_CALENDAR_KEYS)

A rule within a calendar that selects days, e.g. BD (every business day) or CD (every calendar day). The resulting days are listed in REDADM.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_STATUS shows mismatches, e.g. overlapping load cycles, and, after some time in operation, actual loads without a matching load cycle.

  • REDADM.STA_OBJECTS_CAL_MAP shows the expected deliveries per object with their alert limits.

  • REDADM.STA_CALENDAR_BASE and REDADM.STA_CALENDAR_DAYS show 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);