MDX query slower with Calculated Measures on a OLAP Universe

[Former Member]

All,

We have BOXI 3.1 ( base version) and we build universe on top of MSAS 2005 cubes and create webi reports against it.

This is what we have noticed.

1. Assume you have a measure like Sales in cube. A report with that measure comes back in decent time.

2. Now the cube has some calculated measures like say Sales MTD or Sales YTD. If i drag one or two of these, the query performance sees a very drastic impact. Same query that used to return in 2 minutes for regular measure now times-out after 30 mins or higher.

Does any one know why calculated measures have this kind of impact? Is there any tuning i can do at the cube level for these calculated measures to improve performance.

Regards

[Former Member]

Thank you folks for your response. These hints have been very useful

[Former Member]

Hey

We make all our calculations in the transformation when we load data. Because we had the same experience.

Regards,

Rikke

[Former Member]

Hi

In SSAS, calcuated members are done on the fly where as with normal measures you can pre aggregate them. You can push the calculation to the back end, i.e. to SSAS DSV or the source database itself.

Another workaround is to partition the cube so that when the calculation is done it covers a smaller data set.

http://msdn.microsoft.com/en-us/library/aa224803(SQL.80).aspx

Thanks,

Daniel