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 the 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:

  1. Right-click the Dimension object group in the left pane and select New Object.
  2. Give the new dimension a name for example dim_kpi.
  3. Select a Normal dimension and click OK on the Properties screen to define the dimension table.
  4. 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

  • Oracle
  • SQL Server/DB2


integer 
integer


varchar2(12) 
varchar(12)


varchar2(256) 
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.

  • No labels