KB30379: How does a change in the code page or collation settings of the data warehouse affect MicroStrategy 10.x

In general, a change in the code page or collation settings for a database or tables in a data warehouse is transparent to Strategy. This is because the Strategy Intelligence Server does not attempt to sort report results unless an explicit sort criteria is specified. In the case that a specific sort criteria is specified for report results, the Analytical Engine component in the Strategy Intelligence Server or Desktop product is responsible for the sorting of data. Since this sorting is dependent on the local (Strategy Intelligence Server or Desktop product) locale and code page, a change in the database code page will not typically affect the displayed results.
 
However there are some operations that depend on the order in which data is sorted before being returned to the Intelligence Server and the results of these operations would change based on the new data warehouse settings. The following discussion explains situations where users might experience changes as a result of the change in the code page:
 

• Reports using olap functions (such as moving sum etc) that sort metrics according to an attribute form: With a change in the collation settings the order of the attributes in the results could change affecting the results of the olap function.
   
  For example users should consider a report using the MovingAverage function with a window size > 1. The window size is the number of rows around each result row that will be used in the olap function calculation. The window size > 1 makes the result of the olap function susceptible to changes in the ordering of the data.
  
  In addition to using the function however, the other criteria that would determine whether such an olap function could return different results is the question of whether the function is evaluated in the analytical engine or on the data warehouse. If  the warehouse database does not support the function specific function, the Analytical engine component would perform the function calculations and would yield consistent results, regardless of a change in the data warehouse code page. However if the database has the capacity to support the function, and the results are calculated in the SQL passed to the warehouse there is a potential to see different results with a change in the database code page.
   
  
• Reports with a rank filter based on string data: Because the rank function is sensitive to a changes in code page / collation settings, the filter using the rank function could return different results for the report.
   
  For example - consider a scenario as follows:
  
1. User creates a metric based on an attribute form such as "Max(Region@Desc)"
2. A report is created  - modify the filter condition to be based on a set qualification
3. For the set qualification users use the metric from step (1) and a qualification using the rank function such as "Rank of the top 5"
4. The user now adds the "Employee" attribute to the report template - thus choosing the Employees from the top 5 regions based on their Region Description.
• The results of a report built similarly would be dependent on the evaluation of the rank function which in turn is dependent on the sort order of the character data in the database.
• The sort order of attribute elements in element prompts: As mentioned earlier, when sorting is performed by the Analytical engine component, the database sort order will not affect the results of a report. Even though prompt elements use the same report architecture, the key difference in the display of prompt elements and report results is an Intelligence Server feature called "Incremental fetch for prompt elements". This feature allows the Intelligence Server to only return a partial set of the prompt attribute elements for display, and if the user browses further, another set of results is retrieved from the data warehouse and displayed.The reason for this feature is to reduce the Intelligence Server memory usage, especially for large prompt result sets.
  
  The reason this feature is affected by the change in the database code page or collation settings is because to satisfy the requirements, the Intelligence server orders the results from the database by attribute element description (if a sort order is defined for the attribute, otherwise only the attribute element ID is used). So the analytical engine component only receives a subset of the entire results and the displayed order of the prompt results may vary significantly.
  
  For example consider the following two screenshots. The first set of data is shown with the database using a regular Latin1 code page and collation sequence (case insensitive). The second screenshot shows the displayed results for a binary collation sequence (sorting A - Z and then a - z). Because the database sorts the lower case characters after the upper case ones, the lower case prompt elements are not retrieved in the first set of results as per the incremental fetch feature, and so even with the analytical engine ranking of the results, they are not see in the same position.
  
  

• Above: Prompt elements displayed with the database sort order case insensitive.
  
  

• Above: Prompt elements displayed with the database sort order set to binary.