search

mysticfall / pivot4j

Pivot4J provides a common API for OLAP servers which can be used to build an analytical service frontend with pivot style GUI.
Other
128 stars 101 forks source link

Drill through opens empty popup window #182

Closed surendiranganesan closed 8 years ago

surendiranganesan commented 9 years ago

Hi,

While connecting derby database drill through option is working fine.

I am connecting Pivot4j with mysql .

If i open any drill through it opens new empty window.

but if i click on drill through of a empty cell it opens a window with table inside and no records on it.

Kindly help me on this.

Regards,

mysticfall commented 9 years ago

Could you check the log file first to see if it contains any error message, please?

ghost commented 9 years ago

Hi there,

it turns out, it happens the same to me. Im using the foodmart database.

Log spits out this:

ERROR [Pivot4JExceptionHandler] Se ha producido una excepción no controlada
javax.faces.FacesException: java.sql.SQLException: Column 'the_year' not found.
    at org.pivot4j.analytics.ui.DrillThroughDataModel.load(DrillThroughDataModel.java:227)
    at org.primefaces.component.datatable.DataTable.loadLazyData(DataTable.java:818)
    at org.primefaces.component.datatable.DataTableRenderer.preRender(DataTableRenderer.java:94)
    at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:82)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:674)
    at javax.faces.component.UIData.encodeEnd(UIData.java:1721)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:85)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:68)
    at org.primefaces.component.layout.LayoutUnitRenderer.encodeEnd(LayoutUnitRenderer.java

etc.

Thanks in advance

mysticfall commented 8 years ago

Sorry, I wasn't able to reproduce the problem still. Maybe it's something only happens in MySQL, or with a specific MDX?

I'd appreciate If you could instruct me how to reproduce it with the FoodMart database (with what MDX, what cell to click, etc).

P.S.: @rasalbatros Can you paste the whole stacktrace instead? There should be more lines, starting with 'Caused by:' around the quoted content.

ghost commented 8 years ago

It is MySQL, Foodmart database, mondrian 3 schema (with mondrian 4 happens as well).

When you open it, select Unit Sales on the Columns, select products to the rows. Click on the loupe to Drill Trough the table, the window pops up...but no results are shown.

MDX: SELECT {[Measures].[Unit Sales]} ON COLUMNS, {[Product].[Products].[All Products]} ON ROWS FROM [Sales]

Log spits out the results I told you:

ERROR [Pivot4JExceptionHandler] Se ha producido una excepción no controlada javax.faces.FacesException: java.sql.SQLException: Column 'the_year' not found. at org.pivot4j.analytics.ui.DrillThroughDataModel.load(DrillThroughDataModel.java:227) at org.primefaces.component.datatable.DataTable.loadLazyData(DataTable.java:818) at org.primefaces.component.datatable.DataTableRenderer.preRender(DataTableRenderer.java:94) at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:82) at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:674) at javax.faces.component.UIData.encodeEnd(UIData.java:1721) at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:85) at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:68) at org.primefaces.component.layout.LayoutUnitRenderer.encodeEnd(LayoutUnitRenderer.java:49) at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:674) at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:85) at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:68) at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:83) at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:68) at org.primefaces.component.dialog.DialogRenderer.encodeEnd(DialogRenderer.java:44) at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:674) at javax.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:554) at org.apache.myfaces.context.servlet.PartialViewContextImpl$PhaseAwareVisitCallback.processRenderComponent(PartialViewContextImpl.java:823) at org.apache.myfaces.context.servlet.PartialViewContextImpl$PhaseAwareVisitCallback.visit(PartialViewContextImpl.java:784) at org.apache.myfaces.component.visit.PartialVisitContext.invokeVisitCallback(PartialVisitContext.java:213) at javax.faces.component.UIComponent.visitTree(UIComponent.java:1016) at javax.faces.component.UIComponentBase.visitTree(UIComponentBase.java:1191) at javax.faces.component.UIForm.visitTree(UIForm.java:345) at javax.faces.component.UIComponent.visitTree(UIComponent.java:1044) at javax.faces.component.UIComponentBase.visitTree(UIComponentBase.java:1191) at javax.faces.component.UIComponent.visitTree(UIComponent.java:1044) at javax.faces.component.UIComponentBase.visitTree(UIComponentBase.java:1191) at org.apache.myfaces.context.servlet.PartialViewContextImpl.processPartialRendering(PartialViewContextImpl.java:536) at org.apache.myfaces.context.servlet.PartialViewContextImpl.processPartial(PartialViewContextImpl.java:415) at org.primefaces.context.PrimePartialViewContext.processPartial(PrimePartialViewContext.java:60) at javax.faces.component.UIViewRoot.encodeChildren(UIViewRoot.java:516) at javax.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:541) at org.apache.myfaces.view.facelets.FaceletViewDeclarationLanguage.renderView(FaceletViewDeclarationLanguage.java:1891) at org.apache.myfaces.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:313) at org.apache.myfaces.lifecycle.RenderResponseExecutor.execute(RenderResponseExecutor.java:116) at org.apache.myfaces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:267) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:200) at org.pivot4j.pentaho.servlet.FacesDispatcherServlet.service(FacesDispatcherServlet.java:113) at org.pentaho.platform.web.servlet.PluginDispatchServlet.service(PluginDispatchServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.pentaho.platform.web.http.filters.PentahoWebContextFilter.doFilter(PentahoWebContextFilter.java:185) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.pentaho.platform.web.http.filters.PentahoRequestContextFilter.doFilter(PentahoRequestContextFilter.java:87) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378) at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109) at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101) at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.springframework.security.providers.anonymous.AnonymousProcessingFilter.doFilterHttp(AnonymousProcessingFilter.java:105) at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.pentaho.platform.web.http.security.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:191) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.springframework.security.ui.basicauth.BasicProcessingFilter.doFilterHttp(BasicProcessingFilter.java:174) at org.pentaho.platform.web.http.security.PentahoBasicProcessingFilter.doFilterHttp(PentahoBasicProcessingFilter.java:115) at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235) at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.pentaho.platform.web.http.filters.HttpSessionPentahoSessionIntegrationFilter.doFilter(HttpSessionPentahoSessionIntegrationFilter.java:263) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.springframework.security.wrapper.SecurityContextHolderAwareRequestFilter.doFilterHttp(SecurityContextHolderAwareRequestFilter.java:91) at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390) at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:175) at org.springframework.security.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:99) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.pentaho.platform.web.http.filters.SystemStatusFilter.doFilter(SystemStatusFilter.java:55) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.pentaho.platform.web.http.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:114) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.pentaho.platform.web.http.filters.WebappRootForwardingFilter.doFilter(WebappRootForwardingFilter.java:70) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.pentaho.platform.web.http.filters.PentahoPathDecodingFilter.doFilter(PentahoPathDecodingFilter.java:34) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:620) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Column 'the_year' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144) at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:5073) at org.apache.commons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328) at org.apache.commons.dbcp.DelegatingResultSet.getObject(DelegatingResultSet.java:328) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at mondrian.util.DelegatingInvocationHandler.invoke(DelegatingInvocationHandler.java:76) at com.sun.proxy.$Proxy164.getObject(Unknown Source) at org.pivot4j.analytics.ui.DrillThroughDataModel.load(DrillThroughDataModel.java:217) ... 97 more

