Want to know The Truth About CPM?

03 January 2011

Stupid Programming Tricks #6


Number Six in my Stupid Tricks collection. 

Should I be worried that so much of what I know falls into this category?  I’ll Keep Calm, and Carry On and hope that one day I’ll get smart.  Ah, but a man’s reach should exceed his grasp, Or what’s a heaven for?

Here’s a block, there’s a block, where are MY blocks?

Block creation is sort of the Achilles heel of BSO calc script writing.  We have all written an allocation script (or two, or three, or four) that looks good but when we run it – nothing, naught, nada, zip, zero, zilchAfter a hopefully quick sanity check to ensure that the data assignment and the Excel retrieve are pointing to one and the same data set, the answer is almost always…block creation, the bane of calc scripts.

Happily, there are lots of ways to create blocks, including sparse assigns, crossdims on the left hand side of the equal sign within a calc block, SET CREATEBLOCKONEQ, the sometimes performance-sapping SET CREATENONMISSINGBLKS, and of course, the old standby DATACOPY.  11.1.2 has its new and nifty XWRITE function which you can combine with a LOOPBACK function.  Oooh, tricky.

But it’s DATACOPY that is the surefire, never-fails, gold standard way to create blocks.  And it is.  Until you remove the blocks, especially when you think you didn’t.  Arrrgh.

That old devil CLEARDATA

Quick quiz -- what does CLEARDATA do?  Why, it clears data.  Duh.

And what doesn’t it do?  Remove blocks.

Sez who?  Our friend the TechRef:
CLEARDATA does not clear blocks, even if all data values in a block are #MISSING. Use CLEARBLOCK if you wish to clear blocks from the database, which can improve performance.

What does CLEARDATA have to do with DATACOPY?  Well, typically after copying data to create blocks, clearing out the data at the target is a good idea as the code that uses the blocks may or may not touch all of the DATACOPY targets.  This may seem inefficient, but given the speed of DATACOPY (usually), this isn’t an issue.

Watch out for that documentation

I was working on an allocation and used that the never-fails, sure-shot, good-as-gold block creator, DATACOPY.

But something odd was happening – the script was running, but I wasn’t getting any results.  What oh what could it be?  Surely not a block creation issue as I was, after all, using DATACOPY to create the blocks and then CLEARDATA to remove the data values. 

