Category Archives: MySql/MariaDB

Additional Databases

Posted on by
1

Sample Databases

The Pentaho Demo comes with SampleData (SteelWheels based) HSQLDB that will suffice for modeling and learning all the Pentaho tools. But you may want to model metadata* or mondrian from scratch, require more rows in your tables, or want to replicate lots of samples that refer to the Microsoft Server. Well, at least that what crossed my mind when I started using these tools.

So I looked up the links to recreate two additional databases in MySQL for you to use. 🙂

I. DATABASE

Two Databases

The classic databases for learnimg SQL and MDX are AdventureWorks (OLTP) [Updated link on 2022] and FoodMart (DW). AdventureWorks is a relational database and FoodMart is a Data Wharehouse database.

Download them and Unzip them. You need to add this line on the top of FoodMart file:

CREATE DATABASE IF NOT EXISTS foodmart; USE foodmart;

Upload them to your MySql database and grant access to pentaho_user.

mysql -u root-user -p root-password < AWBackup.sql
 mysql -u root-user -p root-password < foodmart_mysql.sql
 echo "grant all on adventureworks.* to pentaho_user@localhost identified by 'password';`" | mysql -u root-user -p root-password
 echo "grant all on foodmart.* to pentaho_user@localhost identified by 'password';" | mysql -u root-user -p root-password

II. CONFIG

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open jdbc.properties
  3. For new datasources, add (You can define it also in the web app if you like) 
    FoodMart/type=javax.sql.DataSource
FoodMart/driver=com.mysql.jdbc.Driver
FoodMart/url=jdbc:mysql://localhost:3306/foodmart
FoodMart/user=pentaho_user
FoodMart/password=password
AdventureWorks/type=javax.sql.DataSource
AdventureWorks/driver=com.mysql.jdbc.Driver
AdventureWorks/url=jdbc:mysql://localhost:3306/adventureworks
AdventureWorks/user=pentaho_user
AdventureWorks/password=password

Add datasources in the web app

Download the FoodMart mondrian (snowflake schema) here and unzip them. We will use the FoodMart file in next section.

Add datasources in the web app

  1. Click at the left top menu: File → Manage Data Sources
  2. Create a JDBC Data Source for each Database:
    a. Click on the New Data Source button
    b. Name the Datasource and type: FoodMart, Database. Click Ok.
    c. Create (plus sign icon) a new Connection: FoodMart.
    Name: FoodMart; Select Type: MySql; Host: localhost; Database: foodmart;
    user name: pentaho_user; password: password.
    d. Click Test and it should report a succesfull connection, click Ok.
    e. Repeat for AdventureWorks just change name and Database: adventureworks.
    f. After you create both data sources, click Cancel to return to the Data Source Dialog.
  3. Create the Analisys Data Source for the FoodMart model:
    a. Click in the Gear Icon.
    b. Select New Connection.
    c. Click on Import Analysys.
        select the downloaded FoodMart.Mondrian.xml file.
    d. Click Import.

That’s it.

To use FoodMart.mondrian.xml you can use the Desktop Tools Schema-Workbench and Aggregation-Designer (here is an excelent tutorial by Diethard Steiner), but for the Pentaho-Server you will need to install jPivot4J from the marketplace as jpivot or Saiku cannot read it.

* There are two tutorials for using the meta-data editor to configure user or group business models and to provide restriction access to databases. The first one is by Diethard Steiner on 2009 or one written by Herwin Rayen in 2014. The PRD can be programmed to use this access. But I have to tell that no client has ever asked/payed for it. 🙂

Pentaho Server 8.1 with MariaDB (MySql)

Posted on by
6

[Edited on:
– December 2018 to correct config drivers for MariaDB.
– June 13th 2018, to add the solution note to Sql parameters.]

Migrate your Database

The official way to change the database is at pentaho-wiki and pentaho-doc. These are notes to my 2011 post that modifies the DEMO to use MariaDB instead of the HSQLDB that comes with it.

Important: I verified this on a Ubuntu 18.04 and MariaDB 10.1, keeping the  jcr (Jackrabbit repository) in thefilesystem and have a problem with JINDI SQL Queries that I have to substitute with JDBC SQL Queries (see the final note).

I. DATABASE DRIVER

Download the driver [Connector/J mariadb-java-client-2.3.0.jar] to ~/Pentaho8/pentaho-server/tomcat/lib.

