Review - Calc Script Basics
What is a Calc Script?
• Series of commands, equations, and formulas that control calculation of the database
• Text files with a .CSC extension
•/* Creates Initial Budget */
•SET UPDATECALC OFF;
•CLEARDATA Budget;
•Budget = Actual * 1.1;
•CALC ALL;
Why Use a Calc Script?
• Define calc order
• Calculate a subset of data
• Clear or copy data
• Calculate formulas not in outline
• Perform multiple-pass calculations
• Create and use temporary variables
Try to Limit Cross-Dims
• Using cross-dimensional operator:
• Faster, easier to maintain: •FIX (Sales)
• Budget = Actual * 1.1;
•ENDFIX
•FIX (Budget)
• Sales = Sales->Actual * 1.1;
•ENDFIX
Clearing Data
• Clear a subset of the database based on a sparse dimension:
• Clear a subset of the database based on a dense dimension:
•FIX (“Fruit Soda”, “Texas”)
• CLEARBLOCK ALL;
•ENDFIX
•CLEARDATA Actual->Sales;
Copy Data
• Copy all values for one member to another:
• Copy a subset of values:
•FIX (Sales, COGS)
• DATACOPY Actual TO Budget;
•ENDFIX
•DATACOPY Actual TO Budget;
Copy Data cont.
• Another way to copy a subset of values:
• Still another way to copy a subset of values:
•FIX (Sales, COGS)
• Actual = Budget;
•ENDFIX
•FIX (Actual)
• Sales = Sales->Budget;
• COGS = COGS->Budget;
•ENDFIX
Multi-pass Calculations
• Use to perform goal-seeking or simultaneous equations (circular references)
• Example of a simultaneous equation:
•LOOP(30)
• Bonus = Profit * .1;
• Profit;
•ENDLOOP;
Create and Use Temporary Variables
• Create initial budget based on a value stored in a variable:
•VAR BudgetInc = 0.1;
•SET UPDATECALC OFF;
•FIX (Sales, COGS)
• Budget = Actual * (1 + BudgetInc);
•ENDFIX
•CALC ALL;
Agg
• Syntax
• AGG(Dimname, Dimname,…);
• Summary
• Aggregates dimension(s) according to the outline operators
• Member formulas are ignored
• Very fast
• Fewer than 6 levels
Calc Dim
• Syntax
• Calc Dim(DimName, DimName,…)
• Summary
• Calculates the dimensions that are listed
• All dense dimensions are calculated first in the order they appear
• Sparse dimensions are calculated last - also in the order they appear
• If you need a different order, use multiple Calc Dim commands
• Includes member formulas
ClearBlock vs. ClearData
• ClearBlock is designed to clear an entire block
• At least a large portion of the block
• No Member name, just block type
• ClearData is designed to clear a member
• Usually before it is recalculated
• In many cases either one will work
DataCopy
• Syntax
• DataCopy MemberName TO MemberName;
• Summary
• Copies data from one member to another
• All intersections are included
• Great for copying versions
• Will create blocks if they do not exist
If/Else/ElseIf/EndIf
• Syntax
• IF (condition) ElseIf/Else EndIf;
• Summary
• Condition must be if parenthesis
• In a calc script, associate with a member name
• Does really matter which member name
• Each If must end with EndIf
• Most effective on dense members
Fix/EndFix
• Synatx
• Fix(MemberNames, Functions Etc.) ENDFIX
• Summary
• Used to focus or limit a calculation
• Most effective on sparse members
• Used extensively
Focusing Calculations
• Two most common methods
• Fix
• If
• Other methods:
• CrossDim operator
• MemberSet functions
• Relationship functions
• Variables
FIX
• FIX limits the members that are processed
• Most effective when used on members of sparse dimensions
• Only blocks that meet the Fix criteria are processed
• Remaining blocks are simply skipped
• Typical uses
• Only calculate one scenario
• Limit calc to current year
More on FIX
• FIX works properly on dense members
• May result in all blocks being processed
• All statements between the FIX and ENDFIX are executed as a block
• FIX statements can be nested
• Multiple members from the same dimension are treated as
“or”
• In other words, all members will be processed
What You Cannot Do With FIX
• You cannot assign values to a member that is included in the FIX statement
• Actually, values cannot be assigned to any member from a dimension in the FIX statement
• Example
• Fix(Sales)
• Sales = 100;
• ENDFIX
• Would result in an error
FIX On
• Members from one or more dimensions
• Can list specific member names
• Can include member set functions
• Can include AND and OR
• Use AND to get the intersection of two or more functions
• Caution on empty sets
IF Statements
• Conditional processing is often needed to process business rules
• Can include complex conditions
• And, Or, Not, etc.
To If or FIX
• Generally, IF works best on dense dimension members
• Particularly when ElseIF or Else are included
• Block is only brought into memory once and all related conditions are processed
• FIX works best on sparse dimension members
• Old saying “Fix on Sparse, If on dense”
• There are always exceptions
• BUT… avoid unnecessary IFs when, say, a FIX would do
FIX Workshop
• What does this calc script do?
• Essbase 9.0 can skip an empty FIX
SET UpdateCalc Off;
FIX (@CHILDREN(“100-10”)) Actual = #Missing;
ENDFIX
SET EmptyMemberSets On;
SET UpdateCalc Off;
FIX (@CHILDREN(“100-10”)) Sales = 100;
ENDFIX
IF vs. FIX examples
• This is a waste of an IF
• This works better
FIX (Jan, Feb)
Sales = 100;
ENDFIX Sales (
IF (@ISMBR(Jan, Feb)) Sales=100;
ENDIF)
IF vs. FIX examples
IF vs. FIX examples
IF Optimization
• While we’re on the subject
• A “calc member block” is just a member formula, so no need to repeat “Sales”
Sales (
Checking for #Missing or Zero
• This is the old-school way of checking for a non-value
• Combine the two checks into one
Sales (
IF (Jan==#Missing or Jan==0)) 100;
ENDIF)
Sales (
IF (Jan + 0 == 0) 100;
ENDIF)
Replacing zeros with #Missing
• Old-school way
• Don’t need an IF
Actual (
IF (Actual==0)
#Missing;
ENDIF)
SET UpdateCalc Off;
Actual = Actual * Actual / Actual ;
@IS Functions outside of IF
• This works
• This is faster (and more confusing to read)
Sales (
IF (@ISUDA(Market,"Major Market")) 100 ;
ELSE
#Missing;
ENDIF)
Sales = 100 / @ISUDA(Market,"Major Market");
Other Method to Focus Calculations
• “Cross Dim” operator
• Allows reference to specific cells
• Frequently in another block
• Technique for complex allocations
• Relationship functions
• Can be used to get a value from another part of the database
• Example is @ParentVal(…)
• MemberSet functions
• Return member names
• Can be used to calculate those members or in a Fix/If
Why Create Blocks
• Often need blocks that do not exist
• Typically when performing allocations
• Actually, one of the most challenging aspects of writing calc scripts
• If a block does not exist, not processed by a calc script
• Except outline calculations
• Frequent cause of calculation problems
• Example
• IF (Budget > 100)
• Actual = 50;
• ENDIF
• Assumes Scenario is sparse
Ways to Create Blocks
• There are basically four methods to create blocks:
• Load data
• Calculate the database
• Use the DataCopy command
• Place a sparse member on the left side of an assignment statement
• Must not set equal to a constant
• SET CREATEBLOCKONEQ ON|OFF;
• SET CREATENONMISSINGBLK ON|OFF;
Load Data
• During a data load, a block is created if it doesn’t already exist
• Can use this to create blocks
• Create data records based on source information
• Load the file
• One trick is to use the Replace function of the load rule
Calculate the Database
• An outline rollup will create upper level blocks
• Both Calc All/Dim and AGG will cause blocks to be created
• Only blocks containing data are created
• Empty blocks are not written
DataCopy Command
• Easiest way to create blocks
• If an intersection for the “To” member does not exist, it is created
• Frequently, the only practical method to create the needed blocks
• Danger in that it can create excessive numbers of blocks
Assignment Statements
• If
• The member on the left of the assignment statement is sparse
• And
• The database setting “Create bocks on equations” is selected
• Blocks are created
• Not always possible to have a sparse member on the left side of the assignment statement
• Try fixing on a dense member and assigning to a sparse member
• E.g. Fix on the account member and assign to another
• May need to change dense/sparse settings
Workshop
• Which is the more efficient calc script?
•FIX (Sales)
• Budget = Actual * 1.2;
•ENDFIX
•FIX (Budget)
• Sales = Sales->Actual * 1.2;
•ENDFIX
Workshop
• Which is the more efficient calc script?
• Limit cross dim operators if possible
•FIX (Sales)
• Budget = Actual * 1.2;
•ENDFIX
•FIX (Budget)
• Sales = Sales->Actual * 1.2;
•ENDFIX
Workshop
• You have a sales analysis application with the following dimensions:
• Accounts – dense (member formulas and all upper levels are stored)
• Periods – dense (no member formulas and all upper levels are stored)
• Product – sparse (no member formulas and all upper levels are stored)
• Region – sparse (no member formulas and all upper levels are stored)
• Scenario – sparse (member formulas on dynamically calc’d members)
• Introduction date – attribute dimension (no member formulas)
• Sales Manager – attribute dimension (no member formulas)
Workshop
• Choose the best consolidation calc script:
•CALC DIM(ACCOUNTS, PERIODS);
•AGG(PRODUCT, REGION, SCENARIO,
“INTRODUCTION DATE”, “SALES MANAGER”);
•CALC DIM(ACCOUNTS);
•AGG(PERIODS, PRODUCT, REGION);
•CALC DIM(ACCOUNTS, PERIODS, PRODUCT, REGION, SCENARIO, “INTRODUCTION DATE”,
“SALES MANAGER”);
•CALC DIM(ACCOUNTS, PERIODS);
•AGG(PRODUCT, REGION);
Workshop
• Choose the best consolidation calc script:
•CALC DIM(ACCOUNTS, PERIODS);
•AGG(PRODUCT, REGION, SCENARIO,
“INTRODUCTION DATE”, “SALES MANAGER”);
•CALC DIM(ACCOUNTS);
•AGG(PERIODS, PRODUCT, REGION);
•CALC DIM(ACCOUNTS, PERIODS, PRODUCT, REGION, SCENARIO, “INTRODUCTION DATE”,
“SALES MANAGER”);
•CALC DIM(ACCOUNTS, PERIODS);
•AGG(PRODUCT, REGION);
Check Your Understanding
• Choose the true statement:
• If/then – dense dimensions; fix – sparse dimensions
• If/then – sparse dimensions; fix – dense dimensions
• You need to clear Actuals data (which is in the sparse Scenario dimension). What command should you use?
• How can you create a block in Essbase?
Check Your Understanding
• Choose the true statement:
• If/then – dense dimensions; fix – sparse dimensions
• If/then – sparse dimensions; fix – dense dimensions
• You need to clear Actuals data (which is in the sparse Scenario dimension). What command should you use?
• CLEARBLOCK
Check Your Understanding
• How can you create a block in Essbase?
• Load data
• Calculate the database
• Use the DataCopy command
• Place a sparse member on the left side of an assignment statement
• SET CREATENONMISSINGBLK ON|OFF;
Design & Optimize Calculations
Tips and Tricks
Goal
• Make the calculation run faster
• Variety of settings/changes available to make things run faster
• Many limiting factors
• Existing reports/processes
• Source systems
• Hardware
Serial vs. Parallel Calculation
• Serial Calculation
• Default
• Current Essbase behavior
• Each calc is executed serially
• Parallel Calculation
• Set at system, application, database or calc script
• Generates tasks
• Schedules tasks to run on up to 4 threads
• Operating system can schedule each thread on a separate CPU
Parallel Calculation
• Apply multiple processors to a calc
• Only straight forward calcs can use this
• If order dependent portions, calculated in serial
• Maximum of 4 processors
• Recommend 1 less than number on server
• Available in Essbase XTD Analytic Services 6.5
Parallel Calculations
• Set at the server, application, database or individual calc script level
• Can run on up to 4 threads
• Essbase will analyze the outline and calculation request to determine if parallel calc is possible
• Check the application log to see how parallel calc is being used
• Calculating in parallel with [2] threads
Parallel Calculation
• Essbase will review the request
• Complex formula interdependencies will force a serial calc
• If parallel processing is feasible, Essbase splits the request into independent tasks that can be run concurrently
Parallel Calculations
• Set number of worker threads
• In essbase.cfg – CALCPARALLEL appname dbname n
• In calc script – SET CALCPARALLEL n
• N = 1 through 4; default is 1
• Set number of dimensions in task suffix
• In essbase.cfg – NUMTASKDIMS appname dbname n
• In calc script – SET NUMTASKDIMS n
• N = 1 to number of sparse dimensions
• Use when fixing on the last sparse dimension
Cache Settings
• More is not always better
• Sometime Hyperion Essbase seems to spend more time maintaining caches than calculating
• Usually find a point where increasing cache does not improve performance
• Typically a trial and error process
• Set uncommitted / 0 on “Transaction” tab
Dense/Sparse Settings
• This will make the largest difference
• Other factors influence decisions
• Attribute dimensions
• If only a portion of the database is calculated ideally you want to “Fix” on it as sparse members
• Reduces the number of blocks processed
• May need to try a number of combinations
• Smaller blocks almost always calc faster
Control Calculation Environment
• Use SET Commands
• Set CACHE HIGH;
• New cache
• Tracks the block used in the calc
• Set CALCHASHTBL ON;
• Good for flat dimensions
• Set FromBottomUp ON;
• Faster than TopDown
• Make sure you get correct results
FRMBOTTOMUP
• Top down calculations are less efficient b/c more blocks are calculated then necessary
• By default Essbase does bottoms up
• You can explicitly force a bottoms up calc by:
• @CALCMODE in a formula
• SET FRMBOTTOMUP in a calc script
• CALCOPTFRMLBOTTOM UP in Essbase.cfg
• Increases performance
Set Functions in Calc Scripts
• Set AggMissg – defines whether to aggregate #missing child values and overwrite a parent value with #missing.
Improves performance if set to ON.
• Set Cache – defines the amount of calculator cache to be applied to this script. Improves performance if properly set.
• Set LockBlocks – defines the amount of lockblocks to be applied to this script.
• Set Msg – defines the type of information to be reported by the calculator.
• Set UpdateCalc – defines whether to use intelligent calculation capabilities.
More SET Commands
• SET LOCKBLOCK HIGH;
• Allows more blocks to be locked
• Not really optimization but …
• SET CALCTASKDIMS n;
• Determines how many dimensions are used to generate opportunities for the parallel calculation
Dynamic Calc
• Can improve calc times significantly
• Make entire dimension dynamic calc
• Never needs to be calculated
• Especially effective for Time dimension
• Normally calculates very slowly
• All upper level members of dense dimension
• Keeps block smaller
• Upper 1-2 levels of sparse dimension(s)
• Be aware of how many blocks are needed to calc highest level
Custom Functions
• Can write functions in Java
• Compile, save on server, and register
• Call from calc scripts
• Run slower than same calc command
• May run faster than complex series of database passes required to perform the same thing
Custom Macros
• Can predefine combinations of commands
• SumRange is actually a Macro
• Combines Sum and Range functions
• Not necessarily faster but might avoid errors
• Need to define parameters
• May be possible to save a pass of blocks
Database Statistics
• Not a optimization technique but a tool
• Can monitor calculation impacts
• Number of blocks created
• Can refresh while calc is running
• Block creation rate
• Not necessarily best for the calc…
• Copy and paste into Excel
Sparse Dimension Aggregation Order
• Sparse dimensional aggregation should be ordered from fewest blocks created to most blocks created
• To find this information
1. Load data into the database and record the total number of blocks using the Essbase application manager
2. Calculate a single Sparse dimension
3. Again record the total number of blocks using the Essbase application manager
4. Repeat for all Sparse dimensions
Block Mode
• @CALCMODE (within a calc script)
• Cell
• Block
• Top Down
• Bottom up
• By default Essbase uses block mode
• Block mode groups cells within a block and simaltaneously calcuates the cells in each group
• Block mode is faster
• Block mode can cause data dependency issues
Cell Mode
• Cell mode calculates each cell sequentially
• Functions that cause cell mode calculation:
• @ANCEST
Intelligent Calculation
• Intelligent calculation allows Essbase to remember which blocks in the database need to be calculated based on new data coming in, and which haven’t been impacted (and
don’t need calculation)
• Intelligent calculation is wonderful when you’re running a default calc
• But intelligent calculation is the devil’s work when you’re running a calc script
Intelligent Calculation
• Use only on databases with no complex calculations or formulas
• Use on those databases that simply aggregate
• Proves most effective for sparse incremental updates
Review Blocks that are Processed
• Can use “SET MSG DETAIL”
• Each block is listed as it is calculated
• See application log
• Can determine number of times each block is processed
• Can also see if expected blocks are being processed
• Often, extra blocks are being read
• Correct by updating FIX
Simulate the Calculation
• Method to predict the actual calc time
• Based on the number of blocks that would be created
• SET MSG ONLY;
• SET NOTICE HIGH;
• CALC ALL;
• Does not actually calc the database but provides timings
• Run the calc, and use ratio of actual time to simulated
• Not perfect but…
Testing Calc Scripts
• The following methodology works well:
• Use a test database
• Create test data in Excel
• Keep it on its own sheet
• Clear the database
• Use Lock & Send to load test data
• Run the calc from Excel
• Retrieve onto a test sheet
More on Testing
• Best to know expected outcome before retrieving
• Can create a calc script to clear the database
• Minimizes switching between Excel and App Manager
• The key is small amounts of data which allows short test cycles
• Many people test with large amounts of data resulting in long cycles and inability to get many tests in per day
• When initial testing is complete, add to the test data set to make sure everything is working properly
Typical Issues
• Intelligent Calc
• Prevents blocks from being calculated
• FIX on too few or too many members
• Nested FIX’s are OR’s, not AND’s
• Dynamic Calc runs after Batch Calc
• Dynamic calc members do not make good counters
• Referring to the wrong block
• Remember, every intersection is processed unless the calculation is focused
Recap - Calculation Optimization Tips
• @XREF
• If you are using @XREF on dynamic calcs, exclude from calcs
• @XREF will only work on existing blocks
• Calc only those dimensions requiring calculation
• Example – Do you need to rollup Scenario (Actual + Budget)?
• AGG and CALC DIM are not the same
• Agg is faster for straight aggregating dimensions
• Fix on Sparse, If on Dense
• Reduce the number of passes through the database
• Simplify if possible
• Unary calcs instead of formulas
• Dynamic calcs, Dynamic Time Series
Check Your Understanding
• How do you turn on parallel calc?
• When should you use intelligent calculation?
• Which is faster – cell mode or block mode?
• What are potential issues with block mode?
• What functions force cell mode?
Check Your Understanding
• How do you turn on parallel calc?
• Set number of worker threads
• In essbase.cfg – CALCPARALLEL appname dbname n
• In calc script – SET CALCPARALLEL n
• Set number of dimensions in task suffix
• In essbase.cfg – NUMTASKDIMS appname dbname n
• In calc script – SET NUMTASKDIMS n
• When should you use intelligent calculation?
• Most of the time turn intelligent calc OFF
• Using default calc and running sparse incremental updates
• Which is faster – cell mode or block mode?
• Block mode
• What are potential issues with block mode?
• Data dependency issues
Check Your Understanding
• What functions force cell mode?
• @ANCEST