Pentaho Business Intelligence suite configuration: use MySQL 5.x in Windows

ArticleDirectory

• Part 1: Preparation
• Part 2
• Configure JDBC Security connection
• Configure Hibernate and quartz
• Configure SMTP Parameters
• Configure the publishing password Publishing
• Start the business intelligence platform

Pentaho Business Intelligence suite configuration: use MySQL 5.x in Windows  Open-source bi  Add comments

12th 17 2010

 

Pentaho developer Community Resource:

PAT (pentaho analysis tool)

CDF (Community dashboard Framework)Use the user console of pentaho's Business Intelligence server to create a data panel framework

Mapreduce (community build Framework)

CDA (Community data access)

A data access layer for CDF (Community dashboard framework ).

Part 1: Preparations 1. install and configure Java

Download and install the latest Java JDK from Sun Developer Network.

Configure the Java Runtime Environment in Windows:

Click "my computer"-"properties"-"advanced" to add new environment variables, such (You must change it to a system variable. User variables cannot be used. In the win764-bit environment, repeated tests have problems.)

You can also add the catalina_opts environment variable so that the Apache-Tomcat server can use more memory than the default value. The variable value is:

-Xms256m-xmx768m-XX: maxpermsize = 256 m-dsun. RMI. DGC. Client. gcinterval = 3600000-dsun. RMI. DGC. server. gcinterval = 3600000

See:

Install and configure MySQL 5.x Server

Use the default value.

Deploy the pentaho Platform

Download the biserver-ce-3.7.x.stable.zip file from the pentaho SourceForge projects page. The decompressed file directory is as follows:

C:
|-Pentaho
|-Adminstration-Console
|-Biserver-ce

 

 

Double-click the run start-pentaho.bat in the biserver-ce directory, the pentaho Bi Server service runs automatically and can then be accessed using http: // localhost: 8080

If tomcat has been configured on the computer, move the following bold text folder to the Apache-Tomcat installation directory.

C:
|-Pentaho
|-Adminstration-Console
|-Biserver-ce
|-Pentaho-Solutions
|-Tomcat
|-Common
| '-Lib
| '- Mysql-connector-java-5.0.7.jar (Optional)
|-Webapps
| '- Pentaho
| '- Pentaho-Styles
| '- Sw-Styles

MySQL Java connector MySQL Java Connector

If Apache-Tomcat does not have a MySQL Java connector, copy the mysql-connector-java-5.0.7.jar to the Tomcat commonlib directory.

SQL script pack-SQL script package

Download the MySQL 5.x SQL script package to configure the required database.

Part 2 configure the database

Decompress the MySQL 5.x SQL script package and extract five SQL files:

1_create_repository_mysql. SQL

Create a hibernate Database

2_create_quartz_mysql. SQL

Create a quartz Database

3_create_sample_cece_mysql. SQL

Add the sample data provided by pentaho to the hibernate Database

4_load_sample_users_mysql. SQL

Add the user and role of the sample database to the hibernate database.

5_sample_data_mysql. SQL

Create a sample database

Open the "MySQL"-"MySQL Server 5.1"-"MySQL command line client" installed in the "Start" menu and go to the command line interface (similar to the DOS interface) after entering the administrator password, execute the following command (in order ):

Mysql>Source export create_repository_mysql. SQL;
... Output
Mysql>Source 2_create_quartz_mysql. SQL;
... Output
Mysql>Source 3_create_sample_cece_mysql. SQL;
... Output
Mysql>Source 4_load_sample_users_mysql. SQL;
... Output
Mysql>Source 5_sample_data_mysql. SQL;
... Output

Run "mysql> show databases;" to view the newly created database. The database structure is as follows:

• Hibernate *
  • Authorities
  • Datasource
  • Granted_authorities
  • Users
• Quartz
  • Qrtz_blob_triggers
  • Qrtz_calendars
  • Qrtz_cron_triggers
  • Qrtz_fired_triggers
  • Qrtz_job_details
  • Qrtz_job_listeners
  • Qrtz_locks
  • Qrtz_paused_trigger_grps
  • Qrtz_scheduler_state
  • Qrtz_simple_triggers
  • Qrtz_trigger_listeners
  • Qrtz_triggers
