How sorting works in OLAP Services

Article translations Article translations
Article ID: 244263 - View products that this article applies to.
This article was previously published under Q244263
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SUMMARY

Important This article contains information about how to modify the registry. Make sure to back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows registry


OLAP Services supports the following three different sort orders:
  • Case-insensitive
  • Case-sensitive
  • Binary
Case-insensitive and accent-sensitive is the default sort order installed.

MORE INFORMATION

There is no user interface that allows you to change the sort order if you are running English OLAP Services on English Windows NT. However, if you are running OLAP Services on a non English Windows NT version (for example, a Japanese version), you can change the sort order by choosing the appropriate value in the Sort order dialog box, which is found in the Processing tab of the Server Properties dialog box.

Otherwise, to change the value you must change the registry value for the following keys:
HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\CurrrentVersion\CompareCaseNotSensitiveStringFlags HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\CurrrentVersion\CompareCaseSensitiveStringFlags
For case-insensitive, the value should be:
CompareCaseNotSensitiveStringFlags (0x00011001) CompareCaseSensitiveStringFlags (0x00000000)
For case-sensitive, the value should be:
CompareCaseNotSensitiveStringFlags (0x00000000) CompareCaseSensitiveStringFlags (0x00000000)
For binary, the value should be:
CompareCaseNotSensitiveStringFlags (0x00000002) CompareCaseSensitiveStringFlags (0x00000002)
For accent-insensitive, the value should be:
CompareCaseNotSensitiveStringFlags (0x00011011) CompareCaseSensitiveStringFlags (0x00000000)
The sorting of dimension members are controlled through this setting. Note that you want to match the sort order of your OLAP Services with that of the relational database (for example SQL Server). Otherwise, processing of your cube may fail with the following error:
Error (211): Unknown dimension member.
Also, if you change the sort order setting for a server, you need to reprocess your cubes before querying them to make sure that your cubes show correct results. For example, lets assume your dimension table contains two members, John and john. For the case insensitive server, you would have only one member (for these two members) in the cube. However, if you change the sort order to be case sensitive, you would expect to see separate entries for John and john but you will still have only one member until you reprocess the cube. Similarly, the order in which a member is kept in the list will also not change until you reprocess the cube to apply the current settings.

Note that OLAP Services does not invalidate the structure of cubes after the sort order has changed and does not force you to reprocess the cube.

An OLAP client can have a different sort order than the server based on the same registry keys mentioned previously (if present) or on the connection string parameter CompareCaseNotSensitiveStringFlags and CompareCaseSensitiveStringFlags. It is important to understand that a mismatch in a client and server sort order can cause incorrect results for some queries.

REFERENCES

For more information about connection string properties, see the following topics in Microsoft SQL Server Books Online:
  • DBPROP_MSMD_COMPARECASENOTSENSITIVESTRINGFLAGS Property
  • DBPROP_MSMD_COMPARECASESENSITIVESTRINGFLAGS Property

Properties

Article ID: 244263 - Last Review: February 28, 2014 - Revision: 5.2
APPLIES TO
  • Microsoft SQL Server OLAP Services
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbnosurvey kbarchive kbinfo KB244263

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com