PDA

View Full Version : [Mondrian] Mondrian performance



Pranav.Matekar@zycus.com
03-03-2007, 04:18 AM
hi

i am new to this group
currently i am evaluating mondrian

i have done some testing on mondrian
with 6,000,000 records

and 60,000 elements in one dimension
and 6000 elements in others 10 dimensions

i have used top in top, ie. top 10 supplier of top 10 categories (60,000
suppliers ), via using generate and topcount
it's taking more than 10 min

and if i am applying filters on this query
result is not coming

i am currently testing via oracle
i have created indexing on each column of star schema
i have done analyze schema on oracle


i used following setting
mondrian.native.topcount.enable=true
mondrian.rolap.CachePool.costLimit=2147483647
mondrian.rolap.queryTimeout=600

i checked logging of mondrian
it's doing top in memory
for 60,000 elements, if mondrian is doing top in memory and i am using top
in top for this dimension with filters
getting result in 1 min is difficult.

is there any solution for this problem?

thank you.


Pranav
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Haridasan T
03-03-2007, 12:00 PM
Greetings,

I am also new to Mondrian. Appreciate if your could let me know how to
connect to Oracle. I copied the Oracle thin driver to apache/tomcat folder.
But unable to connect to Oracle database. I am only able to run with access
database.

Which all XML files need to be changed
(server.xml and datasources.xml)

Please do mail me the syntax also

Thanks

Hari Nair


On 3/3/07, Pranav.Matekar (AT) zycus (DOT) com <Pranav.Matekar (AT) zycus (DOT) com> wrote:
>
>
> hi
>
> i am new to this group
> currently i am evaluating mondrian
>
> i have done some testing on mondrian
> with 6,000,000 records
>
> and 60,000 elements in one dimension
> and 6000 elements in others 10 dimensions
>
> i have used top in top, ie. top 10 supplier of top 10 categories (60,000
> suppliers ), via using generate and topcount
> it's taking more than 10 min
>
> and if i am applying filters on this query
> result is not coming
>
> i am currently testing via oracle
> i have created indexing on each column of star schema
> i have done analyze schema on oracle
>
>
> i used following setting
> mondrian.native.topcount.enable=true
> mondrian.rolap.CachePool.costLimit=2147483647
> mondrian.rolap.queryTimeout=600
>
> i checked logging of mondrian
> it's doing top in memory
> for 60,000 elements, if mondrian is doing top in memory and i am using top
> in top for this dimension with filters
> getting result in 1 min is difficult.
>
> is there any solution for this problem?
>
> thank you.
>
>
> Pranav
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


--
http://cfo-portal.blogspot.com/

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Pranav.Matekar@zycus.com
03-03-2007, 12:24 PM
hi,

i never tried tomcat
i used direct jdbc connection

Connection conn = DriverManager.getConnection(
"Provider=mondrian;" +
"Jdbc=jdbc:oracle:thin:UserName/Password (AT) 192 (DOT) 168.1.1:1521:orcl1;" +
"JdbcDrivers=oracle.jdbc.driver.OracleDrive;"+
"Catalog=file:///C:/mondrian.xml;",
null,
false);

