29 April, 2008

Mondrian + MySQL Setup

Introduction

Mondrian is a very popular open source OLAP Server and is also one of the products that are very oftenly asked in several discussion forums. One of those including installation and initial configuration issues.

In this article I will try to guide step by step on how to get Mondrian installed and configured using MySQL community database server and Windows XP as its operating system platform.

Prerequisites

These are the applications that are needed before you can using Mondrian, follow on the link for more information on how to setup each application :



MySQL Foodmart Database Sample


Mondrian provided Foodmart database sample in MS Access format. But I have provided a full generated MySQL script populate Foodmart so you use it in MySQL Community database server. You can download the tar.gz compressed file from my wiki site (pictured).



Extract the script file using your favorite compression utility - 7zip is the one I would highly recommend.

Create a database and execute the script. Here I show you a sample commands to create a Foodmart database - assuming that your mysql is running and your mysql.exe client is located in C:\mysql\bin with your previously extracted script file in C:\foodmart_mysql.sql.

Open command line prompt (Start | Run | type cmd | click OK)

cd C:\mysql\bin
mysql.exe -uroot -e "create database Foodmart"
mysql.exe -uroot Foodmart < C:\foodmart_mysql.sql
mysql.exe -uroot Foodmart -e "show tables"



You should see result like below if all running well...

+-------------------------------+
| Tables_in_foodmart            |
+-------------------------------+
| account                       |
| agg_c_10_sales_fact_1997      |
| agg_c_14_sales_fact_1997      |
| agg_c_special_sales_fact_1997 |
| agg_g_ms_pcat_sales_fact_1997 |
| agg_l_03_sales_fact_1997      |
| agg_l_04_sales_fact_1997      |
| agg_l_05_sales_fact_1997      |
| agg_lc_06_sales_fact_1997     |
| agg_lc_100_sales_fact_1997    |
| agg_ll_01_sales_fact_1997     |
| agg_pl_01_sales_fact_1997     |
| category                      |
| currency                      |
| customer                      |
| days                          |
| department                    |
| employee                      |
| employee_closure              |
| expense_fact                  |
| inventory_fact_1997           |
| inventory_fact_1998           |
| position                      |
| product                       |
| product_class                 |
| promotion                     |
| region                        |
| reserve_employee              |
| salary                        |
| sales_fact_1997               |
| sales_fact_1998               |
| sales_fact_dec_1998           |
| store                         |
| store_ragged                  |
| time_by_day                   |
| warehouse                     |
| warehouse_class               |
+-------------------------------+


Configuring and Running Tomcat

You will need to copy mysql driver, i.e. mysql-connector-java-5.1.6-bin.jar file to your Tomcat's library folder.
  • Extract your MySQL zipped driver file you have downloaded before to get the jar file
  • Copy the jar driver to C:\apache-tomcat-5.5.17\common\lib folder - which is the location for any jar drivers used in our web application. For version 6 of tomcat you just put it in APACHE_TOMCAT/lib folder

Run your server by typing "catalina run" in Tomcat's bin folder.
cd c:\apache-tomcat-5.5.17\bin
catalina run


Check your running server by browsing into http://localhost:8080. If everything works fine, our next step is to deploy Mondrian web application.

Deploying Mondrian

Extract mondrian distribution package and goto lib directory. You will find mondrian.war file there.
Copy the war file into your Tomcat's webapps directory while Tomcat is still running. The war file will be extracted and deployed into Tomcat automatically.






Browse into your http://localhost:8080/mondrian and you shall get a Mondrian welcome page like below.




Configure JSP File


Browse into Mondrian's application queries folder where you can find several sample jsp files. Here I open my C:\apache-tomcat-5.5.17\webapps\mondrian\WEB-INF\queries folder and find the following jsp files :
  • fourheir.jsp
  • mondrian.jsp
  • colors.jsp
  • arrows.jsp

Edit those files using your favorite text editor, and change following line :


<jp:mondrianQuery id="query01"
jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUrl="jdbc:odbc:MondrianFoodMart"
catalogUri="/WEB-INF/queries/FoodMart.xml">

..... this is your existing mdx query ....

</jp:mondrianquery>


into your MySQL database configuration. For example ...

<jp:mondrianQuery id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/Foodmart?user=root&password="
catalogUri="/WEB-INF/queries/FoodMart.xml">
 
..... this is your existing mdx query ....
 
</jp:mondrianQuery>




Browse Your Multi Dimensional Data

Now you are ready to to play with JPivot. Browse into your local mondrian host address : http://localhost:8080/mondrian, choose the first mondrian examples "JPivot pivot table".

It will take some time as Mondrian will need to populate its initial cache before the view shows up.

You will then see a table which you can drill through in a hierarchical way and a heading toolbar which comprises of configuration icons.


Congratulations, your Mondrian with MySQL database has been successfully setup !

Updated : More on Mondrian Sample's Configuration

To find more about configuring each sample to work well with above installation please refer to our wiki page at http://pentaho-en.phi-integration.com/mondrian/configuring-mondrian-sample.


47 comments:

Antoine said...

Thanks for the blog. I am struggling identifying:
mysql-connector-java-6.0.16-bin.jar

Where can I find the file?
Can I user mysql-connector-java-5.1.3-rc-bin.jar
and rename it to mysql-connector-java-6.0.16-bin.jar instead?

How can I know it is correctly set up?

Feris Thia said...

Hi Antoine,

Thanks for the commment.

You can put your jar file in server wide library folder or in your web app library folder.

For example in server wide you can put it in :

[TOMCAT SETUP]\lib

Replace [TOMCAT SETUP] with your own Apache Tomcat's directory.

And to test your setup, I have write a wiki article with a sample jsp file. Visit the article here (http://sites.google.com/a/dlpage.phi-integration.com/pentaho/mondrian/check-mysql-setup).


Feris Thia

Pious said...

Hi, after doing all that, i'm getting this:

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException: Mondrian Error:Syntax error at line 2, column 2, token 'EOF'

Any ideas?

Feris Thia said...

Hi Pablo,

Yes, it seems that you have remove your mdx query in jp:mondrianQuery tag. Well, it is a mistake in my instructions.. I have updated this. Very sorry for that.

Add mdx query in these following jsp files - inside of jp:mondrianQuery tag :
----------------------------------
- mondrian.jsp
select
{[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
----------------------------------
- fourhier.jsp

select {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Gender].[All Gender], [Marital Status].[All Marital Status],
[Customers].[All Customers],
[Product].[All Products] ) } on rows
from Sales where ([Time].[1997])
----------------------------------
- colors.jsp

with member [Measures].[ROI] as '(([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost])', format_string = IIf((((([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]) * 100.0) > 150.0), "|#.00%|style='green'", IIf((((([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]) * 100.0) < 150.0), "|#.00%|style='red'", "#.00%"))
select {[Measures].[ROI], [Measures].[Store Cost], [Measures].[Store Sales]} ON columns,
{[Product].[All Products]} ON rows
from [Sales]
where [Time].[1997]
----------------------------------
- arrows.jsp

with member [Measures].[ROI] as '(([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost])', format_string = IIf((((([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]) * 100.0) > 150.0), "|#.00%|arrow='up'", IIf((((([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]) * 100.0) < 150.0), "|#.00%|arrow='down'", "|#.00%|arrow='none'"))
select {[Measures].[ROI], [Measures].[Store Cost], [Measures].[Store Sales]} ON columns,
{[Product].[All Products]} ON rows
from [Sales]
where [Time].[1997]

----------------------------------

You can visit this page for more description on this tag.

Feris Thia

Giovanni said...

Hi, thanks for the blog.
But, after doing all that, i'm getting this:

JPivot had an error ...

org.apache.jasper.JasperException: Exception in JSP: /testpage.jsp:44

at org.apache.jasper.servlet.JspServletWrapper.
handleJspException(JspServletWrapper.java:451) ...
at java.lang.Thread.run(Unknown Source)

Any ideas?

Feris Thia said...

Hi Orso,

Can you be more detail ?

Have you setup and running your MySQL correctly ? Have you load the sample data ? Have you copied the jdbc jar to Tomcat's library folder ? Have you configured the jsp ?

Thanks,

Feris

Anonymous said...

This is a very good tutorial! Thank you very much for allowing me to set up a working Mondrian instance in 15 minutes!

Fyi: I encountered some problems when editing .jsp files.

I had to replace tag names (mondrianquery with mondrianQuery, jdbcurl with jdbcUrl, .. etc, ...) and all worked fine.

Thank you again.

Anonymous said...

hi feris, i have error like this :

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: Mondrian Error:Syntax error at line 6, column 2, token '<'

anu ideas ?

thank's

Anonymous said...

Hi feris
thanks for the blog, it's a very good tutorial

all my system work fine (MySQL, Tomcat, mondrian foodmart ex) all tests run but only the second test "Jpivot jpivot table by XMLA " responds

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to create envelope from given source:

I work with
Tomcat 6.0.18
java 1.5.0_15
mysql 5.0.51a
mysql-connector 5.1.6
mondrian 3.0.4

Anonymous said...

About the topic 'JPivot had an error ...' I had the same problem I have solved deleting the file xalan.jar from the lib directory

Quadri said...

Hi,please I need your assistance.I need to install mondrain inorder to connect my application called OpenI to mysql but am having a challenge on how to go about it.
I have gone through the install documentation,but i don't understand it.I get stuck at the point of loading test data with the command am asked to run.
I am unable to run the command,so I decided to copy it and past it on my command line(terminal) but all I can see after pressing enter is this error:
----------------------
Unrecognized option: -tables
Could not create the Java virtual machine.
--------------------------
and I have posted to their mailing list but no reply.
Please I will be very gratefull if someone can help me on this.

My OS is Linux(Fedora 7).

Thanks very much.

Feris Thia said...

Hi Quadri,

I have preloaded foodmart sample data from Mondrian into a downloadable script. You can find it at http://pentaho-en.phi-integration.com/mondrian/mysql-foodmart-database.


Regards,

Feris

Quadri said...

Hi Feris Thia,

Thanks very much for your support and the usefull reply of yours,I am very gratefull.

Actually, I have gotten a response from the mailing list on how to go about the command and am able to load the test data with the command now.

Although,I still downloaded the mysql-foodmart-database but I did not load it since the command has worked and thanks for this.

But,am facing another challenge now which I will be very gratefull if you can also helped me with.

I continue with the installation guide after resolving the loading of test data and I got stuck at the stage of configuring Mondrian as an XML/A provider.

Am still unable to connect to mysql database due to inaccurate datasources.xml which I described, I guess.

When I start my mondrain and try to view "http://192.168.19.89:8080/mondrian/testpage.jsp?query=xmla" and I got this on my browser page:

---------------------------------
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Unable to internalize message
----------------------------------

I also tried to view this "http://192.168.19.89:8080/mondrian/testpage.jsp?query=arrows" on my browser but all what I can see is this error:
----------------------------
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:mysql://192.168.19.89/foodmart?user=foodmart&password=foodmart

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:mysql://192.168.19.89/foodmart?user=foodmart&password=foodmart
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)... ... ...
----------------------------

I have posted this also to mondrain mailing list including my files such as the datasources.xml and mondrain.properties,may be you can see my mistakes in the files.
This is the url: "http://forums.pentaho.org/showthread.php?p=204303#post204303"

Thanks very much for your support and I will be expecting your reply soon.

Cheers bye.

Feris Thia said...

Hi Quadri,

Following your post in the forum it is apparent to me that it's your MySQL connection on host 192.168.19.89 isn't allowed.

Try execute these 2 commands on your MySQL server.

CREATE USER 'foodmart'@'%' IDENTIFIED BY 'foodmart';

GRANT ALL ON *.* TO 'foodmart'@'%';

Note:
'foodmart'@'%' means user 'foodmart' from 'any' host.

Hope this helps...

Feris

Quadri said...

Hi Feris,

Thanks very much for your usefull message,I did exactly as you said and the only thing I can notice is that this URL {http://192.168.19.89:8080/mondrian/testpage.jsp?query=arrows} which was displaying some errors b4 is now working fine due to the two commands you gave.

But it's only four out of the Eight links that are working fine for now,have been trying to resolve this but I couldn't for now and that is why I have not reply since.

Please, help me look into this also.I am very gratefull for your support,because am progressing on this little by little.

These are the things I did and the results:

1. When I click on this link {JPivot pivot table}, I got this:
--------------------------------
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:mysql://192.168.19.89/foodmart

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:mysql://192.168.19.89/foodmart
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
--------------------------------

2. When I click on this link {JPivot pivot table by XMLA}, I got this:
------------------------------------
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Unable to internalize message

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Unable to internalize message
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
------------------------------------

3. When I click on this link {JPivot with 4 hierarchies}, I got this:

-----------------------------
Mondrain/JPivot Test Page. This was successfull,no error here.
-----------------------------

4. When I click on this link {JPivot with role 'California Manager' set}, I got this:

--------------------------------
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: java.lang.NullPointerException

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: java.lang.NullPointerException
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
--------------------------------

5. When I click on this link {JPivot with arrows}, I got this:

-------------------------------
Mondrain/JPivot Test Page. This was successfull,no error here.
-------------------------------

6. When I click on this link {Various queries formatted using the Mondrian tag-library}, I got this:
----------------------------
HTTP Status 500 -
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: java.lang.NullPointerException org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
----------------------------

7. When I click on this link {Basic interface for ad hoc queries}, I got this:
--------------------------
JSP Page. This was successfull,no error here.
--------------------------

8. When I click on this link {XML for Analysis tester}, I got this:
--------------------------
Mondrian XML for Analysis Tester Page . This was successfull,no error here.
--------------------------

Thanks very much for your support,I will be expecting your reply soon.
I will also like to use this opportunity to wish you a happy and prosperous new year in advance.

Thanks,
Quadri.

Feris Thia said...

Hi Quadri,

I will try to answer you in order :
1. Edit WEB-INF/queries/mondrian.jsp to use the same <jp:mondrianQuery> tag and attribute as of your arrows.jsp file. I think your tag will look somehow like this :
<jp:mondrianQuery
id="query01"
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://192.168.19.89/foodmart?user=foodmart&password=foodmart"
catalogUri="/WEB-INF/queries/FoodMart.xml">

2. Edit WEB-INF/queries/xmla.jsp to use the same <jp:xmlaQuery> tag and attributes as mondrianXMLA.jsp

3. OK
4. Use instruction no.1 but this time edit WEB-INF/queries/testrole.jsp
5. OK
6. Check your WEB-INF/mondrian.properties file for mondrian.webapp.connectString entry. Fix the connection string to your foodmart db (mysql).
7. OK
8. OK

Hope this helps..

And Quadri... I also wishing you a Happy and Prosperous New Year 2009.

Keep in touch..

Regards,

Feris

Quadri said...

Hi Feris,

That's grate, everything is now working fine besides error no.2 which is this link:{JPivot pivot table by XMLA} and 6 {Various queries formatted using the Mondrian tag-library}.

I followed the steps you gave and the rest are working fine now except error 2 and 6 which I posted earlier.

These are the contents in my files:

This is my xmla.jsp:
-----------------------------
... ... ...Please I intentionally remove the heading tag in other to post this syntax.
... ... ...

......
uri="http://192.168.19.89:8080/mondrian/xmla"
dataSource="Provider=Mondrian"
catalog="MondrianFoodMart">
select
{[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
-----------------------------

and this is my mondrianXMLA.jsp
-----------------------------------
... ... ... Please I intentionally remove the heading tag in other to post this syntax.

..... ... ....
uri="http://192.168.19.89:8080/jpivot/xmla"
dataSource="Provider=Mondrian"
catalog="MondrianFoodMart">
select
{[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
-----------------------------------

and for my error no.6, where you asked me to check for WEB-INF/mondrian.properties file for mondrian.webapp.connectString entry and Fix the connection string to my foodmart db (mysql).

I could not found this: mondrian.webapp.connectString, what I saw there is this: mondrian.test.connectString .I tried to changed the test to webapp but still the same error, and I don't really now what to do on this.


This is my mondrian.properties

-----------------------------------
# Allow the use of aggregates
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true
mondrian.native.topcount.enable=true
mondrian.native.filter.enable=true

# mondrian.properties
mondrian.result.limit=50000

# For XML/A JSPs
mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:mysql://192.168.19.89/foodmart?user=foodmart&password=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/FoodMart.xml;
----------------------------------------------

Please am very sorry for taking much of your time on this,thanks very much for your support.

Hope to hear from you soon.

Please can I know why TAG is not allowed to be posted here.

Cheers bye,

Quadri.

Feris Thia said...

Hi Quadri,

Sorry for late reply...

It seems that you haven't configured Mondrian as XMLA (XML for Analysis) provider and web context connection string.

I have written a section of wiki articles detailing configurations for each sample to work.

You can find these articles at http://pentaho-en.phi-integration.com/mondrian/configuring-mondrian-sample.

The article come with brief description and some screenshots to get better understandings.

Regards,

Feris

Anonymous said...

Greetings,


Special Thanks to Feris for guiding us through the world of Mondrian.


For others, if your "JPivot with arrows" is working but
"JPivot pivot table" is not working,
I suggest you properly replace the old driver strings with:
...
jdbcDriver="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://localhost/FoodMart?user=root&password=yourPassWord"
...
in all the "mondrian\WEB-INF\queries\*.jsp" files in all places.


While arrows.jsp has only one place to replace,
files like mondrian.jsp has three places to replace in the same way.


Also, modify "mondrian\WEB-INF\mondrian.properties" file so that it looks like:
...
Provider=mondrian;Jdbc=jdbc:mysql://localhost/FoodMart?user=root&password=yourPassWord;
JdbcDrivers=com.mysql.jdbc.Driver;
...


Above fixed many of my Mondrian problems, if not all.
Hope this helps :-)

shyam said...

Hai Guys,

I dont want to use jpivot as my ui i wanna use olap datagrid from flex as ui and mondrian any have idea on this please reply me.

Unknown said...

Hi all,

THANK YOU VERY MUCH!!
I was getting crazy with mondrian deployment..

Your post is the best I've found over the hundreds I've read..

It's very well explained, and a newbie like me can understand it fast.

Again, many thanks.

Ana

Gerardo L said...

Hi, I cannot load the tables correctly, the script crashes when trying to load the records for time_by_day table

Thanks

MoRLOK said...

Mondrian uses "ISO-8859-1" encoding for xmla responses. As such , you get such results even though there is utf-8 encoding in mysql.

HOW TO CHANGE RESPONCE TO UTF-8

Anonymous said...

PLEASE ANSWER...

Is it possible to put another database instead of FoodMart or is this just an example where you can try samo operations like drill,...?

Unknown said...

when i tried to extract the foodmart_mysql.tar.gz file, i am getting the file corrupted error.Please help me on this?

Unknown said...

hi all,
i am new to mondrian, while deploying the mondrian on tomcat, i am getting the following error.
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Can not find Default Member with name "[Time.Weekly].[All Time.Weeklys].[1997]" in Hierarchy "Time.Weekly"

Can you please help me on this

Anonymous said...

Hi,
I found your blog useful. I have managed to make connections to my own database, which is on MySQL. The problem i face now is related to "drill THROUGH".
I have not been able to find a solution. Each time I click on drillthrough i get the green arrow, but when i click on the green arrow, i get blank files. I am using an open source tool called OpenI. Do you have any idea what sort of settings or changes in query i need to make?

thanks
anonymos

Anonymous said...

Thanks for taking the time to document this process. You've saved me a lot of time and your instructions were dead on.

For folks having issues - I used the mysql query browser to build the Foodmart table. It may make things a bit easier.

山高人为峰(金峰) said...

Very useful, Thank you very much.

Feris Thia said...

Many thanks to all of you who find this article useful !

Regards,

Feris

Anonymous said...

how to conect Mondrian with Oracle

Anonymous said...

hi

i want to use my own database created in oracle.

plz let me know the details steps.

thanks
sumeet

Feris Thia said...

Hi Anonymous,

Currently I don't have any sample for Oracle.

Our domains are SQL Server and MySQL. Sorry for that, we don't have enough proficieny in Oracle.

Regards,

Feris

Domain Murah said...

thanks, this is my first project using Mondrian.

Anonymous said...

Hi,
am using
tomcat 6...
mondrian 3..
Oracle,

I would like to display the output of all mdx queries in the same file

When I click
on this link {Various queries formatted using the Mondrian tag-library}, I got the following error
the others links are working
except also for xml/a test
----------------------------
HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: java.lang.NullPointerException
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:491)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:401)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
root cause

javax.servlet.ServletException: javax.servlet.jsp.JspException: java.lang.NullPointerException
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:858)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
org.apache.jsp.taglib_jsp._jspService(taglib_jsp.java:121)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
root cause

java.lang.NullPointerException
mondrian.rolap.RolapConnection.execute(RolapConnection.java:642)
mondrian.web.taglib.ResultCache.getResult(ResultCache.java:79)
mondrian.web.taglib.ResultCache.getDOM(ResultCache.java:90)
mondrian.web.taglib.TransformTag.doEndTag(TransformTag.java:58)
org.apache.jsp.taglib_jsp._jspx_meth_mdx_005ftransform_005f0(taglib_jsp.java:225)
org.apache.jsp.taglib_jsp._jspService(taglib_jsp.java:91)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
note The full stack trace of the root cause is available in the Apache Tomcat/6.0.29 logs.
best
rim

Unknown said...

i followed every steps to install mondrian and i copied mysql jar file too. but when i am running , following error comes.. please five some idea..

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: Could not load Jdbc Driver com.mysql.jdbc.Driver
org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.jpivot.olap.model.OlapException: Could not load Jdbc Driver com.mysql.jdbc.Driver
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:491)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:401)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

Anonymous said...

can u help me to resolve my problem??

if i click link for jPivot, will showing
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.jsp.JspException: /WEB-INF/queries/mondrian.jsp(10,7) equal symbol expected

org.apache.jasper.JasperException: javax.servlet.jsp.JspException: /WEB-INF/queries/mondrian.jsp(10,7) equal symbol expected
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:460)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127)
at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:619)

