Setting up Pentaho

[shawntolidano]

I downloaded the demo and, as it is runnable right out of the zip, installed and used it easily. I'm now interested in applying it to my own data. What customization is necessary to attach the Pentaho framework to my own mySQL database and my own tables with my own data? Is it at the code level? Are there configuration files? I believe once I have it referencing our own data, we can piece together the rest of this amazing puzzle. Thanks in advance.

[mbatchelor]

There shouldn't be any code changes required to integrate with your RDBMS data. For the JBoss server to be able to talk to your database, you need to copy your JDBC driver to the jboss/server/default/lib.

Then, you need to build a report to go against the database of your choice. The steps you need to go through line up like this:

• Pick a Report Engine (BIRT or Jasper Reports at present)
• Download the report designer for your chosen engine
• Configure the report designer to be able to use your database
• Create and test your report within the designer
• Copy the report into a Pentaho solution
• Create an Action Sequence document that will run the report (see samples)

[grsw]

How to move pentaho-demo to MySQL ?

Assuming that you have the preconfigured pentaho-demo installed properly
(the root directory of pentaho-demo installation will be referenced as PENTAHO_DEMO),
and you have MySQL database engine running and available, JDK 1.4 (or 1.5) configured,
and Ant build utility available, you may follow the instructions to
migrate pentaho-demo databases and repositories into MySQL database.


The process of migrating pentaho-demo to MySQL consist of the three parts:
- Transfer sample databases and repositories from HSQL to MySQL
- Change the configuration files to point to the MySQL instead of HSQL
- Change the definitions of reports and actions to poing to MySQL instead of HSQL

1. HSQL to MySQL transfer:
Preconfigured pentaho-demo installation goes with the repositories (shark workflow,
quartz scheduler) and sample databases stored as HSQL databases,
The first thing to do is to create appropriate database schemas in MySQL:
- create user named 'pentaho' in MySQL.
- create the following schemas in MySQL:
'hibernate', 'quartz', 'sampledata', 'shark'
- grant all necessary priveleges on the newly created schemas to 'pentaho' user


