MDX RANK function: unexpected results

• Antonio-1150806

  SSC Rookie

  Points: 31

  More actions

  August 31, 2009 at 3:50 am

  #204170

  Hi all,

  i'm using SQL Server 2005 (build 9.00.3239.00). In one of our cubes (called "Cube 1") we have defined an MDX calculation script. This script does a number of things, among these it creates an hidden set based on our shared [Country] dimension:

  CREATE HIDDEN SET [CountryComponents]

  AS

  GENERATE(

  [CalculatedCountries]

  , {

  [Country].[Country].CurrentMember

  , StrToSet([Country].[Country].CurrentMember.PROPERTIES("Component Countries MDX"))

  , [Country].[Country].CurrentMember

  }

  ,ALL

  );

  A simple inspection of the [CalculatedCountries] set through the Management Studio gives me these members (i'm not listing all members, just the first ones to give an idea):

  Belgium-Luxembourg

  Belgium

  Luxembourg

  Belgium-Luxembourg

  BRICs

  Brazil

  Russia

  India

  China

  BRICs

  EU Developed countries

  United Kingdom

  Belgium

  France

  Germany

  Italy

  Netherlands

  Sweden

  Austria

  Luxembourg

  Finland

  Greece

  Ireland

  Portugal

  Spain

  Denmark

  Slovenia

  EU Developed countries

  EU Developing countries

  Bulgaria

  Cyprus

  Czech Republic

  Estonia

  Hungary

  Latvia

  Lithuania

  Malta

  Poland

  Romania

  Slovakia

  EU Developing countries

  ...

  So far so good, no unexpected results.

  The cube script then contains this statement:

  CREATE MEMBER CURRENTCUBE.[Measures].[CountryFirstOccurance]

  AS

  RANK([Country].[Country].CurrentMember, [CountryComponents])

  , VISIBLE = 0;

  I view the [Measures].[CountryFirstOccurance] calculated member through Management Studio with this simple MDX query:

  select

  [CalculatedCountries] on rows

  , [Measures].[CountryFirstOccurance] on columns

  from [Cube 1];

  to see the value of [Measures].[CountryFirstOccurance] for each member in the [CalculatedCountries] set. Below are the top results from this query:

  Belgium-Luxembourg -> 1

  BRICs -> 5

  EU Developed countries -> 28

  EU Developing countries -> 29

  ...

  Here is where i'm having some strange results: for member "EU Developed countries" the value of [Measures].[CountryFirstOccurance] is 28, when in fact it should be 11, right ? The value of [Measures].[CountryFirstOccurance] is correct for all other members in the [CalculatedCountries] set.

  For "EU Developed countries" the rank function should return 11 because that is the 1st occurrance of member "EU Developed countries" in the [CalculatedCountries] set. Instead it returns 28, which is in fact the 2nd occurrance of "EU Developed countries" in the set. For all other members in the set the [Measures].[CountryFirstOccurance] member rightly shows the first occurrance of each country in the set.

  I don't understand this behaviour, if anyone could provide any hint it would be much appreciated.

  Thanks in advance 🙂

  Antonio