MDX, CellSet contains meta data?

Question

So I have the following MDX query which runs against the AdventureWorks example cube.

SELECT NON EMPTY ([Date].[Fiscal Semester of Year].[Fiscal Semester of Year],{[Measures].[Internet Order Count], [Measures].[Average Unit Price]}) ON 0,
NON EMPTY([Product].[Status].[Status] ,[Customer].[Country].[Country]) ON 1
FROM [Adventure Works]

This gives a result which looks a bit like:

      H1     H2    H1    H2
     X1 X2  X1 X2 X1 X2 X1 X2
A A1
A A2
A A3
B A1       numbers
B A2
B A3

The CellSet has various bits of metadata, but none of it describes that structure, as far as I can tell. It doesn't seem to include useful things like Measure names or Dimension names, etc.

Any clues? If I execute the above query in Management Studio it formats it nicely for me. Is that because it is being clever with its parsing of the MDX, or because it is accessing some metadata I haven't found yet?

As an aside, I can query the cube via the connection and get lots of cube related info that way, but I need a way to relate that back to my query result.

The chances are I will have a CellSet, but not the source MDX that generated it.

Thanks

Might have solved my own problem: in CellSet.Axis.Set.Hierarchy.* is a whole load of information which seems to be relevant. — Ian

RickNZ RickNZ · Accepted Answer · 2011-12-28T03:26:48

Try something like this:

CellSet cs = cmd.ExecuteCellSet();
Axis columns = cs.Axes[0];
TupleCollection columnTuples = columns.Set.Tuples;
for (int i = 0; i < columnTuples.Count; i++)
{
    string caption = columnTuples[i].Members[0].Caption;
}
Axis rows = cs.Axes[1];
TupleCollection rowTuples = rows.Set.Tuples;
foreach (Position rowPos in rows.Positions)
{
    string caption = rowTuples[rowNum].Members[0].Caption;
}

MDX, CellSet contains meta data?

1 Answers