MicroStrategy ONE

Create a Freeform SQL Incremental Refresh Report (IRR)

Starting in MicroStrategy ONE (June 2024), you can use freeform SQL incremental refresh reports (IRRs) to update data in a freeform SQL cube.

Prerequisites

  • You must have the Use Freeform SQL Editor and Create schema objects privileges to access and use the Freeform SQL Editor.

  • To use, create, and edit freeform SQL IRRs, you must have the following privileges:

    • Use Report Editor

    • Use analytics

    • Create application objects

    • Modify the list of Report objects (use object browser)

    • Create configuration objects

    • Use design mode

    • Use Freeform SQL Editor

  • If there is no database instance defined in the metadata, the Freeform SQL Editor can not load and a message appears.

  • Database instances are available for selection only if they have been set up correctly for use with Freeform SQL.

    1. Right-click a project and choose Properties.

    2. Navigate to the Data Sources tab.

    3. Click Add Data Source.

Create Freeform SQL Incremental Refresh Reports

  1. Open the Workstation window.
  2. In the Navigation pane, click Datasets and find your freeform SQL cube.
  3. Right-click the cube and choose Define Incremental Refresh Report.
  4. In Refresh Type, select the refresh method from the following options:
    • Update: Adds new data if it does not already exist in the Intelligent cube. If there are matches between the data, the values are updated.
    • Insert: Adds only new data that is fetched by the IRR to the Intelligent cube without altering existing data in the cube.
    • Delete: Removes existing data from the Intelligent cube if it matches the definition criteria in the IRR.
    • Update Only: Replaces the existing Intelligent cube. This option does not add new data to the Intelligent cube.

      • Replace is not supported by freeform SQL IRRs.

  5. Click Edit Freeform SQL to switch to the SQL Editor.
  6. Edit the SQL and click Execute SQL to map the columns of the SQL statement.

    7. Attributes should not be edited. Metrics can only be the subset of original metrics.

  7. Go to the report view to view the new data.

Update a Freeform SQL Intelligent Cube with a Freeform SQL Incremental Refresh Report (IRR)

  1. After you create the freeform SQL incremental refresh report, in Datasets, right-click the dependent freeform SQL cube and choose Refresh Cube.

    You can also update a cube with a Freeform SQL IRR by right-clicking the IRR and click Execute > Execute.

  2. In the refresh cube page, click Use an Incremental Refresh Report.

  3. Select the IRR and click Refresh.