MDX very slow in SSRS

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?

thank you
LanticrogersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
The report runs the whole report before it displays. Which could take up a lot of time. If you are using a SP I would add afield that already does the totaling for you, then display it in your report with the other data.
0
LanticrogersAuthor Commented:
I don't use a SP, i'm querying the cube using MDX in my Report (build with SSRS) and the problematic measure is the calculated one.  Even it if run the whole report I don't understand why it takes so much time while I can get the same information (same columns) in the cube browser within few seconds. They are both querying the same cube and I also try my report without putting that calculated measure in a column, so it is not showing on the report. So it clearly takes time doing the query, not showing the report since I'm not even showing that measure in my report. It is frustrating since using cubes suppose to accelerates things up.
0
grzegorzsCommented:
The main difference between browser in BIDS (where you browse cube I think...) and SSRS is the way of generating MDX query.
To proper comparing queries you should copy the MDX query from the query designer in SSRS, paste it into and execute in the management studio (SSMS) or MDX Studio (the best MDX tool I know). Duration should be the same.
Then you can start with optimization.
Can you paste the whole MDX query from SSRS here?

best regards
Grzegorz
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
planoczCommented:
Lanticrogers  are you still having problems with this report?
0
LanticrogersAuthor Commented:
It helped me to fix the problem.

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.