MDX Query runs for 13 hours to return 79 rows

Question

MDX Query runs for 13 hours to return 79 rows

jonathan.stokes

SSC Veteran

Points: 244
More actions
January 7, 2010 at 3:17 am

#137188

For some reason, when I add a certain attribute from a dimension the query hangs and eventually returns in 13 hours. Has anyone got any ideas of how I can troubleshoot this problem. This dimension is based at the lowest level. IE the reference of a policy in an insurance cube.
I am using ssas 2005 on a server that I believe is patched to service pack 3.
I am running a native mdx query in management studio but the same thing happens when connecting to excel.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Is your cube partitioned? If so, approx how many rows per partition? Do you have aggregations on the partitions? Could you post a sample of the query (names changed to protect the innocent)? With any MDX, if you could also post the 'intent' this can help in making suggestions around re-formating/re-working the query to be more performant.

As far as what you can do to start diagnosing the query execution, you could set up a trace (very similar to how you would for SQL Server itself). This can tell you quite a few things, such as which partition/s it's hitting and also which aggregation/s (if any) it's using to generate the result set.

Steve.

jonathan.stokes SSC Veteran Points: 244 More actions · Answer 2

Thanks for the response.

I managed to trace the problem and found that there is a dimension called 'Aggregation' that runs MDX from itself and that is causing the problem. It basically decides the level of aggregation to be displayed. I.E cumulitive values or Amount to Date values.

I have gone back to the guys that built the cube and they are going to re-examine the query and look at the level of aggregatin being applied.I am also going to see how this dimension works in terms of determining the aggregatio level.

Funnily enough, it only goes wrong when you bring something into the where clause. Selecting everything returns 100,000 rows in under 10 seconds.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Not wanting to be disparaging but making a user use a dimension to select the measure aggregation method rather than selecting a measure that physically aggegates differently, seems a little strange. The require effort on the user is the same. In some ways, the explicitly named measure with the different ahhrehation is actually better as the user can (usually) see the measure name to be informed of the data value nature versus if they simply filter the 'aggregation' dimension, and these filters aren't permanently presented, or not on the report when printed etc etc, then it's not explicit as to what the report contains.

The other thing that this could affect is the aggregation strategy within the cube. It could be that choosing the aggregation method on the fly doesn't allow SSAS to use prebuilt aggregations to create the resultset.

Steve.

jonathan.stokes SSC Veteran Points: 244 More actions · Answer 4

I actually thought the same thing but thought I was being a little pedantic. Good point about the user not knowing what is showing on printed matter.

I am going to be meeting with the guys who developed the cube and I will raise this with them. However, since the cube has gone live, it may be hard to get this changed now. Unfortunately, I was not part of the develoment team when this was deployed.