This chapter describes Lookup Table Driver functionality.
· Lookup Table Driver Summary and Detail Screens
· Navigation within the Summary Screen
· Navigation within the Detail Screen
Upon initially navigating to Profitability Management > Rule Specification > Lookup Table Driver, a summary screen is displayed showing a set of Lookup Table Driver rules. Using search criteria, you can control the set of rules that are displayed. When you Add, Edit, or View a rule, a detailed screen is displayed.
When you first navigate to the Lookup Table Driver summary screen, the rules stored within your current default Folder are presented in a summary grid. The Lookup Table Driver summary screen has two containers: Search and Lookup Table Driver.
Your default Folder functions as a search constraint. The value of your default Folder is set in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability. You may select a different Folder or you may remove the Folder constraint entirely by selecting the “blank” Folder, i.e., no Folder. You may also search by a rule's Name or Description.
· Search Control: You may search for Lookup Table Driver rules by Folder, Name, or Description. Enter your desired search criteria and click on the Search control.
· Reset Control: Restores the default Folder, removes any Name or Description constraint you may have specified, and refreshes the screen.
The Lookup Table Driver container presents a grid containing all of the Lookup Table Driver rules that meet your search criteria. The Lookup Table Driver summary grid offers several controls that allow you to perform different functions when a rule is selected.
To select a rule, click on a check box in the first column of the grid. More than one rule can be selected at a time but this will cause some of the controls to become disabled. Clicking on a check box a second time de-selects the rule.
You may select or deselect all of the Lookup Table Driver rules in the summary grid by clicking on the check box in the upper left-hand corner of the summary grid directly to the left of the Name column header.
· Add: Clicking on the Add control begins the process of building a new Lookup Table Driver rule. The Add control is disabled if any rows in the grid have been selected.
· View: Selecting a single row out of the grid enables the View control. Clicking on the View control allows you to view the contents of a Lookup Table Driver rule on a read-only basis. The View control is only enabled when a single Lookup Table Driver rule has been selected.
· Edit: Selecting a single row out of the grid enables the Edit control. Clicking on the Edit control allows you to modify a previously saved Lookup Table Driver rule. The Edit control is only enabled when a single Lookup Table Driver rule has been selected.
· Delete: Selecting one or more rows out of the grid enables the Delete control. Clicking on the Delete control deletes the Lookup Table Driver(s) you have selected. OFSAAI will not allow you to delete Lookup Table Driver rules which have any dependencies (see View Dependencies below).
· Copy: Selecting a single row out of the grid enables the Copy control. Clicking the Copy control allows you to create a copy of an existing Static Table Driver rule. The Copy control is only enabled when a single Static Table Driver rule has been selected. When you click on Copy, a Save As pop window will appear. Click Save after entering the Name, Description, Folder, and Access Type Details.
· View Dependencies: Clicking on the View Dependencies control generates a report on any Allocation rules that depend on the Lookup Table Driver rule you have selected. The View Dependencies control is only enabled when a single rule has been selected.
The following columns categorize each allocation rule in the summary grid:
· Name
· Creation Date
· Created By
· Description
· Last Modified Date
· Modified By
· Access Type
· Folder
Figure 119: Lookup Table Driver Summary Screen
When you Add, Edit, or View a Lookup Table Driver rule, the Lookup Table Driver Detail screen is displayed. In addition to Name, Description, Folder, and Access Type, the definition of a Lookup Table Driver includes the specification of a source table and a lookup table, the mapping of source columns to lookup table columns, a lookup table filter (optional), and a lookup table return column.
The Audit Trail container is a standard footer container for every OFSAA rule type. It displays Created By, Creation Date, Last Modified By, and Modification Date on the Audit Trail tab. The User Comments tab may be used to add comments to any rule, subject to a maximum of 4000 characters.
Specify the Lookup Table Driver rule's Name and Description, select a Folder in which the Lookup Table Driver rule is to be stored, and specify whether you want the Lookup Table Driver rule to be “Read/Write” or “Read Only” (Access Type). Naming your Lookup Table Driver rule is required before it can be saved. Default values for Folder and Access Type are stored in Application Preferences.
Figure 120: Lookup Table Driver Definition
Lookup Table Driver rules are used in conjunction with Allocation rules (of the Lookup Driver Table type) to match Instrument-level data with data from user-defined lookup tables. Each Instrument table row retrieved within the Allocation rule's Source definition is matched with your lookup table to return a lookup table factor. For each row, the resulting lookup table factor is arithmetically combined (typically multiplication) with the column specified in the Allocation rule's Source definition to update another column within the same row. A very typical use case might be the allocation of Loan Loss Reserves, Economic Loan Loss Provision, or Credit Risk Capital to each of your commercial loan instruments as a function of Product, Remaining Term to Maturity, and Credit Rating. Static Driver Table rules also support this kind of “matching”, but only for key processing dimensions (only for Product in this example). Lookup Table Driver rules extend the functionality of Static Table Driver rules by allowing you to also match on Instrument-level measures or attributes (Remaining Term to Maturity and Credit Rating in this example).
Additional examples of how you might use a Lookup Table Driver rule include the following kinds of assignments:
· Risk equity as a function of Product (a dimension), Division (a rollup point within a dimension), Credit Score (an instrument-level attribute), and Remaining Term to Maturity (also an instrument-level attribute)
· Loan Loss Reserve or Economic Provision (expected loss) as a function of Product (a dimension), Amortization Type (an instrument-level attribute), and Loan to Value Ratio (also an instrument-level attribute).
· Account Maintenance Expense as a function of Product (a dimension) and Current Net Book Balance (an instrument-level measure)
Select a Source instrument table and a Lookup table. The tables available in the Lookup drop-down list box are limited to tables that you have registered as Lookup Tables within your data model (see OFS Data Model Utilities User Guide for details on creating and registering Lookup Tables.)
Figure 121: Lookup Table Driver (New)
When you select a Lookup table, the Source – Lookup Mapping container (described in the following sections) responds by displaying one row for each lookup column within your registered lookup table.
Click the Lookup Table Data Information icon adjacent to the Lookup drop-down list to navigate to the Lookup Table Data Information page.
Figure 122: Lookup Table Data Information
The following is an example of the Lookup Table Data Information page. The actual columns will depend on the Lookup Table you selected.
· Adding a Row: Click the Add icon to add a new row to the selected Lookup table.
A new row will be added. Enter the values for the row and click Save.
· Deleting Rows: Click the Delete icon to delete the row(s) from the selected Lookup table.
· Excel Export/Import: Excel export/import functionality is used for adding/editing Lookup Table Data Information in Lookup Table Drivers. Click the following button to access the Export/Import functionality.
Lookup Table Driver rules support three types of matching:
· Range Lookup
· Exact Match
· Hierarchy Match
Figure 123: Source Lookup Mapping
One of your options in defining a lookup table is to define a minimum column and a maximum column to be employed in a “Range Lookup” against each row of selected instrument data. For example, you may wish to assign an Account Maintenance fee against certain checking account products as a function of balances ranges, example, one fee amount for accounts having balances between 0 and 1,000 and a different fee amount for balances between 1,000 and 5,000, and a third fee amount for accounts having balances greater than 5,000.
If the lookup table you chose in the “Source and Lookup Table” container (discussed above) includes range lookup columns, a Range Lookup row (i.e., a row whose Lookup Type is Range Lookup) will automatically be generated within the Source – Lookup Mapping container. Within this automatically generated row, select the source column that you want to compare to the Range Lookup columns from your lookup table. In the example described above, you might want to compare the Average Net Book Balance for each account with the range values from your lookup table. Range Lookup supports all instrument-level measures, attributes, and dimension members (numbers, dates, or strings).
NOTE:
Range Lookups require that your lookup data not have overlapping ranges that would lead to ambiguous lookup values.
An exact match is a literal database join. Exact Match supports all instrument-level measures, attributes, and dimension members (numbers, dates, or strings).
Similar to Static Table Drivers, Lookup Table Drivers allow you to match leaf values from a Source instrument table with any leaf member that belongs to a hierarchical rollup point of that leaf. You might have, for example, sets of driver statistics that vary by the region where regions can be defined as rollup points in an Organizational Unit hierarchy. If you had North, South, East, and West regions, you could store your lookup data in 4 regional sets. If there were 300 cost centers in the West region, by using Hierarchy Match functionality you avoid the repetition of 299 sets of otherwise identical driver data for the West region. Hierarchy matching is only supported for Key Processing dimensions.
You may constrain the data within your selected lookup table by applying a Lookup Table Filter. Chose No Filter, Data Filter, or Group Filter. If you have chosen either Data Filter or Group Filter, continue by selecting a Folder and a Filter Name. Note that the Filter Name drop-down list box will only display filters that are applicable to your chosen lookup table.
Figure 124: Lookup Table Filters
Specify the column within your lookup table from which to return a value for each lookup.
Figure 125: Lookup Return Value
Lookup tables may contain multiple lookup columns. For example, you may define a lookup table called Risk Factors that contains return columns for Credit Risk Factor, Operating Risk Factor, Economic Loan Loss Provision Factor, and Loan Loss Reserve Factor. In this example, you could subsequently define 4 separate Lookup Table Driver rules to be used within 4 separate Allocation rules (one Lookup Table Driver rule and one Allocation rule for each defined lookup column). In this example, each of your Allocation rules might utilize the same instrument column source (as defined in each Allocation rule's Source definition), for example, Average Balance.
As another example, you might define a lookup table called Expense Factors that contains return columns for Account Maintenance Expense, Account Origination Expense, ATM Transaction Unit Cost, and Check Processing Unit Cost. In this example, you might develop 4 Lookup Table Driver rules and 4 Allocation rules. Here, you would probably utilize different Source columns within your Allocation rule definitions. For maintenance expense and origination expense, you might choose to allocate a flat amount for each account (example, use the value of 1.00 for each account; you may accomplish this using Record Count as the Source column since the Record Count column within Instrument tables is typically set to 1). For ATM expense and Check Processing expense, you might utilize Instrument source columns of ATM Transaction Count and Number of Checks Processed.