chenq Posted June 26, 2007 Share Posted June 26, 2007 Our web site is to develop a new web data analysisis system for usage within company. I think olap in JI is a convenient tool. But while all the data in olap is calculated automatically, for some data, such as UserCount, shouldn't be sumed simply. We have generated different data rows for different dimensions in database. How can I deal with this problem in olap? Can I make the olap get user count from different rows according to the different dimesion level ?Post edited by: chenq, at: 2007/06/26 07:42 Link to comment Share on other sites More sharing options...
swood Posted June 26, 2007 Share Posted June 26, 2007 I don't follow your description. Can you show us some example data? ShermanJasperSoft Link to comment Share on other sites More sharing options...
chenq Posted June 27, 2007 Author Share Posted June 27, 2007 hi swood:here is my example: our pagevisit data is stored in a fact table querypagevisitdetail,for the sake of performance, we have some aggregate tables, for example the table sum_site.create table SUM_SITE( ID INTEGER not null, PLATFORMID INTEGER not null, PV INTEGER, IPCOUNT INTEGER, VIEWERS INTEGER, SECLEN INTEGER, ACTIVITYTIME DATE default sysdate, ADMINID INTEGER, DATETYPE INTEGER, NEWGUESTS INTEGER, TYPE INTEGER, TYPEINF VARCHAR2(50), TOTALGUESTS NUMBER) table SUM_SITE contains two dimension: platform and time. because of the column 'ipcount' is calculated by 'distinct count'. For time dimension,we summarizes different rows for each time level, distinguished by the column 'datetype'. For platform dimension, data is summarized into another table,sumpagevisitcreate table SUMPAGEVISIT( ID VARCHAR2(32) not null, DATETYPE INTEGER, ACTIVITYTIME DATE, PV INTEGER, IPCOUNT INTEGER, VIEWERS INTEGER, SECLEN INTEGER, NEWGUESTS INTEGER, TOTALGUESTS INTEGER, SYSCREATE DATE)Table SUMPAGEVISIT eliminates the dimesion platform, stands for the pagevisit data across all the platforms.Now you can see, to get correct measure value of column 'ipcount',we have to visit precomputed values from different tuples or different tables, but not from the rollup value. I'm reading the mondrian documentation for the aggregate table, for it may be a way to this situation. I don't know whether it can deal with our requirements, and until now I'm struggling throug the schema file for aggregate table. by the way, the forum's session timeout seems to be too short, my session timed out soon before i can post this message,(I finish it within half an hour), and I have to rewrite it again. would you like to set a longer timeout?Post edited by: chenq, at: 2007/06/27 05:45 Link to comment Share on other sites More sharing options...
swood Posted June 27, 2007 Share Posted June 27, 2007 It looks like SUM_SITE is the table with the most fine grained data you want to analyze. That is the main fact table. Tables like SUMPAGEVISIT are aggregate tables. Are the ACTIVITYDATE and PLATFORM hierarchical? ACTIVITYDATE is likely to have Year/Month/Day. It is best to create a separate table that just contains the unique ACTIVITYDATEs and the level columns which are foreign keys to the fact and aggregate tables, as in the Time dimension in the FoodMart example. ShermanJasperSoft Link to comment Share on other sites More sharing options...
chenq Posted June 29, 2007 Author Share Posted June 29, 2007 I've been through the cube with different aggregate tables to deal with the data for distinct count. thanks.But I now fall into a strange problem: when I try to define new dimension in my schema file, the mdx return exception in jasperserver.log:14:30:04,180 ERROR JSErrorPage_jsp,http-8090-Processor22:146 - org.eigenbase.xom.XOMException: Document parse failed: [Fatal Error] :1:1: Content is not allowed in prolog. at org.eigenbase.xom.wrappers.GenericDOMParser.handleErrors(Unknown Source) at org.eigenbase.xom.wrappers.JaxpDOMParser.parseInputSource(Unknown Source) at org.eigenbase.xom.wrappers.GenericDOMParser.parse(Unknown Source) at org.eigenbase.xom.wrappers.GenericDOMParser.parse(Unknown Source) at mondrian.rolap.RolapSchema.load(RolapSchema.java:258) at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:202) at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:89) and output on the jsp is:Error Message: com.jaspersoft.jasperserver.api.JSException: mondrian.olap.MondrianException: Mondrian Error:Internal error: while parsing catalog null Error Trace: com.jaspersoft.jasperserver.api.JSException: mondrian.olap.MondrianException: Mondrian Error:Internal error: while parsing catalog nullcom.jaspersoft.jasperserver.api.JSException: mondrian.olap.MondrianException: Mondrian Error:Internal error: while parsing catalog null at com.jaspersoft.jasperserver.war.control.OlapModelController.getOlapSession(OlapModelController.java:248) at com.jaspersoft.jasperserver.war.control.OlapModelController.viewOlap(OlapModelController.java:142) at sun.reflect.GeneratedMethodAccessor350.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:434) at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:372) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:350) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) but the new Dimension definition is just copied and modified from another Dimension, it seems that nothing special is used. And even I get rid of the new Dimension from my schema, the original tested mdx query return such exception. After I reuse the backup schema file, everything goes right again. I'm using JasperServer1.2.1 Link to comment Share on other sites More sharing options...
chenq Posted June 29, 2007 Author Share Posted June 29, 2007 I've just find the problem: my file editor seems to have done something with the schema file, result in the file being wrong. Now I reopen the file, it goes right.Is there any rules when user expanding the Dimensions? When I expand the Dimensions in some order, there will be an exception:Exception Class: class javax.servlet.jsp.el.ELException Message: An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy and nothing more can be found in log. But when I do it in some other order, it's all right. I've found an article in this forum said that this exception appear in open source version, and in pro version it's ok. What's the problem? Is it a problem of Mondrian or JPivot? Link to comment Share on other sites More sharing options...
swood Posted July 2, 2007 Share Posted July 2, 2007 Try developing the schema in the workbench: http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&id=25164&catid=10. This will allow you to create and validate the schema, and test MDX. ShermanJasperSoft Link to comment Share on other sites More sharing options...
chenq Posted July 3, 2007 Author Share Posted July 3, 2007 I've upgrade my jasperserver to 2.0, and I can read the error message from the console now. I've just resolve all the problems and the odd errors disappears now. I'll try the url that you've just told me, thanks. Now I find that ,if I click urls(such as the expand and collapse icon) before the response returns, it will fall into "Jasper Server is busy" exception. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now