MDX very slow in SSRS

Posted on 2010-04-07
Medium Priority
Last Modified: 2016-02-14
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
Question by:Lanticrogers
  • 2
  • 2
LVL 27

Expert Comment

ID: 30014676
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.

Author Comment

ID: 30020316
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.

Accepted Solution

grzegorzs earned 750 total points
ID: 30112961
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
LVL 27

Expert Comment

ID: 32999339
Lanticrogers  are you still having problems with this report?

Author Closing Comment

ID: 33002962
It helped me to fix the problem.


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question