Formulas

The procedures in this section manage member formulas and their supporting metadata. Formulas are compiled for query-time evaluation.

SET_FORMULA

Minimum role: CUBE_ADMIN

SET_FORMULA(
    P_CUBE_NAME,
    P_DIM_NAME,
    P_MEMBER,
    P_EXPRESSION
) -> VARCHAR

Assigns a calculation formula to a member or removes an existing formula.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.
`P_DIM_NAME`	`VARCHAR`	req	Dimension containing the member.
`P_MEMBER`	`VARCHAR`	req	Member to which the formula is assigned.
`P_EXPRESSION`	`VARCHAR`	opt	Formula expression. Pass `NULL` to remove the formula.

Examples

Assign a guarded ratio:

CALL CUBE.SET_FORMULA(
    'FINANCE',
    'VIEW',
    'Margin_Pct',
    '[ACCOUNT].[Margin] / NULLIF([ACCOUNT].[Revenue], 0) * 100'
);

Remove a formula:

CALL CUBE.SET_FORMULA(
    'FINANCE',
    'VIEW',
    'Margin_Pct',
    NULL
);

VALIDATE_FORMULA

Minimum role: CUBE_ADMIN

VALIDATE_FORMULA(
    P_CUBE_NAME,
    P_NAME,
    P_EXPRESSION,
    P_HIERARCHY
) -> VARIANT

Checks a formula’s syntax without assigning it to a member.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.
`P_NAME`	`VARCHAR`	opt	Member name used as context in validation messages.
`P_EXPRESSION`	`VARCHAR`	opt	Formula expression to validate.
`P_HIERARCHY`	`VARCHAR`	opt	Dimension context. This procedure retains the legacy `P_HIERARCHY` parameter name.

Returns

Returns severity and message entries indicating OK, WARNING, or an error.

Example

CALL CUBE.VALIDATE_FORMULA(
    'FINANCE',
    'Margin_Pct',
    '[ACCOUNT].[Margin] / NULLIF([ACCOUNT].[Revenue], 0)',
    'VIEW'
);

IMPORT_FORMULAS

Minimum role: CUBE_ADMIN

IMPORT_FORMULAS(
    CUBE_NAME,
    DIM_NAME,
    UPDATED_BY,
    MEMBER_DIM_MAP_JSON
) -> VARCHAR

Imports formulas stored in one dimension’s source table and resolves member references against a member-to-dimension map.

All dimensions must exist before running this procedure because member resolution requires the complete member list.

Parameters

Parameter	Type	Req/Opt	Description
`CUBE_NAME`	`VARCHAR`	req	Cube name.
`DIM_NAME`	`VARCHAR`	req	Dimension that owns the formulas.
`UPDATED_BY`	`VARCHAR`	opt	User identifier recorded for audit. Defaults to `SYSTEM`.
`MEMBER_DIM_MAP_JSON`	`VARCHAR`	opt	Pre-built member-to-dimension map. Omit to have the procedure build one.

Example

CALL CUBE.IMPORT_FORMULAS(
    'FINANCE',
    'VIEW',
    'admin@company.com'
);

IMPORT_ALL_FORMULAS

Minimum role: CUBE_ADMIN

IMPORT_ALL_FORMULAS(
    P_CUBE_NAME,
    P_UPDATED_BY
) -> VARCHAR

Imports formulas for every dimension in a cube, building the member-to-dimension map once for the entire operation.

This is faster and more reliable than calling IMPORT_FORMULAS separately for each dimension.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube whose formulas will be imported.
`P_UPDATED_BY`	`VARCHAR`	opt	User identifier recorded for audit.

Returns

Returns counts of formulas imported, dimensions covered, elapsed time, and errors.

Example

CALL CUBE.IMPORT_ALL_FORMULAS(
    'FINANCE',
    'admin@company.com'
);

FINALIZE_FORMULAS

Minimum role: CUBE_ADMIN

FINALIZE_FORMULAS(P_CUBE_NAME) -> VARCHAR

Completes formula setup for a cube by compiling definitions for query-time use.

Run this procedure after a bulk formula import.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.

Example

CALL CUBE.FINALIZE_FORMULAS('FINANCE');

REFRESH_FORMULA_METADATA

Minimum role: CUBE_ADMIN

REFRESH_FORMULA_METADATA(P_CUBE_NAME) -> VARCHAR

Rebuilds a cube’s formula metadata, including dependency and solve-order information.

Run this procedure after editing formulas outside the normal formula-management procedures.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.

Example

CALL CUBE.REFRESH_FORMULA_METADATA('FINANCE');

FORMULA_COST_ESTIMATOR

Minimum role: CUBE_ADMIN

FORMULA_COST_ESTIMATOR(P_CUBE_NAME) -> VARIANT

Estimates the relative computational cost of each formula in a cube. Use it to identify potentially expensive formulas before they affect query users.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.

Returns

Returns formula cost estimates as a VARIANT.

Example

CALL CUBE.FORMULA_COST_ESTIMATOR('FINANCE');