Essbase calculation engine - Black magic demystified – Part 2

Introduction

I assume all of you read part 1 of this blog and found it to be useful. Here is the link

https://www.essbaselabs.com/2019/08/essbase-calculation-engine-black-magic.html

When Calculation trace was presented in KScope19, one of the questions we got was whether it can tell when the block does not exist and when we need to use @CREATEBLOCK. In this post, I want to just address that question. For getting continuity I want to use the model from my previous blog. It will make the example a bit artificial, but will still show the feature working well.

Extend the Model

In this model I only had one sparse dimension, so in order to create an example where CREATEBLOCK is needed, I added another product P1-a5 as child of P1.

Now I want to write a calculation script to copy the half of shipped units from P1-a1 to P1-a5 just to get an artificial shipped percentage created for FY19. This is so that I can forecast for FY2020 based on this. I will have to recalculate shipped percentage as well before rerunning forecast calculation. At least that is the plan. But let us get started with assigning shipped units of P1-a1 to P1-a5 to get started.

Running my new script

Ok, I ran my new script and I still had my calculation trace ON. So I decided to make sure P1-a5 is being copied correctly. I selected that cell from smart view and then click Essbase and then calculate and run fill_P1a5, which is the calculation script I created earlier. I was expecting to see how the number was computed in the script using the formula and line I wrote and the numbers being copied over. But instead I found something else. 

 It is telling me that the cell was not modified and the reason is that the block containing the cell that I was planning to write did not exist. If you are familiar with Essbase you would know that dense formulas only works on existing blocks. This is so that we avoid creating entire database. Since P1-a5 blocks were not created since I made changes to the outline, those blocks did not exist and hence the formula on ShippedUnits in the calculation script did not write anything to this cell.

In this case we need to make sure the blocks are created before executing this formula. One way to do that is walk through all the blocks in P1-a1 and then use @CREATEBLOCK to create the corresponding P1-a5 blocks. This way P1-a5 blocks will be created only for the cases where corresponding P1-a1 blocks exist. Ok let me try that. Let us see how the script looks like now.

My modified script

Let me try this and see what the tracing says and what actually happens.

Wow! It’s telling me lot of things. In the first pass its saying that the cell’s previous value is “None”. Note that this is different from the new value, which is #Mi. It then shows the line on which this block was created. Then it shows that in the next pass the values are now being assigned using which formula.  Please note one more point, it says “Cell update count: 2” for the second pass. This is telling us that the cell was modified twice during this calculation. You can use this sometimes to understand performance problems or inefficiencies in your calculation scripts. If the same cell is getting modified several times in the same script, perhaps most of the modifications except the last one is not needed and we can use this to understand how to get rid of them.

Also let us look at the results.

Yes, the values are now written to P1-a5. Great!

Conclusion

CALCTRACE works well to indicate whether the lack of block existence is the reason why certain values are not written. It educates much more and gives lot of insights. Please keep trying and let us know what more you like to see from this feature.