INF: How Sorting Works in OLAP Services

ID: Q244263

The information in this article applies to:

Microsoft SQL Server OLAP Services version 7.0

SUMMARY

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

OLAP Services supports the following three different sort orders:

Case-insensitive

Case-sensitive

Binary

Case-insensitive 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)

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

Additional query words:

Keywords :
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo