Tag Archives: 9.4.x

Tips & Tricks #6: How the Incremental Refresh Option Works in MicroStrategy 9.4.x

Starting with MicroStrategy 9.2.1, Intelligent Cubes have a new feature to update information without republishing it. It is referred to as Incremental Refresh. There are different ways each Incremental Refresh type option works.

Defining an Incremental Refresh Report

Prior to MicroStrategy 9.2.1, if the data in an Intelligent Cube needed to be updated, users had to re-publish the Intelligent Cube,  either manually or using a schedule. This process will cause all the data for the Intelligent Cube to be loaded from the data warehouse into Intelligence Server’s memory, so that the existing data for the Intelligent Cube is overwritten.

MicroStrategy 9.2.1 introduced a new feature known as Incremental Refresh Options, which allow Intelligent Cubes to be updated based on one or more attributes by setting up incremental refresh settings to update the Intelligent Cube with only new data. This can reduce the time and system resources necessary to update the Intelligent Cube periodically.

For example, if a user has an Intelligent Cube that contains weekly sales data, the user may want this Intelligent Cube to be updated at the end of every week with the sales data for that week. By setting up incremental refresh settings, he can make it so that only data for one week is added to the Intelligent Cube, without affecting the existing data and without having to reload all existing data.

Users can select two types of objects for the incremental fetch: a report or a filter.

  • 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.

In order to set up an incremental refresh report, the user should first right-click on the Intelligent Cube and select Define Incremental Refresh Report:

This will bring up the Incremental Refresh Options editor:

Here, the user can define one of the following Refresh type options:

  • Update:  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:  If new data is available, it is fetched and added to the Intelligent Cube. Data that was already in the Intelligent Cube is not altered.
  • Delete: The data that meets the filter or report’s definition is deleted from the cube. For example, if the Intelligent Cube contains data for 2008, 2009 and 2010, and the filter or report returns data for 2009, all the data for 2009 is deleted from the cube.
  • Update only: 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 type of object used for the incremental fetch can be selected in the Advanced tab:

Users simply have to run the incremental fetch report, and this will automatically refresh the data in the Intelligent Cube.

Incremental Refresh Options Examples

In this example, the following database table is used. This is a transaction table for item, status, quantity sold (qty_sold) and transaction number.

Cube definition:

Report Objects:

– Item

– Status

– Transaction Number

– Quantity

Filter: Transaction Number greater than or equal to 100

Initial data:

Data is updated as below on the database side:

Line 2 – qty_sold number is updated

Line 3 – status is altered from confirmed to canceled

Line 4 – newly added

Line 5 – newly added

UPDATE

Insert new rows from report data and overwrite overlapping rows between old cube data and report data.

Line 2 – qty_sold number is updated.

Line 3 – Status canceled row is newly inserted, and line 4, the original data is not modified. For any change for any other attribute, a new line is added and the previous line also persists.

Line 5 – Newly added transaction is inserted.

And the new data with transaction_number 1 is not added because it does not meet the filter criteria to have transaction_number >= 100.

INSERT

Only insert new, non-overlapping rows from report data.

Line 2 – qty_sold number is NOT updated.

Line 3 – Status canceled row is newly inserted.

Line 5 – Newly added transaction is inserted.

And the new data with transaction_number 1 is not added.

DELETE

Remove overlapping rows from old cube data.

Delete Incremental Refresh report is not executed against the warehouse, and executed for Intelligent Cube with the following query. All the data meeting the criteria is deleted.

SQL

Delete from CUBE  IncrementalRefreshTestwhere [Transaction Number]@[transaction_date] >=  100

UPDATE ONLY

Only overwrite overlapping rows from report data.

Line 2 – qty_sold number is updated.

Summary

In summary, when defining an Incremental Refresh report, take the following behavior into consideration.

  • Update/Update only option does not compare all the attribute elements.
  • Delete option is performed on the Intelligent Cube, and data is not compared with the warehouse.