6.3.5.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.
Lookup Table Drivers Details
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.
Figure 6-74 Lookup Table Drivers - Details tab
Source and Lookup Selection
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 6-75 Lookup Table Drivers - Source and Lookup Tables tab
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 6-76 Source - Lookup Mapping Grid
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.
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
You may constrain the data within your selected lookup table by applying a Lookup Table Filter. Choose 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 will only display filters that apply to your chosen lookup table.
Lookup Return Value
Specify a Column or an Expression within your lookup table from which to return a value for each Lookup.