Once a Detail 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.
Generating a Procedure
- To generate a procedure, right-click the Fact table in the left pane and select Properties.
- Click 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.
KPI parameter definition
The first window displayed when generating a Detail Fact table update procedure is the KPI parameter definition dialog:
The KPI parameter definition window prompts for a parameter to hold the start period that we are interested in. This period sets the first period that is processed when the KPI Fact table is updated. Once entered, click OK.
If this parameter is not set, the default current period is set from dim_date. The format is YYYYMM.
The dim_date custom update procedure daily_date_roll can be updated to set these parameters automatically if you do not wish to control them manually. This is good practice.
Similarly, a KPI parameter definition dialog prompts for a parameter to hold the end period. Once entered, click OK.
Define Rollup date dimension and column
As with Rollup tables, we now need to specify which date in the transactional data we will use to control the rollups and to which level – in this example, for the Monthly level rollup, we choose dim_order_date as our Date Dimension and order_cal_month as our Rollup Column and click OK.
Once again for our Yearly rollup, in this example, we choose dim_order_date as our Date Dimension and order_cal_year as our Rollup Year and click OK.
The next screen sets the Daily level for the update procedure to understand the lower-level detail, we choose dim_order_date as our Date Dimension and order_date as our Date Column and click OK.
Select the KPI dimension and identifier
Finally, we need to specify where the KPIs are defined and how they are defined, so in this example, we choose dim_sales_kpi as the KPI Dimension and KPI_ID as the KPI Identifier. Click OK.