MicroStrategy ONE

Defining an incremental refresh for an Intelligent Cube

Once you have defined an Intelligent Cube, you can use the following methods to define the incremental refresh options, depending on your requirements:

  • Define Intelligent Cube republish settings. This is recommended if the Intelligent Cube must be updated along only one attribute, or if updates along multiple attributes must be made simultaneously.
  • Define an incremental refresh filter or report. This is recommended if the Intelligent Cube must be updated along different dimensions at different times, or if you want to use the results of a report to update the Intelligent Cube.

If you choose to define an incremental refresh filter or report, you should not re-publish the Intelligent Cube by double-clicking, or by publishing it on a schedule. Doing so will overwrite any changes made by the incremental refresh filter or report.

Defining Intelligent Cube refresh settings

The refresh settings change the way the Intelligent Cube is updated when you re-execute it manually, or when it is published on a schedule, as described in Publishing Intelligent Cubes using a schedule.

This is recommended if the Intelligent Cube must be updated along only one attribute, or if Intelligent Cube must be updated along multiple attributes simultaneously.

Prerequisites

  • The Intelligent Cube's definition must include a filter that qualifies on the attributes for which the Intelligent Cube must be updated.

    For example, if the Intelligent Cube must be updated with new data for the Store attribute, the filter must qualify on Store.

To define Intelligent Cube refresh settings

  1. In Developer, navigate to the Intelligent Cube you want to define republish settings for.
  2. Right-click on the Intelligent Cube, and select Edit. The Intelligent Cube Editor opens.
  3. On the toolbar, choose Data > Configure Intelligent Cube. The Intelligent Cube Options dialog box opens.
  4. Navigate to the Data Refresh category.
  5. Choose one of the following options:

  • Full Refresh: This is the default. The Intelligent Cube's SQL is re-executed, and all the data is loaded from the data warehouse into Intelligence Server's memory.

    Use this option under the following conditions:

    • If the data in the Intelligent Cube is outdated.
    • If the Intelligent Cube must be re-processed based on a metric. For example, an Intelligent Cube that contains data for the top 200 stores by Profit must be re-calculated every time it is updated, and thus must use the Full Refresh option.

  • Dynamic refresh: The Intelligent Cube's filter is evaluated. If new data is returned, it is added to the Intelligent Cube, and data that no longer meets the filter's criteria is deleted from the Intelligent Cube. The dynamic refresh option is illustrated in the image below.

    Select this option for Intelligent Cubes that have a rolling set of data—for example, an Intelligent Cube that always contains data for the past six months.

    If the data to be added and deleted cannot be determined, the Full Refresh option is used as a fallback.

  • Update: The Intelligent Cube's filter is evaluated. If new data is returned, it is added to the Intelligent Cube, and if the data returned is already in the Intelligent Cube, it is updated where applicable.

    Select this option if your data is updated often—for example, an Intelligent Cube that contains daily sales data, and is updated at the end of every day.

  • Insert: The Intelligent Cube's filter is evaluated. If new data is returned, it is added to the Intelligent Cube. Data that is already in the Intelligent Cube is not altered.

    Select this option if old data does not change once it is saved to your data warehouse.

Defining an incremental refresh filter or report

For complex update requirements, such as updating an Intelligent Cube for different dimensions at different intervals, you can define an incremental refresh filter or report to update your Intelligent Cube.

For example, an Intelligent Cube contains monthly sales data for 2009 and 2010 for year-on-year comparison. Once the year 2011 begins, you only need to keep the data for 2010, and the data for 2009 can be removed from the Intelligent Cube. You can define one incremental refresh that runs at the end of every month, and adds that month's data to the Intelligent Cube, and a second incremental refresh that deletes the previous year's data at the end of every year.

You can also add incremental refreshes to add data for other dimensions, such as Country. For example, you have an Intelligent Cube that contains data for the USA, UK and France, and you want to add data for Germany. You can define an incremental refresh that fetches all the data for Germany, and adds it to the cube.

You can define incremental refreshes using filters or reports, as described below:

  • Filter: The data returned by a filter is compared to the data that is already in the cube. By default, the filter defined for the Intelligent Cube is used as the filter for the incremental refresh.
  • Report: The results of a report are used to populate the Intelligent Cube. By default, the report template used is the same as the Intelligent Cube's template.

If you choose to define an incremental refresh filter or report, you should not re-publish the Intelligent Cube by double-clicking it, or by publishing it on a schedule. Doing so will overwrite any changes made by the incremental refresh filter or report.

Defining an incremental refresh filter

You can define an incremental refresh filter to update the data in an Intelligent Cube. This is the default option for both ROLAP and MDX Intelligent Cubes, and is unavailable for Intelligent Cubes created using Freeform SQL queries or Query Builder.

