mondrian.olap.ResourceLimitExceededException: Mondrian Error:Number of members to be read exceeded limit (10,000)

Question

I have a problem similar to the one reported on the Pentaho blog in the post below:

https://forums.pentaho.com/threads/47819-Help-regd-member-restriction/?p=141499

And that query is very simple:

MDX: SELECT NON EMPTY {[Measures].[QTD_EMPRESAS]} on 0, NON EMPTY {[BAIRRO.BAIRRO_H].[TODOS_BAIRRO_H],[BAIRRO.BAIRRO_H].[06], [BAIRRO.BAIRRO_H].[061]} on 1 FROM [V_DM_EMPRESAS_LOCALIDADE] WHERE {[BAIRRO_F.BAIRRO_H].[06],[BAIRRO_F.BAIRRO_H].[061]}

If i use one filter it is return successfully, but with two filter, like the query above error occurs. Both filters together add up to 200 records, less than the limit reported in the error.

The dimension BAIRRO have more than 10000 records, but i filter only two BAIRRO.

Error occurred:

Caused by: mondrian.olap.ResourceLimitExceededException: Mondrian Error:Number of members to be read exceeded limit (10,000)
    at mondrian.resource.MondrianResource$_Def11.ex(MondrianResource.java:1180)
    at mondrian.rolap.SqlMemberSource.getMemberChildren2(SqlMemberSource.java:993)
    at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:891)
    at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:864)
    at mondrian.rolap.NoCacheMemberReader.getMemberChildren(NoCacheMemberReader.java:179)
    at mondrian.rolap.RolapCubeHierarchy$NoCacheRolapCubeHierarchyMemberReader.readMemberChildren(RolapCubeHierarchy.java:970)
    at mondrian.rolap.RolapCubeHierarchy$NoCacheRolapCubeHierarchyMemberReader.getMemberChildren(RolapCubeHierarchy.java:1027)
    at mondrian.rolap.NoCacheMemberReader.getMemberChildren(NoCacheMemberReader.java:159)
    at mondrian.rolap.RolapSchemaReader.internalGetMemberChildren(RolapSchemaReader.java:186)
    at mondrian.rolap.RolapSchemaReader.getMemberChildren(RolapSchemaReader.java:169)
    at mondrian.rolap.RolapSchemaReader.getMemberChildren(RolapSchemaReader.java:162)
    at mondrian.olap.DelegatingSchemaReader.getMemberChildren(DelegatingSchemaReader.java:78)
    at mondrian.olap.fun.AggregateFunDef$AggregateCalc.getChildCount(AggregateFunDef.java:571)
    at mondrian.olap.fun.AggregateFunDef$AggregateCalc.optimizeMemberSet(AggregateFunDef.java:490)
    at mondrian.olap.fun.AggregateFunDef$AggregateCalc.optimizeChildren(AggregateFunDef.java:398)
    at mondrian.olap.fun.AggregateFunDef$AggregateCalc.optimizeTupleList(AggregateFunDef.java:252)
    at mondrian.rolap.RolapResult.<init>(RolapResult.java:314)
    at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:662)
    at mondrian.rolap.RolapConnection.access$000(RolapConnection.java:52)
    at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:613)
    at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:611)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

To avoid this bug, in the Mondrian schema, I configured Role and in the HierarchyGrant configuration i am setting rollupPolicy to false.

Is this really a good strategy? Or is there a better one?

Answer 1

Role and Hierarchy grants are for security so I wouldn't set those unless you need them for data access control.

It looks like your mondrian.result.limit configuration setting is set too low at 10,000. For optimizing Mondrian performance, I would start with this mondrian.properties and tweak settings from there:

https://github.com/pentaho/pentaho-platform/blob/master/assemblies/pentaho-solutions/src/main/resources/pentaho-solutions/system/mondrian/mondrian.properties