The setup 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.
  4. Create the update procedure for the fact table.
  5. Set up each individual KPI.

KPI Setup

KPI Dimension

The KPI dimension provides a means of selecting a specific KPI. This dimension needs to be built in advance of the KPI fact table. It does not need to be populated, as the KPI fact table will add entries to this table as required. A key and some form of unique identifier for the KPI is required, e.g. they may be numbered or given short names. 

Create the KPI Fact Table

Right-click the KPI table and select Create/Recreate to physically create the table in the database.

Defining column types and default values

  1. List all the columns for the KPI fact table. 
  2. Amend the properties for each column. The column types can be one of the following:

    Type

    Description

    Dimension Key

    Any dimension that is not the time or KPI dimension

    Time Dimension Key

    Time dimension key

    KPI Dimension Key

    KPI dimension key

    Month-to-date Measure

    Used to identify the normal KPI measures. These are calculated to populate the KPI during each run.

    Year-to-date Measure

    Used to identify year-to-date measures for the KPI. Optionally calculated or derived from existing rows and measures in the KPI fact table.

    Last year same month Measure

    Used to identify measures for same month last year comparisons.  Derived from existing rows and measures in the KPI fact table.

    Previous year-to-date Measure

    Used to identify previous year-to-date measures. Derived from existing rows and measures in the KPI fact table.

    Fact Table Key

    Not normally used

    Date

    Not normally used

    Attribute

    Not normally used

    Measure

    Not normally used

    If an Attribute or Measure column is specified then this column will just be set to its default value. In this way an update_time column can be set by making its default value sysdate.


    For each column set a default value. 
  • For measures and dimension keys, this would normally be zero (0). Nulls can be represented as two single quotation marks, i.e. ' '
  • sysdate can be used for a default date value
  • These default values populate the columns where and when no other information is available. For example if a KPI only makes use of 4 of the 6 dimensions available then the remaining 2 dimensions will be set to their default values.

Create the 'Update' procedure

Right-click the KPI fact table name and select Properties. In the Properties window, set the update procedure list to the (Build Procedure...) option. Click OK to initiate a series of window prompts to create a procedure used to update the kpi fact table.
The first two dialogs ask for parameter names to define the start and end periods for which the KPIs will be updated. The default naming convention is fact_table_name_START and fact_table_name_END. Accept these default names if possible. These two parameters need to be created, and need to be updated once the KPIs have been defined and tested. Under normal operation, these parameters should probably be set daily from the daily_time_roll procedure.

The date dimension and column for rollup are required. Normally this would be cal_month or fin_month.
This is based on the assumption that most KPIs are monthly or annual. Subsequently, a rollup year and date will be required from the same date dimension.

The KPI dimension is required. Also, the identifier column (not the key) that uniquely identifies the KPI. This would normally be a number or short name (max 64 chars). 

The procedure is then created and compiled. 

Define each KPI

Once the procedure has successfully compiled, we are ready to start setting up each of the KPIs. Right-click the KPI table name to get the pop-up menu.
This menu is similar to the other fact table menus except for the additional options to display and add KPIs. A display of the KPIs will list any KPIs that are currently defined. The result set is as appears below:

The 'Id' is the same unique identifier that will be found in the kpi dimension. The Name is a name entered in the properties of the KPI and does not relate to the dimension in any way. The Active option indicates if the KPI will be processed when the Update or Process menu options are selected. Each KPI can be defined as a SQL Statement, a Procedural Block or a Procedure. The current method used to update the KPI is one of these three and is displayed under Method. The description is a comment or Description field entered in the Properties of the KPI and in no way relates in the KPI dimension. 
Right-click anywhere in the List window to access the KPI add option. Clicking the right mouse button while positioned over the 'Id' opens a pop-up menu which provides options to copy, modify, delete, add, and view the KPI statement.
When the add KPI option is selected, a Properties screen appears as shown below. The id and name fields are required. If the Active check box is selected, the procedure is enabled for update.
The Select Columns button enables the selection of the columns that this KPI will update during the month-to-date update pass. The date and KPI dimensions will be updated for you so are not provided as choices. The only choices provided are the 'Month to date measures' and the other 'Dimensions'. Select those measures and dimensions that will be updated. All non-selected measures and dimensions will be set to their default values. The resultant choice is shown in the Update Columns window. This window should only be updated via the Select Columns button.

Once these initial values are defined, the Statement tabs can be selected to define the update SQL statement. If a procedural block or procedure is required for the update (i.e. too complex for a single SQL statement), then select the Procedural Block tab.

The KPI Statement

The Statement tabs present edit windows that enable the input of a SELECT statement that will return the values for the columns being updated.
They must return the values for the columns selected in the Properties tab in the order shown in the Update Columns window. An example of a completed KPI statement follows:

The first two lines are comments inserted (when modifying) to provide a reminder of the columns and order required by the select statement. Anything prior to the initial SELECT statement is ignored.
The statement must make use of at least one of the variables provided to select the appropriate period. This variable is replaced with the values for the period being updated at run time. These values are derived from the start and end parameters defined for the KPI fact table.
In order to group by the non-selected columns and any attributes, etc. WhereScape RED must be able to break this statement into its component Select, From, Where, Group By, and Having components (where present). If a complex statement is used that has more than one of these key words then the primary one must be in UPPER case. For example if a sub query is being used then the main SELECT and FROM key words must be in upper case and must be the only occurrence of these key words in uppercase. WhereScape RED checks and issues a warning if it is unable to resolve the main key words.
To test a statement, right-click the KPI Id and select View Statement. This provides a window with the full statement with the default columns inserted and the parameters converted. This statement can be cut and run in SQL Admin (shipped with WhereScape RED) or in a database specific utility for testing.

Procedural Block

The Procedural Block tab presents an edit window. If there is anything in this window it is actioned, instead of the statement. A button in the lower right allows a rudimentary template block to be built.  If used, this block must do the actual insert statement rather than just the select. It must also handle ALL dimension, month to date measure and non standard columns. In addition, it will need to put default values in columns not actively being updated. The KPI dimension and date dimension keys must also be updated. The output from a 'View Statement' option for another KPI gives an indication of what the block must do. The same month last year, year to date and previous year to date measures are still handled automatically. The same $KPI_ variables as shown above the statement edit window can and must be used.

Procedure

If for some reason it is not possible to implement the KPI in either the statement or procedural block then a procedure may be created. The name of such a procedure can be entered in the window at the bottom of the Procedural Block tab. If any value is present in this window, then the procedure takes precedence over both the statement and block. 
This procedure must adhere to the standard set of parameters as with all other procedures to be executed by the scheduler. It must undertake the same tasks as defined above in the Procedural Block section, and as with those tasks it does not need to set the year to date, last year same month and previous year to date values.

Copying KPIs

Right-click the KPI Id and select Insert Copy to insert an exact duplicate of the KPI. This copy can then have its ID and Name changed along with its statement to create a new KPI. This is particularly useful when only small differences exist between a number of KPIs.

Testing/Development

During testing it would be normal to DISABLE all KPIs except the one currently being developed. In this way, the update or process options will only utilize that KPI. As mentioned above the 'View Statement' option of the KPI popup menu displays, and allows the cutting of, the statement that will be executed to acquire the KPI information. This statement can be tested in SQL*Plus or some other environment if problems are encountered.

  • No labels