KB46248: Dynamic sourcing does not work with error "CMI_FILTERS_CANNOT_ENSURE_ALL_DATA_NEEDED_IS_IN_CUBE" when the cube is updated using incremental refresh reports with different time range filter in MicroStrategy Server 9.4.x

SYMPTOM:
 
Dynamic sourcing does not work with error "CMI_FILTERS_CANNOT_ENSURE_ALL_DATA_NEEDED_IS_IN_CUBE" when the cube is updated using incremental refresh reports with different time range filter in Strategy Server 9.4.x.
 
STEPS TO REPRODUCE:
 

1. Create a cube with Year, Quarter, Month, Day, and metric Cost. The filter is "Day(ID) between 1/1/2011 and 12/31/2011" as below and publish the cube.

• Right click the cube and create two Incremental Refresh Reports to update the cube. And the filter is “Day(ID) between 1/1/2010 and 12/31/2010” and “Day(ID) between 1/1/2009 and 12/31/2009” respectively in each Incremental Refresh Report as below:

 
 
 
 
 
Sample Code/Error
Error code legend:
CMI_FILTERS_CANNOT_ENSURE_ALL_DATA_NEEDED_IS_IN_CUBE - The Intelligent Cube cannot be used for dynamic sourcing for the report because it cannot be verified that the Intelligent Cube filter is less restrictive than the report filter.
CMI_REPORT_TEMPLATE_ATTRIBUTE_FORM_MISSING_FROM_CUBE_TEMPLATE - A report template attribute or attribute form is missing from the cube template 
  
CAUSE:
 
This is working as designed. When we do insert to update the cube, it is not doing automatically merge with two filter expression: "Day(ID) between 1/1/2009 and 12-31-2009" and "Day(ID) between 1/1/2010 and 12/31/2010". We just keep them as separate expression, not a continued time range. When we do dynamic sourcing with filter express crossing different years, our engine could not find exactly matched filter expression, which will cause no cube hit.
 
ACTION:
 
Make sure these filters could merge into a continued time range by defining them with common point. For instance, the two filters in the two Incremental Refresh Reports could be "Day(ID) between 1/1/2009 and 1/1/2010" and "Day(ID) between 1/1/2010 and 1/1/2011", then the engine will merge these two filter expressions into one, since there is a common point 1/1/2010.
 
 

• Run the two Incremental Refresh Reports to update the cube with data from year 2010 and year 2009.
• Create a Dynamic Sourcing report with filter within the same year, for instance, "Day(ID) between 1/1/2009 to 12/1/2009, and the cube will be hit. However, if the Dynamic Sourcing report is with filter across different years, for instance "Day(ID) between 3/1/2009 to 12/7/2011", the cube will not be hit, and getting below error in SQL view: