Data Refresh Options:

1. Full Refresh: Same as in 9.0.1. Whole report will be refreshed
2. Dynamic Refresh:  Data will be refreshed based on the filter on Cube

Example:

• let’s say cube filter has ‘Today minus 5 days’
• Assume cube published on 18^th of September
• So the Cube will hold data from Sept-13^th to Sept-18
• What happens when cube refreshed on Sept-20^th (Now the cube should hold data from Sept -16^th to Sept-20^th)
• When the cube refreshed, it generates filter condition as follows;

Incremental Data fetch Condition

(a11.DAY_DT between DATE '2011-09-15' and DATE '2011-09-20'  and (not a11.DAY_DT between DATE '2011-09-13' and DATE '2011-09-18'))

So the report fetches data for only two days

Later part of the Cube SQL, it deletes the old data which not required for the ‘Between Today minus 5 days as Sept-20^th’

Data Deletion Condition:

([Day]@[DAY_DT] between 2011-09-13 00:00:00.000 and 2011-09-18 00:00:00.000

 and [Day]@[DAY_DT] not between 2011-09-15 00:00:00.000 and 2011-09-20 00:00:00.000)

• Update Option: Inserts new data and deletes overlapping rows between old and new

If any history gets updated then that can be also updated

• Insert Option: Just inserts the data in the cube memory

SQL pattern for Dynamic Refresh option when cube published for second time (recursive);

What is ‘Define Incremental Refresh Report Option’ (right click on Cube)

 Filter Option:

Let’s say, the cube hold data for 4 weeks of data for countries Canada and USA. Now I want to include a Germany alone for the same 4 weeks. Then instead of modifying the original cube…we can make use of ‘Incremental Refresh Report Options’

• Right click on Cube à select ‘define incremental refresh report options’
• Add Germany to the filter
• Save that as ‘Incremental Refresh’

Note:

• The filter should use the any one of the attribute present in the Cube
• Once incremental refresh is created, Cube should not refreshed only Incremental refresh can be scheduled
• Any number of Incremental refresh can be created against one cube

Report Option: (Haven’t tried much on this option yet)

From MSTR :  This option allows you to define a report that is used for the incremental refresh. When the incremental refresh is executed, the results of the report are compared to the data that is already in the cube, and is updated based on the option you choose in the Incremental Refresh Options, General tab). It must include all the attributes and metrics that are present in the Intelligent Cube. The Report should have at least one metric from the cube.

Dynamic Sourcing:

If a normal grid holds same or few objects of an Cube, then dynamically I-Server determines that report has to hit the DB or Cube memory. Since in this example, grid holds few or same number of objects as in CUBE, the I-Server makes this report to hit Cube

PS:Thanks to my colleague who also helped me in my busy schedule to collect few good points on this topic.