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.

Latest Comments

  1. Ken says:

    Hi, thank for the article and very helpful and clear explanation.

    I have been dealing with a lot of intelligent cubes recenlty and I’m quite happy with all the features and functionalities. One thing that bothers me though, is the Delete one. I appreciate if you can give me some ideas in this case:

    I have a huge cube, 8 GB with 400M rows. For the sake of simplicity, let’s say I have Day, Shop, Manager and Sales. The cube is being maintained daily and its a 5 years rolling window cube. So everynight, we chop data older than 5 years of today and add yesterday.

    Now, when there is a change in a shop, which is usually the manager, people in business don’t want to keep the history of that, no SCD, they just overwrite the name of the guy! therefore, I have to propagate that change too. So, either I have to update rows for that store or delete and re-insert it. Unfortunately, update doesn’t help here because its not the metric that changed but the attribute and it add the new value make everything duplicate. On the other hand, delete doesn’t help becuase I can’t give it a list of stores flagged for change to be able to add the stores again. Even when I use the filter option of the incremental report, the generated SQL now wants to retreive everything from the DW and then remove the overlapps which means getting all the data once for deletion and then again for insertion.

    I am hoping if there is way to incrementally delete data from a cube wihtout getting the whole dataset form DWs.

    Thanks in advance,

    Ken

  2. Arthur says:

    Hi Ken
    Did you ever get an answer to this problem. I have a similar issue where we have a relationship between 2 attributes that can change (team and member of the team ). We only wish the current relation ship to be in the cube. The team member can move from Team A to Team B. We only wish to see the latest team (Team B) in the cube. I have a refresh report to delete all rows for the team member that moves. However when I run it I get the following error:
    (Error(s) occurred while loading report: DSSSQLEngine: converting Report : Error executing incremental refresh report C Delete Change Org FM1 Device Track and Trace GUID: A24DB3F34F8A54F4D604B1938FC921D5 The deletion report filter is either empty, has a metric qualification, or contains objects not on the target Intelligent Cube

    The cube is built using a view that only shows the current relationship between Team and Team Member, so I assume the delete is looking for rows for the member with a team of Team B, whereas I the member is associated with Team A in the cube.

    Is there a way to delete from the cube when selecting just on the member?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s