Expand On - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

An Expand On element may only be used underneath an Expand On clause, which can be used to generate a time-series expansion of a Period type column or expression. Dragging an Expand On element underneath the Expand On Clause gives the following result.

Variable Creation > Input > expert options: SQL Elements Pane > Expand On

The first sub-node underneath the Expand On element is required and must resolve to a Period data type. The second and third sub-nodes underneath the Expand On element are optional and their use depends on the properties selected on the Properties panel for the Expand On element, described below. The approach taken here is that sub-nodes are used for parameters that require a SQL expression, and other parameters can or must be selected on the Properties panel.

To display the Properties panel for the Expand On Clause, highlight the Expand On element and click Properties to the right of the SQL element tree.

Variable Creation > Input > expert options: SQL Elements Pane > Expand On Properties

By default, the Expand On Type is set to By Interval, in which case the middle sub-panel is labeled BY Expand Interval and displays in read-only fashion the interval literal entered as the second of three expressions underneath the Expand On SQL element. If, however, By Anvchore Period or By Anchor Point is selected as the Expand On Type, the middle sub-panel is labeled By Anchor and the following dialog appears.

Variable Creation > Input > expert options: SQL Elements Pane > Expand On Properties 2

The fields on these dialog boxes and the sub-nodes of the Expand On SQL element are described below, with the resulting expert clause described in more detail in the Teradata User Documentation, beginning with Teradata release 13.10, particularly SQL Data Manipulation Language, B035-1146, in the chapter The SELECT Statement and the topic EXPAND ON Clause.
  • Expand On Period (sub-node) — This is the required sub-node of the Expand On node containing the Period type column or expression that is the basis of the time-series expansion. Note that this element is displayed in a read-only fashion as (period expression) on the Expand On sub-panel.
  • Expand Column Alias — This required field provides an alias for the expanded value of the period of expansion. This alias can be referenced in the Variables defined in this Variable Creation analysis, using the Expand Column Reference in the Other category of SQL elements.
  • Expand On Type
    The possible types include:
    • By Interval — Expansion is performed based on a user-defined date/time interval.
    • By Anchor Period — Expansion is performed based on user-specified anchored time durations on a time line.
    • By Anchor Point — Expansion is performed based on user-specified anchored points on a time line.

      As mentioned above, the selection made here affects the options displayed on the Properties dialog, and is the key to the type of expansion performed. For more information, refer to the Teradata user documentation mentioned earlier.

  • Expand On Interval (sub-node) — When Expand On Type is set to By Interval, this element must contain an Interval literal that defines how the Expand On Period is segmented or expanded. Note that this element is displayed in a read-only fashion as (interval literal) on the BY Expand Interval sub-panel.
  • Anchor Name — This field allows the selection of a keyword describing the expansion interval. The values available in Teradata release 13.10 are shown with release blank in the table below.
    Beginning in Teradata release 14.00, the values representing the beginning or ending value of a calendar period are affected by the system business calendar set in the current session.
Anchor Name Values
Value Available Version
Anchor_Millesecond  
Anchor_Second TD 14.0
Anchor_Minute TD 14.00
Anchor_Hour TD 14.00
Day TD 14.00
Monday  
Tuesday  
Wednesday  
Thursday  
Friday  
Saturday  
Sunday  
Week_Begin TD 14.00
Week_End TD 14.00
Month_Begin  
Month_End  
Quarter_Begin TD 14.00
Quarter_End TD 14.00
Year_Begin TD 14.00
Year_End TD 14.00
  • At Time Literal — If the element type of the Period expansion expression is Timestamp, the At Time Literal is used to determine the Time portion of the Timestamp value of the anchor during expansion. If not specified here, a default of '00:00:00.000000+00:00' is used for anchored time values and begin-type anchors, and '23:59:59.999999+00:00' is used otherwise.
  • For Period (sub-node) — This optional sub-node represents the period of interest for expansion, effectively limiting the number of rows in the expansion. The overlapping period of the qualified row and the period constant specified here is the effective expansion period for each row. Note that this element is displayed in a read-only fashion as (period expression) on the FOR sub-panel.