The update procedure for a data warehouse object can be generated by RED using parameters provided by the user, or it can be generated using a prepared template. Templates appropriate to the table type must first be created before they can be selected and used to generate update procedures. The option to select a template is only available if RED detects the presence of an appropriate template.
Note
- Update procedures for Table objects are stored in the RED repository meta database. However, if required, the location of stored procedures can be changed and also be distributed across different Targets. The location of stored procedures can be changed and locked via the Procedure Properties window. Refer to Procedure Migration for details.
- Update procedures for Data Vault and Source Mapping objects can only be generated using a template. Refer to Data Vault Templates and Generating Update Procedures for Source Mapping Objects respectively for details.
Generating an Update Procedure without a Template
If RED does not detect a template, the drop-down options to rebuild with or without a template is not displayed in the Rebuild button. In this case, RED will manually regenerate the update procedure when Rebuild is clicked, by prompting the user to respond to a series of input requests. Refer to the Generating Update Procedure topic in each of the data warehouse object sections, for further information and the steps to manually generate update procedures.
Generating an Update Procedure via a Template
If RED detects the presence of a template, the Rebuild button provides additional options, in a drop down list, for rebuilding the selected update procedure. Two additional rebuild options are provided:
- Rebuild without template - RED rebuilds the update procedure, but ignores any previously used template. RED prompts you for inputs as required for the rebuild.
- Rebuild with template - Prompts you to select from a list, a template that is appropriate to the current object type. Depending on the selected template, the user is prompted to provide responses that will be used by the parameters within the template.
When you click the Rebuild button, RED rebuilds the procedure using the last selected option as the default method. If a template has not been previously used, then RED prompts you for inputs as required for the rebuild.
Note
- If a template has been previously used, RED uses this template by default when Rebuild is clicked. The name of this template is displayed below the Update Procedure field of the table Properties screen. The same applies to Custom Procedures that were generated via a template.
- If you are using WhereScape RED on SQL Server or Oracle and you are licensed for the Star schema option, a template for Set based processing of Fact tables (e.g. wsl_sqlserver_proc_fact_set or wsl_oracle_proc_fact_set) is available from the RED Template objects list pane.
- During procedure generation, a message prompt is displayed if any source tables are missing in the Source Join property of the Source tab in the Update Build Options screen. Select from the following options:
- Yes to return to the Source Join screen and edit the Source Join property.
- No to continue with the procedure generation.
- Cancel to cancel the procedure generation.
The following table summarizes the conditions that will display the drop-down options in the Rebuild button or that require a template. All other conditions will require a manual rebuild.
Table Object | SQL Server | Oracle | Teradata | DB2 | Greenplum | Netezza | Hive/PDW/Custom |
---|---|---|---|---|---|---|---|
Stage | Both | Both | Both | Both | Both | Both | Template Only |
Data Vault Stage | Template Only | Template Only | Template Only | Template Only | Template Only | Template Only | Template Only |
Fact | Both | Both | Both | Both | Both | Both | Template Only |
Fact Rollup | Both | Both | RED Automation Only | Both | RED Automation Only | RED Automation Only | Template Only |
Fact KPI | Both | Both | RED Automation Only | Both | RED Automation Only | RED Automation Only | Template Only |
Aggregate | Both | Both | Both | Both | RED Automation Only | RED Automation Only | Template Only |
Dimension | Both | Both | Both | Both | Both | Both | Template Only |
Data Store | Both | Both | Both | Both | Both | Both | Template Only |
Normal, Hub, Link, Satellite, Custom1 or 2 | Both | Both | Both | Both | Both | Both | Template Only |
Both | This object type / database combination supports both RED automation for code generation and template based code generation. |
RED Automation only | This object type / database combination uses RED automation for code generation and template based code generation is not currently available. |
Template only | This object type / database combination only supports template based code generation. |