MDX Error: Query Optimizer generated too many subcubes in the query plan

Question

Hi,

I'm trying to ask to a cube on SQL Server 2008 with a huge mdx query.

This query contains more than 20 calculated members (defined on the query).

Each member contains a restriction for a dimension on the cube. Not always need all dimensions, but sometimes it is needed.

Well, it works fine until 16 members. With more than 16 members a message error appears: "Query Optimizer generated too many subcubes in the query plan".

Is there any way to solve this error?

Thanks!

Julieta

Answer 1

Your getting stuck in a performance bottleneck and I think your first step would be to determine whether the performance bottleneck is within the formula engine or the storage engine. To accomplish this, determine the amount of time required by the formula engine and the storage engine to execute the poorly performing MDX query when the cache is cold. The time spent by the storage engine can be determined by adding up the elapsed time for each Query Subcube event in a SQL Server Profiler trace. The time spent by the formula engine can be determined by subtracting the time spent by the storage engine from the total execution time for the Query End event.

You already have a start because you know that 16th calculated member causes the error to occur so you could work backwards from there to determine what is limiting the subcube events.

See this article for more detailed information about going about this: http://74.125.95.132/search?q=cache:XgP38c6S9-UJ:www.badlydressedboy.com/docs/IdentifyingAndResolvingMDXBottlenecksSSAS.doc+query+optimizer+generated+too+many+subcubes+in+the+query+plan&cd=14&hl=en&ct=clnk&gl=us