Adding columns

Once created the KPI fact table will need to have columns added. New columns can be added by dragging columns from the browser window, or 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 will depend 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.

1. 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.

2. A kpi fact table should also have the column dss_update_time added. This column is required if this 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.

The set-up of a KPI fact table is somewhat more complex than the other types of fact table. Once all columns have been defined the following steps need to be taken:

1 Create and populate a KPI dimension that provides at least an id (unique identifier) for each KPI and a description. The key from this dimension must be one of the columns in the fact table.

2 Physically create the KPI fact table in the database once all columns including the KPI dimension key have been added.

3 Define the column type and default value for ALL columns in the table. There must be at least one date dimension key and one KPI dimension key. These attributes are set by selecting a column and modifying its properties.