When WhereScape RED generated a new procedure to support the update of a Dimension, it also generates a Get Key function (procedure in SQL Server and DB2). This function is also generated when Dimension Views are created and can be generated for retro-fitted Dimensions.
The Get Key function is used to return an artificial key when supplied a business key. The normal syntax is to call the function passing the business key and be returned a status indicating the result of the lookup.
In a successful lookup the artificial key for the dimension record is also returned. If the lookup fails because the business key is not found in the Dimension, then an appropriate status is returned, and the artificial key is set to 0.

Note

  • Two parameters exist on the generated Get Key function that allow manipulation of the process. It is possible to automatically add a new Dimension record when a match is not made. It is also possible to record the lookup failure in the detail/error log. By default, both these options are disabled.
  • The Get Key function is not available for Dimension objects that are stored in custom database targets.

Modifying the 'Get Key' function

There may be a situation where a Dimension record can have multiple business keys. 
For example: You may be able to look up a 'factory' based on the factory code, the name of the factory or its address. The generated Get Key function only supports the primary business key which in this case may be the factory code. However, we may have a fact record that only contains the factory name. In such a case, we need to modify the Get Key function or add a second function. The advantages of adding a second function are that any code generated in the future that uses this Dimension works without modification. The disadvantages are the multiple points of maintenance required when changing the Dimension. Both options are discussed below:

Adding a second 'Get Key' function for a dimension

Choose a name similar to the existing function. If say we have a 'get_dim_factory_key' function, then we may choose a name like 'get_factory_name_key'. Proceed to create and populate the function as follows:
Right-click the Procedure group in the left pane and select New Object to create a new procedure. Enter the name and click ADD.

  1. A Properties window is displayed. Change the type to Function for Oracle, leave as a Procedure for SQL Server. Enter a description under the Purpose column and click the UPDATE button.
  2. Expand the Procedure group in the left pane and double-click the new object just created. This opens the procedure editor window.
  3. Select the Tools > View Procedure or Template menu option.
  4. Select the existing Get Key function from the procedure drop-down. A new window appears on the right, showing the existing Get Key function.
  5. Click in the existing function window. Right-click and choose Select All and then right-click again to select Copy.
  6. Click in the main editor window and paste the existing function into the window. The existing function window can now be closed if desired.
  7. Using the menu option Edit > Replace, change all occurrences of the old function name to the new name.
  8. Change the various occurrences of the business key name and variable to match the new one and test it all.

Once a new Get Key function has been produced, it is necessary to modify the Stage update procedure to utilize the new function.

Tip

An alternative approach is to create a Dimension View, specifically to generate a different kind of get key function. Then use this view's surrogate key in the Stage table instead of the Dimension table's surrogate key. This approach avoids modifying the staging table procedure.

Extending the 'Get Key' function

If you want to keep all the information for the lookup of dimension keys in one location, then extend the functionality of the existing Get Key function. The normal practice in this case is to include all potential business keys as parameters. Multiple select statements can be employed to select the keys based on the appropriate business key. To choose the correct statement either use Nulls in the unused business keys or include an additional parameter to indicate which business key is being passed.
If this method is used, then any Stage update procedures must be modified to handle the new calling syntax.

  • No labels