Querying OLAP server

Question

I use the following code to execute a query in C#:

 AdomdConnection con = new AdomdConnection("Datasource=local;...");

            con.Open();
            AdomdCommand command = con.CreateCommand();
            command.CommandText = input;

            AdomdDataReader reader = command.ExecuteReader();
  while (reader.Read())
            {
for(i =0; i<reader.fieldCount; i++){
      a[i]=reader.GetString(i);
}
return a;

Howeever, this code returns the full path in the hierarchy for each cell. I.e., each row of data is like [AllGeography, Canada, Vancouver, Allproduct, bikes, accessories, 297483]. I want to retrieve only the leaves and the measure value that is :[vancouver, accessories, 297483]. What should I do? How I can specify the leaves?

Answer 1

Because the result of MDX query is actually multidimentional, i feel myself more comfortable with ExecuteCellSet. You can get the whole CellSet, then you get Measures via coordinates.

For example (if you have one measure in query):

AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT 
            [Geography].[Geography].[Country].&[Canada].Children ON 0, 
            [Product].[Id] ON 1
            FROM [Cube]
            WHERE [Measures].[Your Measure]";

CellSet cs = cmd.ExecuteCellSet();

TupleCollection CanadaChildren = cs.Axes[0].Set.Tuples;
TupleCollection ProductIds = cs.Axes[1].Set.Tuples;

for (int row = 0; row < CanadaChildren.Count; row++)
{
    for (int col = 0; col < ProductIds.Count; col++)
    {
        a[i++] = cs.Cells[col, row].Value;
    }
}
conn.Close();

If you have several measures, than it will be a third dimention in query and a third cooridinate in a cellset.