Category Archives: OLAP Model

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. 🙂

Advertisement