When a table within the data warehouse schema, that is unknown to WhereScape RED, is dropped onto a table target the following dialog appears.
If this is an integration then click Yes to proceed with the integrate process. The standard New Object dialog appears and it would be advisable to leave the name of the object unchanged so that it matches the existing table.
A dialog asks if the bulk of the columns in this table are derived from another table. If they are enter the table from which these columns derive at this stage. The purpose of this dialog is simply to set the Source Table field against each of the columns for the table.
Artificial Key definition
If the table target is a dimension or fact table then the following dialog appears to enable the definition of any artificial or surrogate key. If no such key exists then simply proceed to the next question.
Get Key Function
If the target table is a dimension you will be asked if you want to generate a get key function. The get key function is used by WhereScape RED for rapid prototyping. It returns the artificial key when passed the business key. Although this function is possibly not necessary in the existing environment it is useful if the dimension is to be used in any expansion of the data warehouse. If an existing get key type function exists, then this can be used, although changes will probably need to be made to the generated procedures to utilize this existing get key function.
The Get Key function is not available for dimension objects that are stored in custom database targets.
Update Procedure
If the target table is a dimension, stage, fact or aggregate a dialog box will ask if an update procedure or update template is required. If selected a subsequent dialog will define the structure and content of this procedure. If an existing procedure is to be modified to include the scheduler imposed procedure standards it is best to select this option and use the existing procedure name to cut down on the amount of work required. The existing procedure can then be loaded into the generated procedure using the procedure editor.
Artificial Key Sequence
If a get key function or update procedure is to be built and an artificial key is in use for the table then a dialog will prompt for the artificial key sequence. A drop-down list provides all the sequences defined for this database user. If no such sequence is used then select the *** no sequence *** option.
Update Procedure Definition
If an update procedure was selected then the following dialog will display to help define how the update procedure is generated.
As defined in this dialog box the scheduler needs specific parameters to control and run a procedure. An existing procedure will not have these parameters. There are two forms of generated procedure. The first option is to generate a procedure that looks much like a standard WhereScape RED procedure and then manually insert the changes from the existing procedure. The second option is to generate a wrapper procedure that calls the existing procedure. This second option will still require manual changes to the procedure to return a valid return code and message.
Business Key definition
If a get key or update procedure has been selected a dialog will now prompt for the selection of the business key from the table. Multiple columns may constitute the business key, but they must uniquely identify each record in the table.
Index definition
Any indexes associated with the table will now be automatically defined and loaded into the metadata. Changes may need to be made in terms of when the indexes are dropped and to set the Drop Before Update checkbox if appropriate and the scheduler is to be used to manage these indexes.
Procedure creation
If defined the get key and update procedures will now be generated. They will need to be manually edited and compiled. Refer to Integrating procedures for details.