stevenjackal said...

Hi Feris Thia,

Is that possible to run it on PHP? Other than that, how can i integrate with open report/ jasper report? really appreciate if you could give me some guideline/info to do this.

Ankur said...

Hi Ferris,

I have setup mondrian and trying to run it on my sales data. But it is throwing the following error. Can you please help me with this.


JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "test" with value "${query01.result.overflowOccured}": An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Order Count]} ON COLUMNS, {([Status].[All Status], [State].[All States])} ON ROWS from [SalesCube] ])

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "test" with value "${query01.result.overflowOccured}": An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Order Count]} ON COLUMNS,
{([Status].[All Status], [State].[All States])} ON ROWS
from [SalesCube]
])
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:491)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:401)

Just Another Blogger said...

Hi Ankur,

It's still very generic to me. How do you design your schema ? Are you using Schema Workbench ? Can you execute MDX in the Schema Workbench ? If so.. post your schema here, will you ?

Regards,

Feris

sunmoon said...

Could you please give steps for connecting PostGre SQL database in Mondrian.

I've Schema file and database file.

masuno said...

how to get the load time on mondrian?

cristhian said...

Thanks... very good this post...

TuyetPham said...

Hi,after doing all that, i'm getting this
JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException: com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to create envelope from given source:


org.apache.jasper.JasperException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException: com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to create envelope from given source:
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:500)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:395)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:308)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:259)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:264)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)

hari said...

Excellent blog. Could you please guide us on how to install mondrain 3.7 as well along with pre-requesties? FoodMart + MySQL. We tried the same steps but couldn't. Only older version of mondrian could be configured.

Balachandra S said...

I am getting this error when i click on jPivot Pivot table, could you please help me how to reslove this

balachandraks@gmail.com

JPivot had an error ...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: com.tonbeller.wcf.utils.SoftException: javax.xml.transform.TransformerConfigurationException: javax.xml.transform.TransformerException: java.io.FileNotFoundException: C:\Program%20Files\Apache%20Software%20Foundation\Tomcat%208.0\webapps\mondrian\WEB-INF\jpivot\toolbar\htoolbar.xsl (The system cannot find the path specified)

Richa said...

Hi - Is FoodMart a Pentaho proprietary database? Can we use it to build visualization in any other tool?