Variables & Dynamic Time Series

Cube variables provide reusable values that can be referenced from POVs and formulas. Dynamic Time Series (DTS) definitions provide declarative period-to-date accumulation such as YTD and QTD without writing the accumulation as a formula.

MANAGE_CUBE_VAR

Minimum role: CUBE_PUBLIC

MANAGE_CUBE_VAR(
    P_CUBE_NAME,
    P_ACTION,
    P_VAR_NAME,
    P_VAR_VALUE,
    P_ACTIVE,
    P_IDS
) -> VARCHAR

Creates, updates, activates, deactivates, or deletes cube variables.

A variable referenced as &Name in a POV or formula lets a saved report follow a changing period, scenario, or other value without editing the report definition.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.
`P_ACTION`	`VARCHAR`	req	`UPSERT` or `DELETE`.
`P_VAR_NAME`	`VARCHAR`	opt	Variable name for `UPSERT`.
`P_VAR_VALUE`	`VARCHAR`	opt	Variable value for `UPSERT`.
`P_ACTIVE`	`BOOLEAN`	opt	Whether the variable is active.
`P_IDS`	`ARRAY`	opt	Variable IDs to remove with `DELETE`.

Examples

Create or update an active variable:

CALL CUBE.MANAGE_CUBE_VAR(
    'FINANCE',
    'UPSERT',
    'CurrentPeriod',
    'Mar',
    TRUE,
    NULL
);

Delete a variable by ID:

CALL CUBE.MANAGE_CUBE_VAR(
    'FINANCE',
    'DELETE',
    NULL,
    NULL,
    NULL,
    ARRAY_CONSTRUCT(42)
);

Referencing variables

Reference a variable in a POV with &Name:

{
  "YEARS": ["&CurrentYear"]
}

For a variable name containing spaces, use the bracketed form:

&[Name With Spaces]

GET_CUBE_VARS

Minimum role: CUBE_PUBLIC

GET_CUBE_VARS(P_CUBE_NAME) -> TABLE

Lists the variables defined for a cube.

Parameters

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

Returns

Returns:

VAR_NAME
VAR_VALUE
ACTIVE

Example

CALL CUBE.GET_CUBE_VARS('FINANCE');

MANAGE_CUBE_DTS

Minimum role: CUBE_ADMIN

MANAGE_CUBE_DTS(
    P_CUBE_NAME,
    P_ACTION,
    P_DTS_DIM,
    P_DTS_MEMBER,
    P_BASE_MEMBER,
    P_GRAIN,
    P_SKIP_POS,
    P_ACTIVE,
    P_IDS
) -> VARCHAR

Manages Dynamic Time Series definitions.

DTS is the declarative mechanism for defining period-to-date members such as YTD and QTD. The accumulation behavior is configured through this procedure rather than written as a formula.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.
`P_ACTION`	`VARCHAR`	req	`UPSERT` or `DELETE`.
`P_DTS_DIM`	`VARCHAR`	opt	Dimension containing the DTS members.
`P_DTS_MEMBER`	`VARCHAR`	opt	Dynamic member to define, such as `YTD`.
`P_BASE_MEMBER`	`VARCHAR`	opt	Member being accumulated, such as `Periodic`.
`P_GRAIN`	`VARCHAR`	opt	Grain at which accumulation resets: `YEAR`, `QUARTER`, or `MONTH`.
`P_SKIP_POS`	`NUMBER`	opt	Skip position.
`P_ACTIVE`	`BOOLEAN`	opt	Whether the DTS definition is active.
`P_IDS`	`ARRAY`	opt	IDs to remove with `DELETE`.

Examples

Define YTD accumulation:

CALL CUBE.MANAGE_CUBE_DTS(
    'FINANCE',
    'UPSERT',
    'VIEW',
    'YTD',
    'Periodic',
    'YEAR',
    1,
    TRUE,
    NULL
);

Define QTD accumulation:

CALL CUBE.MANAGE_CUBE_DTS(
    'FINANCE',
    'UPSERT',
    'VIEW',
    'QTD',
    'Periodic',
    'QUARTER',
    1,
    TRUE,
    NULL
);