Hi Guys,

I'm working on an SSAS 2012 OLAP cube with around 100 dimensions and a single measures group. When I try to run a simple DRILLTHROUGH query such as:

DRILLTHROUGH MAXROWS 1000 SELECT FROM [Cube] WHERE ([Measures].[Fact_Count])

I get an error as below.

The specified query is too complex to be evaluated as a single statement.

Does anyone have any experience of this error and how to work around it? If I remove the majority of the dimensions from the cube I can run this query successfully. Looking at the limits set for analysis services I should be well within these.

Thanks in advance 

Sean

Hi Scaneyw,

Based on my research, this is a SQL Server Analysis Services (SSAS) design limitation. In SSAS, Drillthrough generates internal CrossJoin query that contains all attribute hierarchies in measure group. So the error appear when MDX parser receives statement with> 1000 nodes.

Here are some tips for you to avoid this error.

• Reduce number of dimension attribute hierarchies in measure group below 1000.
• Delete unneeded attributes and disable Hierarchy for attributes that are only used as properties.
• Consider creating smaller Measures Groups with less dimensions + attribute hierarchies.

If you have any concerns about this feature, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope the complete drillthrough feature is will be improved in future SQL Server Versions.

Regards,
Charlie Liao

If you have any feedback on our support, please click here.

Thanks for this Charlie I had similar findings. I also have a limit of around 5000 attributes that stops browsing of the cube in Excel! This is well below the 2,147,483,647 attribute limits in the SSAS documentation.

I'll have to look at restructuring the cube, thanks for your help.

Sean

Hi Charlie,

It's unusual this is the only information I can find in relation to this error.

I was hoping you could explain a little more about your statement...."In SSAS, Drillthrough generates internal CrossJoin query that contains all attribute hierarchies in measure group. So the error appears when MDX parser receives statement with> 1000 nodes"

How do I know how many attributes and/or Attribute Hierarchies do I need to remove and/or disable. Exactly what do I need to re-structure to get my drill through to work.

My scenario by the way while prototyping my cube was weighed up between creating an excessive amount of dimensions versus using my fact tables as dimensions. I realise that using fact tables as dimensions is not desirable however it does simplify my cube significantly and I get to the granular data.

 I connected via the "dimension usage" one extra dimension into a measure group and now the drillthrough is too complex for that measure group.

Look forward in hearing some advice.

Kind Regards,

Jemahl