Calc Scripts

Calc scripts are used to perform complex, parametrized calculations. Conceptually, a calc script consists of an optional parameter declaration followed by one or more statements.

Example

param @scenario, @dept

scope ([Scenario].&[@scenario], [Period].[2025].LeafDescendants, [Account].[Risk])
  freeze ([Department].&[@dept])
    [New] = myfunction(([Account].[Balance], [Market].SMB), [Account].[CreditLimit].Parent * 1.2) 
               - [Account].[Balance].Ancestor(2).Children.First * 0.2
  end freeze
end scope

Parameters

If the script supports parameters, it must start with the keyword param followed by a list of parameters. Parameter names must start with @, e.g. param @scenario, @dept.

Parameters can be used in two ways:

As a member reference — the parameter supplies the key, name, or tag of a member:

param @scenario, @dept

scope ([Scenario].&[@scenario], [Department].&[@dept])
  [New] = 1000
end scope

Here @scenario and @dept are passed in as strings identifying members (e.g. a scenario key, a department key) and are resolved against the dimension the same way a literal key/name/tag would be.

As a scalar value — the parameter is used directly as a number in an expression, instead of identifying a member:

param @rate

scope ([Scenario].[Actual])
  [New] = [Account].[Balance] * @rate
end scope

Here @rate is passed in as a numeric value (e.g. 0.5) and used directly in the calculation. Unlike member-reference parameters, a scalar parameter is not tied to any dimension — it stands on its own as a term in the expression, and can be combined with mathematical operators, other member references, or function arguments, e.g. [New] = @rate * ([Account].[Balance], [Period].[Jan 2026]).

Directives

A calc script can set directives with set <directive> on|off.

direct_write controls how the script writes its results to the database.

set direct_write on|off

• off (the default, used when the directive is omitted) — results go through the standard writeback path: the WM_xxx writeback table, then the write procedure (with any custom-writeback hooks), then the audit. This is the same behavior calc scripts have always had.
• on — results are written straight to the fact table, skipping the writeback pipeline. This is much faster for assignments that write many cells.

warning

Turning direct_write ON will bypass any before/after/instead of hooks in the write procedure. The compiler will issue a warning if it detects write hooks in your environment. Audit log will always reflect the shape of the operation instead of individual cells, regardless of the number of cells written. Automatic calcs will still be triggered (if applicable).

info

direct_write can be toggled on or off throughout the script; each assignment will use the current state of the directive.

Statements

After the optional parameter declaration, a calc script can contain one or more statements. There are six statements supported: Scope, Freeze, Dense, Sparse, Assignment, and Call (see Define / Call below).

Scope

Scope is a compound statement that limits the scope of enclosed statements:

scope (set_exp [, set_exp...])
  statement...
end scope

The scope keyword introduces in parentheses a list of set expressions which will limit the scope of the enclosed statements. Each set expression in the list must refer to a different dimension.

Embedded scope statements are conjunctive (restrictive) — each statement will further restrict the calculation:

scope ([Period].[2025].SelfAndDescendants)
  scope ([Period].[*].LeafDescendants)
     -- inner statements will apply to Jan 2025 through Dec 2025
  end scope
end scope

Embedded scope statements for different dimensions are equivalent to a single scope statement listing all dimensions:

-- These are equivalent:
scope ([Account].[Test])
  scope ([Scenario].[Actual])
    scope([Period].[Jan 2025])
       -- do something
    end scope
  end scope
end scope

scope ([Account].[Test], [Scenario].[Actual], [Period].[Jan 2025])
  -- do something
end scope

Freeze

Freeze is a compound statement that protects certain cells from being updated:

freeze (set_exp [, set_exp...])
  statement...
end freeze

The freeze keyword introduces in parentheses a slice of the model which will remain unaffected by the subsequent statements.

Example:

scope ([Period].[2025].LeafDescendants, [Account].[Test])
  freeze([Period].[Q1 2025].Children, [Market].[SMB])
    [New] = 1
  end freeze
end scope

Embedded freeze statements are disjunctive (additive) — each subsequent freeze statement adds another model slice that is frozen. For this reason, unlike scope statements, one freeze statement that refers to multiple dimensions is NOT equivalent to multiple embedded freeze statements.

Dense/Sparse

Controls how assignments handle cells:

dense dimension[, dimension...]
sparse dimension[, dimension...]

Dimensions listed in the dense statement will become dense for the rest of the scope or until listed in a sparse statement. All dimensions are by default assumed to be sparse until listed in a dense statement.

See the Assignment section below for an explanation of dense vs. sparse behavior.