ghost commented 8 years ago

If you need anything else to try to reproduce it please let me know, thanks.

mysticfall commented 8 years ago

@rasalbatros Shouldn't [Product].[Products].[All Products] be just [Product].[All Products] instead? If I copy & paste the MDX you provided as it is, it fails to run on my end (without opening the drillthrough window), because the FoodMart schema which is shipped with Pivot4J does not define the Products hierarchy.

On the other hand, if I change it to [Product].[All Products], I can open drillthrough dialog without any problem. Maybe you were using a modified version of the FoodMart schema?

I'm pretty sure I haven't changed much the version I ship with the project, since I don't know much about Mondrian anyway :)

I suppose it might be helpful if you can compare your schema with the one shipped with the project, and see if there's any incompatible changes.

cgoudey commented 8 years ago

I have a problem that looks similar. Drill pop-up is empty.

When trying to "Export" from the drill window I get a pop-up with error "java.sql.SQLException: Column 'nb_ilp' not found."

From MySQL log I see the query is : select cube_memo_date_creation.annee as Annee, cube_memo_faits.nb_ilp as Moy ILP from cube_memo_date_creation as cube_memo_date_creation, cube_memo_faits as cube_memo_faits, cube_memo_consultation as cube_memo_consultation, cube_memo_canal_envoi as cube_memo_canal_envoi where cube_memo_faits.id_date_creation = cube_memo_date_creation.id and cube_memo_date_creation.annee = 2015 and cube_memo_date_creation.mois = 1 and cube_memo_date_creation.jour = 2 and cube_memo_faits.id_consultation = cube_memo_consultation.id and cube_memo_faits.id_canal_envoi = cube_memo_canal_envoi.id order by cube_memo_date_creation.annee ASC

It looks like pivot4j is looking for nb_ilp in the result instead of looking for the alias "Moy ILP"

mysticfall commented 8 years ago

@cgoudey Is it legal to use space in SQL alias? If not, it seems there might be an issue in the SQL generation code.

However, I'm not sure if it's the same problem with the original post. And the part which generates the SQL lies in Mondrian, so I doubt there's much I can do for now.

If it can be reproduced with the FoodMart database, then I might be able to dig into the Mondrian code and see if anything can be done from the Pivot4J's side.

I'm sorry that I couldn't be much help now. Please let me know if you can reproduce the problem with the FoodMart database.

cgoudey commented 8 years ago

It seems that it is legal if you escape the alias string (this is the case in the query I pasted even if it doesn't show in my comment) This alias comes from the name of a measure in the mondrian xml : <Measure name="Moy ILP" column="nb_ilp" formatString="0.00" aggregator="avg"></Measure> I don't expect this to be a problem (the FoodMart database also comes with measure names containing a space), and everything works fine except drilling. I can't test with the FoodMart database for now but I'll try later.

cgoudey commented 8 years ago

I just realized one thing that was maybe not clear : the query is just fine and runs without error in the database. So the problem does not really lie in the query generation but when picking in the result set. I suppose some code is probably looking for nb_ilp in the result set instead of looking for the alias "Moy ILP" See http://stackoverflow.com/questions/19062453/java-sq-sqlexception-column-not-found for example.

I can't say if it's Mondrian or jpivot4j related though.

mysticfall commented 8 years ago

I think you are right about the immediate cause about the SQLException. Initially, I thought it weird to see an alias with a whitespace, but probably it would show a different message if it was the real problem.

Anyway, I still suspect it to be a problem in Mondrian itself, as there is no SQL related code in Pivot4J at all. What it does in regards to drillthrough operation is either call cell.drillThrough() which is Olap4J's API, or compose a MDX statement and pass it to Mondrian via JDBC interface.

GregBee2 commented 8 years ago

I had the same problem and after some searching I found this lied within the JDBC-driver used: I used version 5.0.3 and could solve the problem using the attribute useOldAliasMetadataBehavior=true in my connection string (ref [http://thinkinginsoftware.blogspot.be/2011/05/javasqlsqlexception-column-name-not.html])

mysticfall commented 8 years ago

@GregBee2 Ah... the mystery was solved, finally! Thanks much for pointing this out.