Once a detailed fact table has been defined in the metadata and created in the database, an update procedure can be generated to handle the update of the fact table.
- You can also generate an update procedure via a template, refer to Rebuilding Update Procedures for details.
- If the fact table is created in a custom database target, then an additional processing option is available—PowerShell script-based processing. Refer to Script Templates for Custom Database Table Objects for details.
Generating a Procedure
- To generate a procedure, right-click on the fact table in the left pane and select Properties.
- Click on the Rebuild button to start the process of generating the new procedure.
- A series of prompts are displayed during the procedure generation based on the type of load information.
Define Fact Business Key Columns
The first dialog displayed when generating a detail fact table update procedure is the define fact business key columns dialog, asking for the business key that will uniquely identify each fact table record. The source table from which the fact table is derived would normally have some form of unique constraint applied. In most cases this will be the business key. In the example below, the order_id and order_line_no are selected for the business key list.
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 fact table, 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.
None of the columns chosen as the business key should ever contain a NULL value.
Fields | Description |
|---|---|
Allow Where Clause Editing | If the Allow Where Clause Editing option is selected, then the next dialog to be displayed is the Source Join wizard, else this is skipped. |
Insert Hint | Enter a database hint to be used in the INSERT statement. |
Update Hint | Enter a database hint to be used in the UPDATE statement. |
Source Join Wizard
This wizard is used to join source tables and add 'Where' clauses.
Source table joins should have been performed in the stage table, refer to Generating the Staging Update Procedure for details.
Since most of the real work is done in the stage table, this is all that is needed to build the Detail Fact Update Procedure.