The convenient way of migrating databases from HSQL is to use the Transfer utility,
but to make it working we need to build the utility from the sources:
- download hsqldb_1_8_0_2 (http://sourceforge.net/project/showf...group_id=23316),
and unzip it to the directory of your choice (will be referenced as HSQLDIR)
- copy the files attached to this post (MySQLTransferHandler.java, HelperFactory.java)
to the HSQLDIR/src/org/hsqldb/util directory
- open the command line (or terminal) and change the current directory to
HSQLDIR/build, run the following command 'ant hsqldbutil'
- when the build process is successfully finished we will have hsqldbutil.jar file
in the HSQLDIR/lib directory. Copy this file to PENTAHO_DEMO/data directory.
- Copy MySQL JDBC driver archive (mysql-connector-java-3.1.10-bin.jar)
to the PENTAHO_DEMO/data directory.
- Start the HSQL database server using PENTAHO_DEMO/data/start_hypersonic.* script
- open the command line (or terminal) and change the current directory to
PENTAHO_DEMO/data. Run the following command (if you are on UNIX change the classpath separator to ':'):
java -cp hsqldbutil.jar;mysql-connector-java-3.1.10-bin.jar;./lib/hsqldb.jar org.hsqldb.util.Transfer
- The 'Source Database' dialog appears. From the 'Type' list choose 'HSQL Database Engine Server'.
In the 'URL' input field, add the database name 'jdbc:hsqldb:hsql://localhost/hibernate'. Press 'OK'.
- The 'Target Database' dialog appears. From the 'Type' list choose 'MySQL Connector/J'.
Enter the URL pointing to your MySQL 'hibernate' schema. For me it is: 'jdbc:mysql://localhost:3306/hibernate'.
Enter the 'pentaho' username and password. press 'OK'. Press 'Start Transfer' in the 'HSQL Transfer Tool' window.
Quit the Transfer utility after the transfer is finished.
- Repeat the transfer for the 'quartz', 'shark', and 'sampledata' databases.

2. Changing pentaho-demo configuration files
- Copy MySQL JDBC driver to the JBoss libraries. You may place mysql-connector-java-3.1.10-bin.jar file
in the PENTAHO_DEMO/jboss/server/default/lib directory.
- Edit PENTAHO_DEMO/jboss/server/default/deploy/pentaho.war/WEB-INF/classes/hibernate.cfg.xml:
Comment out the HSQL configuration. Uncoment and change MySQL Configuration, to something like:

com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/hibernate
org.hibernate.dialect.MySQLDialect
pentaho
pentaho_password

- Edit PENTAHO_DEMO/jboss/server/default/deploy/pentaho.war/WEB-INF/classes/quartz.properties.
Change the data source entries pointing to HSQL to make them point to MySQL database:

org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.myDS.URL = jdbc:mysql://localhost:3306/quartz
org.quartz.dataSource.myDS.user = pentaho
org.quartz.dataSource.myDS.password = pentaho_password
org.quartz.dataSource.myDS.maxConnections = 5

- Edit PENTAHO_DEMO/pentaho-solutions/system/shark/conf/Shark.conf file.
Change the data source definitions, to make them point to MySQL database:

DatabaseManager.DB.sharkdb.JdbcDriver="com.mysql.jdbc.Driver"
DatabaseManager.DB.sharkdb.Connection.Url="jdbc:mysql://localhost:3306/shark"
DatabaseManager.DB.sharkdb.Connection.User="pentaho"
DatabaseManager.DB.sharkdb.Connection.Password="pentaho_password"


- For the convenience change the PENTAHO_DEMO/start-pentaho.bat script. Remove the line
which starts hypersonic database. HSQL database is not necessary, because we have
all of the repositories stored in MySQL.

3. Changing actions and reports definition:

Change the data source definitions within action definitions files, and report definition files,
to point to appropriate MySQL databases.
driver: 'com.mysql.jdbc.Driver' instead of 'org.hsqldb.jdbcDriver'
URL: 'jdbc:mysql://localhost:3306/sampledata' instead of 'jdbc:hsqldb:hsql://localhost/sampledata'
user: pentaho
password: pentaho_password you defined for the 'pentaho' MySQL user.

The following files should be changed:
PENTAHO_DEMO/pentaho-solutions/samples/getting-started/quadrant-budget-burst.action.xml
PENTAHO_DEMO/pentaho-solutions/samples/getting-started/sample-data-query.action.xml
PENTAHO_DEMO/pentaho-solutions/samples/getting-started/quadrant-budget-for-region-hsql.rptdesign
PENTAHO_DEMO/pentaho-solutions/samples/reporting/BIRT-quadrant-budget-for-region-hsql.rptdesign
PENTAHO_DEMO/pentaho-solutions/samples/reporting/BIRT-quadrant-budget-hsql.rptdesign
PENTAHO_DEMO/pentaho-solutions/samples/reporting/jasper-reports-test-1.action.xml
PENTAHO_DEMO/pentaho-solutions/samples/reporting/jasper-reports-test-2.action.xml
PENTAHO_DEMO/pentaho-solutions/rules/department_managers.action.xml
PENTAHO_DEMO/pentaho-solutions/rules/query_rule1.action.xml

After all of this changes you can start the demo by running PENTAHO_DEMO/start-pentaho script.
Open the browser with http://localhost:8080/pentaho/jsp URL, and try the examples with the MySQL back-end database.
They should work as expected (they work for me).

Regards
Grzegorz http://forums.pentaho.org/archived_a...nsferPatch.zip

Post edited by: grsw, at: 08/27/2005 11:28

Post edited by: grsw, at: 08/27/2005 11:31

[ser]

Hi grsw,
thx for the detailed description. can you say for which version of pentaho is it?

I use the pentaho_demo-1.2.RC1.341. I do every step what you describe it but there is some files not available or modificated becouse of the new version. (I think so). Down thison messgae i post the unclear (for me) cases. can you please describe it for this version or tell me how can i do it?

My last question how can i configure the pentaho-designer so, that i can use the data of mysql database.

Thx and best regard

ser

. e.g.
__________________________________________________
- Edit PENTAHO_DEMO/jboss/server/default/deploy/pentaho.war/WEB-INF/classes/quartz.properties.
Change the data source entries pointing to HSQL to make them point to MySQL database:

org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.myDS.URL = jdbc:mysql://localhost:3306/quartz
org.quartz.dataSource.myDS.user = pentaho
org.quartz.dataSource.myDS.password = pentaho_password
org.quartz.dataSource.myDS.maxConnections = 5
________________________________-
Change the data source definitions within action definitions files, and report definition files,
to point to appropriate MySQL databases.
driver: 'com.mysql.jdbc.Driver' instead of 'org.hsqldb.jdbcDriver'
URL: 'jdbc:mysql://localhost:3306/sampledata' instead of 'jdbc:hsqldb:hsql://localhost/sampledata'
user: pentaho
password: pentaho_password you defined for the 'pentaho' MySQL user.

The following files should be changed:
PENTAHO_DEMO/pentaho-solutions/samples/getting-started/quadrant-budget-burst.action.xml
PENTAHO_DEMO/pentaho-solutions/samples/getting-started/sample-data-query.action.xml
PENTAHO_DEMO/pentaho-solutions/samples/getting-started/quadrant-budget-for-region-hsql.rptdesign (this is the only file which i could find it)
PENTAHO_DEMO/pentaho-solutions/samples/reporting/BIRT-quadrant-budget-for-region-hsql.rptdesign
PENTAHO_DEMO/pentaho-solutions/samples/reporting/BIRT-quadrant-budget-hsql.rptdesign
PENTAHO_DEMO/pentaho-solutions/samples/reporting/jasper-reports-test-1.action.xml
PENTAHO_DEMO/pentaho-solutions/samples/reporting/jasper-reports-test-2.action.xml
PENTAHO_DEMO/pentaho-solutions/rules/department_managers.action.xml
PENTAHO_DEMO/pentaho-solutions/rules/query_rule1.action.xml

[rutfield]

I could use some guidance as well. FOr some reason, the hypersonic database loses is not accessible after a while, so I thought it owuld be useful to persist them to MySQL.

1. HSQL to MySQL transfers
This looks great. The only thing I needed to do was download a newer version of hsqldb_1_8_0_2 (I used _4). The difference may be due to MySQL 5, but it involved data types and VARCHAR declarations being different in the CREATE TABLE COMMANDS.

2. I am packaging Pentaho as an ear under JBoss Portal Server.
I expect to only need to change references under the pentaho-solutions directory... not jboss/server/default/deploy - but I could be wrong.
If I change the hsqldb-ds.xml in the deploy directory, then all the portal software will use MySQL as well and possibly impede performance. I really would like to know which ones I need to change.

Modifying the quartz and Shark files makes sense to me.

3. I suspect the list of files to be changed is outdated. Should it be all the *.xaction files which use hsqldb under pentaho-solutions?

When I access the reports which were working, I now get the following. I did not expect it to reference hibernate anywhere.

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

type Exception report

message

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

exception

javax.servlet.ServletException: Servlet execution threw an exception
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)


root cause

java.lang.ExceptionInInitializerError
org.pentaho.repository.HibernateUtil.initialize(HibernateUtil.java:153)
org.pentaho.repository.HibernateUtil.<clinit>(HibernateUtil.java:81)
org.pentaho.repository.runtime.RuntimeRepository.setSession(RuntimeRepository.java:74)
org.pentaho.core.system.PentahoSystem.getRuntimeRepository(PentahoSystem.java:627)
org.pentaho.core.solution.SolutionEngine.execute(SolutionEngine.java:173)
org.pentaho.core.services.BaseRequestHandler.handleActionRequest(BaseRequestHandler.java:132)
org.pentaho.ui.servlet.ViewAction.doGet(ViewAction.java:117)
javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)


note The full stack trace of the root cause is available in the Apache Tomcat/5.5.17 logs.

Thanks.
CR

[GrayMatter]

hi

I want migrate Pentaho repository, shark, hibernate and quarts databases from HSQLDB to DB2.

if follow the set of instruction posted above, will i be able migrate from HQSDB to DB2.
I'm newbie so i don't know much about the JAVA file(MySQLTransferHandler.java, HelperFactory.java).what kind of changes should i make to this JAVA file to meet my requirement .