Querying

The procedures in this section provide the primary interfaces for querying Casabase Cube.

QUERY_CUBE

Minimum role: CUBE_PUBLIC

QUERY_CUBE(CUBE_NAME, POV_STRING, OUTPUT_TABLE, ALIAS_TABLE) -> TABLE

The primary query interface. Resolves a point of view into a result set, applying hierarchy aggregation, member formulas, time intelligence, and row-level security.

The fact table and measure column come from the cube configuration. You do not supply them in the procedure call.

Parameters

Parameter	Type	Req/Opt	Description
CUBE_NAME	VARCHAR	req	Cube to query.
POV_STRING	VARCHAR	req	JSON point of view. Use one key per dimension. Every dimension value must be a JSON array containing member names and/or operator objects.
OUTPUT_TABLE	VARCHAR	opt	Unqualified table name. When supplied, results are written to SHARED_DATA.<name> using full replace, and a status row is returned instead of the query result.
ALIAS_TABLE	VARCHAR	opt	Alias set to use for display names, such as Default.

Returns

Returns a table containing the dimensions named in the POV plus AMT.

Dimensions omitted from the POV do not appear as columns unless they have a configured default member.

Caution

Every POV dimension value must be a JSON array.

This fails:

{"TIME":"Qtr1"}

This works:

{"TIME":["Qtr1"]}

Commas inside a string are not interpreted as member separators.

Examples

Query a single figure:

CALL CUBE.QUERY_CUBE(
    'FINANCE',
    '{"MEASURES":["Units"],"TIME":["Qtr1"],"YEARS":["Curr Year"]}',
    NULL,
    NULL
);

Query children of a member and return display names from an alias table:

CALL CUBE.QUERY_CUBE(
    'FINANCE',
    '{"MEASURES":["Units","Transactions"],
      "TIME":[{"children":"Qtr1"}],
      "YEARS":["Curr Year"]}',
    NULL,
    'Default'
);

Write results to a reporting table:

CALL CUBE.QUERY_CUBE(
    'FINANCE',
    '{"MEASURES":["Units"],"TIME":[{"bottom":"Time"}],"YEARS":["Curr Year"]}',
    'Q1_DETAIL',
    NULL
);

SELECT *
FROM CUBE_DEV_APP.SHARED_DATA.Q1_DETAIL;

POV operators

Supported POV operators are:

member, children, ichildren, descendants, idescendants, bottom, parent, ancestors, iancestors, siblings, isiblings, root, members, and uda.

An i prefix includes the anchor member.

Caution

Do not use {"leaves":"Member"} to request leaf descendants. Use {"bottom":"Member"} instead.

QUERY_CUBE_PIVOT_JSON

Minimum role: CUBE_PUBLIC

QUERY_CUBE_PIVOT_JSON(CUBE_NAME, POV_STRING, COLUMN_DIMS, ALIAS_TABLE) -> VARIANT

Uses the same query semantics as QUERY_CUBE, but returns the result as a pivoted JSON object with column headers. It is intended for spreadsheet add-ins and grid components.

Parameters

Parameter	Type	Req/Opt	Description
CUBE_NAME	VARCHAR	req	Cube to query.
POV_STRING	VARCHAR	req	JSON point of view using the same format as QUERY_CUBE.
COLUMN_DIMS	ARRAY	opt	Dimensions to pivot into columns. At least one is required for a pivoted result. Maximum two.
ALIAS_TABLE	VARCHAR	opt	Alias set to use for display names.

Returns

Returns a JSON object containing:

• row_dimensions
• column_dimensions
• column_names
• headers
• data
• row_count
• column_count
• max_rows
• truncated

Check truncated before relying on a large result set.

Example

CALL CUBE.QUERY_CUBE_PIVOT_JSON(
    'FINANCE',
    '{"MEASURES":["Units","Transactions"],
      "TIME":[{"children":"Qtr1"}],
      "YEARS":["Curr Year"]}',
    ARRAY_CONSTRUCT('TIME'),
    NULL
);