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.
- 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.
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.
– Item
– Status
– Transaction Number
– Quantity
Filter: Transaction Number greater than or equal to 100
Initial data:
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.
SQL
Delete from CUBE IncrementalRefreshTestwhere [Transaction Number]@[transaction_date] >= 100
UPDATE ONLY
Only overwrite overlapping rows from report data.
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.
Comments