File Library

Power Tools: Using VAR + LOOP for If-Then Procedure Logic and ARRAY for Speed
Topic: Essbase
Owner: Michael J Henderson
Date: 2013-06-26
Subtopic: Tips and Techniques

Part 1 VAR + LOOP: All Essbase developers have seen examples of business logic that depend on circumstances … perhaps a portion of a calculation is needed only at quarter end and not at intervening period close, perhaps there is different logic needed for FORECAST than for BUDGET, and so on. VAR allows us to temporarily store and use a single numeric value in calc code. LOOP allows us to run a section of code multiple times … or even only once. Further, LOOP can be opened or closed by a numeric break variable … and that is where VAR comes in handy. We will teach and demonstrate the use of VAR + LOOP to selectively execute a block of code depending on either flag values stored in the Essbase cube or by flags set as substitution variables.

Part 2 ARRAY: In a recent tuning exercise, we were able to cut calc times for a large cube from hours to literally seconds by applying a combination of best practices and innovative approaches. One trick we stumbled upon was the use of the ARRAY function to hold the list of currency conversion rates by currency. We believe that by loading the array once at the beginning of the calc script, repetitive reads of the same factor are eliminated.

Download File   JSims.PowerTools.pptx
Download File   JSims.PowerTools.pptx

Become a member of ODTUG to gain access to more than 12,500 files in our technical database.

  • Not a member? Click through to the topic of interest to browse a list of available presentations.
  • Already a member? Log-in here to access the full database

Click here to see the Full Techincal Resource Database