Overview
This is the third post in a series on developing Essbase CDFs and using Java API for Essbase. Prior posts were:
- Essbase CDF Workshop Part I. We saw how to change sample CDF provided by Oracle, recompile the file, and register function with Essbase server.
- In Essbase Scrambler we saw how to query essbase hierarchies, update member properties and scramble data and metadata in files.
Here I’m going to show a use case of script troubleshooting and optimizing with CDFs. If you have some other use cases for the next post, please let me know. You can use Essbase Scrambler (download here) if you want to send me your outline or sample data.
Calculation Troubleshooting
Sometimes calculation results just don’t make sense. You stare at the code, it should work one way, but it works in totally different way. In all programming languages you can output the current state of your objects, variables, etc into a log file. It’s not so easy with Essbase calc scripts though. Essbase provides SET MSG command, which is helpful, but very high level, difficult to read, and does not provide specific information from specific place in your script. It would be much easier to troubleshoot our script if we could write into log specifically what we need.
Ok, so let’s start with prerequisites:
You need to install and register either the original sample jar for strings manipulation, or the modified one discussed in Essbase CDF Workshop Part I.
You need to install and register either the original sample jar for strings manipulation, or the modified one discussed in Essbase CDF Workshop Part I.
I’ll use the following example that Tim Faitsch brought up on one of the forums (Big thanks to Tim for this example, and allowing me to use it!). Apparently this calculation produces unexpected result:
FIX("Jan","COGS","100-10"){
"Actual"(
IF("Florida" == 1)
"New York" = "Connecticut";
"Massachusetts" = "New Hampshire";
ELSEIF("Florida" == 2)
"Massachusetts" = "Connecticut";
"Connecticut" = #MISSING;
ELSEIF("Florida" == 3)
"Massachusetts" = "Connecticut";
"Connecticut" = 500;
)
}
|
The script is based on Sample.Basic, and as Tim was describing it: “If you set Florida to 1 and run it, both New York and Massachusetts get updated with the correct values. If you set Florida to 2, both Massachusetts and Connecticut end up as #MI no matter what value is originally in Connecticut. If you set Florida to 3 then both Massachusetts and Connecticut are set to 500 even if Connecticut is not 500 to begin with.”
Lets replicate the issue, and submit the following data:
Cola
|
Actual
|
Jan
| |
COGS
| |||
New York
|
1
| ||
Massachusetts
|
2
| ||
Florida
|
3
| ||
Connecticut
|
4
| ||
New Hampshire
|
5
| ||
California
|
6
| ||
Oregon
|
7
| ||
Washington
|
8
| ||
Utah
|
9
| ||
Nevada
|
10
| ||
Texas
|
11
| ||
Oklahoma
|
12
| ||
Louisiana
|
13
| ||
New Mexico
|
14
| ||
Illinois
|
15
| ||
Ohio
|
16
| ||
Wisconsin
|
17
| ||
Missouri
|
18
| ||
Iowa
|
19
| ||
Colorado
|
20
| ||
Market
|
Florida=3, so we expect Massachusetts =4 (Initial value of Connecticut). After we run the script we get this:
Cola
|
Actual
|
Jan
| |
COGS
| |||
New York
|
1
| ||
Massachusetts
|
500
| ||
Florida
|
3
| ||
Connecticut
|
500
| ||
New Hampshire
|
5
|
Well, doesn’t seem right. If you add SET MSG DETAIL to the top of that script you can get a hint on why this is happening. But let’s debug this with our CDF and see exactly what’s going on. We use JechoString to output current market, then values of Massachusetts and Connecticut before calculation, and after.
//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
set msg summary;
FIX("Jan","COGS","100-10"){
"Actual"(
IF("Florida" == 1)
"New York" = "Connecticut";
"Massachusetts" = "New Hampshire";
ELSEIF("Florida" == 2)
"Massachusetts" = "Connecticut";
"Connecticut" = #MISSING;
ELSEIF("Florida" == 3)
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Current Market: ",@NAME(@CURRMBR("Market"))));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Before calc Massachusetts: ",@JgetStringFromDouble("Massachusetts",@_false,@_false)));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Before calc Connecticut: ",@JgetStringFromDouble("Connecticut",@_false,@_false)));
"Massachusetts" = "Connecticut";
"Connecticut" = 500;
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","After calc Massachusetts: ",@JgetStringFromDouble("Massachusetts",@_false,@_false)));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","After calc Connecticut: ",@JgetStringFromDouble("Connecticut",@_false,@_false)));
)
}
|
Current Market: New York
Before calc Massachusetts: 2
Before calc Connecticut: 4
After calc Massachusetts: 4
After calc Connecticut: 500
Current Market: Massachusetts
Before calc Massachusetts: 4
Before calc Connecticut: 500
After calc Massachusetts: 500
After calc Connecticut: 500
Current Market: Florida
Before calc Massachusetts: 500
Before calc Connecticut: 500
After calc Massachusetts: 500
After calc Connecticut: 500
Current Market: Connecticut
Before calc Massachusetts: 500
Before calc Connecticut: 500
After calc Massachusetts: 500
After calc Connecticut: 500
....
Current Market: Colorado
Before calc Massachusetts: 500
Before calc Connecticut: 500
After calc Massachusetts: 500
After calc Connecticut: 500
|
Now the results sort of make sense. Since there was no Market in the FIX, the calculation block was running for every market. So the calculation produced intended result for the first iteration (After calc Massachusetts: 4, After calc Connecticut: 500). Starting from second iteration it produced correct, but unintended results, since Connecticut was already assigned 500. If you put any member of the market dimension into FIX this will fix the issue. Lets try this out:
//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
FIX("Jan","COGS","100-10","Market"){
"Actual"(
IF("Florida" == 1)
"New York" = "Connecticut";
"Massachusetts" = "New Hampshire";
ELSEIF("Florida" == 2)
"Massachusetts" = "Connecticut";
"Connecticut" = #MISSING;
ELSEIF("Florida" == 3)
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Current Market: ",@NAME(@CURRMBR("Market"))));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Before calc Massachusetts: ",@JgetStringFromDouble("Massachusetts",@_false,@_false)));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Before calc Connecticut: ",@JgetStringFromDouble("Connecticut",@_false,@_false)));
"Massachusetts" = "Connecticut";
"Connecticut" = 500;
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","After calc Massachusetts: ",@JgetStringFromDouble("Massachusetts",@_false,@_false)));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","After calc Connecticut: ",@JgetStringFromDouble("Connecticut",@_false,@_false)));
)
}
|
Now if you retrieve you can see that… data remained the same. It didn’t work. So what happened? Let’s go to our /home/oracle/Documents/testJecho.txt file again. It didn’t change at all. Meaning the calculation for our block "100-10","Market" never happened. And here you need to recall that if your block doesn’t exist it will not be calculated unless you do something about it. One possible fix would be to add SET CREATENONMISSINGBLK ON; before the script (which is usually not the best way to deal with missing blocks).
//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET CREATENONMISSINGBLK ON;
FIX("Jan","COGS","100-10","Market"){
"Actual"(
IF("Florida" == 1)
"New York" = "Connecticut";
"Massachusetts" = "New Hampshire";
ELSEIF("Florida" == 2)
"Massachusetts" = "Connecticut";
"Connecticut" = #MISSING;
ELSEIF("Florida" == 3)
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Current Market: ",@NAME(@CURRMBR("Market"))));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Before calc Massachusetts: ",@JgetStringFromDouble("Massachusetts",@_false,@_false)));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","Before calc Connecticut: ",@JgetStringFromDouble("Connecticut",@_false,@_false)));
"Massachusetts" = "Connecticut";
"Connecticut" = 500;
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","After calc Massachusetts: ",@JgetStringFromDouble("Massachusetts",@_false,@_false)));
@JechoString(@LIST("filename","/home/oracle/Documents/testJecho.txt","After calc Connecticut: ",@JgetStringFromDouble("Connecticut",@_false,@_false)));
)
}
|
Now we get the intended result:
Cola
|
Actual
|
Jan
| |
COGS
| |||
New York
|
1
| ||
Massachusetts
|
4
| ||
Florida
|
3
| ||
Connecticut
|
500
|
And the output in the /home/oracle/Documents/testJecho.txt file contains
Current Market: Market
Before calc Massachusetts: 2
Before calc Connecticut: 4
After calc Massachusetts: 4
After calc Connecticut: 500
|
So the correction to the sentence above would be “Since there was no Market in the FIX, the calculation block was running for every market with existing block.”
Keep in mind that we would probably never realize we have this issue, without this recursive logic between Massachusetts and Connecticut. If our script contained just this:
//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET CREATENONMISSINGBLK ON;
FIX("Jan","COGS","100-10","Market"){
"Actual"(
IF("Florida" == 1)
"New York" = "Connecticut";
"Massachusetts" = "New Hampshire";
)
}
|
It would always create the expected result, despite the fact it could run 20 times faster (repeating calculation 20 times - once for each Market, instead of once). That’s why it is important to monitor execution of your script even if it behaves as expected.
No comments:
Post a Comment