How to Enhance Table Lookup Alias To Be Nearly Perfect

Follow this discussion of proposed changes to PSPA Table Lookup Alias functionality for improved configurability without developer assistance and increased flexibility and performance.

Overview

Table Lookup Aliases are a foundational configuration capability that finds a result value in a table based on an effective date and one or two lookup values. Table Lookup Aliases are primarily used to retrieve Interest Rates, Benefit Rates, Early Retirement Factors, and Actuarial Factors. The alias may resolve to a single value that persists throughout a calculation for a particular plan and member or change as a program process through multiple historical periods.

This article proposes enhancement ideas that would improve Table Lookup Alias functionality to make it easier to set up without developer assistance, provide more configuration options for greater flexibility, improve execution performance, help avoid array sizing issues, fix some existing limitations, and generally expand the usefulness of Table Lookup Aliases to a broader range of applications.

Universal Table Lookup Alias Table

The biggest implementation challenge with Table Lookup Aliases is the dependence on the existence of a table (or view) with a very specific field layout that stores the table lookup data. Often, a developer is needed to create a new table, build an associated page for users to maintain the lookup data, and grant security. This means a new lookup table can not simply be configured, which delays implementation and requires additional resources.

A no-code solution could be achieved by adding the ability to configure a new Table Alias. The Table Alias configuration would define the table structure. A universal page would be used to maintain entries in the defined table, including the last update user and timestamp collection. The Table Lookup Alias configuration page would provide the option to reference a Table Alias while keeping the existing Table Name functionality intact for backward compatibility.

All of this would enable implementers to completely configure a Table Lookup Alias and provide users with a way to maintain the table data without requiring development efforts.

Table Aliases could be configured with multiple number, date, or character result values for each set of lookup inputs. For example, one table could include all member and beneficiary age combinations and specify different factor results for each combination. Merging this data into one table can simplify maintenance.

Tools on the Table Alias configuration and Universal Table maintenance pages could assist with the conversion of data from existing sources to the new universal format by automating configuration and copying data to the universal table. Even existing delivered table lookup tables could be converted to the new universal format.

Increase Table Lookup Alias Table Structure Flexibility

Currently, there are very rigid table structure requirements for Table Lookup Aliases.  Tables must have EFFDT and TL_SEQ_NUM fields in the first two columns, The Lookup criteria fields in the third or fourth column, and the Lookup result in the fourth or fifth column.  

Many table lookup tables are custom-created to meet plan requirements, but the ideal format for maintaining table lookup data might not align with this required rigid structure.  To reconcile the difference between the ideal table structure for data maintenance and the required table lookup table structure, often a custom view is created to support each Table Lookup Alias to be configured.

Proposed Changes:

• Provide a prompt on Table Name to more easily identify records that meet relaxed requirements to be used as a Table Lookup Alias table.
• Allow any table with EFFDT and TL_SEQ_NUM to be used as a Table Lookup Alias table.

Implementing a prompt on Table Name also fixes an issue where a fatal PeopleCode error is returned when a table name longer than 18 characters is entered on the Table Lookup Alias configuration page. The table name should be a prompt restricted to valid Application Designer tables that meet the table lookup structure requirements.

Multiple Lookup Tables stored in a single Database Table

A universal table structure with an additional table identifier key might make it easier to maintain multiple sets of lookup table values using a single page.  For example, when storing different interest rates for different types of employee accounts in the same table and maintained on a single page.  

Proposed Changes:

• Set and display the default lookup criteria fields for One-Dimensional and Two-Dimensional Table Lookup Aliases and allow configuration changes to select other fields on the table.
• Allow configuration of an additional Where Clause to support the selection of a subset of data from the table to support universal records that contain multiple lookup tables.

Multiple Result Fields stored on a single Database Table Row

It can be more efficient to store many different lookup result values on the same table row for a particular lookup criteria.

For example, storing multiple different optional form factors for a particular member and beneficiary age combination.

The Table Lookup Alias page identifies the result field to be returned. For a one-way table lookup, the result is the 4th field on the record; for a two-way table lookup, the result is the 5th field on the record. If the base table stores multiple result values, a view needs to be built to return the alternative results in the appropriate column – again requiring a development effort.

Adding the ability to configure the result field to be selected by the Table Lookup Alias would enable configuration without developing a view.

The name of the result field is resolved by the page and stored with the configuration data, but the result field name is not currently displayed or editable on the configuration page.

Proposed Changes:

• Set and display the default lookup result field and allow configuration changes to select other fields on the table.

Implied Gaps in Lookup Table Data

Many clients have accounts that are credited interest less frequently than monthly. For example, annual interest for an account with monthly accumulation periods. A common solution is to set up an Interest Method based on a Table Lookup Alias that turns the interest rate on and off. The table could be loaded with zero rate entries, or a view can be constructed to do something similar.

If the Table Lookup Alias configuration included an Interpolation Method option for an Exact Match, it could be set up so that an interest rate is only returned if the end date for the period being processed exactly matches the table entry. This would make it easy to set up the interest rate table with the exact dates interest should be computed and the associated interest rate that should be used.

