Stupid Programming Tricks #6

Introduction

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, zilch.  After 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

Dimension	Type
Year	Dense
Measures	Dense
Product	Sparse
Market	Sparse
Scenario	Dense

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:

SET UPDATECALC OFF ;
SET AGGMISSG ON ;
SET EMPTYMEMBERSETS ON ;
 

CALC ALL ;

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. 

SET UPDATECALC OFF ;

SET AGGMISSG ON ;

SET EMPTYMEMBERSETS ON ;

CALC ALL ;

FIX(@LEVMBRS("Market", 0))

  DATACOPY "Actual"->"Product" TO "Budget"->"100-10" ;

  FIX("Budget")

    CLEARDATA "100-10" ;

  ENDFIX

ENDFIX

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.

Arrgh.

Fix it in one step

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

SET UPDATECALC OFF ;

SET AGGMISSG ON ;

SET EMPTYMEMBERSETS ON ;

CALC ALL ;

FIX(@LEVMBRS("Market", 0))

  DATACOPY "Actual"->"Product" TO "Budget"->"100-10" ;

    FIX("Budget")

      "100-10" = #Missing ;

    ENDFIX

ENDFIX

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.

Conclusion

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. 

Be seeing you.