Assignment

Assignment statements calculate values:

measure = expression

The left side of the assignment must be a measure member. The [@Measure] dimension prefix should be omitted—for example, write [Opening] instead of [@Measure].[Opening].

As shorthand for the [New] measure specifically, the assignment target can be written as the keyword this instead:

this = [Account].[Rent] * 0.1
-- same as
[New] = [Account].[Rent] * 0.1

The expression can use:

• Mathematical operators: +, -, *, /, %
• Comparison and logical operators, inside iif(): =, <>, <, >, <=, >=, like, and, or, not
• Empty values: the null keyword, and the is null / is not null tests
• Parentheses
• Numbers, strings in single or double quotes ('USD' or "USD"), and true / false (the numbers 1 and 0)
• Scalar functions and aggregate functions
• IsActual()
• Member expressions, including relative references such as [Period].CurrentMember.Lag(12) and runtime members such as [Currency].KeyToMember(expr)
• Member property values, e.g. [Department].CurrentMember.Property("BonusRate")
• Cross-dimensional tuples

Assigning null (or an expression that evaluates to null) clears the cell's value:

scope ([Account].[Temp], [Scenario].[Plan], [Period].[2025].LeafDescendants)
  [New] = null
end scope

Important differences from formulas:

• Member references must include the dimension name, e.g., [Account].[Test] or [@Measure].[Opening]
• Dimensions can be referenced by role, using an @ prefix, e.g., [@Time]
• Member expressions that return several members (e.g. [Account].[Opex].Children) add up their values

See Formulas vs. Calc Scripts for a full comparison.

Assignments are executed for all cells in scope simultaneously. If the expression references cells that are updated by the statement, the old value of the cell will be used for calculation.

Assignment Independence

All assignment statements within a calc script execute at the same time. This means each assignment must be independent — one assignment cannot rely on a value calculated by another assignment in the same script.

Why this matters:

Consider this incorrect example:

scope ([Scenario].&[@scenario], [Account].[Retained Earnings], [Period].[2025].LeafDescendants)
  [Opening] = ([Period].CurrentMember.PrevMember, [@Measure].[Closing])
  [Closing] = [@Measure].[Opening] + [@Measure].[New]
end scope

This creates a circular dependency:

• The first statement calculates Opening using Closing from the previous period
• The second statement calculates Closing using Opening from the current period

Because assignments execute simultaneously for all members, they will read the old values (before any updates). This produces incorrect results.

The correct approach:

Break the circular dependency by ensuring each assignment only references values that already exist:

scope ([Scenario].&[@scenario], [Account].[Retained Earnings], [Period].[2025].LeafDescendants)
  -- Calculate Closing from known values: prior period's Closing + cumulative New
  [Closing] = ([Period].&[202412], [@Measure].[Closing])
                             + Simple_Agg([Period].[2025].FirstLeafDescendant, [@Measure].[New])

  -- Set Opening to the previous period's Closing
  [Opening] = ([Period].CurrentMember.PrevMember, [@Measure].[Closing])
end scope

Now each assignment is independent:

• Closing is calculated from prior period's closing and cumulative flow (both already exist)
• Opening references the previous period's closing (which already exists)

IsActual()

IsActual() returns 1 when the cell's period is an actual period for the cell's scenario, and 0 otherwise — the same rules as in formulas: always 1 for an Actual scenario; for a Forecast, 1 up to the scenario's Last Actual Period (or the last period flagged as actual in the time dimension); 0 for Plan and Benchmark scenarios. A summary period is actual only when its last period is.

-- Seed the forecast: keep actuals, calculate the remaining periods
scope ([Scenario].[Forecast], [Account].[Revenue], [Period].[2025].LeafDescendants)
  [New] = iif(IsActual() = 1, [@Measure].[New], [Account].[Units] * [Account].[Price])
end scope

IsActual() requires the model's Scenario dimension to be of type Scenario.

Variables

A calc script can declare local variables to avoid repeating the same tuple or expression multiple times. A variable is declared with an @-prefixed name, optionally preceded by the set keyword (purely for readability — it has no effect on behavior):

scope([Scenario].[Actual], [Account].[Rent])
  @ppUtil = ([Account].[Utilities], [Period].CurrentMember.PrevMember)
  [New] = @ppUtil * 0.5
end scope

Variables are resolved at compile time: every reference to @ppUtil is replaced with the tuple (or expression) it was assigned, before the script is compiled. The example above is exactly equivalent to:

scope([Scenario].[Actual], [Account].[Rent])
  [New] = ([Account].[Utilities], [Period].CurrentMember.PrevMember) * 0.5
