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:
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-passwordecho "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
- Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
- Open jdbc.properties
- 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
- Click at the left top menu: File → Manage Data Sources
- 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. - 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. 🙂
The 
Interesting IT Tip's 