I'm using english sql2k/olap2k both with sp3a. The sql2k server and all db:s collation are Finnish_Swedish_CI_AS, the default language for user as well as my own language is set to Swedish. The regional serversettings, w2k, are also set to Swedish. In the properties for the olap data source the locale is 1053 (that number is for Swedish/Finnish).
I have a table with cityname column (char) and citycode column (smallint) and when I create a dimension and process it the citynames are sorted in correct Swedish alphabetical order i.e. the swedish letter Ö is sorted last in the list.
When I use this dimension in a cube (citycode in dimension related to citycode column in cubes fact table) and process the cube the citynames are no longer in swedish alphabetical order i.e. swedish letter Ö is sorted after letter O. Furthermore when I look in the dimension itself the sorting has changed to the wrong one as in the cube.
I am aware of possibility to use a third column in the dimension table for sorting purpose (as member property) but why should I have to do so? All documentation that I could find states that my configuration automatically should sort in correct alphabetical order and so says also Michael Kaplan in his document 'International Features in Microsoft SQl Server 2000'.
Any idea anybody?