Thursday, October 30, 2008

Viewing the MDX cellset with WPF

When executing an MDX query there's various bits of useful metadata that can be returned in the cellset over and above the members and dimensions you've explicitly specified in your query. This can include things like the formatted value (as specified by the cube definition) as well as other attribute values for the dimension member you're explicitly querying against.

This kind of stuff can be invaluable if you're writing your own front-end app to access OLAP data, not least because it saves a whole heap of faffing about with WITH clauses (query scoped calculated measures). Something like:

with Member (
) as TheYear,
select {
[measures].TheYear, [measures].[measure]
} on Columns,{
} on Rows from Cube
...can just become:

select {
} on Columns,{
} dimension properties member_name, member_value,
[date].[date].[date].Year on Rows
on Rows from Cube
Trouble is you'd really struggle to work this out since all that metadata is helpfully hidden from view when you execute MDX in BIDS (and MDX studio), which makes it all a bit hit-and-miss. So I wrote a little WPF app just to visualise the actual cellset returned. Pretty basic stuff - load the results into a dataset and bind it to a grid. I could fiddle with my DIMENSION PROPERTIES clause with immediate gratification.

But it took me hours to get the binding working. One of the problems is that the MDX columns have names like '[Measures].[MyThing]' and you can't just set that as the property name of your binding and expect the binding infrastructure to cope:

{Binding Path=[Measures].[SomeMeasure]}
The binding infrastructure sees the dot and tries to walk the path, with predictable results:

System.ArgumentException: Measures is neither a DataColumn nor a DataRelation for table Table

[NB: If you had a column simply named SomeMeasure this would work, due to the magic of ICustomTypeDescriptor, but that's another story]

So instead you have to use the indexer syntax on the DataRowView:

{Binding Path=['[Measures].[SomeMeasure]']}
Or (if that made you wince)

{Binding Path=Row['[Measures].[SomeMeasure]']}
But those don't work either:

System.ArgumentException: Column '"[Measures].[SomeMeasure]"' does not belong to table Table

Even whilst the same binding path works 'just fine thanks' in the debugger. It took me a long, long time to realise there's an extra set of quotes in that error message. The WPF binding syntax doesn't require quotes for string indexers:

{Binding Path=Row[[Measures].[SomeMeasure]]}
It looks so wrong but it works.


longitude said...


i am also working on vieweing mdx cellset with wpf. is there any way i can see the source code ?? It seems that this is a very old post, has there been any updates.

piers7 said...

I will see if I can dig out the code, but it has been a while.

In the meantime I discovered that if you hover over the row selector in the results pane in Management Studio, you see the same metadata. Or is it double click. I forget.

Popular Posts