Dimensions
>
Adding a second 'Get Key' function for a dimension
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:
1 Using the right mouse menu with the 'Procedure' group highlighted in the left pane, select the 'New Object' option to create a new procedure. Put the name chosen in the name field and click ADD.
2 A properties dialog will appear. 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.
3 Expand the 'Procedure' group in the left pane and double click on the new object just created. This will start the editor.
4 Select the Tools/View procedure or template menu option. This will start a dialog box.
5 Select the existing 'Get Key' function from the procedure drop down. A new window will appear on the right hand side showing the existing 'Get Key' function.
6 Click the mouse in the existing function window. Using the right mouse menu choose 'Select All' and then using the right mouse menu again choose 'Copy'.
7 Click the mouse in the main editor window and paste the existing function into the window. The existing function window can now be closed if desired.
8 Using the menu option Edit/Replace change all occurrences of the old function name to the new name.
9 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 will obviously be 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.