Managing Lookup Tables

Objective

After completing this lesson, you will be able to create and populate a lookup table in SAP Incentive Management.

Lookup Tables

Lookup Tables are customized tables that house values based on multiple sets of criteria, where the output value represents the intersection of multiple dimensions.

Lookup Tables:

  • Contain a table of values based on multiple sets of criteria where the stored values represent the intersection of multiple dimensions.
  • Are also known as Multi-Dimension Lookup Tables (or MDLTs). They locate values as a result of the intersection of multiple dimensions or axes.
  • Are constructed using dimensions and indices.
  • Can be populated manually or through data loads.

The following image is an example. In this case, the Region, Products, and Sales Status are the Dimensions.

A diagram showing the relationship between dimensions, indices, and values in a lookup table.

This Lookup Table tells the system the commission rate is 4%, when the Region is EMEA, the Product is Bikes, and the Sales Status is Silver. The value returned can be used in the Incentive Rule.

Lookup Tables vs Rate Tables

Some similarities exist between lookup tables and rate tables. The following matrix shows the differences and similarities between the two and may help you decide when to use each one.

Rate TablesLookup Tables
Easy to set upComplex initial setup
Handles step commissionDoes not handle step commission
Only calculates commission rates based on attainment or a calculated resultCan calculate any numeric value based on any input of any data type including strings and categories
Can only be used in Incentive RulesCan be used in any rule or formula
Can be effective datedEach individual cell in the matrix can be effective dated

Best Practices for Lookup Tables

  • Map out your lookup table before creating them.
  • If you leave values null, check the box to make all null values zero or use conditioning to ensure a rule does not attempt to calculate using a null value.
  • Avoid many nested Lookup Table references. Keep them as simple as possible.
  • Use Lookup Tables when the list of indices is relatively static.

Exercise: Create a Lookup Table

Business Example:

In this exercise, you will create a lookup table that uses two different input values to calculate the bonus amount that should be paid to each participant. This will use two custom fields enabled in the Participant record: GA1: Sales Status and GN1: Bonus Level.

A Lookup Table with three dimensions is displayed.
ExerciseStart Exercise

Steps

  1. Select Compensation Elements → Lookup Tables from the Manage Plans tile.

  2. Select the Add icon and accept the default effective dates.

  3. Enter the standard fields:

    1. Name: LT_Bonus_Lookup.

    2. Return Type: USD.

    3. Treat Empty Values as Zero: Check this box.

  4. Enter a dimension for the sales status.

    1. Choose the Add icon in the Lookup Table Structure section.

    2. Enter the Dimension name Sales Status.

    3. Set the type to String.

    4. Select Finish.

  5. Add Indices to the Dimension with values GOLD, SILVER and BRONZE.

    1. In the Define Indices section, click the New icon.

    2. Enter the value GOLD.

    3. Leave the Effective Dates at the default and select Finish.

  6. Repeat these steps to add two more index values for SILVER and BRONZE, being sure to enter the values in all caps.

  7. Use the same steps as above to add a Dimension called Bonus Level with the following settings:

    1. Type: Numeric.

    2. Input Unit Type: Integer.

  8. Add the Index values for the Bonus Level dimension.

    1. Select on the new icon on the Define Indices window.

    2. Set the value to 1 and set the unit type to Integer.

    3. Repeat these steps to add the values 2, 3, and 4.

      The Structure tab of a lookup table, showing two dimensions called Sales Status and Bonus Level, and three indices for the Sales Status of Gold, Silver and Bronze.

    4. Select Save.

  9. Add the Values to the table.

    1. Select the Value tab.

    2. Select the Edit (pencil) button.

    3. Enter the values as shown in the table on the previous page.

    4. Save changes to the Lookup Table.

Summary

  • Lookup Tables hold values based on multiple criteria, and their output represents the intersection of multiple dimensions.
  • They can be used in any rule or formula, with individual cells having individual, effective dates.
  • As a best practice, always plan out lookup tables before creating them, ensure null values are checked, and avoid nested references.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn