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:
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.
Right-click the KPI table and select Create/Recreate to physically create the table in the database.
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. |
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.
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 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.
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.
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.
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.
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.