Happily, my client, Dave Anderson, is smarter than I am (NB – Consultant  trick #1 – somehow convince the client you’re not an idiot, make sure he’s smarter than you, have him solve the problems, you blog about the solution.) and suggested that the blocks I thought I was creating in fact weren’t really there.

I will admit a certain degree of skepticism because I knew CLEARDATA does not clear blocks, even if all data values in a block are #MISSING.  (NB – Consultant trick #2, when nothing works, and the client makes a suggestion, at least go through the motions of listening to him.  You might be surprised.

I ran just that segment of the code and did a before and after block count.  Yes, I cleared out the entire database and ran everything from scratch and stopped right before this step; that was my initial block count.  Then I ran the code and checked the block count in EAS.  Uh-oh, same number of blocks.  Commenting out the CLEARDATA gave me…a higher block count.  Double uh-oh and a what the ? moment.

MVFEDITWWW, aka Sample.Basic

My Very Favorite Essbase Database In The Whole Wide World is the place I prove out BSO concepts. 

Sample.Basic’s density


Yeah, it’s weird, particularly the dense Scenario dimension at the end.  But the density setting of Scenario will be important as we’ll shortly see.

Are you sitting comfortably? Then I'll begin.

Lock and load

I created a copy of Sample.Basic, cleared it out, and populated the following spreadsheet.
If this is the only data in the database, how many level zero blocks do you think there are?  You get a gold star if you say, “One, and duh, what else could there be?”

Fire for effect

Now run this code:

How many upper level blocks?  Eight – take a look at the spreadsheet below and count the highlighted rows:

I know, I know, block creation 101. But this is important and there’s a test at the end of the semester, so let’s review:  You had one block from the lock and send and eight blocks from the CALC ALL. 

A change is as good as a rest

Bringing DATACOPY and CLEARDATA in, let’s create a block and then clear out the data



FIX(@LEVMBRS("Market", 0))
  DATACOPY "Actual"->"Product" TO "Budget"->"100-10" ;
    CLEARDATA "100-10" ;

Data is copied to Budget->100-10.  Does this increase the block count?  Nope, Budget is dense and data already exists at Actual->100-10, so the DATACOPY doesn’t add a block.

The number of blocks is now nine – one from the lock and send, eight from the aggregation in CALC ALL, and zero from the DATACOPY as the block at 100-10->Budget already exists.

CLEARDATA does just what it ought to – remove the value of Actual->Product from Budget->100-10 and nothing more.  It sure doesn’t remove the block.

Stop making sense

So let’s make this all blow up/work just like most Essbase databases and make Scenario sparse, not dense.

In theory, the database will now have 10 blocks:
1 level 0 from a lock and send
8 upper from the CALC ALL
1 additional 0 from the DATACOPY

Clear out the database, send that first sheet, and then run the code with the CLEARDATA.

What happens?  Do we in fact have 10 blocks?  Nope, because a CLEARDATA when dealing with purely sparse dimensions…removes blocks.  Well, it also clears out the data, which is after all sort of implied in its name.


Fix it in one step

Get rid of the CLEARDATA and replace it with =#Missing.  Yes, it’s that easy.



FIX(@LEVMBRS("Market", 0))
  DATACOPY "Actual"->"Product" TO "Budget"->"100-10" ;
      "100-10" = #Missing ;

Run it all again and ta-da, 10 blocks.  Whew. 

Yes,  assigning  a value of #Missing is slower than CLEARDATA.  But it works, and in the end that’s what counts.


This was a real stinker to figure out.  CLEARADATA not removing blocks is (was) such a basic piece of my BSO knowledge I refused to believe my eyes when my code didn’t work. 

So this was a salutary lesson in observing and questioning.  And oh yes, listening to your client as well. 


Unknown said...

Did you know that if you do a data copy from a dynamic calc member to a stored member it won't create the blocks? I suppose since there isn't a block to copy from, it can't create a new one?

Cameron Lackpour said...


Yes, I am aware of that, and I think your explanation about there being no block to copy is spot on.

I usually DATACOPY the constituent stored parts of the formula -- a pain, but if you copy all of them (some may be missing) you'll get the target sooner or later.



Anonymous said...

I will bet if you ran the same calc on the same database in versions 2.X through 7.X the behavior would be different. I recently learned the Set FRMLBOTTOMUP prevents the creation of data blocks through all documented means except data loads. That is new behavior and is not documented anywhere.

Anonymous said...

"Yes, assigning a value of #Missing is slower than CLEARDATA" if you run a clearblock empty, it should clear the block since it has no data.

Unknown said...

Funny I came across this topic months ago when searching something else. I filed it in the back of my brain as one of those things I'd never come across.

Well I came across it today.

I needed to get stat accounts in my Budget scenario to match GL accounts in my Actual actual scenario. Since I needed to add two members I needed to use a formula instead of a direct copy. I still utilized a Datacopy to create the blocks. I then followed up with a Cleardata as appropriate.

I knew the blocks were there because I tested my Datacopy before I added the Cleardata statement. The data was where it belonged. Therefore, I presumed, the blocks were there.

I then ran the code. NO DATA! C'mon this is an easy calc why NO DATA? I tried everything. I even went for a run.

I came back read your blog post, changed my clear data to # missing. Wah Lah! It works. Now I feel smart, I think. This is a great party conversation for Essbase geeks. I can't wait to use it.

Pete said...

Hey Cameron,

Any chance you remember what version you tested this in?

Because in DOESN'T remove the blocks.

I've tested it a couple of times and I'm fairly confident.

I think our shared thoughts that the entire block creation thing changing in is true. So many things that work differently in later versions (like SET CREATEBLOCKONEQ) seem to be exactly as the documentation explains in


ps: Yes - it has been an eye-opener going back to!

test said...

Hi Cameron,

I came across this issue yesterday and went back to admin guide which states that CLEARDATA does not remove blocks, but I observed yesterday that it did remove blocks when dealing with sparse members only in the FIX, but was quite skeptic about this finding until I read your post. Thanks !