Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer

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]
)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.