If you have the desktop tools, copy the driver also to ~/Pentaho8/report-designer/lib, ~/Pentaho8/data-integration/lib, ~/Pentaho8/shema-workbench/lib.

II. DATABASE CONTENT

Create content in Database

Start the MariaDB engine and run the suplied scripts in ~/Pentaho8/pentaho-server/data/mysql5, to create required databases:

cd ~/Pentaho8/pentaho-server/data/mysql5
 mysql -u root-user -p root-password < create_quartz_mysql.sql
 mysql -u root-user -p root-password < create_repository_mysql.sql

Download the Sample Database (here), unzip and upload it:

mysql -u root-user -p root-password < SampleData_2018_mysql.sql

III. TOMCAT CONFIG

Hibernte Settings

  1. Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
  2. Open hibernate-settings.xml.
  3. Find the  tag config-file and change it to 
    system/hibernate/mysql5.hibernate.cfg.xml
  4. Open mysql5.hibernate.cfg.xml change the class drivers 
    <property name="connection.driver_class">org.mariadb.jdbc.Driver</property>
<property name="connection.url">jdbc:mariadb://localhost:3306/hibernate</property>

System connections

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
  2. Open context.xml
  3. Change classname, url and SQL query 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.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
		maxWait="10000" username="hibuser" password="password"
		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/hibernate"
		validationQuery="select 1" />

	<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
		maxWait="10000" username="pentaho_user" password="password"
		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/quartz"
		validationQuery="select 1"/>

</Context>

Spring config

  1. Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
  2. Open applicationContext-spring-security-hibernate.properties
  3. Change driver, url and dialect to 
    jdbc.driver=org.mariadb.jdbc.Driver
jdbc.url=jdbc:mariadb://localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open jdbc.properties
    1. Change the driver definitions to mysql 
      SampleData/type=javax.sql.DataSource
SampleData/driver=org.mariadb.jdbc.Driver
SampleData/url=jdbc:mariadb://localhost:3306/sampledata
SampleData/user=pentaho_user
SampleData/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.mariadb.jdbc.Driver
Hibernate/url=jdbc:mariadb://localhost:3306/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=org.mariadb.jdbc.Driver
Quartz/url=jdbc:mariadb://localhost:3306/quartz
Quartz/user=pentaho_user
Quartz/password=password
SampleDataAdmin/type=javax.sql.DataSource
SampleDataAdmin/driver=org.mariadb.jdbc.Driver
SampleDataAdmin/url=jdbc:mariadb://localhost:3306/sampledata
SampleDataAdmin/user=pentaho_admin
SampleDataAdmin/password=password

Stop HSQLDB from starting

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
  2. Open web.xml
  3. Find the word HSQLDB. There are to points to edit. You will delete “–>” on the first line and “<–” on the last, to make it into a comment.In version 8.1 go to lines 25 and 167. They should look like this: 
    <!-- [BEGIN HSQLDB DATABASES]
 <context-param>
 <param-name>hsqldb-databases</param-name>
 <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
 </context-param>
 [END HSQLDB DATABASES] -->

    and

    <!-- [BEGIN HSQLDB STARTER]
 	<listener>
 	<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
 </listener>
 [END HSQLDB STARTER] -->

IV. Change Web Server Datasources

  1. Start your BI server and log-in  (localhost:8080/pentaho) as Admin.
  2. Click at the left top menu: Manage Data Sources
  3. You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
  4. Change the database type to MariaDb. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password). Click on TEST. Click OK if its succesfull.

Here, you can define a connection to your databases or you can return later.

That’s it, you should have a working Server with MariaDB. To test it by using the samples: Browse: Samples → Public → Steel Wheels.

V. Modify your SQL Queries that uses Parameters

But there is a problem if you want to use a query wit SQL with parameters. Maybe there is a missconfiguration in CDA or the repository, or a problem with the MySql driver for a MariaDB engine, as the parameters in the JNDI configuration report an error and do not work.

To get a result I use only SQL JDBC, by supplying these config parameters on each query

driver:    org.mariadb.jdbc.Driver
 url=      jdbc:mariadb://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password

Troubleshoot Log

If you have problems check the log file is ~/Pentaho8/pentaho-server/tomcat/logs/catalina.out its very verbose so look for ERROR lines