6.3.4.2.3 Lookup Table Column Types

A Range Lookup column type is to be used for columns that is meant to store a range of value from a minimum value to a maximum value. The minimum and the maximum values are stored in two separate Lookup columns in a Lookup column pair. The logical condition that relates to this column pair of values is: Range Lookup Minimum value =< matching instrument data <= Range Lookup Maximum value.

Range Lookups require that your lookup data do not have overlapping ranges as that would lead to ambiguous lookup values.

An Exact Match is a literal database join and is used for columns meant to store a single value that is to be related as: matching instrument data = Exact Match column value. Exact Match supports all instrument-level measures, attributes, and dimension members (numbers, dates, or strings).

A Code Match is to be used for columns that store the code (VARCHAR) value of an instrument attribute, such as a Product Code, GL Account Code, or Org Unit Code, rather than the attribute’s internal numeric identifier. A Code Match performs a literal match between the code value in the source instrument table and the code value stored in the Lookup table.

A Hierarchy – ID Match is to be used for columns that would store dimension leaf member values. A Hierarchy Match of a Lookup table Driver lets you to match on dimension member values from a source instrument table with any leaf member that belongs to a hierarchical rollup point of that leaf. Hierarchy matching supports only Key Processing dimensions.

A Hierarchy – Code Match combines the two behaviors above: it is used for dimension columns identified by their code value, and lets you match a source instrument’s dimension member code against any leaf member code that belongs to a hierarchical rollup point. As with Hierarchy Match, Hierarchy-Code Match supports only Key Processing dimensions. Use Hierarchy-Code Match instead of Hierarchy Match when your lookup data identifies dimension members by code rather than by leaf member value.

A Lookup Table may contain one or multiple Return Columns. The Return columns are meant to hold the values that are expected to be returned in response to a match arrived with the source instrument table. The return values expected in the return columns are, in most cases, numeric values.

A Lookup table may contain any number of rows, each row having the values of the different column types used, including the return column types. A Lookup table does not hold a duplicate row.

Auto Partition - Whenever an Allocation Lookup table is created with As-of-Date column present in it, the table gets registered with a Partition created on the As-of-Date column. This Auto-Partitioning is to support the Data Housekeeping/ Data Retention "Drop Partition" policy on these tables.

User can input data into a Lookup table via two ways. One is that user can use the Lookup Table Data screen and type in the data directly in the GUI, or can also use the Export/Import functionality in the Lookup Table Data screen. The other is that user can use the scheduler-based data loading and deletion capabilities of a Lookup Table.