Dimension Management

The procedures in this section manage core cube dimensions, from validating source metadata through building, rebuilding, configuring, and removing dimensions.

CREATE_DIMENSION

Minimum role: CUBE_ADMIN

CREATE_DIMENSION(
    CUBE_NAME,
    DIM_NAME,
    INPUT_TABLE_NAME,
    PARENT_COL,
    CHILD_COL,
    ALIAS_COL,
    OPERATOR_COL,
    DATA_TABLE_COL_NAME,
    USE_SECURITY,
    IS_SHARED_COL,
    SHARED_VALUE,
    FORMULA_COL,
    TB_TYPE_COL,
    TB_TYPE_SKIP_COL,
    SORTORDER_COL,
    IS_TIME_DIM,
    UDA_COL,
    SKIP_SNAPSHOT,
    COMMENT_COL,
    STORAGE_COL,
    PROPERTY_COLS,
    SKIP_DIMENSION_VIEW,
    SKIP_PROFILE
) -> VARCHAR

Builds a dimension from a parent-child source table. This is the core modeling procedure: it reads the source table and creates the structures used by the query engine.

Parameters

Parameter	Type	Req/Opt	Description
`CUBE_NAME`	`VARCHAR`	req	Cube name.
`DIM_NAME`	`VARCHAR`	req	Dimension name. This becomes the POV key.
`INPUT_TABLE_NAME`	`VARCHAR`	req	Fully qualified parent-child source table.
`PARENT_COL`	`VARCHAR`	req	Column containing the parent member name.
`CHILD_COL`	`VARCHAR`	req	Column containing the member name.
`ALIAS_COL`	`VARCHAR`	req	Alias column, or JSON mapping multiple alias sets to columns.
`OPERATOR_COL`	`VARCHAR`	req	Consolidation operator column, such as `+`, `-`, or `~`.
`DATA_TABLE_COL_NAME`	`VARCHAR`	req	Fact-table column corresponding to this dimension.
`USE_SECURITY`	`BOOLEAN`	req	Enables row-level security capability on this dimension.
`IS_SHARED_COL`	`VARCHAR`	opt	Column identifying shared members.
`SHARED_VALUE`	`VARCHAR`	opt	Value identifying a shared member. Default is `true`.
`FORMULA_COL`	`VARCHAR`	opt	Column containing member formulas.
`TB_TYPE_COL`	`VARCHAR`	opt	Time balance type column: `FLOW`, `FIRST`, `LAST`, or `AVERAGE`.
`TB_TYPE_SKIP_COL`	`VARCHAR`	opt	Time balance skip column: `NONE`, `MISSING`, `ZEROS`, or `BOTH`.
`SORTORDER_COL`	`VARCHAR`	opt	Custom sort-order column.
`IS_TIME_DIM`	`BOOLEAN`	opt	Marks the dimension as the time dimension.
`UDA_COL`	`VARCHAR`	opt	User-Defined Attributes column.
`SKIP_SNAPSHOT`	`BOOLEAN`	opt	Skips the automatic snapshot.
`COMMENT_COL`	`VARCHAR`	opt	Member comment column.
`STORAGE_COL`	`VARCHAR`	opt	Data storage column.
`PROPERTY_COLS`	`VARCHAR`	opt	JSON mapping additional member properties, such as solve order, to source columns.
`SKIP_DIMENSION_VIEW`	`BOOLEAN`	opt	Skips regenerating the consumer dimension view.
`SKIP_PROFILE`	`BOOLEAN`	opt	Skips deriving the cube profile.

Setting USE_SECURITY = TRUE makes row-level restriction possible on the dimension. It does not restrict users merely because security is enabled. A user is restricted only when applicable security rules exist for that user.

The SKIP_ options support bulk builds. You can skip view and profile generation on intermediate dimension builds and allow the final call to regenerate them once.

Examples

Create a straightforward dimension:

CALL CUBE.CREATE_DIMENSION(
    'FINANCE',
    'ENTITY',
    'SHARED_DATA.FINANCE_ENTITY_INPUT',
    'PARENT',
    'CHILD',
    'ALIAS',
    'OPERATOR',
    'ENTITY',
    FALSE
);

Create a dimension with formulas, time balance, UDAs, and security:

CALL CUBE.CREATE_DIMENSION(
    'FINANCE',
    'ACCOUNT',
    'SHARED_DATA.FINANCE_ACCOUNT_INPUT',
    'PARENT',
    'CHILD',
    'ALIAS',
    'OPERATOR',
    'ACCOUNT',
    TRUE,
    'IS_SHARED',
    'true',
    'FORMULA',
    'TB_TYPE',
    'TB_SKIP',
    'SORTORDER',
    FALSE,
    'UDA'
);

VALIDATE_DIMENSION

Minimum role: CUBE_ADMIN

VALIDATE_DIMENSION(
    INPUT_TABLE,
    PARENT_COL,
    CHILD_COL,
    OPERATOR_COL,
    IS_SHARED_COL,
    SHARED_VALUE,
    ALIAS_COL
) -> VARCHAR

Checks a parent-child source table for structural problems before building from it, including orphans, cycles, duplicates, and invalid consolidation operators.

Run this procedure before CREATE_DIMENSION when onboarding new metadata.

Parameters

Parameter	Type	Req/Opt	Description
`INPUT_TABLE`	`VARCHAR`	req	Fully qualified input table.
`PARENT_COL`	`VARCHAR`	req	Parent column name.
`CHILD_COL`	`VARCHAR`	req	Child column name.
`OPERATOR_COL`	`VARCHAR`	req	Consolidation operator column name.
`IS_SHARED_COL`	`VARCHAR`	opt	Shared-member indicator column.
`SHARED_VALUE`	`VARCHAR`	opt	Value identifying a shared member. Default is `true`.
`ALIAS_COL`	`VARCHAR`	opt	Alias column to validate.

