Temporal Table Support (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 1Introduction and Profiling

Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
wbc1492033894304.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2300
Product Category
Software

Temporal tables, as available in Teradata 13.10, are supported in Teradata Warehouse Miner primarily through the support of temporal qualifiers in the Variable Creation analysis (not available in the Teradata Profiler product). Temporal qualifiers may precede the SELECT keyword or follow the table name, derived table or joined table in the FROM clause of a Select statement. Temporal support is also provided through the support of the Period data types, literals, functions, operators and built-in functions, as well as the EXPAND ON expert clause in the Variable Creation analysis. Refer also to the Teradata User Documentation beginning with version 13.10 of the database, particularly the volume entitled Temporal Table Support, B035-1182, as well as the volume SQL Data Manipulation Language, B035-1146, in the chapter entitled The SELECT Statement and the topic EXPAND ON Clause.

When a temporal or bitemporal table is referenced in a query or used as input to an analysis, the current session Temporal Qualifier is used by default when selecting from that table, and the default session Temporal Qualifier is CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME. This means that temporal tables may be analyzed by any Teradata Warehouse Miner analysis using the default session Temporal Qualifier. If a different qualifier is desired, the most straightforward solution is to create a view that incorporates the desired qualifier. For example, the SQL below would create a view incorporating the CURRENT VALIDTIME qualifier.

REPLACE VIEW "twm_source"."twm_temporal_customer_view" AS CURRENT VALIDTIME
SELECT * FROM "twm_source"."twm_temporal_customer";

A view such as the one defined above can be used to perform any available analysis on the underlying table in the view. Such a view definition can be built using either a Variable Creation analysis, utilizing the selectors available on the analysis parameters panel, or a Free Form SQL analysis.

If it is not practical to create a view with an embedded temporal qualifier, the current session Temporal Qualifier can be changed for some types of analyses by using a statement such as the following, executed before the desired analyses in a Free Form SQL analysis.

set session CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME;

All of the desired analyses to be performed with this temporal qualifier must be executed in the same session, and this is not possible for those types of analysis which make their own connection or connections, including the analytic algorithms and their scoring analyses, the Matrix analysis and the Data Explorer analysis. For other types of analyses, it is still necessary to ensure that the same connection is used for the set session command and each of the analyses that depend on it, which is best guaranteed by executing an entire project that contains the Free Form SQL analysis and dependent analyses.