OK, let’s start to find out the answer about one of the questions that we had in the last post.
"How can I get the value from the other application?"
The simple answer is... USE *LOOKUP/*ENDLOOKUP!
The simplest example is the currency conversion because you need to read rate value from the rate application to convert
currency values of the finance application.
(NOTE:*LOOKUP/*ENDLOOKUP also can be used for reading value of the current application.)
Here is the syntax of *LOOKUP/*ENDLOOKUP
The syntax is:
*LOOKUP {Application}
*DIM [{LookupID}:] {DimName}="Value" | {CallingDimensionName}[.{Property}]
[*DIM …]
*ENDLOOKUP
{Application} is the name of the application which you will retrieve value.
{DimensionName} is a dimension in the lookup application.
{CallingDimensionName} is a dimension in the current application.
{LookupID} is an optional variable that will hold the value so that you can use it in the script.
This is only required when multiple values must be retrieved.
Now, let's do it step by step.
Here are our requirements for the currency conversion.
1. You need to get the rate values from rate application for currency conversion (LC to USD and EUR).
2. The member id of RATE dimension in the rate application should be the same as RATETYPE property of the account dimension in the finance application.
3. The member id of RATEENTITY dimension in the rate application should be "DEFAULT"
4. The rule of currency conversion is 'DESTINATION CURRENCY/CURRENT CURRENCY'
First, you need to define *LOOKUP with application name.
*LOOKUP RATE
*ENDLOOKUP
Second, specify dimensions of RATE application with *DIM statement.
(Let's assume the rate application has RATEENTITY, INPUTCURRENCY, RATE, CATEGORY and TIME dimension.)
*LOOKUP RATE
*DIM RATEENTITY
*DIM INPUTCURRENCY
*DIM RATE
*DIM CATEGORY
*DIM TIME
*ENDLOOKUP
Third, assign the member id value of each dimension from the current application (Finance) or use fixed value.
If you need to retrieve multiple value according to different member id values of specific dimensions,
Make copies of that dimension and assign different values.
*LOOKUP RATE
*DIM RATEENTITY="DEFAULT" // Fixed value
*DIM INPUTCURRENCY="USD" // Fixed value
*DIM INPUTCURRENCY="EUR" // Fixed value, Copy same dimension for another value
*DIM INPUTCURRENCY=ENTITY.CURR // added one more for the currency conversion as variable value
*DIM RATE=ACCOUNT.RATETYPE // Variable value based on the current application
*DIM CATEGORY
*DIM TIME
*ENDLOOKUP
Fourth, Put variables for multiple retrieving values in front of each duplicate dimension name.
*LOOKUP RATE
*DIM RATEENTITY="DEFAULT"
*DIM DESTCURR1:INPUTCURRENCY="USD"
*DIM DESTCURR2:INPUTCURRENCY="EUR"
*DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
*DIM RATE=ACCOUNT.RATETYPE
*DIM CATEGORY
*DIM TIME
*ENDLOOKUP
-------------------------------------------------------------------------
Note: If you want to get some value based on two or more dimensions,
You should use the same variable name when you map dimensions.
Here is an example.
*LOOKUP OWNERSHIP
*DIM INTCO="IC_NONE"
*DIM PARENT="MYPARENT"
*DIM PCON:ACCOUNTOWN="PCON" // PCON is used for ACCOUNTOWN
*DIM PCON:ENTITY=ENTITY // PCON is used for ENTITY
*DIM IC_PCON:ACCOUNTOWN="PCON" // IC_PCON is used even though it searches same "PCON"
*DIM IC_PCON:ENTITY=INTCO.ENTITY // IC_PCON is used for INTCO.ENTITY
*ENDLOOKUP
Even though the member id of ACCOUNTOWN dimension is same, the variable should be defined as a different variable because the member id of ENTITY dimension is different in the combination.
If the 'ENTITY' property of INTCO dimension has I_FRANCE value, above *LOOKUP will select below two records and each variable will have different value.
IC_NONE,MYPARENT,PCON,FRANCE,100 => PCON
IC_NONE,MYPARENT,PCON,I_FRANCE,80 => IC_PCON
---------------------------------------------------------------------------
Last, Remove dimension names (TIME and CATEGORY> that don’t have any fixed value or variable value because it will be passed as current value
automatically.
*LOOKUP RATE
*DIM RATEENTITY="DEFAULT"
*DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
*DIM DESTCURR1:INPUTCURRENCY="USD"
*DIM DESTCURR2:INPUTCURRENCY="EUR"
*DIM RATE=ACCOUNT.RATETYPE
*ENDLOOKUP
Now we get the values so how can we use these values?
You can use it using LOOKUP(Variable) in your *REC statement as below
*WHEN ACCOUNT.RATETYPE
*IS "AVG","END"
*REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)
*REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EUR”)
*ENDWHEN
NOTE: You can use LOOKUP(variable) with *WHEN and *IS statement.
Ex) *WHEN LOOKUP(PCON) //as a condition value of when
*IS <= LOOKUP(IC_PCON) //as a value of IS
*REC(FACTOR=-1, PARENT ="MYPARENT",DATASRC="ELIM")
*ENDWHEN
We reviewed how to define *LOOKUP/*ENDLOOKUP statement and how to use it.
Now it is time to find out how it works in the script engine.
Let's assume below records are in the rate application and see what will happen during execute of the script logic.
RATEENTITY, INPUTCURRENCY, RATE, CATEGORY, TIME, SIGNEDDATA
DEFAULT, USD, AVG, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, AVG, ACTUAL, 2011.JAN, 1.22
DEFAULT, CHF, AVG, ACTUAL, 2011.JAN, 0.91
DEFAULT, USD, END, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, END, ACTUAL, 2011.JAN, 1.24
DEFAULT, CHF, END, ACTUAL, 2011.JAN, 0.93
RATCALC, USD, AVG, ACTUAL, 2011.JAN, 1
RATCALC, USD, AVG, ACTUAL, 2011.JAN, 1
Here are your current finance application records that need to be processed.
ACCOUNT, ENTITY, CATEGORY, TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC, 5000
REVENUE, SWITZERLAND, ACTUAL, 2011.JAN, LC, 1000
As you can see, there is no relationship between finance application and rate application.
We know Switzerland currency is CHF but there is no information in each fact table record.
It only has LC (local currency) value.
Then, how can script logic find the value and calculate it?
The key point is 'ENTITY.CURR' which we used it for mapping dimension as below.
*DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
ENTITY.CURR means 'CURR' property value of ENTITY dimension.
Therefore, Switzerland which is one of the Entity dimension member should have 'CHF' value in its 'CURR' property.
Same thing is for mapping RATE dimension of rate application as below.
*DIM RATE=ACCOUNT.RATETYPE
So the 'RATETYPE' property value of INVENTORY and REVENUE account should have 'AVG' or 'END' value.
Therefore, the Script engine will do the following steps to process the first record of the fact table.
1. Select RATEENTITY = "DEFAULT"
2. Select INPUTCURRENCY = "CHF” (because current Entity member's 'CURR' property value is 'CHF')
OR INPUTCURRENCY = "USD"
OR INPUTCURRENCY = "EUR"
3. Select RATE = "END” (because current account member's 'RATETYPE' property value is 'END')
4. Select CATEGORY = "ACTUAL” (There is no statement so it is same as current application CATEGORY value.)
5. Select TIME = "2011.JAN” (There is no statement so it is same as current application TIME value.)
All above selection will be combined with 'AND' condition.
So the 3 records below will be selected and its signeddata value will be assigned to each variable.
DEFAULT, USD, END, ACTUAL, 2011.JAN, 1 => DESTCURR1 will be 1
DEFAULT, EUR, END, ACTUAL, 2011.JAN, 1.24 => DESTCURR2 will be 1.24
DEFAULT, CHF, END, ACTUAL, 2011.JAN, 0.93 => SOURCECUR will be 0.93
After the script logic engine executes below statements, it will generate 2 records.
*WHEN ACCOUNT.RATETYPE
*IS "AVG","END"
*REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)
*REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EUR”)
*ENDWHEN
ACCOUNT, ENTITY, CATEGORY, TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC, 5000
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, USD, 5376.34 // 5000 * (1/0.93)
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 6666.67 // 5000 * (1.24/0.93)
For the 2nd record in the fact table, the 3 records below will be selected from the rate application fact table because
Revenue account has 'AVG' RATETYPE.
DEFAULT, USD, AVG, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, AVG, ACTUAL, 2011.JAN, 1.22
DEFAULT, CHF, AVG, ACTUAL, 2011.JAN, 0.91
After it processes 'REVENUE' records, there will be 6 records in the fact table as below.
(4 records will be generated in total.)
ACCOUNT, ENTITY, CATEGORY, TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC, 5000
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, USD, 5376.34
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 6666.67
REVENUE, SWITZERLAND, ACTUAL, 2011.JAN, LC, 1000
REVENUE, SWITZERLAND, ACTUAL, 2011.JAN, USD, 1098.90 // 1000 * (1/0.91)
REVENUE, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 1340.66 // 1000 * (1.22/0.91)
We finished learning how to use *LOOKUP/*ENDLOOKUP statement.
Here are some questions and answers that I got frequently.
Question 1: What if rate application doesn't have the value?
Then currency conversion will not happen.
Question 2: I don't have any records in the fact table of current application. What will happen?
The script logic always reads records from the current application.
Therefore, if there are no records in the fact table of the current application,
Nothing will happen.
Question 3: Can we lookup parent member value instead of base member (leaf member)?
MS version can do it with *OLAPLOOKUP statement instead of *LOOKUP but NW version doesn't have it yet.
I will explain about *FOR/*NEXT in the next post.
