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!!
If anyone stumble with this question, then, you're likely making the same mistake as I am:
After quite some hours comparing other folks' Mondrian schemas with mine, and having a look at the book "Mondrian in action", I realized I was making a total misuse of the parentColumn, which is (I think) aimed to point to the same hierarchy level, such as in employees with supervisors that are also employees.
The solution is to completely remove parentColumn property of the xml.