MDX very slow in SSRS
Posted on 2010-04-07
I have a SSAS Cube which has few measures like "Actual Amount", "Budget Amount" and "Actual Amount YTD".
When I browse the cube in Designer, the data is shown very fast. But when I use that cube in a SSRS report, it takes forever. If I only use Actual Amount and Budget Amount in the SSRS, it is very fast but once I add the Actual Amount YTD it takes forever.
The Actual Amount YTD formula is:
AGGREGATE(PeriodsToDate([Posting Date].[Fiscal Year - Month].[Fiscal Year], [Posting Date].[Fiscal Year - Month].CurrentMember),[Measures].[Actual Amount])
I wonder why it is so fast when browsing cube but so slow when using the same columns in the SSRS report. Is there a way to optimize that?