The creation of a summary table proceeds initially in the same way as an aggregate table.

  1. In the left pane double-click 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. 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).
  3. Drag over columns from dimensions that are linked to the fact table. Delete the dimension keys to allow a rollup to the level of the dimension elements.

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

  4. In the Properties of the aggregate table change the Table Type to Summary:

  5. Create the aggregate summary table in the database by right-clicking on the aggregate and selecting Create(ReCreate).
  6. Create a procedure to update the aggregate summary by right-clicking the aggregate, selecting Properties, and selecting (Build Procedure...) in the Update Procedure field. The aggregate summary table is rebuilt each time the procedure is executed.

    Any column transformations added to a measure column of an aggregate summary 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))..


  • No labels