Proposed Changes:

• Add Exact Match Only option to Table Lookup Alias Interpolation Methods that returns a zero or blank result when an exact match on lookup criteria is not found.

Performance Options / Array Size Management

When a Table Lookup Alias is resolved for the first time, the full set of table data is loaded into memory. The program then proceeds to find the appropriate table row using COBOL logic each time the Table Lookup Alias needs to be resolved. This is fast and efficient for small tables that must be utilized repeatedly, such as looking up the interest rates for each historical account period during a delete and rebuild process.

However, this approach is inefficient for large tables that are used less frequently, such as looking up an actuarial factor once per calculation. Storing large lookup tables in memory may require COBOL array size increases.

If there were an option to configure Table Lookup Aliases to retrieve only the result row using dynamic SQL logic, processing could be more efficient, and COBOL array sizing changes could be avoided in certain cases. Implementers could configure the Table Lookup Alias to use the method likely to provide the fastest performance.

Support for Negative Lookup Values

Table Lookup Aliases do not currently support returning a negative lookup value, and the Employee Accounts process does not currently support a negative interest rate in interest calculations. The program logic should be updated to support negative interest rates, which may be applicable to account types that earn an investment rate of return.

Configurable Value or Alias when Lookup Value is Below/Above Maximum

The Table Lookup Alias configuration options for what value to return when the lookup is below or above the range of values stored in the applicable table are limited to using the min/max value or returning an error. It might be useful also to have the option to specify a value or Alias to be returned.

This capability could be used to specify the result that should be returned when an exact match is not found, as pertains to the prior enhancement discussed.

Custom Statement Option for Lookup Value

Sometimes, the best sources for the Table Lookup inputs might be a Custom Statement Alias. For example, when an adjusted or rounded value is required, and it is not resolved as another type of Alias or Function Result.

The Table Lookup Alias page does not allow Custom Statements to be selected for the Lookup Value Aliases. Being able to specify a Custom Statement Alias as an input to the Table Lookup increases configuration flexibility. The page should be updated to include Custom Statement Aliases in the Lookup Value Alias prompts, and the program should be updated to resolve the Custom Statement when applicable.

Commentary

As always, solutions should be well-designed. These proposals seem to fit the following design goals:

1. Backward compatible – no impact on existing configuration/users.
2. No-Code configuration capabilities
3. An easy path to adoption – assisted set up and conversion of existing tables
4. Improved performance
5. Increased configuration flexibility
6. Better auditing capabilities

I have seen many universal or consolidated table lookup solutions that attempt to solve the delivered challenges, but they still fall short by requiring new translate values and views to add a new table successfully. A robust solution would never require an Application Designer change to implement a new table.

One advantage of custom table lookup table maintenance pages is the ability to set up security for each page individually. The proposed universal table design does not support row-level security without additional development.

Please weigh in on the conversation and provide your suggestion for Table Lookup Alias enhancements in the comments.

Example

The T_INT_TL Table Lookup Alias uses a view to select a subset of data from a universal interest table, remaps the posting_date field to the lookup criteria field, and sets the rate for non-interest crediting months at zero.

The original configuration uses a custom view.

The view uses the following SQL to set up the table lookup data for this table lookup alias in the required table lookup structure:

SELECT DISTINCT a.effdt 
, -1000 
, to_date('01-JAN-1900' 
,'dd-mon-yyyy') 
, 0 
 FROM ps_ct_pa_tl_int a 
WHERE CT_PA_INT_USAGE = 'TRS' 
 UNION 
SELECT a.effdt 
, trunc(months_between(a.posted_date 
, a.effdt)) - 982 
, a.posted_date 
, a.interest_rate 
 FROM ps_ct_pa_tl_int a 
WHERE CT_PA_INT_USAGE = 'TRS' 
 UNION 
SELECT a.effdt 
, trunc(months_between(a.posted_date 
, a.effdt)) - 981 
, a.posted_date + 1 
, 0 
 FROM ps_ct_pa_tl_int a 
WHERE CT_PA_INT_USAGE = 'TRS' 
  AND NOT EXISTS ( 
SELECT 'x' 
 FROM ps_ct_pa_tl_int b 
WHERE a.effdt = b.effdt 
  AND b.ct_pa_int_usage = a.ct_pa_int_usage 
  AND b.month_idx = add_months(a.month_idx,1)) 
 ORDER BY 1,3

After Changes, additional information and configuration options are presented on the Table Lookup Alias page.

An alternative configuration utilizing the enhancements eliminates the need for a custom view.

After Changes, the configuration page and process:

1. Allows a table that includes additional keys to be selected.
2. Allow the Lookup Field 1 to be changed from default (MONTH_IDX) to another field on the table (POSTED_DATE).
3. Allow additional Where Clause criteria to be specified to use a subset of the full table.
4. Require an exact match of the employee account period to the interest rate table or otherwise use a zero interest rate for the period.

Additional Resources

Special Challenges of Very Large COBOL Array Size Increase

Issue: Negative Interest Rates Not Supported