can data in olap defined by develper for levels?

[chenq]

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

[swood]

I don't follow your description. Can you show us some example data?

 

 

Sherman

JasperSoft

[chenq]

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,sumpagevisit

create 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

[swood]

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.

 

 

Sherman

JasperSoft

[chenq]

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 null

com.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

[chenq]

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?

[swood]

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.

 

 

Sherman

JasperSoft

[chenq]

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.