Random Notes on MDX Filtering

1. Filter() and IIF work funny in the columns hierarchy.
Consider this:

FILTER([MarketValue], [MarketValue] >1000) ON COLUMNS,
[Region].[Region].[Region] ON ROWS
FROM [MyCube]

In this case the MarketValue in red actually means the global number for MarketValue, not the per region one. I.e., if the global MarketValue is 1024, then all regions will be returned. If global market value is 999, none of the regions will be returned. IIF works in a similar way, and so does IsEmpty. Thus,


will return regions where market value is not empty, whereas

SELECT FILTER([MarketValue], NOT IsEmpty([MarketValue]))

will return market value for all regions, empty or not, provided that the global market value is not empty.

To avoid this shenanigans one should filter on ROWS. E.g.

[MarketValue] ON COLUMNS,
FILTER( [Region].[Region].[Region], [MarketValue] > 1000) ON ROWS
FROM [MyCube]

2. Performance of FILTER on even medium sized sets is not so good.
Even when the filter is trivial, something like 1=1, the query still takes several times longer than without the filter. If you filter on multiple fields, it makes situation even worse.

If you rewrite the query above like this

WTIH MEMBER [BigMarketValue] AS IIF([MarketValue]>1000, [MarketValue], NULL)
[Region].[Region].[Region] ON ROWS
FROM [MyCube]

you might get much better performance.

Leave a Reply

Your email address will not be published. Required fields are marked *