*LOOKUP / *ENDLOOKUP

Allowed uses: By Commit, SQL

This set of instructions can be used with a WHEN/ENDWHEN structure to retrieve (lookup) some other values that may be needed either in the calculation of the new value or to define some criteria to be evaluated. The lookup can be performed in the current application or into a different application.

The lookup mechanism defines a relationship between the current record being processed and another record in a corresponding user-defined record set. For example, in a currency translation, you may want to identify, in the RATE application, the value of the rate for the current entity, category, and period.

The syntax is:

*LOOKUP {App}

*DIM [{LookupID}:] {DimensionName}="Value" |

{CallingDimensionName}[.{Property}]

[*DIM …]

*ENDLOOKUP

In the above example, the lookup retrieves three different values from the INPUTCURRENCY dimension (the rate of the currency of the current entity, the rate of the currency EURO, and the rate of the currency USD). Each of these values has been assigned a specific identifier (SOURCECURR, DESTCURR1 and DESTCURR2) that are used somewhere in the WHEN/ENDWHEN structure.

Any dimension not specified in the lookup instruction is assumed to match with a corresponding dimension in the source application. In the above example, the following instructions have been omitted, because redundant:

*DIM CATEGORY=CATEGORY

*DIM TIME=TIME

In the following example, a currency translation in the two reporting currencies USD, and EURO is performed.

// ----------------------------------------------- Get the rates

*LOOKUP RATE

*DIM ENTITY2="DEFAULT"

*DIM RATE=ACCOUNT.RATETYPE

*DIM SOURCECURR:INPUTCURRENCY=ENTITY.CURR

*DIM DESTCURR1: INPUTCURRENCY="USD"

*DIM DESTCURR2: INPUTCURRENCY="EURO"

*ENDLOOKUP

// ----------------------------------------------- Translate

*WHEN ACCOUNT.RATETYPE

*IS "AVG","END"

*REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)

*REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EURO”)

*ELSE

*REC(CURRENCY=”USD”)

*REC(CURRENCY=”EURO”)

*ENDWHEN

*COMMIT

// ---------------------------------------------------------------------------------

Below is a different example of how a LOOKUP amount can be used to define a WHEN criteria. In this case, what is being tested is an amount (corresponding to a consolidation METHOD) in the lookup application. (The logic is a simplified version of a real one. Take it only as an example of valid syntax)

Finally, a LOOKUP keyword can also be used as part of an *IS statement, as shown in the following example:

The *WHEN instruction can also take a property as a parameter of one of the dimensions of the application against which a *LOOKUP has been performed, even if such dimension does not exist in the current application.

In the following example, a currency translation checks for the MD field of the source currency, to decide what formula to apply to the rate (Multiply or Divide):

The LOOKUP instructions must define the link between the dimension referenced to in the WHEN statement and one of the dimensions of the current application. In the above example the logic understands automatically that it needs to evaluate the MD field of the InputCurrency matching the Currency of the current Entity.

MDX-based LOOKUP

The *LOOKUP / *ENDLOOKUP structure generates an SQL query, and, as such, is unable to return values calculated by the OLAP application. To overcome this limitation a different version of the LOOKUP instruction is available. This instruction generates an MDX query and, as a result, allows the retrieval of any value available in the application.

The syntax is:

*OLAPLOOKUP [{application name}]

All other instructions specified inside the structure remain the same as a regular LOOKUP instruction.

The above example is able to retrieve the REVENUE account of entity SALESEUROPE, even if one or both these members are parent or otherwise calculated by the application.

This feature, although less efficient than an SQL-based LOOKUP, can be particularly useful for accessing aggregated data that need to be used in the factor of an allocation logic. Below is an example of allocation logic where the expense HUMAN_RES_EXP incurred by the entity CORP_SERVICES is allocated to all children of EUROPE based on their number of employees (HEADCOUNT):