Create a Metadata loop in Calc Manager 1

I’m on a project right now where I’m looking at optimizing an On-Prem migration to the cloud which went wrong.

I guess most of you are familiar with the best practices while writing an Essbase calculation script. I bet you all know how to use an IF condition inside a FIX statement.

We all know that Scenario is DENSE in sample basic application. Let’s changes the setting and make it a sparse dimension and run the calc from Tech Ref.

SET MSG DETAIL;
SET NOTICE HIGH ;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS OFF;

FIX(@LEVMBRS ("Year",0))
FIX(@LEVMBRS ("Product",0))
FIX(@LEVMBRS ("Market",0))
FIX("Actual")
"Profit_Calc"
(
IF (Sales > 100)
(Sales - COGS) * 2;
ELSE
(Sales - COGS) * 1.5;
ENDIF;
)
ENDFIX
ENDFIX
ENDFIX
ENDFIX

It is a straight forward calc, and after execution, you should see a similar message.

[Tue Jul 16 13:26:14 2019]Local/Sample/Basic1/admin@Native Directory/9184/Info(1012668)
Calculating [ Measures(Profit_Calc)] with fixed members [Year(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec); Product(100-10, 100-20, 100-30, 200-10, 200-20, 200-30, 200-40, 300-10, 300-20, 300-30, 400-10, 400-20, 400-30, 100-20, 200-20, ]

[Tue Jul 16 13:26:14 2019]Local/Sample/Basic1/admin@Native Directory/9184/Info(1012672)
Calculator Information Message: Executing Block – [100-10], [New York], [Actual]

[Tue Jul 16 13:26:14 2019]Local/Sample/Basic1/admin@Native Directory/9184/Info(1012672)
Calculator Information Message:

Completion Notice For Block Number [0,4]

[Tue Jul 16 13:26:14 2019]Local/Sample/Basic1/admin@Native Directory/9184/Info(1012672)
Calculator Information Message:

Total Block Created: [0.0000e+000] Blocks
Sparse Calculations: [0.0000e+000] Writes and [1.0000e+000] Reads
Dense Calculations: [1.0000e+000] Writes and [1.0000e+000] Reads
Sparse Calculations: [0.0000e+000] Cells
Dense Calculations: [1.2000e+001] Cells

Looks great, it did read all 12 months, did read one dense calc and wrote one dense calc.

Now let’s think about a different scenario where you want to run a calc on multiple accounts, well now you don’t have the option to open the block using a dense member and many people will fall back to the below-given calc script, which is opening the block using a sparse member.

FIX(@LEVMBRS ("Year",0))
FIX(@LEVMBRS ("Product",0))
FIX(@LEVMBRS ("Market",0))
FIX("Profit_Calc")
"Actual"
(
IF (Sales > 100)
(Sales - COGS) * 2;
ELSE
(Sales - COGS) * 1.5;
ENDIF;
)
ENDFIX
ENDFIX
ENDFIX
ENDFIX

Let’s see what happens if we run the script above.

[Tue Jul 16 13:25:25 2019]Local/Sample/Basic1/admin@Native Directory/8684/Info(1012668)
Calculating [ Scenario(Actual)] with fixed members [Year(Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec); Measures(Profit_Calc); Product(100-10, 100-20, 100-30, 200-10, 200-20, 200-30, 200-40, 300-10, 300-20, 300-30, 400-10, 400-20, ]

[Tue Jul 16 13:25:25 2019]Local/Sample/Basic1/admin@Native Directory/8684/Info(1012672)
Calculator Information Message: Executing Block – [100-10], [New York], [Actual]

[Tue Jul 16 13:25:25 2019]Local/Sample/Basic1/admin@Native Directory/8684/Info(1012672)
Calculator Information Message:

Completion Notice For Block Number [0,4]

[Tue Jul 16 13:25:25 2019]Local/Sample/Basic1/admin@Native Directory/8684/Info(1012672)
Calculator Information Message:

Total Block Created: [0.0000e+000] Blocks
Sparse Calculations: [1.0000e+000] Writes and [2.0000e+000] Reads
Dense Calculations: [0.0000e+000] Writes and [0.0000e+000] Reads
Sparse Calculations: [0.0000e+000] Cells
Dense Calculations: [0.0000e+000] Cells

Why are there 2 reads? It is because the other one is from Budget!!!

Do you see the issue in opening a Sparse block? It is going to read all the blocks with the sparse members used in the FIX statement, and sparse calculations are time-consuming. (sometimes you got to do it for BLOCK creation).

Now I get it you don’t want to repeat the same code block for 12 times (all months) and create a maintenance nightmare!!! However, you can and should take advantage of Calc Manager’s metadata loop block.

If I’m not wrong (the blog draft I had in my previous blog post dates back to 2015) the feature is present in on-prem and cloud version from years.

Metadata loop works similar to a FIX statement; it repeats the code inside the loop and operates on the current member from the loop. To create/use metadata loop, you must create a template.

Once you create the template, navigate to “Design Time Prompt” to create a DTP.

Once you create the DTP, you can navigate to “Template Designer and drag the metadata loop (must in the designer mode). The functions supported on On-Prem calc manager versions are limited.

I think almost all member set functions are supported on the cloud version.

Below given is the screenshot of the template I was working.

SET MSG DETAIL;
SET NOTICE HIGH ;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS OFF;
FIX(@RELATIVE ("Total Expenses",0))
FIX(@LEVMBRS ("Product",0))
FIX(@LEVMBRS ("Market",0))
FIX("Actual")
%Template(name:="TMPL_ProfitCalc",application:="Sample",database:="Basic1",dtps:=())
ENDFIX
ENDFIX
ENDFIX
ENDFIX

If you look at the script, you’ll now see that it replaced the members from the loop.

There you go, it is that easy to optimize some scripts. Happy looping 🙂