Moving from SQL to MDX

Question

Moving from SQL to MDX

Answer 1

With the query covering 16 measures detailed by 25 hierarchies from 5 dimensions, there's not too much potential of speeding things up, and those StrToSet calls in subselect clauses do not help either. SSAS MD cubes have never been designed for the task of returning a huge spreadsheet of data, and the performance observed with this query is but natural. Whatever your original problem was, you should try looking for solutions other than torturing a cube with such queries. E.g., your parameters in subselects hint at SSRS, so you could look for SSRS-based solutions.
Returning to MDX, you could try replacing individual attribute hierarchies in the crossjoin with member properties for another attribute you query from the same dimension, and you could create an aggregation covering this subset of the cube's space.

Answer 2

Hi,

Apart from the question, whether the MDX is correct or not.
I think we are facing a query issue, that the MDX query included to many crossjoin of the dimensions. This would greatly impact the performance of the query with its large amount of calculation.
I think you could try to arrange the query with other structure and avoid crossjoin.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November