5.4.4 - Calendar Support (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 1Introduction and Profiling

prodname
Teradata Warehouse Miner
vrm_release
5.4.4
category
User Guide
featnum
B035-2300-077K
The Teradata Profiler product does not make use of calendar functions, but calendar views and UDF calls could be included in a Free Form SQL analysis.

Prior to release 14.00 of the Teradata RDBMS, a Teradata calendar view called CALENDAR in the SYS_CALENDAR database was the only calendar available. The Teradata Warehouse Miner product mimics the measures provided by this view, calculating them “inline” in the SQL generated by a Variable Creation analysis, as a higher performing and more convenient option than joining in the CALENDAR view.

In Teradata 14.00, two new calendars were introduced in addition to the Teradata calendar, one that follows the conventions of the International Standards Organization (ISO), and the other an Oracle compatible calendar. Any of these calendars (named Teradata, ISO and Compatible) may be accessed through either the CALENDAR view previously mentioned, or through a BusinessCalendar view that can be customized to specify holidays, work days, and so forth. When either the CALENDAR or BusinessCalendar view is joined into a query, it is the session property calendar that determines whether the Teradata, ISO or Compatible calendar is used.

The user may replace the CALENDAR view with a Teradata Database version for performance reasons, but the best performance using the Teradata calendar should be available using the Teradata Warehouse Miner ‘inline’ SQL Calendar functions.

In addition, almost all of the measures available by joining in the CALENDAR or BusinessCalendar view are also available by calling supplied User Defined Functions (UDFs). One of the parameters common to the BusinessCalendar UDFs is the calendar name (Teradata, ISO or Compatible), providing an alternative to setting the session calendar prior to calling the UDF or prior to executing a query that joins in the BusinessCalendar view.

To support this extensive calendar functionality, the following features are provided in the Variable Creation analysis.
  • SQL elements for the original Teradata calendar measures, with inline SQL calculation.
  • SQL elements for Calendar Functions that calculate the original measures and one additional one (Week Number of Quarter) by creating calls to supplied calendar UDFs.
  • SQL elements for various before/after functions, such as the Sunday before a given date (these functions, introduced in Teradata 14.10, return either a date, timestamp or timestamp with time zone, as opposed to an integer).
  • If it is desired to execute the basic CALENDAR functions as UDFs, they may be found using the User Defined Function SQL element in the SYSLIB database (for example, TD_DAY_OF_WEEK).
  • If it is desired to execute calendar functions by joining to the system supplied business calendar views, the views may be selected from and joined to in the usual manner in a Variable Creation analysis. Note that some available business calendar functions are only available by joining to these views, including the following:
Views to Join Business Calendar functions to
Function Description
BusinessWeekBegin and End First/last working day in which date occurs
BusinessMonthBegin and End First/last working month in which date occurs
BusinessQuarterBegin and End First/last working quarter in which date occurs
BusinessYearBegin and End First/last working year in which date occurs

An additional, more general feature is provided on the Connection Properties dialog to allow the specification of one or more pre-execution commands, primarily to allow specification of the calendar to use for a session. The pre-execution commands (separated by semi-colons if more than one are specified) are executed once before a project is executed, a chain of referenced analyses is executed, or an individual analysis is executed.

Note that pre-execution commands are not performed inside a stored procedure produced by the product as an output option or when performing maintenance functions or metadata queries. In order to have the desired effect when the stored procedure output option has been requested, the pre-execution commands must be performed by the user just prior to calling the stored procedure. Pre-execution commands are however inserted prior to published SQL when the Publish analysis is used to send a model to the Model Manager web-based application. They are placed either ahead of all ADS SQL, or if scoring only, ahead of all scoring SQL.

Also, pre-execution commands that set session values will have no effect on analytic algorithm, scoring or matrix analyses, since these types of analysis create their own sessions independent of this feature.

Finally, since the application uses session pooling when accessing the RDBMS, setting the session calendar can lead to unexpected results if care is not taken because a session with a particular setting may be reused unknowingly. For this reason, a warning is given if the pre-execution commands are changed, to the effect that the user should change or reset any previously set session parameters, or else restart the application. This warning is not given if pre-execution commands are supplied when previously there were none in effect. When publishing to the Model Manager web-based application, it is recommended that any SET SESSION commands be reset in a post-processing command on the last executed analysis, once again due to the use of session pooling.