We all have dealt with Block creation issues (and we’ve solved it using DATACOPY, @ALLOCATE, SET CREATEBLOCKONEQ, SET CREATENONMISSINGBLK, even doing a lock and send)
After 20+ years a dedicated function for creating blocks, yes we should be excited about it.
However it turns out not so exciting (Maybe I’m doing it wrong, maybe techref didn’t capture the usage correctly, maybe,……)
So I started out testing my favorite command.
This script shows us the typical block creation issue.
“Actual”(
IF(@ISCHILD(East))
100;
ENDIF
)
ENDFIX
After running the script I don’t expect to see any values. (I’m not proven wrong, no surprises here)
FIX(Jan,Sales,”100-10″,@RELATIVE(Market,0))
“Actual”(
IF(@ISCHILD(East))
@CREATEBLOCK (@CHILDREN(East));
100;
ENDIF
)
ENDFIX
What I liked about it is you can use it in an IF statement (at least syntax is correct)
I ran it expecting a magic, miracle,……(nada). No EXCEL returned all #Missing (no blocks created) 🙁
In order to prove that the function is doing something I turned on Messaging in calcs. Still nothing that says blocks are created (or copied)
Going back to basics
I started suspecting my scripting skills and then decided to lock and send a value to a single intersection.
Before lock and send
After lock and send
Now the puzzle starts 😉
Run the script again. You won’t believe what you see.
It created the blocks (East has 5 children, 5 blocks). It shows up in the log also that it did create blocks.
Hold the excitement right there. Let’s see what is the result in SmartView.
BUMP, it just calculated one single block….so where are those 4 blocks….. I think we’ll have to wait more time so see how it works (if it works)
All this started from because of this thread in OTN (https://forums.oracle.com/forums/thread.jspa?threadID=2531198&tstart=0)
Cameron Lackpour pointed out something (But creating blocks at those other sparse members isn’t the same thing as valuing them)
That made me change the calc to what is given below.
FIX(Jan,Sales,”100-10″,@RELATIVE(Market,0))
“Actual”(
IF(@ISCHILD(East))
@CREATEBLOCK (@CHILDREN(East));
ENDIF
)
“Actual”(
IF(@ISCHILD(East))
100;
ENDIF
)
ENDFIX
So first block for creating block and then second for calculating….. Ta da
It works, it works (however it requires 1 block for creating the rest)
Update 3rd May 2013 1:10 AM
Gabby Rubin commented on the same OTN thread where he explained how it behaves.
“Using a dense member in the formula will use a bottom-up approach which will create new blocks based on the existing ones. If you want to create the blocks in the top-down approach (similar to CREATEONMISSINGBLK), you should use a sparse member (but not the same one as the one in the @CREATEBLOCK formula)”
FIX(Jan,Sales)
“100-10”
(
@CREATEBLOCK (@CHILDREN(East));
)
ENDFIX
It created 5 blocks (for 5 children of East). I can sleep now.
Celvin,
Doesn't East have five children? New York, Massachusetts, Florida, Connecticut, and New Hampshire? Wouldn't that correspond to the five blocks in question?
It appears as though that bit of it is working as advertised.
Regards,
Cameron Lackpour
@Cameron
I made it work via a dumb way. Not sure why it is so.
However it still requires a single block in the database.
As far as I am concerned it is another useless function. The problem with block creation has never been creating the block itself. Block creation issues cause problems because of the time it takes to actually create the block, and block creation is prevented by using FRMLBOTTOMUP.
Unless this function works successfully with SET FRMLBOTTOMUP ON – it will never be used.
@anon, yes you are true, there was always different ways to create the block. GR confirmed that it doesn't work with bottom up approach.
That was proved when i used a dense member in the block
Seems to me that you should then be able to just add the 100 as a +100 to the @Createblock section, eliminating the dual statements for create/set. Perhaps not a significant gain in this example, but in certain places it should help a lot.
-DougWare as Anon.
Ultimately as I stated on May 2, this function is not only useless, but it's in fact harmful. It will cause more problems than it solves. Unknowing consultants/admins will try to use it, and end up blowing up their cubes and will end up creating potentially millions of empty blocks, thereby severely degrading performance.
If you utilize this function, you HAVE to run a CLEARBLOCK EMPTY afterwards.
So in reality – Oracle did junior consultants and companies that own the software a large disservice by introducing this function. They need to insert a disclaimer or warning in the techref about the risks, otherwise whenever this is used improperly (which will probably be most of the time) – it's going to cause a lot of downstream pain.
Perfectly agree on that part. I've seen people using NONMISSINGBLOCK ON and create blocks for all possible combinations.
If used wisely it can solve some issues(however I prefer using other methods like DATACOPY or Allocate), if not can create more problems
Thank you ..