A rollup or combined Fact table is typically created by dragging a Fact table onto a Fact table list. In the example below, double-clicking on the Fact table object group under the Sales project produces a list in the middle pane, showing the existing Fact tables.
A fact_sales_analysis rollup Fact table is created after selecting and dragging the fact_sales_detail table in the right pane into the middle pane.
The initial object create window appears. Note that the name implies that this is a rollup Fact table.
The name is changed to the one chosen above.
A list of columns is displayed and those not required are deleted (everything except additive measures and dimensions). 

Drag and drop additional columns from other Fact tables into the middle pane if required. Columns that have the same name need to be changed.
For example, if we acquired a quantity column from fact_sales_detail and a column of the same name from fact_forecast we may choose to change the names to actual_quantity and forecast_quantity. 
Once all columns have been defined:

  1. Create the table.
  2. Edit the properties of the table and request that a new procedure be created. 
  3. Click OK and the window to create a template procedure is commenced. This process can include the definition of the date dimension granularity. Monthly rollup is the norm.
  4. To just combine Fact tables with no rollup, select the date dimension key as directed in the window. Other forms of dimension rollup require some alteration to the produced template procedure.

Rollup Fact Columns

The columns for a rollup Fact table are typically the dimensions and only those key measures that are added.
For example, in the sales world, measures may simply be quantity, kilograms, $value, forecast_quantity, forecast_$value, budget_quantity, budget_$value.

  • No labels