5.3.7.1.1 Navigation in the Detail 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 or a lookup table expression (both filters and expressions are optional), and a lookup table return column.
The Audit Trail pane is a standard footer pane for every OFSAA rule type. The Audit Trail pane displays the following sections – Audit, Comments and Tags.
The Audit tab contains the audit data of an object as:
- Created By
- Created Date
- Modified By
- Modified Date
- Authorized By
- Authorized Date
The Comments tab shows the existing comments for the object. Only the latest comment is editable, and the deletion of existing comments is not allowed. Users can add new comments for the current object.
The Tags tab shows the tags associated with the object. We can add new tags or remove the existing tags.
Lookup Table Driver Details Pane
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 you save it. The default values for Folder and Access Type are stored in User Preferences.
Source and Lookup Selection Pane
Select a Source table and a Lookup table. The source table list is limited to the instrument tables under PBSMCS. The tables available in the Lookup drop-down list are limited to lookup tables that have been created through the Lookup Table user interface.
Figure 5-82 Source and Lookup Tables Pane
Lookup Table Data Screen
Click on the Table Data button in the Source and Lookup Selection pane to invoke the ‘Lookup Table Data’ window that displays the lookup table columns and the data contained in the lookup table. The actual columns of the Table Data depend on the Lookup Table selected.
Figure 5-83 Lookup Table Data
The Lookup Table Data screen allows several operations on the selected lookup table data.
- Enter the number of rows you want to add, and then click the Add icon to add the number of new rows to the Lookup table.
- Once the new rows are added, enter the values for the rows and click Save. This saves the values of the rows in the database for the lookup table, and then closes the screen.
- Alternatively, the user can click on Apply to save the values of the rows in the database for the lookup table but does not closes the screen.
- Click the Delete icon to delete a row(s) from the Lookup table.
- Click the Export or the Import icons to access the Export or Import functionality. While exporting, the data from this screen is exported as a .xls file.
Source – Lookup Mapping Grid
When you select a Lookup table in the Source and Lookup Selection pane, the Source – Lookup Mapping Grid responds by displaying one row for each lookup column within your selected lookup table. Thus, the number of rows in the Mapping Grid is dynamic and gets structured based on the columns present in the selected lookup table.
Lookup Table Driver rules support three types of matching:
- Range Lookup
- Exact Match
- Hierarchy Match
Figure 5-84 Source - Lookup Mapping Grid
The mapping grid container has a table structure consisting of four columns. The grid maps the columns from the selected lookup table with the columns from the selected source instrument table. The column Hierarchy Level is used only for Hierarchy Match lookup type and stores the folder name containing the hierarchy, the hierarchy’s name, and the hierarchy level name.
The Lookup types in the first column gets inherited from the Lookup table selected. An Exact Match displays all the columns of the source table in the second column of the grid, the Source Table Column dropdown. Similarly, a Range Lookup type displays all the columns of the source table in the Source Table Column drop-down. A Hierarchy Match displays only the dimension columns of the source table in the Source Table Column dropdown.
- Range Lookup: One of the options in defining a lookup
table is to define a minimum column and a maximum column that you can employ 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, such as 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” pane includes range lookup columns, a Range Lookup row (this is a row whose Lookup Type is Range Lookup) is automatically generated within the Source – Lookup Mapping pane. 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. - Exact Match: An exact match is a literal database join. Exact Match supports all instrument-level measures, attributes, and dimension members (numbers, dates, or strings).
- Hierarchy Match: 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 region where regions are 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 four 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 supports only Key Processing dimensions.
Lookup Table Filters Pane
Figure 5-85 Lookup Table Filters Pane
Lookup Return Value
Figure 5-86 Lookup Table 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 four separate Lookup Table Driver rules to be used within four 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 four Lookup Table Driver rules and four 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 (for 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.
Note:
Lookup table driver supports all types of expressions as return types except any expression involving an aggregate function.