Once a stage table has been defined in the metadata and created in the database, an update procedure can be generated to handle the joining of any tables and the lookup of the dimension table artificial keys.

You can also generate an update procedure via a template, refer to Rebuilding Update Procedures for details.

Parameters

If WhereScape RED parameters exist in the metadata, the following window is displayed. Any parameters selected in this dialog (by moving them to the right pane), are included in the generated update procedure as variables. 

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.

  • WhereScape RED parameters should be unique within the first 28 characters to avoid conflicting variable 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 more information on WhereScape RED Parameters.

Multiple Source Tables

WhereScape RED provides a wizard that enables you to graphically generate the join clause for an object with multiple source tables. The Source Join wizard enables you to specify the primary (driving) table and the source tables to join. A graphical representation of the tables and the join criteria is displayed by the wizard which you can manipulate to change the defined joins.

The Source Join wizard guides you in generating the join clause through the following steps:

  1. Choose Primary Table The first source table encountered is the default primary (driving) table which can be changed before any joins are defined.
  2. Choose Tables to Join Once the primary table is set, the remaining source tables are listed in this drop-down. Select a table to join and then click Add. The two tables are graphically added in pane 4, which is the basis for the join displayed in pane 3.
  3. Defined Joins The join type defaults to Inner join but this can be changed from the drop down.
  4. Join criteria of selected join The column(s) used to join the two tables can be joined manually by clicking the corresponding join column connection point from the table on the left and then dragging the line that appears to the connection point of the required column in the table on the right. Alternatively, if a column with a matching or similar name exists in the left-hand table(s) then a context menu is provided on the columns on the right-hand table. You can right-click the column to display the context menu and then select a column from the list.

     To aid manual column join, you can expand or collapse columns in the tables on the left and toggle between natural and alphabetic column ordering. 

  5. Once the join column(s) for the table pair are defined, then any additional source tables can be added from the drop-down list in step 2 and the above column mapping process repeats to join this table.
    This process is repeated until all source tables are joined.
  6. Clicking the Next button or the Join Query tab progresses to the join editor window, where custom joins, database functions, etc. can be added.

If RED is unable to parse a join statement, the user cannot navigate back to the define join tab, and the following warning is displayed:

Dimension Joins

For each dimension key, a dialog appears asking for the business key from the stage table that matches the business key for the dimension.
In the example below, we are asked for the stage table business key for the customer dimension. The dimension name is shown both on the first prompt line and at the lower left side of the window.
The customer dimension has a unique business key named code.
We must provide the corresponding business key from the staging table. In the case of our example, this is the customer_code column.

  1. Click OK after the correct business key has been entered.
  2. If the business key does not exist and will be derived from another dimension or from some form of lookup, then enter any column and edit the procedure once produced.


The Add Text button and the associated message and edit box are only shown if the user possesses a full license, thus enabling the advanced procedure build options. When the Add Text button is clicked, any data in its edit box is placed in the Stage table column list. In this way, a number or string can be assigned as part or all of a Dimension join.

Slowly Changing Dimension Information

If the dimension being joined was defined as a slowly changing dimension, then an additional window appears, as shown below. This window asks for a date field in the Stage table that enables RED to determine which version of the slowly changing dimension (the customer_name field, below) to use based on the specified date range in the Customer Dimension. 
Select the appropriate date field for your business needs and click OK. If you wish to take the last (or current) version for the dimension, select No Date.

For Example:
As shown in the screen above, we have defined the customer_name as an item in the dimension that we expect to have versions for over time, e.g. each time the data warehouse processing sees a new customer_name value, the dimension will record the date range for that version's validity even though the business key (customer_code in this example) remains the same. This implies we want to create a new dimensional record whenever a customer name is changed even though the customer_code remains the same. 
Let's say a customer changes their name on the 5th of the month. If the Staging Table Dates field is set to order_date, any order received before the 5th of the month is identified under the old customer name and any order received on or after the 5th has the new customer name.
Alternatively, by setting the Staging Table Dates to ship_date, we can specify that any order shipped on or after the 5th of the month is shipped with the new name.

Staging Business Key

Once all the dimensional joins have been defined, we will be asked to define the unique business key for the staging table. This is the column or columns that allow us to uniquely identify each record in the staging table. 
In the example below, the unique business key is a combination of the order_number and the order_line_no.

  • The order of the columns in the business key list is set to the order in which the columns appear in the table. This is done to optimize performance for any indexes.
  • NULL VALUES: None of the columns chosen as the business key should ever contain a NULL value. See the note at the start of the Dimension chapter.

The Include Update Statement checkbox provides two options in the generated procedure. If selected, then an update/insert combination will be included. If clear, then only an Insert statement will be included in the procedure. If cleared, you must be sure that the business key for the table is unique, otherwise either a duplicate key error will occur or records will be lost, depending on the type of procedure.

Index re-creation

Finally, you are asked if you want to drop and re-create any indexes defined on the table:

  • Click Yes to drop and re-create.
  • No to leave existing indexes in place.


  • No labels