Skip to main content

Configure Stored Procedure

A stored procedure is a predefined SQL routine that can be executed automatically before each cube job (refresh or load). Stored procedures allow you to perform custom pre-processing steps, such as updating staging tables, recalculating flags, cleaning data, or preparing aggregates. When configured, the stored procedure runs before every scheduled or manual cube job.

When to configure stored procedures​

  • Preparing source data: Set up staging tables or intermediate data needed for the cube.
  • Optimizing job performance: Reduce the amount of data processed during cube build or refresh.
  • Enforcing business rules: Apply calculations to ensure cube data meets required conditions before loading.

Make the cube editable​

  1. In the Administration page, select OLAP Manager from the sidebar.
  2. In the cube list, select the cube where you want to configure tracking.
  3. Clear the In Production checkbox.
  4. Click Save.

Configure a stored procudure​

  1. In the Options panel on the right, click the General icon.
  2. Select Stored Procedure.
    Or right-click the Workspace and select Stored Procedure.
  3. Enter the SQL command for the procedure. Typically, this procedure might update specific tables or set flags that will be used by the data source during the cube build.
  4. Click Confirm.

Examples​

In this example, @LIB_ is automatically replaced at runtime with the correct environment’s database and schema name:

exec @LIB_UpdateFactTable

For cubes with complex joins, use a stored procedure to populate a staging table with only the required records. This reduces job runtime by allowing the cube to process prepared data:

exec @LIB_PrepCubeStagingData