Query query = conn.parseQuery("select {[Store].members} on columns from
Sales ");
Result result = conn.execute(query);


if u want to connect via Datasource, try DataSource=dataSourceName; and
remove Jdbc=jdbc:oracle:thin:UserName/Password (AT) 192 (DOT) 168.1.1:1521:orcl1;

thank you.

Pranav
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Laurent Valdes
03-03-2007, 12:50 PM
Hi Pranav,

did you saw my last mail ?
Do you have some help for my problem ?

Thank you !

Laurent

2007/3/3, Pranav.Matekar (AT) zycus (DOT) com <Pranav.Matekar (AT) zycus (DOT) com>:
>
>
>
> hi,
>
> i never tried tomcat
> i used direct jdbc connection
>
> Connection conn = DriverManager.getConnection(
> "Provider=mondrian;" +
> "Jdbc=jdbc:oracle:thin:UserName/Password (AT) 192 (DOT) 168.1.1:1521:orcl1;"
> +
> "JdbcDrivers=oracle.jdbc.driver.OracleDrive;"+
> "Catalog=file:///C:/mondrian.xml;",
> null,
> false);
>
> Query query = conn.parseQuery("select {[Store].members} on columns from
> Sales ");
> Result result = conn.execute(query);
>
>
> if u want to connect via Datasource, try DataSource=dataSourceName; and
> remove Jdbc=jdbc:oracle:thin:UserName/Password (AT) 192 (DOT) 168.1.1:1521:orcl1;
>
> thank you.
>
> Pranav
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


--

Julian Hyde
03-03-2007, 04:51 PM
The query you are executing is a classic BAD query for mondrian. It
requires a lot of intermediate results -- the cartesian product of the
dimensions you are generating over -- in memory, so it will take a lot
of time and a lot of memory.

The iterator-based evaluation mechanism which Richard Emberson recently
introduced may help with some queries of this style, but that mechanism
is not fully mature yet.

Those kind of queries are well outside mondrian's sweet spot. I
recommend that you don't use mondrian for them.

Julian




_____

From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org]
On Behalf Of Pranav.Matekar (AT) zycus (DOT) com
Sent: Saturday, March 03, 2007 12:18 AM
To: mondrian (AT) pentaho (DOT) org
Subject: [Mondrian] Mondrian performance



hi

i am new to this group
currently i am evaluating mondrian

i have done some testing on mondrian
with 6,000,000 records

and 60,000 elements in one dimension
and 6000 elements in others 10 dimensions

i have used top in top, ie. top 10 supplier of top 10 categories (60,000
suppliers ), via using generate and topcount
it's taking more than 10 min

and if i am applying filters on this query
result is not coming

i am currently testing via oracle
i have created indexing on each column of star schema
i have done analyze schema on oracle


i used following setting
mondrian.native.topcount.enable=true
mondrian.rolap.CachePool.costLimit=2147483647
mondrian.rolap.queryTimeout=600

i checked logging of mondrian
it's doing top in memory
for 60,000 elements, if mondrian is doing top in memory and i am using
top in top for this dimension with filters
getting result in 1 min is difficult.

is there any solution for this problem?

thank you.


Pranav


_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Pranav.Matekar@zycus.com
03-04-2007, 07:31 AM
hi Julian,

thanks Julian.

it's true that such query is very bad.
i think userdefined function will help me,
so that i am trying some specific types of queries via using userdefined
functions

currently i am facing one small problem, in writing userdefined functions.
i am getting one object of

[Lmondrian.olap.Member;

this class

there is interface of type mondrian.olap.member

i tried to cast it with this interface.
but it's giving class cast exception.

can u give me some information about such types of object?

thank you.


Pranav
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Haridasan T
03-04-2007, 08:52 AM
test.jsp
I am connecting to an Oracle XE database
but getting error

Could u help to resolve?
JPivot had an error ...

javax.servlet.ServletException: javax.servlet.jsp.JspException:
javax.servlet.jsp.JspException: Mondrian Error:Internal error: Error while
creating SQL dialect

javax.servlet.ServletException: javax.servlet.jsp.JspException:
javax.servlet.jsp.JspException: Mondrian Error:Internal error: Error
while creating SQL dialect
at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:844)
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)

<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:oracle:thin:@localhost:1521:XE"
catalogUri="/WEB-INF/queries/SALES.xml">

select {[Measures].[COST],[Measures].[SALES]} on columns,

{([COMPANY].[All Company], [YEAR].[All Year], [LOCATION].[All Location],
[SP].[All SP])} ON rows

from [SALES]

</jp:mondrianQuery>