• Sampledata
  • Customer_w_ter
  • MERs
  • Department_managers
  • Dim_time
  • Employees
  • Offices
  • Orderdetails
  • Orderfact
  • Orders
  • Payments
  • Products
  • Quadrant_actuals
  • Trial_balance

Note: if an error is reported when running the 5_sample_data_mysql. SQL script, replace the timestamp value "00.000000000" with "00.000000" in the text editor ".

Configure JDBC Security connection

Next we will direct the pentaho Bi platform to the MySQL database server, instead of using the built-in hsql database. Below, you will need to modify several configuration files:

ApplicationContext-spring-security-jdbc.xml

File Location: pentaho-solutionssystem

<! -This is only for Hypersonic. Please update this section for any other database you are using->
<Bean id = "datasource"
Class = "org. springframework. JDBC. datasource. drivermanagerdatasource">
<Property name = "driverclassname" value ="Org. HSQLDB. jdbcdriver"/>
<Property name = "url"
Value ="JDBC: HSQLDB: hsql: /localhost: 9001/hibernate"/>
<Property
Name = "username" value = "hibuser"/>
<Property name = "password" value = "password"/>
</Bean>

Replace the highlighted part:

<! -This is only for Hypersonic. Please update this section for any other database you are using->
<Bean id = "datasource" class = "org. springframework. JDBC. datasource. drivermanagerdatasource">
<Property name = "driverclassname" value ="Com. MySQL. JDBC. Driver"/>
<Property name = "url" value ="JDBC: mysql: // localhost: 3306/hibernate"/>
<Property name = "username" value = "hibuser"/>
<Property name = "password" value = "password"/>
</Bean>

ApplicationContext-spring-security-hibernate.properties

File Location: pentaho-solutionssystem

Original file:

JDBC. Driver =Org. HSQLDB. jdbcdriver

JDBC. url =JDBC: HSQLDB: hsql: /localhost: 9001/hibernate

JDBC. Username = hibuser

JDBC. Password = Password

Hibernate. dialect =Org. hibernate. dialect. hsqldialect

To:

JDBC. Driver =Com. MySQL. JDBC. Driver

JDBC. url =JDBC: mysql: // localhost: 3306/hibernate

JDBC. Username = hibuser

JDBC. Password = Password

Hibernate. dialect =Org. hibernate. dialect. mysqldialect

Hibernate-settings.xml

File Location: pentaho-solutionssystemhibernate

Original file:

<Config-File> system/hibernate/Hsql. hibernate. cfg. xml</Config-File>

To:

<Config-File> system/hibernate/Mysql5.hibernate. cfg. xml </Config-File>

Configure Hibernate and quartz

Hibernate and quartz must use the corresponding database created on the MySQL server. Therefore, you need to modify the context. xml file in the "atatwebappspentahometa-inf" directory.

Original file:

<? XML version = "1.0" encoding = "UTF-8"?>

<Context Path = "/pentaho" docbase = "webapps/pentaho/">

<Resource Name = "JDBC/hibernate" auth = "Container" type = "javax. SQL. datasource"

Factory = "org. Apache. commons. DBCP. basicdatasourcefactory" maxactive = "20" maxidle = "5"

Maxwait = "10000" username = "hibuser" Password = "password"

Driverclassname = "org. HSQLDB. jdbcdriver" url = "JDBC: HSQLDB: hsql: // localhost/hibernate"

Validationquery = "select count (*) from information_schema.system_sequences"/>

<Resource Name = "JDBC/quartz" auth = "Container" type = "javax. SQL. datasource"

Factory = "org. Apache. commons. DBCP. basicdatasourcefactory" maxactive = "20" maxidle = "5"

Maxwafit = "10000" username = "pentaho_user" Password = "password"

Driverclassname = "org. HSQLDB. jdbcdriver" url = "JDBC: HSQLDB: hsql: // localhost/quartz"

Validationquery = "select count (*) from information_schema.system_sequences"/>

</Context>

To:

<? XML version = "1.0" encoding = "UTF-8"?>

<Context Path = "/pentaho" docbase = "webapps/pentaho/">

<Resource Name = "JDBC/hibernate" auth = "Container" type = "javax. SQL. datasource"

