MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

Question

I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query:

SELECT
NON EMPTY
(
    [Measures].[Status]
)
ON COLUMNS,
NON EMPTY
(
    Filter
    ( 
        Crossjoin
        (
            {
                [Value1].[Value1].[Value1A],
                [Value1].[Value1].[Value1B]
            },
            [Value2].[Value2A].[Value2A],
            [Value3].[Value3].[Value3].ALLMEMBERS,
            [Value4].[Value4].[Value4].ALLMEMBERS,
            [Value5].[Value5].[Value5],
            [Value2].[Value2B].[Value2C],
            [Value6].[Value6].[Value6],
            [Value7].[Value7A].[Value7A]
        ),
        (
            [Measures].[Status]
        ) > 0
    )
)
ON ROWS
FROM [Cube]
WHERE
(
    [Value7].[Value7].[Value7A],
    [Value8].[Value8].[Value8A],
    [Value9].[Value9].[Value9A]
)
CELL PROPERTIES VALUE

I have very little knowledge of MDX, but through some trial-and-error, I have found that removing the two [Value1].[Value1] entries from the Crossjoin makes the query return quickly. Or, removing both the [Value2].[Value2A] and [Value5] entries also makes it return quickly. But, obviously, this is changing the query, so is not the solution, but perhaps it provides clues as to where I should look in terms of indexes or suchlike if there is anything like this in SQL Analysis Services (I'm more familiar with SQL Server databases)?

One thing I did try, is to put & before [Value1A] and [Value1B]. This caused the query to return very quickly with no results. Unfortunately, I don't know if this is correct because the query without this change takes too long to be able to see if there are any results. I don't know what difference & is supposed to make, but is this the obvious answer or does it change the query to be different? It is equally possible that the original writer of this query should have used & anyway, but never got to test the query with a real data set.

Any help would be greatly appreciated.

Answer 1

I found an answer here that has helped my situation and now the query returns very quickly. It appears the HAVING clause is a better solution than using the Filter function as long as the dataset is reasonably small. However, I noticed that removing NON EMPTY from the second axis (ON ROWS) causes it to still be slow even using the HAVING clause. I may still need to resolve that problem, too. But, at least the first problem is solved.

Answer 2

Try this:

SELECT
    [Measures].[Status]
ON COLUMNS,
NONEMPTY
({
                [RiskType].[RiskType].[MemberValue123],
                [RiskType].[RiskType].[MemberValue456]
            }*
            [Trade].[TradeType].[TradeType]*
            [Expiry].[Expiry].[Expiry].ALLMEMBERS*
            [Tenor].[Tenor].[Tenor].ALLMEMBERS*
            [YieldCurveCurrency].[YieldCurveCurrency].[YieldCurveCurrency]*
            [Trade].[TradeBook].[XYZ1]*
            [Index].[Index].[Index]*
            [EffectiveStrike].[Effective Strike Name].[Effective Strike Name]
,
            [Measures].[Status]
)
ON ROWS
FROM [RePro]
WHERE
(
    [RiskSet].[RiskSet].[ABC],
    [Portfolio].[Portfolio].[XYZ],
    [RunDate].[RunDate].[17 Sep 2013]
)