Discovery

The procedures in this section help you inspect available cubes, dimensions, members, aliases, and application-managed source tables before querying or building.

LIST_CUBES

Minimum role: CUBE_PUBLIC

LIST_CUBES() -> TABLE

Lists every cube with summary counts. This procedure has no parameters.

Returns

Returns:

• CUBE_NAME
• HIERARCHY_COUNT
• ACTIVE_HIERARCHIES
• FORMULA_COUNT
• HAS_SECURITY
• LAST_UPDATED

If ACTIVE_HIERARCHIES is lower than HIERARCHY_COUNT, one or more dimensions are not built and the cube may not be fully queryable.

Example

CALL CUBE.LIST_CUBES();

LIST_DIMENSIONS

Minimum role: CUBE_PUBLIC

LIST_DIMENSIONS(P_CUBE_NAME) -> TABLE

Lists the dimensions of a cube together with their build configuration.

Parameters

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

Returns

Returns:

• DIM_NAME
• INPUT_TABLE_NAME
• DATA_TABLE_COL_NAME
• ALIAS_COL
• ACTIVE
• AUTO_REBUILD

The values returned in DIM_NAME are the exact dimension names to use as keys in a query POV.

Example

CALL CUBE.LIST_DIMENSIONS('FINANCE');

GET_CUBE_INFO

Minimum role: CUBE_PUBLIC

GET_CUBE_INFO(P_CUBE_NAME) -> TABLE

Returns per-dimension metadata, including the dimension top and any configured default member.

Parameters

Parameter	Type	Req/Opt	Description
P_CUBE_NAME	VARCHAR	opt	Cube name. Omit or pass NULL to return information for all cubes.

Returns

Returns:

• CUBE_NAME
• DIM_NAME
• DESCRIPTION
• DEFAULT_MEMBER
• IS_TIME_DIMENSION
• TOP_OF_DIMENSION

Check DEFAULT_MEMBER before interpreting query totals. When a default member is configured, omitting that dimension from the POV resolves the query to the default member, includes the dimension in the result, and slices the result to that member rather than aggregating across the dimension.

Examples

Return information for one cube:

CALL CUBE.GET_CUBE_INFO('FINANCE');

Return information for all cubes:

CALL CUBE.GET_CUBE_INFO(NULL);

GET_CUBE_DIMENSIONS_META

Minimum role: CUBE_PUBLIC

GET_CUBE_DIMENSIONS_META(P_CUBE_NAME) -> TABLE

Returns dimension sizes and available alias sets for a cube.

Parameters

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

Returns

Returns:

• DIM_NAME
• TABLE_NAME
• DATA_TABLE_COL_NAME
• MEMBER_COUNT
• LEAF_COUNT
• ALIAS_COLS

Use MEMBER_COUNT to assess the likely size of broad member expansions before querying. ALIAS_COLS lists the alias-set names accepted by the ALIAS_TABLE parameter used by query procedures.

Example

CALL CUBE.GET_CUBE_DIMENSIONS_META('FINANCE');

SEARCH_MEMBERS

Minimum role: CUBE_PUBLIC

SEARCH_MEMBERS(INPUT_JSON) -> VARIANT

Finds members in a dimension by member name or alias. Use it to discover valid POV anchors before building a query.

Parameters

Parameter	Type	Req/Opt	Description
INPUT_JSON	VARCHAR	req	JSON object containing the search options described below.

INPUT_JSON supports:

JSON field	Req/Opt	Description
cube_name	req	Cube name.
dimension	req	Dimension to search.
search	opt	Search text. Omit to list members from the start of the outline.
alias_table	opt	Alias set to search.
limit	opt	Maximum number of results. Default is 100.

Returns

Returns a JSON object in this form:

{
  "members": [
    {
      "member": "...",
      "alias": "..."
    }
  ],
  "status": "SUCCESS"
}

Example

CALL CUBE.SEARCH_MEMBERS(
    '{"cube_name":"FINANCE","dimension":"PRODUCTS","search":"Digital","limit":25}'
);

LIST_DATA_TABLES

Minimum role: CUBE_PUBLIC

LIST_DATA_TABLES() -> TABLE

Lists the fact-data tables known to the application. This procedure has no parameters.

Returns

Returns:

• TABLE_NAME
• ROW_COUNT
• COLUMNS
• CREATED
• LAST_ALTERED

Use this procedure when preparing a load and you need to confirm which data tables are already available.

Example

CALL CUBE.LIST_DATA_TABLES();

LIST_DIMENSION_TABLES

Minimum role: CUBE_PUBLIC

LIST_DIMENSION_TABLES() -> TABLE

Lists the dimension source tables known to the application. This procedure has no parameters.

Returns

Returns:

• TABLE_NAME
• ROW_COUNT
• COLUMNS
• CREATED
• LAST_ALTERED

Use this procedure when preparing a dimension build and you need to confirm which source tables are already available.

Example

CALL CUBE.LIST_DIMENSION_TABLES();