@CREATEBLOCK – Surprises in Essbase Part IV 8


I was really excited about these new functions in Essbase.

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.

FIX(Jan,Sales,”100-10″,@RELATIVE(Market,0))

“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)

Techref says use @CREATEBLOCK under a FIX (If you use it without opening a block you get this error “Error: 1012000 Invalid Syntax – Not a CALC command [@CREATEBLOCK]”)

So I changed the script to

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)

Update May 2nd 2013 1:33 PM

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)”

I was prejudiced against that approach (because it is a sparse assignment). However Cameron added some sense to the prejudiced mind and we have a script which works on a blank database.
 
So this was the modification.
 

FIX(Jan,Sales)
“100-10”
(
@CREATEBLOCK (@CHILDREN(East));
)
ENDFIX

It created 5 blocks (for 5 children of East). I can sleep now.


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

8 thoughts on “@CREATEBLOCK – Surprises in Essbase Part IV

  • Cameron Lackpour

    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

  • Anonymous

    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.

  • Celvin Kattookaran

    @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

  • Anonymous

    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.

  • Anonymous

    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.

  • Celvin Kattookaran

    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