Procedure Output Storage Option (Teradata Database)

Teradata Warehouse Miner User Guide - Volume 1Introduction and Profiling

Teradata Warehouse Miner
User Guide

The standard Output Storage options panel that is available with most types of analysis includes options to name and comment on a stored procedure, but only when the option to Store the tabular output of this analysis in the database is selected. If the selected Output Type is Table, the stored procedure is created with parameters for the output database and table. If the selected Output Type is View, the stored procedure is created without parameters. If a stored procedure is requested along with the option to Generate the SQL for this analysis, but do not execute it, the SQL to create the stored procedure and optionally to add a descriptive comment to its data dictionary entry is created but not executed.

When an optional Procedure Comment is entered, it can be up to 255 characters in length and contain substitution parameters for the output category (Score, ADS, Stats or Other), project name and/or analysis name (using the tags <Category>, <Project> and <Analysis>, respectively). Note that the default value of this field may be set on the Defaults tab of the Preferences dialog box that is available from the Tools > Preferences menu option.

When a stored procedure is requested, a note is displayed at the bottom of the Output Storage panel to View the 'RESULTS--data' tab to see any errors/warnings creating the stored procedure. This simply points out that the creation of the stored procedure returns warning and error messages as data, which should be viewed to ensure that the stored procedure will have the desired effect.

The functions that provide the Output Storage option to create a stored procedure include all ADS functions except Refresh (which has stored procedure options of its own), all Descriptive Statistics functions except Correlation Matrix, Data Explorer (which has options of its own), Text Field Analyzer and Scatter Plot, all Reorganization functions except Sample, and all Statistical Test functions. The Output Storage option is not provided for Analytic analyses (algorithms), Matrix functions, Miscellaneous (Free Form SQL), Publish or Scoring functions. Note, however, that the Sample function, Free Form SQL and various Scoring functions can produce a stored procedure using options provided by the Refresh analysis, described in Procedure Options for Refresh (Teradata Database).

Some consideration must be given to any analysis that is referenced for input when the output option to create a stored procedure is requested.
  • If the reference is to a created table, a warning message is given that the desired results may not be achieved, since the stored procedure will not recreate the input table.
  • If the reference is to an analysis that does not build an output table (and therefore automatically builds a volatile table), an error is given, since the procedure cannot work correctly in this case.

    An exception to this is when the referenced analysis uses the Generate the SQL for this analysis, but do not execute it option, leading to the SQL being included as a derived table in the referencing analysis, in which case no error is given and all of the required SQL is included in the stored procedure.

For ADS and Reorganization analyses, as well as for Free Form SQL analyses, the Refresh analysis may be used to include the SQL for all referenced analyses in the stored procedure, as described in Procedure Options for Refresh (Teradata Database).