A KPI fact table is normally created manually.
- Right-click the Fact Table object group in the left pane and select New Object.
- A dialog displays as shown below.
The default object type is Fact Table. This must be changed to KPI Fact Table and a table name entered.
Adding columns
Once created, the KPI fact table needs to have columns added. New columns can be added by dragging columns from the browser window, or by manually adding from the right mouse menu. Dimension keys should normally be dragged to ensure that all the properties are correctly set.
The columns for a KPI fact table are typically the dimensions and generic measures. For example, valid measures could be kpi_count, kpi_quantity and kpi_value. The interpretation of these measures depends on the KPI or group of KPIs being queried. As with the rollup fact table, it is normal and desirable to keep the number of measures as small as possible. Attributes should not be included in these fact tables. These measures must allow Null values for the generated update procedure to function correctly.
- The kpi measure column order is important. For example, if we have mtd count, quantity and value in that order then the prev month, prev ytd and ytd figures also need count first followed by quantity and finally value. If this order is not adhered to then the generated procedure will load the wrong values into the wrong columns.
- A kpi fact table should also have the column dss_update_time added. This column is required if the kpi table is to be used to populate higher level fact tables, aggregates or cubes. This column should be populated with the current date time whenever the particular row is updated.