Factory = "org. Apache. commons. DBCP. basicdatasourcefactory" maxactive = "20" maxidle = "5"

Maxwait = "10000" username = "hibuser" Password = "password"

Driverclassname ="Com. MySQL. JDBC. Driver"Url ="JDBC: mysql: // localhost: 3306/hibernate"

Validationquery ="Select 1"/>

<Resource Name = "JDBC/quartz" auth = "Container" type = "javax. SQL. datasource"

Factory = "org. Apache. commons. DBCP. basicdatasourcefactory" maxactive = "20" maxidle = "5"

Maxwafit = "10000" username = "pentaho_user" Password = "password"

Driverclassname ="Com. MySQL. JDBC. Driver"Url ="JDBC: mysql: /// localhost: 3306/quartz"

Validationquery ="Select 1"/>

</Context>

Modify the Web. xml file in the tomcatwebappspentahoweb_inf directory and configure pentaho bi as follows:

• Pentaho-solutions location
• URL
• Secure address (optional, for remote access to the Administrator console)

Pentaho-solution path

Configure relevant parameters based on your installation:

<Context-param>
<Param-Name> solution-path </param-Name>
<Param-value> C: pentahopentaho-solutions </param-value>
</Context-param>

Fully-qualified-server-URL

Find the following sectionCode:

<Param-value> http: // localhost: 8080/pentaho/</param-value>

Modification example (based on the domain name or IP address of your computer or server ):

<Param-value> http://www.prashantraju.com: 8080/pentaho/</param-value>
Or
<Param-value> http: // 192.168.1.10: 8080/pentaho/</param-value>

Configure SMTP Parameters

Example:

<Email-SMTP>
<Properties>
<Mail. SMTP. Host>Smtp.gmail.com </Mail. SMTP. Host>
<Mail. SMTP. Port> 587 </Mail. SMTP. Port>
<Mail. Transport. Protocol> Smtps </Mail. Transport. Protocol>
<Mail. SMTP. starttls. Enable> True </Mail. SMTP. starttls. Enable>
<Mail. SMTP. Auth> True </Mail. SMTP. Auth> <mail. SMTP. SSL> True </Mail. SMTP. SSL>
<Mail. SMTP. quitwait> False </Mail. SMTP. quitwait>
</Properties>
<Mail. POP3> </mail. POP3>
<Mail. From. Default> Pentahoadmin@gmail.com </Mail. From. Default>
<Mail. userid> Pentahoadmin@gmail.com </Mail. userid>
<Mail. Password> Password </Mail. Password>
</Email-SMTP>

Configure the publishing password Publishing

By default, the publishing function is disabled. To enable it, you need to set a password. Find the publisher_config.xml file in the "pentaho-solutionssystem" directory.

Find the following code:

<Publisher-config>
<Publisher-Password> </publisher-Password>
</Publisher-config>

Modify the example (to publish the content to the pentaho Bi platform, you need the password set below ):

<Publisher-config>
<Publisher-Password>Publishthis</Publisher
-Password>
</Publisher-config>

Start the business intelligence platform

The pentaho Bi platform is based on the webapp on the Apache-Tomcat server. To start Apache-Tomcat, you must set APACHE-Tomcat as a service. The following command line is started:

C: pentahobiserver-cetomcatbin>Service. Bat install tomcat5

After running the preceding command, install the 'tomcat 5' service ′...

Using catalina_home: D: pentahobiserver-cetomcat

Using catalina_base: D: pentahobiserver-cetomcat

Using java_home: C: Program filesjavajdk1.6.0 _ 13

Using JVM: C: Program filesjavajdk1.6.0 _ 13jrebinserverjvm. dll

The 'tomcat 5' service has been installed.

You can find APACHE-tomcat in Windows Service Management and start-stop the management. See:

After successful startup, you can open your browser and enter the URL: http: // localhost: 8080/pentahoOrHttp: // [your_domain_or_ip]: 8080/pentaho. If the pentaho Bi platform is successfully started, you can see the following welcome page:

Find the start-pac.bat in the "C: pentahoadminstration-console" directory, double-click and start the console successfully, enter the address http: // localhost: 8099/in the browser /, you can access the console!

Well done!