Excel Multiselect Issues with SSAS Calculated Member

Ok, it’s an old one, but I struggled to find the right answer, so I thought it was worth a post. I knew it was an issue, and I thought I knew the answer. I was wrong.

Say you create a calculated member to return some kind of item count against a dimension, a bit like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
count( descendants(
  [Date].[Year-Month-Date].CurrentMember,
  [Date].[Year-Month-Date].[Date]
))

and it works just fine when you test it

SELECT {
    [Measures].[Days Count]
    ,[Measures].[Minutes Of Day Count]
} ON COLUMNS
FROM [My Cube]
WHERE {
    [Date].[Year-Month-Date].[Year].[2011]
}

…and then you find it falls apart when someone queries the cube in Excel and uses multi-select in the pivot table filter. So you follow Mosha’s advice to fix it (using Existing):

CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as
count( existing [Date].[Year-Month-Date].[Date] )

…but then someone points out that that doesn’t work either. More recent versions of Excel (2007, 2010) implement multi-select via sub-queries; with just a few days selected it generates a query that (simplified) looks a bit like this:

SELECT {
    [Measures].[Days Count]
    ,[Measures].[Minutes Of Day Count]
} ON COLUMNS
FROM (
    SELECT (
        {[Date].[Year-Month-Date].[Date].&[20110101]
        ,[Date].[Year-Month-Date].[Date].&[20110102]
        })
    ON COLUMNS  FROM [My Cube]
)


and executing that doesn’t give you the right answer (it just counts the number of members in the dimension):

So the old advice, still commonly quoted, is actually wrong. The solution (as of SSAS 2008 and onwards) is to use a dynamic named set:

CREATE DYNAMIC SET [Selected Days] as (
    [Date].[Year-Month-Date].[Date]
)
CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
    count([Selected Days])
)

I found this a bit bizarre at first, but some of the posts I read were themselves a bit confused and left in the ‘existing’. This is not required, and distracts from what’s really going on here: this works by design as this is one of the problems that dynamic sets are intended to fix. A (more recent) Mosha post spells it out:

“Dynamic sets are not calculated once. They are calculated before each query, and, very important, in the context of that's query WHERE clause and subselects”

Ah. The answer, and the explanation. Finally.

Viewing MDX Data with WPF (redux)

Spend most of the day today grappling with binding a WPF datagrid to a DataSet loaded from a parameterized MDX query.

The first gotcha was that SSAS expects its parameterized queries to be passed using the ICommandWithParameters interface, however the OleDb provider for .Net doesn’t support named parameters (except for sprocs). This is a ‘fixed’ Connect issue – fixed as in ‘still broken in .Net 4 but marked as fixed because we can’t be bothered’.

Ahem.

So rather than use ado.net parameters, I’m now using string replacement on my source query text. Just great:

    // So have to do manual parameterization :-(

    query = query

        .Replace("@date", dateKey)

        .Replace("@time", timeKey)

        ;

Then of course the WPF data grid wouldn’t show the data (despite the DataSet visualizer working just fine). It bound and showed columns just fine using AutoGenerateColumns:

    dataGrid1.ItemsSource = dataSet.Tables[0].DefaultView;

 

…but all the rows showed blank!

Eventually I noticed a spew of debug output, listing the binding failures:

System.Windows.Data Error: 17 : Cannot get 'Item[]' value (type 'Object') from '' (type 'DataRowView'). BindingExpression:Path=[Blah1].[Blah2].[Blah3].[MEMBER_CAPTION]; DataItem='DataRowView' (HashCode=66744534); target element is 'TextBlock' (Name=''); target property is 'Text' (type 'String') TargetInvocationException:'System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Blah1 is neither a DataColumn nor a DataRelation for table TheTableName.

at System.Data.DataRowView.get_Item(String property)

--- End of inner exception stack trace ---

This all seemed awfully familiar, and fortunately I happened across a helpful blog article (which I wrote!) explaining the problem. This time it is AutoGenerateColumns that’s generated the wrong binding path, causing WPF to try and find ‘deep’ members (attempting to walk multiple indexers) rather than just bind to a column with that name.

The fix is something like this:

    // This works

    var table = dataSet.Tables[0];

    dataGrid1.Columns.Clear();

    dataGrid1.AutoGenerateColumns = false;

    foreach (DataColumn dataColumn in dataSet.Tables[0].Columns)

    {

        dataGrid1.Columns.Add(new DataGridTextColumn

        {

              Header = dataColumn.ColumnName,

              Binding = new Binding("[" + dataColumn.ColumnName + "]")

        });

    }

    dataGrid1.ItemsSource = table.DefaultView;

Grr.

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 (
  [date].[date].[date].currentmember.properties("Year")
) as TheYear,
select {
  [measures].TheYear, [measures].[measure]
} on Columns,{
  [date].[date].[date]
} on Rows from Cube

...can just become:

select {
  [measures].[measure]
} on Columns,{
  [date].[date].[date]
} 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.