I wrote a short side-note in a previous post that CREATEBLOCKONEQ
did not seem to be working correctly. I had a Linux server with Essbase 11.1.2.3 (no patches at the time), but now has Essbase patched to 11.1.2.3.501. The issue still exists with "501". After a few exchanges with the Oracle support team, the issue has been passed over to the development team for confirmation of whether or not there is a bug in Essbase with CREATEBLOCKONEQ. I did get a workaround from the support team so I thought I would share that here in case you run into the same issue.
The Behavior
Suppose you have a calculation script where you are assigning values to a sparse dimension member but you only want to assign values to blocks that already exist. (Assume CREATEBLOCKONEQ is set to OFF) In the example below I started with an empty Sample.Basic cube. I loaded data to California to create one data block. After running the following calculation I end up with two data blocks (and "New York" has the same data as California).
SET UPDATECALC OFF;
FIX (@RELATIVE("Product",0))
SET CREATEBLOCKONEQ OFF;
"New York" = "California";
ENDFIX
What I see is Essbase creating blocks whether or not this setting is set to OFF at the calculation script level or is unchecked at the database level as shown below. I tried various combinations of non-constant value assignments and each case did the same thing.
Database Settings for Essbase for Sample.Basic BSO cube.
Given either of the above two options for disabling block creation on equation, I was expecting that Essbase would run my calculation without creating any new blocks. If you run into the same behavior, here is a workaround provided by Oracle support that you can try.
The Workaround
The workaround requires that you add an additional SET command to your calculation script to force a bottom-up calculation. Hopefully this will work if you run into the same issue.
SET FRMLBOTTOMUP ON;
SET UPDATECALC OFF;
FIX (@RELATIVE("Product",0))
SET CREATEBLOCKONEQ OFF;
"New York" = "California";
ENDFIX
I hope this helps and I will update this post when I hear back from Oracle support / development on whether or not this is a bug in Essbase. I'm just curious, if you have seen this issue on a Windows environment please send me a note or post a comment here and let me know.
Cheers!