Creating an Aggregate Table

1. In the left pane double-click on the aggregate group to list the aggregates in the middle pane and set aggregates as the drop target.
2. From the Data Warehouse browse (right) pane drag a fact table into the middle pane and enter the aggregate name. Click ADD.

   

3. The aggregate properties are displayed. Click OK.

   

4. The list of columns in the Aggregate is displayed in the middle pane:

   

5. Remove any columns that will not make sense at an aggregated level. For example, dss_fact_table_key, any business keys, any non-additive facts, any measures that relate to detail (e.g. unit price) and any dimension keys not required:

   

   All aggregate table columns (other than dss columns) should always have a source table specified, even if a column transformation is in use.

6. Create the aggregate table in the database by right-clicking on the aggregate and selecting Create(ReCreate).
7. Create a procedure to update the aggregate by right-clicking on the aggregate, selecting Properties and selecting (Build Procedure...) in the Update Procedure field.
   

   WhereScape RED generated update procedures for aggregates are incremental. Incremental updates are based on a date dimension key and number of look-back days. The aggregate update process looks at any records that have been updated in the fact table in the last 7 days (by default).

   To support this, a date dimension key must be selected. The columns chosen must be in both the source fact table and the aggregate. Select this column and click OK.

   

8. Update the table by right-clicking and choosing Execute Update Procedure.
   

   Any column transformations added to a measure column of an aggregate table must always include an aggregate function, usually SUM. For example, an ISNULL added to forecast_quantity should be entered as: SUM(ISNULL(forecast_quantity,0)).

   
   

   

   • DB2: When the procedure runs it first removes the materialized query table, then updates any changes from the fact table into the aggregate. It then re-establishes the materialized query table, enabling query rewrite.