Random Notes on MDX Filtering

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

Consider this:

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

In this case, the MarketValue in italics 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,

SELECT NON EMPTY [MarketValue]

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.

SELECT
[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 the situation even worse.

If you rewrite the query above like this...

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

...you might get much better performance.

CodeProject