end scope

Because a variable declaration is a compile-time construct, not a runtime assignment, it does not need a scope of its own and produces no calculation on its own — it can be declared anywhere a statement is allowed, and must be declared before it is used.

A variable can be assigned any expression that would otherwise be valid on the right-hand side of an assignment: a single member reference, a cross-dimensional tuple, a scalar expression, or a combination of these using operators and functions.

Limitation — a tuple variable cannot be used inside another tuple. Since substitution happens by inserting the assigned expression as-is, using a tuple-valued variable as one member of another tuple would require a tuple nested inside a tuple, which is not supported:

scope([Scenario].[Actual], [Account].[Rent])
  @ppUtil = ([Account].[Utilities], [Period].CurrentMember.PrevMember)
  [New] = (@ppUtil, [@Measure].[Closing]) * 0.5     -- ERROR: tuple inside a tuple
end scope

A variable assigned to a single member reference or a scalar value does not have this restriction, since substituting it does not create a nested tuple.

info

Variables share the @ naming convention used by parameters and dimension roles (e.g. @Time, @Measure). If an @name matches a variable declared earlier in the script, it is always resolved as that variable; otherwise it falls back to being interpreted as a parameter or dimension role.

Define / Call

A calc script can declare reusable, parameterized blocks of statements with define ... end define, and expand them wherever needed with a call statement:

define Gigel @value
  [New] = ([Account].[Bonuses], [Period].CurrentMember.PrevMember) * @value
end define

scope([Scenario].[Actual], [Account].[Rent])
  call Gigel @value=5
end scope

scope([Scenario].[Actual], [Account].[Income])
  call Gigel @value=2
end scope

define <name> @param1[, @param2...] names a block and declares its formal parameters (same @-prefixed naming convention as parameters and variables); the block body is a normal statement list — it can contain scope, freeze, assign, dense/sparse, and even call statements calling other, previously-declared blocks.

call <name> @param1=expr1[, @param2=expr2...] expands the named block in place, binding each formal parameter to the given argument expression. Every declared parameter must be bound exactly once; unknown or duplicate argument names are a compile error.

Rules:

• define blocks are top-level only — they must be declared alongside param, before the main statement list, and cannot be nested inside scope/freeze.
• A block must be defined before it is called — same single-pass rule as variables.
• call itself can appear anywhere a statement is allowed, including nested inside scope/freeze blocks — this is how the same block can be reused against different scopes, as in the example above.
• Recursive calls are not supported — a block calling itself, directly or indirectly through another block, is a compile error. Calc scripts compile to static SQL ahead of time, so there is no runtime call stack to unwind a recursive expansion.

Scoping — call behaves like inlining. A block's body runs inside whatever scope/freeze context is active at its call site, exactly as if the body had been copy-pasted there directly. In the example above, [Period].CurrentMember.PrevMember inside Gigel resolves relative to whichever [Period] scope is active where Gigel was called — the block itself does not carry its own scope.

Argument types. Unlike variables, a call argument can be a scalar expression, a single member reference, or a tuple, and — used at a member position inside another tuple — a tuple-valued argument is spliced in without the "tuple inside a tuple" restriction that applies to variables:

define SetBonus @acct, @amount
  [New] = (@acct, [@Measure].[Closing]) + @amount
end define

scope([Scenario].[Actual])
  call SetBonus @acct=[Account].[Bonuses], @amount=100
end scope

Dense vs. Sparse Assignments

When all dimensions are sparse (the default), assignments are only performed on cells that already exist in the model—similar to an SQL UPDATE statement that only updates existing records.

Example:

scope([Scenario].[Actual], [Period].[Jan 2026], [Account].[Rent])
  [New] = 1000
end scope

If there is no data in the database for Actual scenario and Jan 2026 period, the statement will do nothing.

To force creation of cells, set one or more dimensions to dense. Cells will be created for all in-scope members for dense dimensions, but only where data already exists for the sparse dimensions.

scope([Scenario].[Actual], [Period].[Jan 2026], [Account].[Rent])
  dense [Period]
  [New] = 1000
end scope

This will create new records for the Rent account, Actual scenario in period Jan 2026, but only where data exists in the Rent account, Actual scenario for the combination of members from all other dimensions.

Warning

Do NOT set every dimension to dense. This will attempt to create a very large number of cells in the database, take a very long time, and likely result in a server crash.

For example, if you have 100 markets, 100 departments, 100 regions, and 1000 products, setting all dimensions to dense could attempt to create 1 billion records in the fact table.