Saved Queries & Scheduling

Saved queries are named points of view stored with a cube. They let you define a report once and reuse its POV. Saved queries can also be scheduled to materialize into reporting tables on a recurring basis.

MANAGE_SAVED_QUERY

Minimum role: CUBE_ADMIN

MANAGE_SAVED_QUERY(
    P_ACTION,
    P_PARAMS
) -> VARCHAR

Creates, updates, or deletes saved queries.

Parameters

Parameter	Type	Req/Opt	Description
P_ACTION	VARCHAR	req	ADD, UPDATE, or DELETE.
P_PARAMS	VARCHAR	req	JSON object containing the saved-query parameters. For ADD, cube_name and query_name are required. Supply pov as a JSON object.

Example

CALL CUBE.MANAGE_SAVED_QUERY('ADD', '{
  "cube_name":"FINANCE",
  "query_name":"Monthly P&L",
  "pov":{
    "ACCOUNT":[{"idescendants":"Net Income"}],
    "PERIOD":[{"children":"YearTotal"}],
    "VIEW":["MTD"],
    "SCENARIO":["Actual"],
    "YEARS":["&CurrentYear"]
  }
}');

GET_SAVED_QUERIES

Minimum role: CUBE_PUBLIC

GET_SAVED_QUERIES(P_CUBE_NAME) -> TABLE

Lists the saved queries defined for a cube.

Parameters

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

Returns

Returns:

• QUERY_NAME
• POV
• FORMULAS

QUERY_CUBE accepts a POV rather than a saved-query name. To run a saved query interactively, retrieve its POV with GET_SAVED_QUERIES and pass that POV to QUERY_CUBE. For unattended execution, schedule the saved query.

Example

CALL CUBE.GET_SAVED_QUERIES('FINANCE');

SCHEDULE_SAVED_QUERY

Minimum role: CUBE_ADMIN

SCHEDULE_SAVED_QUERY(
    P_CUBE_NAME,
    P_QUERY_NAME,
    P_TARGET_TABLE,
    P_SCHEDULE,
    P_WAREHOUSE_SIZE,
    P_ENABLED
) -> VARCHAR

Schedules a saved query to materialize into a reporting table on a recurring basis.

Each execution fully replaces the target table so consumers read a complete current snapshot.

Parameters

Parameter	Type	Req/Opt	Description
P_CUBE_NAME	VARCHAR	req	Cube name.
P_QUERY_NAME	VARCHAR	req	Active saved-query name.
P_TARGET_TABLE	VARCHAR	req	Unqualified target table name. The table is created in SHARED_DATA; targets outside that schema are rejected.
P_SCHEDULE	VARCHAR	opt	Cron expression or interval.
P_WAREHOUSE_SIZE	VARCHAR	opt	XSMALL, SMALL, MEDIUM, LARGE, or XLARGE.
P_ENABLED	BOOLEAN	opt	Whether to start the schedule immediately.

The application requires EXECUTE MANAGED TASK to run scheduled queries. Re-running SCHEDULE_SAVED_QUERY for the same saved query replaces its existing schedule.

Schedule formats

USING CRON 0 6 * * * America/New_York
USING CRON 0 8 * * MON-FRI America/New_York
60 MINUTE

These examples represent daily at 06:00 Eastern, weekdays at 08:00 Eastern, and hourly execution, respectively.

Example

CALL CUBE.SCHEDULE_SAVED_QUERY(
    'FINANCE',
    'Monthly P&L',
    'RPT_MONTHLY_PL',
    'USING CRON 0 6 * * * America/New_York',
    'SMALL',
    TRUE
);

Caution

The materialized table is written under the identity that runs the schedule. Consumers subsequently read the materialized table rather than querying the cube.

Do not use one materialized table to serve audiences that require different data access.

LIST_SAVED_QUERY_SCHEDULES

Minimum role: CUBE_ADMIN

LIST_SAVED_QUERY_SCHEDULES(P_CUBE_NAME) -> TABLE

Lists registered saved-query schedules, including their current live state and most recent run outcome.

Parameters

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

Returns

Returns:

• QUERY_NAME
• TARGET_TABLE
• SCHEDULE
• WAREHOUSE_SIZE
• STATE
• LAST_RUN_AT
• LAST_RUN_STATUS
• LAST_RUN_ROWS
• LAST_RUN_MS
• LAST_RUN_ERROR
• TASK_NAME

STATE is read live. If a schedule is suspended outside the application, including suspension after repeated failures, the procedure reports the current state accurately.

Example

CALL CUBE.LIST_SAVED_QUERY_SCHEDULES(NULL);

SET_SAVED_QUERY_SCHEDULE_STATE

Minimum role: CUBE_ADMIN

SET_SAVED_QUERY_SCHEDULE_STATE(
    P_CUBE_NAME,
    P_QUERY_NAME,
    P_ENABLED
) -> VARCHAR

Suspends or resumes a saved-query schedule without discarding its configuration.

Parameters

Parameter	Type	Req/Opt	Description
P_CUBE_NAME	VARCHAR	req	Cube name.
P_QUERY_NAME	VARCHAR	req	Scheduled saved-query name.
P_ENABLED	BOOLEAN	req	TRUE resumes the schedule; FALSE suspends it.

Example

CALL CUBE.SET_SAVED_QUERY_SCHEDULE_STATE(
    'FINANCE',
    'Monthly P&L',
    FALSE
);

UNSCHEDULE_SAVED_QUERY

Minimum role: CUBE_ADMIN

UNSCHEDULE_SAVED_QUERY(
    P_CUBE_NAME,
    P_QUERY_NAME
) -> VARCHAR

Removes and deregisters a saved-query schedule.

Parameters

Parameter	Type	Req/Opt	Description
P_CUBE_NAME	VARCHAR	req	Cube name.
P_QUERY_NAME	VARCHAR	req	Scheduled saved-query name.

Note

Unscheduling a query deliberately leaves its materialized reporting table in place because dashboards or other consumers may still depend on it. Drop the table separately if you also want to remove the materialized results.

Example

CALL CUBE.UNSCHEDULE_SAVED_QUERY(
    'FINANCE',
    'Monthly P&L'
);