Article ID: 244263
Article Last Modified on 7/6/2006
APPLIES TO
- Microsoft SQL Server OLAP Services
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q244263
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:
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
Keywords: kbinfo KB244263