To define an incremental refresh filter

  1. Navigate to the Intelligent Cube for which you want to define the incremental refresh.

  2. Right-click the Intelligent Cube, and select Define incremental refresh. The Incremental Refresh Options dialog box opens.

  3. Under Refresh type, select one of the following options.

    • Update: The incremental refresh filter is evaluated. If new data is available, it is fetched and added to the Intelligent Cube, and if the data available is already in the Intelligent Cube, it is updated where applicable.

    • Insert: The incremental refresh filter is evaluated. If new data is available, it is fetched and added to the Intelligent Cube. Data that is already in the Intelligent Cube is not altered.

    • Delete: The incremental refresh filter is evaluated. The data that meets the filter's criteria is deleted from the cube. For example, if the Intelligent Cube contains data for 2021, 2022, and 2023, and the filter is for data for 2022, all the data for 2022 is deleted from the cube.

    • Update only: The incremental refresh filter is evaluated. If the data available is already in the Intelligent Cube, it is updated where applicable. No new data is added to the Intelligent Cube.

    • Replace: The incremental refresh filter is evaluated. Old cube data is deleted using the report filter and replaced with new data. Your metadata must be upgraded to MicroStrategy 2021 or above to use this option.

    You can change these options at any time by opening the incremental refresh in the Report Editor, and from the Data menu, selecting Configure incremental refresh options.

  4. Click OK. The Report Editor opens with a new incremental refresh. If the Intelligent Cube's definition included a filter, it appears in the Report Filter pane.

  5. In the Report Filter pane, edit the filter if applicable, or create a new filter.

  6. To preview the data that will be updated in the Intelligent Cube, from the View menu, select Preview Data. The data is displayed in a grid view.

    If your security filter prevents you from viewing some data, the preview only displays data that you can view. However, when the incremental refresh is executed, all the data for the filter is updated in the Intelligent Cube, regardless of security filters.

  7. To execute the incremental refresh immediately, click the Run Report toolbar button.

  8. To save and close the incremental refresh, click Save and Close.

Defining an incremental refresh report

You can define a report to update an Intelligent Cube. The results of a report are compared to the data in the Intelligent Cube, and the Intelligent Cube is updated accordingly.

If you are updating an Intelligent Cube based on a Freeform SQL or Query Builder report, this is the only available option.

Prerequisites

  • The report must use all the attributes, and at least one metric from the Intelligent Cube that is being updated. Note that for metrics that are not on the report's template, data is not updated.
  • All attributes and metrics in the report's definition should be identical to the attributes and metrics in the Intelligent Cube. You can verify this by checking the Global Unique Identifiers (GUIDs), by right-clicking the attribute or metric, and choosing Properties.

To define an incremental refresh report

  1. Navigate to the Intelligent Cube for which you want to define the incremental refresh.

  2. Right-click the Intelligent Cube, and select Define Incremental Refresh. The Incremental Refresh Options dialog box opens.

  3. Under Refresh type, select one of the following options. The differences between the options are illustrated in the image below.

    • Update: The incremental refresh report is evaluated. If new data is available, it is fetched and added to the Intelligent Cube, and if the data returned is already in the Intelligent Cube, it is updated where applicable.

    • Insert: The incremental refresh report is evaluated. If new data is available, it is fetched and added to the Intelligent Cube. Data that is already in the Intelligent Cube is not altered.

    • Delete: The incremental refresh report is evaluated. The data that is meets the report's definition is deleted from the cube. For example, if the Intelligent Cube contains data for 2021, 2022, and 2023, and the report returns data for 2022, all the data for 2022 is deleted from the cube.

      If you are using the Delete option,you may use a report with only a subset of the attributes present in the Intelligent Cube.

    • Update only: The incremental refresh report is evaluated. If the data available is already in the Intelligent Cube, it is updated where applicable. No new data is added to the Intelligent Cube.

      The Replace option is not available here, since it can only be used for incremental refresh filters.

    You can change these options at any time by opening the incremental refresh in the Report Editor, and from the Data menu, selecting Configure Incremental Refresh options.

  4. Navigate to the Advanced category.

  5. Select Report and click OK. The Report Editor opens, with a new incremental refresh report. By default, the report's template contains all the attributes and metrics from the Intelligent Cube.

  6. Edit the report, if necessary.

  7. To preview the data that will be updated in the Intelligent Cube, from the View menu, select Preview Data. The data is displayed in a grid view.

    If your security filter prevents you from viewing some data, the preview only displays data that you can view. However, when the incremental refresh is executed, all the data for the filter is updated in the Intelligent Cube, regardless of security filters.

  8. To execute the incremental refresh immediately, click Run Report.

  9. To save and close the incremental refresh, click Save and Close.