Example

CALL CUBE.VALIDATE_DIMENSION(
    'SHARED_DATA.FINANCE_ENTITY_INPUT',
    'PARENT',
    'CHILD',
    'OPERATOR'
);

DROP_DIMENSION

Minimum role: CUBE_ADMIN

DROP_DIMENSION(CUBE_NAME, DIM_NAME) -> VARCHAR

Removes a dimension from a cube, including its built structures and configuration. The source table is left in place.

Parameters

Parameter	Type	Req/Opt	Description
`CUBE_NAME`	`VARCHAR`	req	Cube name.
`DIM_NAME`	`VARCHAR`	req	Dimension to remove.

Example

CALL CUBE.DROP_DIMENSION('FINANCE', 'OLD_SEGMENT');

REBUILD_DIMENSIONS

Minimum role: CUBE_ADMIN

REBUILD_DIMENSIONS(
    P_CUBE_NAME,
    P_REBUILD_ALL,
    P_SKIP_SNAPSHOTS,
    P_ASYNC
) -> VARCHAR

Rebuilds dimension structures from their source tables. A rebuild is required after dimension metadata changes. It is not required after a data load.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	opt	Cube to rebuild. `NULL` covers every cube. Required when `P_ASYNC` is `TRUE`.
`P_REBUILD_ALL`	`BOOLEAN`	opt	`TRUE` rebuilds every dimension. `FALSE` rebuilds only dimensions configured for automatic rebuild.
`P_SKIP_SNAPSHOTS`	`BOOLEAN`	opt	Skips snapshots before rebuilding.
`P_ASYNC`	`BOOLEAN`	opt	Runs rebuilds in parallel and returns immediately. Defaults to `TRUE`.

When P_ASYNC = TRUE, the call returns before the rebuild completes. Monitor progress through the audit log rather than relying on the return value to indicate completion.

Examples

Rebuild all dimensions and wait for completion:

CALL CUBE.REBUILD_DIMENSIONS('FINANCE', TRUE, FALSE, FALSE);

Rebuild all dimensions asynchronously:

CALL CUBE.REBUILD_DIMENSIONS('FINANCE', TRUE, FALSE, TRUE);

Rebuild only auto-rebuild dimensions across all cubes:

CALL CUBE.REBUILD_DIMENSIONS(NULL, FALSE, FALSE, FALSE);

MANAGE_DIMENSION_CONFIG

Minimum role: CUBE_ADMIN

MANAGE_DIMENSION_CONFIG(P_ACTION, P_PARAMS) -> VARCHAR

Updates dimension settings without requiring a full rebuild, including default members, security enablement, auto-rebuild settings, aliases, and similar configuration.

Parameters

Parameter	Type	Req/Opt	Description
`P_ACTION`	`VARCHAR`	req	`UPDATE_DIMENSION`, `UPSERT_SETTINGS`, or `REGISTER_DIMENSION`.
`P_PARAMS`	`VARCHAR`	req	JSON object containing the settings to apply.

Examples

Set a default member:

CALL CUBE.MANAGE_DIMENSION_CONFIG(
    'UPDATE_DIMENSION',
    '{
      "cube_name":"FINANCE",
      "hier_name":"CURRENCY",
      "default_member":"USD"
    }'
);

Clear a default member:

CALL CUBE.MANAGE_DIMENSION_CONFIG(
    'UPDATE_DIMENSION',
    '{
      "cube_name":"FINANCE",
      "hier_name":"CURRENCY",
      "default_member":null
    }'
);

Enable row-level security on a dimension:

CALL CUBE.MANAGE_DIMENSION_CONFIG(
    'UPDATE_DIMENSION',
    '{
      "cube_name":"FINANCE",
      "hier_name":"ENTITY",
      "use_security":true
    }'
);

Setting a default member changes query behavior. When the dimension is omitted from a POV, the query resolves to the default member and includes the dimension as a result column. Naming the dimension top also returns the configured default instead of the dimension-wide total.

RELEASE_BUILD_LOCK

Minimum role: CUBE_ADMIN

RELEASE_BUILD_LOCK(P_CUBE_NAME, P_DIM_NAME) -> VARCHAR

Releases a build lock left behind by an interrupted rebuild. A stale lock prevents the next build attempt from proceeding.

Parameters

Parameter	Type	Req/Opt	Description
`P_CUBE_NAME`	`VARCHAR`	req	Cube name.
`P_DIM_NAME`	`VARCHAR`	opt	Dimension name. Pass `NULL` to release all build locks for the cube.

Examples

Release the lock for one dimension:

CALL CUBE.RELEASE_BUILD_LOCK('FINANCE', 'ENTITY');

Release all build locks for a cube:

CALL CUBE.RELEASE_BUILD_LOCK('FINANCE', NULL);

GET_TASK_HISTORY

Minimum role: CUBE_ADMIN

GET_TASK_HISTORY(P_LIMIT) -> TABLE

Returns execution history for the application’s serverless tasks.

Parameters

Parameter	Type	Req/Opt	Description
`P_LIMIT`	`NUMBER`	opt	Maximum number of rows to return.

Returns

Returns:

RUN_ID
STATE
SCHEDULED_TIME
COMPLETED_TIME
ERROR_MESSAGE

Example

CALL CUBE.GET_TASK_HISTORY(50);