Word of notice: I'm a total novice regarding Mondrian, and I'm struggling so hard to make a cube with a multi-level hierarchy.
These are my tables (Postgresql, but should work on any other DBMS)
CREATE TABLE TMP_OLAP_DIMENSION
(
level_0_id INT NOT NULL
,level_1_id INT DEFAULT NULL
,description VARCHAR(512)
);
INSERT INTO TMP_OLAP_DIMENSION VALUES(1,NULL,'1. Acquisition costs');
INSERT INTO TMP_OLAP_DIMENSION VALUES(1,2 ,'1.02 Administrative, finance, legal and marketing expenses');
INSERT INTO TMP_OLAP_DIMENSION VALUES(2,NULL,'2. Construction costs');
INSERT INTO TMP_OLAP_DIMENSION VALUES(2,1 ,'2.01 Demolition, site preparation and formation');
CREATE TABLE TMP_OLAP_FACTS
(
level_0_id INT NOT NULL
,level_1_id INT DEFAULT NULL
,measure FLOAT NOT NULL
--FOREIGN KEY....
);
INSERT INTO TMP_OLAP_FACTS VALUES(1,NULL,10);
INSERT INTO TMP_OLAP_FACTS VALUES(1,2 ,20);
INSERT INTO TMP_OLAP_FACTS VALUES(2,NULL,30);
INSERT INTO TMP_OLAP_FACTS VALUES(2,1 ,40);
commit
And, after developing the schema with the Mondrian Schema Workbench, the resulting XML is this:
<Schema name="My schema">
<Dimension type="StandardDimension" visible="true" name="My dimension">
<Hierarchy name="My dimension hierarchy" visible="true" hasAll="true">
<Table name="tmp_olap_dimension" schema="public" alias="">
</Table>
<Level name="Level 0" visible="true" column="level_0_id" nameColumn="description" uniqueMembers="true">
</Level>
<Level name="Level 1" visible="true" column="level_1_id" nameColumn="description" parentColumn="level_0_id" uniqueMembers="true">
</Level>
</Hierarchy>
</Dimension>
<Cube name="My cube" visible="true" cache="true" enabled="true">
<Table name="tmp_olap_facts" schema="public" alias="">
</Table>
<DimensionUsage source="My dimension" name="My dimension" visible="true" foreignKey="level_1_id">
</DimensionUsage>
<Measure name="My measure" column="measure" aggregator="sum" visible="true">
</Measure>
</Cube>
</Schema>
(I'll omit the screenshots of Schema Workbench for clarity, but I'll post them if needed).
When attacking the cube through Pivot4j (both pentaho plugin and standalone war), I set measure as a column, and My dimension hierarchy as rows.
It works, but when I try to drill-down the rows to retrieve the measure at a level_1 level, I get the following exception:
15:58:32,348 ERROR [Pivot4JExceptionHandler] Se ha producido una excepción no controlada
javax.el.ELException: mondrian.olap.MondrianException: Mondrian Error:Internal error: assert failed: In the current implementation, parent/child hierarchies must have only one level (plus the 'All' level).
at org.apache.el.parser.AstValue.invoke(AstValue.java:260)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:267)
at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:74)
at javax.faces.component.UICommand.broadcast(UICommand.java:120)
at javax.faces.component.UIViewRoot._broadcastAll(UIViewRoot.java:1172)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:365)
at javax.faces.component.UIViewRoot._process(UIViewRoot.java:1656)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:862)
at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:42)
at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:196)
at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:143)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:198)
at org.pivot4j.pentaho.servlet.FacesDispatcherServlet.service(FacesDispatcherServlet.java:113)
at org.pentaho.platform.web.servlet.PluginDispatchServlet.service(PluginDispatchServlet.java:102)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.pentaho.platform.web.http.filters.PentahoWebContextFilter.doFilter(PentahoWebContextFilter.java:236)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.pentaho.platform.web.http.filters.PentahoRequestContextFilter.doFilter(PentahoRequestContextFilter.java:90)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.pentaho.platform.web.http.security.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:194)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
at org.pentaho.platform.web.http.security.PentahoBasicProcessingFilter.doFilterInternal(PentahoBasicProcessingFilter.java:128)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.pentaho.platform.web.http.filters.HttpSessionPentahoSessionIntegrationFilter.doFilter(HttpSessionPentahoSessionIntegrationFilter.java:276)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.pentaho.platform.web.http.security.CsrfGateFilter.doFilter(CsrfGateFilter.java:136)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.pentaho.platform.web.http.filters.SystemStatusFilter.doFilter(SystemStatusFilter.java:58)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.pentaho.platform.web.http.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.pentaho.platform.web.http.filters.WebappRootForwardingFilter.doFilter(WebappRootForwardingFilter.java:73)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.pentaho.platform.web.http.filters.PentahoPathDecodingFilter.doFilter(PentahoPathDecodingFilter.java:54)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:688)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:609)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:818)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1623)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: assert failed: In the current implementation, parent/child hierarchies must have only one level (plus the 'All' level).
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:999)
at mondrian.olap.Util.newInternal(Util.java:2467)
at mondrian.olap.Util.assertTrue(Util.java:2459)
at mondrian.rolap.SqlMemberSource.makeChildMemberSql_PCRoot(SqlMemberSource.java:1169)
at mondrian.rolap.SqlMemberSource.getMemberChildren2(SqlMemberSource.java:956)
at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:892)
at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:865)
at mondrian.rolap.SmartMemberReader.readMemberChildren(SmartMemberReader.java:249)
at mondrian.rolap.SmartMemberReader.getMemberChildren(SmartMemberReader.java:211)
at mondrian.rolap.RolapCubeHierarchy$CacheRolapCubeHierarchyMemberReader.readMemberChildren(RolapCubeHierarchy.java:631)
at mondrian.rolap.RolapCubeHierarchy$CacheRolapCubeHierarchyMemberReader.getMemberChildren(RolapCubeHierarchy.java:727)
at mondrian.rolap.SmartMemberReader.getMemberChildren(SmartMemberReader.java:177)
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.olap4j.MondrianOlap4jMember$3.execute(MondrianOlap4jMember.java:113)
at mondrian.olap4j.MondrianOlap4jMember$3.execute(MondrianOlap4jMember.java:109)
at mondrian.server.Locus.execute(Locus.java:90)
at mondrian.server.Locus.execute(Locus.java:75)
at mondrian.olap4j.MondrianOlap4jMember.getChildMemberCount(MondrianOlap4jMember.java:106)
at org.pivot4j.impl.QueryAdapter.canExpand(QueryAdapter.java:838)
at org.pivot4j.transform.impl.DrillExpandPositionImpl.canExpand(DrillExpandPositionImpl.java:44)
at org.pivot4j.ui.command.DrillExpandPositionCommand.canExecute(DrillExpandPositionCommand.java:69)
at org.pivot4j.ui.AbstractPivotRenderer.getCommands(AbstractPivotRenderer.java:146)
at org.pivot4j.ui.table.TableRenderer.access$100(TableRenderer.java:60)
at org.pivot4j.ui.table.TableRenderer$3.handleTreeNode(TableRenderer.java:640)
at org.pivot4j.ui.table.TableHeaderNode.walkChildrenAtColIndex(TableHeaderNode.java:891)
at org.pivot4j.ui.table.TableHeaderNode.walkChildrenAtColIndex(TableHeaderNode.java:907)
at org.pivot4j.ui.table.TableRenderer.renderBody(TableRenderer.java:604)
at org.pivot4j.ui.table.TableRenderer.render(TableRenderer.java:483)
at org.pivot4j.analytics.ui.ViewHandler.render(ViewHandler.java:591)
at org.pivot4j.analytics.ui.ViewHandler.structureChanged(ViewHandler.java:953)
at org.pivot4j.impl.PivotModelImpl.fireStructureChanged(PivotModelImpl.java:833)
at org.pivot4j.impl.PivotModelImpl$1.queryChanged(PivotModelImpl.java:111)
at org.pivot4j.impl.QueryAdapter.fireQueryChanged(QueryAdapter.java:197)
at org.pivot4j.impl.QueryAdapter.fireQueryChanged(QueryAdapter.java:182)
at org.pivot4j.impl.QueryAdapter.onQuaxChanged(QueryAdapter.java:1109)
at org.pivot4j.impl.QueryAdapter$1.quaxChanged(QueryAdapter.java:79)
at org.pivot4j.impl.Quax.fireQuaxChanged(Quax.java:163)
at org.pivot4j.impl.Quax.expand(Quax.java:838)
at org.pivot4j.impl.QueryAdapter.expand(QueryAdapter.java:934)
at org.pivot4j.transform.impl.DrillExpandPositionImpl.expand(DrillExpandPositionImpl.java:69)
at org.pivot4j.ui.command.DrillExpandPositionCommand.execute(DrillExpandPositionCommand.java:103)
at org.pivot4j.ui.command.DrillExpandPositionCommand.execute(DrillExpandPositionCommand.java:20)
at org.pivot4j.analytics.ui.ViewHandler.executeCommand(ViewHandler.java:641)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.el.parser.AstValue.invoke(AstValue.java:247)
... 83 more
What am I doing wrong?
Thank you very, very much!!
