WhereScape RED generates template procedures to assist in the various phases of the data warehouse build process. A procedure is generated by selecting the (Build Procedure...) option from a drop-down list field in a table's Properties window to configure the update, initial build, or post load procedures.
For Load tables, a post load procedure can be generated by selecting the option above. This post load procedure is designed to assist in the management of file loads where a trigger file has been used.
Post load procedures can either be manually generated from a RED provided procedure outline or generated leveraging a RED template—refer to Rebuilding Update Procedures for details.
Note
For custom database targets, the option to use Post Load Scripts is provided, as an alternative to post load procedure. Post load scripts can be generated using a RED template—refer to Rebuilding Update Procedures for details.
For Dimension, Stage, Fact and Aggregate tables, an Update or Initial Build procedure can be generated by selecting from the corresponding procedure drop-down list field in the table's Properties.
When an 'Update' procedure generation is selected for a Dimension, a get_key function is also automatically created.
If a new procedure is created from scratch (i.e. not auto generated), then an outline of the syntax required by the WhereScape scheduler can be generated by selecting the Tools > Create Procedure Outline menu option in the procedure editor.
Wrapper procedures
In some cases, multiple procedures will be required to update a table. In such cases, it is best to create a top-level procedure that is seen by the scheduler as the 'Update' procedure. This procedure can in turn, call other procedures.
Example
We may have a fact table that is updated from multiple stage tables. This Oracle wrapper procedure calls two child procedures, one for each stage table that is to update the fact table. A status is reported back to the audit trail for each stage and an overall status ascertained for the fact table update. The Oracle wrapper procedure may look as follows:
Procedure Placeholders
Procedure placeholders can help in moving procedures between environments without the necessity of regenerating those same procedures. In RED, the purpose of these placeholders is to automatically substitute the corresponding strings, which is needed for a specific environment.
The following procedure placeholders described below can be found in the update_xxxx_xxxx procedure.
[TABLEOWNER] is used as a placeholder to replace the schema name defined in the connection or target.
For targets, the [TABLEOWNER] placeholder is derived from the Target Location Database/Schema in the connection. The target can be changed in the table's Storage screen, on the Target Location drop-down list. For more information about Target Location Database/Schema in connections and table storage screens, refer to Connections - Database, Connections - ODBC and Table Storage Properties for details.When moving tables between environments, the [TABLEOWNER] placeholder is determined by the individual connection of the target environment.
Example
During the compilation process of the procedure, [TABLEOWNER].[table_name] is replaced with PRODUSER.tablename, if the table owner is PRODUSER in the destination environment.
Tip
WhereScape RED Tip:
dim_date The TABLEOWNER placeholder is especially useful in update procedures when the related table is moved to a different schema or environment. For example, when moving dim_date to other schemas, [TABLEOWNER] will be replaced with the schema of the table when the procedure is compiled.To do this, prefix the table name in the procedure to [TABLEOWNER].[table_name] (e.g. [TABLEOWNER].[dim_date]). It is then only necessary to do a recompile, instead of rebuilding or regenerating the procedure.
Note
For Oracle and DB2 databases [TABLEOWNER] has already been added to the sample dim_date update procedures.
[FUNCTIONOWNER] is used as a placeholder for Greenplum databases to enable the creation of procedures in different targets without the need of procedure regeneration.
[SEQUENCE.tablename] is used as a placeholder for Oracle and Greenplum database sequences where the Sequence name can be defined for artificial keys in a table's storage screen.
This placeholder can be edited in the table's Storage screen, on the Artificial Key Sequence Name field, if that table contains an artificial key. For more information about replacing the sequence name in Oracle, refer to Table Storage Screen - Oracle for details.
[SCHEMA.tablename] is used as a placeholder for Oracle database schemas in partitioned fact tables, to facilitate moving partitioned fact table procedures between environments without having to regenerate the procedures.
