Versions Compared

Old Version 1

changes.mady.by.user Wara Hermosa

Saved on

compared with

New Version Current

changes.mady.by.user eric revollo

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

OptionsDescription
TemplateEnables you to generate update procedures via a template.
Business Key Columns
Columns that define the business key for update processing. Required to include Update options. Clicking the ellipsis button brings up the Business Key selection screen.

A business key can be made up of multiple columns, but it must provide a unique identifier. Where multiple columns separately uniquely identify rows in the EDW 3NF Object, choose one to act as the primary business key. For example, a source table may have a unique constraint on both a product code and a product description. Therefore the description as well as the code must be unique. It is possible to combine the two columns, but the normal practice would be to choose the code as the business key.
Tip
  • Use the column name ascending/descending buttons to sort column names. To revert to the meta column order, click on the meta column order button.
  • NULL Values - none of the columns chosen as the business key should ever contain a NULL value.


Parameters
Any parameters selected are included in the generated update procedure as variables. The procedure includes code to retrieve the value of the parameter at run time and store it in the declared variable.
Clicking the ellipsis button brings up the Parameters selection screen.

The variables can also be used in column transformations and in the from/where clause for the update procedure. Some databases have a 30 character limit for variable names. WhereScape RED ensures the variables added for any parameters are less than 30 characters long by creating variable names in the form v_ followed by the first 28 characters of the parameter name.
For example, a parameter called MINIMUM_ORDER_NUMBER_SINCE_LAST_SOURCE_LOAD will be available as the variable v_MINIMUM_ORDER_NUMBER_SINCE_L.
Tip
  • WhereScape RED parameters should be unique within the first 28 characters to avoid conflicting variables names.
  • If the desired parameter doesn't exist in the metadata yet, a new parameter can be added by clicking on the Add New button on the bottom leftmost corner of the Select Parameters dialog.
  • Refer to Parameters for details.


Include Initial Load InsertAdds insert statement to the update procedure that runs if the target EDW 3NF object is empty. The benefit of this is improved performance inserting into an empty table without performing any checks to see if rows already exist. The default for this field is off (i.e. an initial insert statement is not added to the procedure).
Process by BatchEnables you to select a column to drive data processing in a loop based on the distinct ordered values of the selected columns. The update procedure loops on this column and performs the delete, update and/or insert for each value. If the column chosen is a date data type (date, datetime or timestamp), then the user can specify yearly, monthly, daily or column level looping. The default for this field is off (do not do batch processing).
Delete Before InsertEnables you to select how to process deletes. It enables a delete statement to be added to the update procedure before any update or insert statement. This is a particularly useful option for purging old data and for updates based on a source system batch number. If this option is selected, the following options are also available:

  • Issue Warning if a Delete occurs: this option sets the procedure to a warning state if any deletes occur.

  • Delete Where Clause: the delete where clause is appended to the generated delete statement to constrain the rows deleted.

Process MethodSelect between Insert/Update and Merge which allows you to use the Merge statement instead of two separate Insert and update statements.
Include Insert Statement

Includes the insert statement in the procedure to insert new rows in the EDW 3NF Object.

Tip
titleInsert

Enter a database compliant hint to be used in the INSERT statement. This is a SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options.

Insert New Rows OnlyUses change detection to work out what rows require inserting.
New Row Identification MethodMethod used to identify that records in the source are not currently recorded in the target table.
Existing Data Selection HintDatabase-compliant hint to be used for the existing data select statement.
Include Update Statement

Includes an update statement in the procedure to update changing rows in the EDW 3NF Object. If this option is chosen, then the Update Changed rows only option is available.

Tip
titleUpdate

Enter a database compliant hint to be used in the UPDATE statement. This is a SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options.


Update Changed Rows OnlyUses change detection to work out what rows require updating. Choosing this option, enables the Change Row identification Method.
Change Row Identification MethodMethod used to identify that records in source have changed from what is currently recorded in the target table.
Existing Data Selection Hint

Database-compliant hint to be used for the existing data select statement.

Tip
titleMerge

Enter a database hint to be used in the MERGE statement. This is a SQL Server only option. Defaults can be configured in Tools > Options > Default Update Procedure Options.


...

A simple join only returns rows where data is matched in both tables. So for example, if table A has 100 rows and table B has a subset of 24 rows. If all the rows in table B can be joined to table A then 24 rows will be returned. The other 76 rows from table A will not be returned.

...

Additional indexes can be added, or these indexes changed. Refer to Indexes

for more details.

Converting an existing EDW 3NF Table to an EDW 3NF History Table

...