On 3/4/07, Pranav.Matekar (AT) zycus (DOT) com <Pranav.Matekar (AT) zycus (DOT) com> wrote:
>
>
> hi Julian,
>
> thanks Julian.
>
> it's true that such query is very bad.
> i think userdefined function will help me,
> so that i am trying some specific types of queries via using userdefined
> functions
>
> currently i am facing one small problem, in writing userdefined functions.
> i am getting one object of
>
> *[Lmondrian.olap.Member;*
>
> this class
>
> there is interface of type mondrian.olap.member
>
> i tried to cast it with this interface.
> but it's giving class cast exception.
>
> can u give me some information about such types of object?
>
> thank you.
>
>
> Pranav
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


--
http://cfo-portal.blogspot.com/

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Pranav.Matekar@zycus.com
03-06-2007, 12:31 PM
hi Julian

can u tell us,
when can we get new version of mondrian with iterator-based evaluation
mechanism?
i want to test this types of queries on that version

can u suggest,
which types of analytical server (MOLAP,ROLAP,HOLAP) or which currently
available analytical server can provide me faster result for such( top in
top ) type of queries ?

Pranav



"Julian Hyde" <julianhyde (AT) speakeasy (DOT) net>
Sent by: mondrian-bounces (AT) pentaho (DOT) org
03/04/2007 02:11 AM
Please respond to
Mondrian developer mailing list <mondrian (AT) pentaho (DOT) org>


To
"'Mondrian developer mailing list'" <mondrian (AT) pentaho (DOT) org>
cc

Subject
RE: [Mondrian] Mondrian performance






The query you are executing is a classic BAD query for mondrian. It
requires a lot of intermediate results -- the cartesian product of the
dimensions you are generating over -- in memory, so it will take a lot of
time and a lot of memory.

The iterator-based evaluation mechanism which Richard Emberson recently
introduced may help with some queries of this style, but that mechanism is
not fully mature yet.

Those kind of queries are well outside mondrian's sweet spot. I recommend
that you don't use mondrian for them.

Julian



From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org]
On Behalf Of Pranav.Matekar (AT) zycus (DOT) com
Sent: Saturday, March 03, 2007 12:18 AM
To: mondrian (AT) pentaho (DOT) org
Subject: [Mondrian] Mondrian performance


hi

i am new to this group
currently i am evaluating mondrian

i have done some testing on mondrian
with 6,000,000 records

and 60,000 elements in one dimension
and 6000 elements in others 10 dimensions

i have used top in top, ie. top 10 supplier of top 10 categories (60,000
suppliers ), via using generate and topcount
it's taking more than 10 min

and if i am applying filters on this query
result is not coming

i am currently testing via oracle
i have created indexing on each column of star schema
i have done analyze schema on oracle


i used following setting
mondrian.native.topcount.enable=true
mondrian.rolap.CachePool.costLimit=2147483647
mondrian.rolap.queryTimeout=600

i checked logging of mondrian
it's doing top in memory
for 60,000 elements, if mondrian is doing top in memory and i am using top
in top for this dimension with filters
getting result in 1 min is difficult.

is there any solution for this problem?

thank you.


Pranav_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian


_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Julian Hyde
03-06-2007, 01:00 PM
_____

From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org]
On Behalf Of Pranav.Matekar (AT) zycus (DOT) com
Sent: Tuesday, March 06, 2007 8:27 AM
To: Mondrian developer mailing list
Subject: RE: [Mondrian] Mondrian performance



hi Julian

can u tell us,
when can we get new version of mondrian with iterator-based evaluation
mechanism?
i want to test this types of queries on that version


I just released mondrian-2.3 RC1. You can download from SF.net.



can u suggest,
which types of analytical server (MOLAP,ROLAP,HOLAP) or which currently
available analytical server can provide me faster result for such( top
in top ) type of queries ?


Microstrategy is the leading ROLAP server; try that. Worth trying the
other leading commercial MOLAP/HOLAP servers too. I haven't run this
kind of query on any of them, so I have no idea how they will perform.

Julian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian