I was surprised today when i saw a post by the end user on the MicroStrategy Discussion Forum – Stating that sorting varies from MicroStrategy 8.x to 9.x. Especially, she highlighted with the special characters in between the letters.
For example ABC-DEF and ABC DEF,
In 8.0, the sorting was
ABC-DEF
ABC DEF
But in 9.3, I am seeing
ABC DEF
ABC-DEF
I found on searching at the knowledge base – what is the change that MicroStrategy has provided in that & it was quite informative & thought can share to everyone for their knowledge. Hope it helps you all for the day!!
New sorting logic for the Analytical Engine in MicroStrategy 9.x
In previous versions of the MicroStrategy products, alphabetical sorting of attribute elements and objects was dependent on the platform – the Analytical Engine in the MicroStrategy Desktop machine (Microsoft Windows) performed sorting in 3-tier and 2-tier mode, while the Analytical Engine on the Intelligence Server (Microsoft Windows or Unix/Linux machines) performed sorting for 4-tier (Web) requests.
In each environment, the Analytical Engine uses the platform specific localization settings to determine the sort order rules to follow. This meant that in 4-tier mode, the sorting result relied on the locale settings for the account under which the Intelligence Server ran whereas in 3-tier mode, the sorted results depended on the client MicroStrategy Desktop environment.
Another problem with the previous architecture is that different platforms used different sort orders rules even for the same locales resulting in slightly different results depending on the operating system platform. Finally, sorting results would be different when the Intelligence Server on the same platform runs under an account with different locales on different machines.
To solve this inconsistency between different environments, a new sorting logic has been adopted for the Analytical Engine with the release of MicroStrategy 9.0.
New Sorting Logic in MicroStrategy 9.0 – Locale Dependent Sorting:
In MicroStrategy 9.0, Locale Dependent Sorting has been introduced to solve the problems described previously. The MicroStrategy Engine uses the ICU (International Components for Unicode) libraries are used for collation, across all products. As these libraries are standard across all platforms, the sorting results and rules are unified across different platforms as well.
ICU implements the Unicode Collation Algorithm, which is a multi-level sort. The rules are as follows:
- If there are any differences in base letters, this difference determines the sort result order.
- Otherwise, if there are any differences in accents, this is the next factor that affects the sort order.
- Otherwise, if there are any differences in case, this is the next factor that affects sort order.
- Otherwise, if there are any differences in punctuation, this affects the sorted results.
Since the logic used to sort has changed between MicroStrategy 8.x and MicroStrategy 9.x, users can expect to see some differences in the sort order of results. The most significant difference users are likely to notice is the difference in the sorting of special characters.
| MicroStrategy 8.1.2 (Windows) | MicroStrategy 9.0 |
| a–b | a b |
| a b | a b |
| a b | a^b |
| a!b | a_b |
| a#b | a–b |
| a$b | a!b |
| a%b | a?b |
| a&b | a(b |
| a(b | a)b |
| a)b | a@b |
| a*b | a*b |
| a/b | a/b |
| a?b | a\b |
| a@b | a&b |
| a\b | a#b |
| a^b | a%b |
| a_b | a~`b |
| a~`b | a$b |
| ab | ab |
Users can refer to the following links for additional information related to collation and ICU sorting:
Collation: http://en.wikipedia.org/wiki/Collation
The following image shows the ascending sort order of the first 128 ASCII characters, when the locale is EN-US using the new ICU sort logic in MicroStrategy Desktop 9.0. Users should note the following:
- The number beside each character is the decimal ASCII code for that character.
- Some characters cannot be displayed, users should refer to the ASCII code for this reference.
- The relationship between the characters in the first 28 rows is EQUAL. (Characters numbered as 0~8, 14~31, 127) – i.e the characters are considered “equal” to each other. So the final sort order on the report is dependent on the order in which the characters are retrieved from warehouse.
- The actual sort order may defer slightly from the result in the picture below, depending on the user’s locale.
Specific differences between the system level sort order in the MicroStrategy Intelligence Server 9.x when compared to the Intelligence Server 8.x are as follows:
- Each MicroStrategy user has a locale associated with their user session.
- Display sorts for report and document results are performed using the collation associated with the users locale in MicroStrategy 9.x rather than the locale for the system for the MicroStrategy Intelligence Server or client application as in MicroStrategy 8.x.
- By switching to the Unicode Collation Algorithm, users will see consistent sorting as per their locale setting no matter what the underlying operating system platform used for the MicroStrategy Intelligence Server is (Solaris, Linux etc) , or the client used for viewing the report data (MicroStrategy Web, Desktop etc) or any other environment variable.
- Unfortunately as a result of switching to the new ICU libraries, there is no way to provide the same sort order as was available in MicroStrategy 8.x since this was operating system dependant, and the ICU libraries use their own set of collation maps.
Have A Good Day!!