The KPI dimension essentially provides a record of each KPI or statistic we are tracking. There should be one row in this table for each such element.
The KPI fact table requires that this dimension contain at least an artificial dimension key and a business key. We will refer to the business key as the KPI Identifier.
The following example covers the manual creation of a basic KPI dimension:
- Right-click the Dimension object group in the left pane and select New Object.
- Give the new dimension a name for example dim_kpi.
- Select a Normal dimension and click OK on the Properties screen to define the dimension table.
- Right-click the dimension name in the left pane and select Add Column, add the three columns defined in the following table.
| Field | Column 1 | Column 2 | Column 3 |
|---|---|---|---|
| Column Name | dim_kpi_key | kpi_identifier | kpi_name |
| Business display | kpi artificial key | kpi unique id | kpi name |
Datatype
| integer | varchar(12) | varchar(256) |
| Nulls | Clear | Clear | Checked |
| Key Type | 0 | A | Clear |
| Artificial Key | Checked | Clear | Clear |
| Primary Business | Clear | Checked | Clear |
The first two columns are required and should be set up as shown above, although a different data type can be used for the second column, and the names can be chosen to suit the requirements.