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.
Michael Sandberg's Data Visualization Blog 
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
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?
Arthur- You just need to add the filter with the attribute which you want to delete. Basically if your refresh report is created from the cube which has the filters. By default it adds the filters from cube to the incremental refresh report. And if you select the Filter based refresh option you get the below error.
The deletion report filter is either empty, has a metric qualification, or contains objects not on the target Intelligent Cube
So you just need to use the filter condition to delete the data in the cube.
Cheers.