Dimension Usage Example in Pentaho Schema Workbench

Many of us have a bit confusion when to use Dimension and Dimension Usage.

Today, I have explored it by opening the FoodMart.xml schema example.
I'd like to share what I understand from this...

Software:
 Mondrian- Pentaho Schema Workbench 3.6.1 stable.
Download link:
http://jaist.dl.sourceforge.net/project/mondrian/schema%20workbench/3.6.1-stable/psw-ce-3.6.1.zip

My understanding points


1) Add "Dimension" to your schema.(Externally to the schema but not to any CUBES)
2) Use the same "Dimension" added to your schema in any number of CUBES as "Add Dimension Usage"
3) Suppose you are using a dimension in 3 different cubes you may think of creating that dimension 3 times. The problem which I understand is replication of dimension is taking place in the schema file.

Using "Add Dimension Usage" feature we can eliminate it.

Also, I read some thing about it in the Mondrian technical document at page 113 - I need to have a complete understanding on it.


Example which I extracted from FoodMart.xml schema is described below.

Click here to download the example


A quick understanding image is given below.

Example developed using : foodmart database of postgresql which is an example database for jasperserver postgres. 

Installation of Pentaho 5.0.1 CE with MySQL db (in Windows 7 / Ubuntu) || Integrating Pentaho 5.0.1 CE with MySQL database

Pentaho, an awesome tool which is making me to play around either in office or at home.

On they way of exploration today(20th Jan 2014) below is the out come of integrating Pentaho 5.0.1 CE BA server with MySQL.

I'd like to say thanks to the Pentaho team for building this awesome tool with highly professional recommended open source BI plat form.

Below description is the step by step procedure for integrating Pentaho with MySQL which will work on both Windows and Linux family of servers.

Environment:
1) OS: Ubuntu /Windows 7
2) Database : MySQL 5.5(Community)
3) Web browser : Mozilla Firefox(Preferable)

Quick Steps : 
1) Installing Java Software and setting JAVA_HOME,JRE_HOME ( Windows/Ubuntu)
2) Downloading Pentaho (Ubuntu/Windows)
3) Downloading MySQL 5.5 Community
4) Executing MySQL .sql scripting files for Pentaho Integration
5) Modifying various configuration files to build the communication of Pentaho Server with MySQL db.

1) Installing Java Software and setting JAVA_HOME,JRE_HOME ( Windows/Ubuntu)
In windows:
Down load java from oracle site (here) - You need to have oracle account to download.
For setting PATH,CLASS_PATH, JAVA_HOME, JRE_HOME  read this blog
In Ubuntu
You can either download sun jdk or open jdk
Find downloading,installing here for open jdk
For ubuntu you no need to set the PATH, CLASS_PATH, JAVA_HOME,JRE_HOME for java open jdk.

2) Downloading Pentaho (Ubuntu/Windows)
In Windows
 http://ncu.dl.sourceforge.net/project/pentaho/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip
For Ubuntu
You just need to provide wget command before the link on command prompt
i.e.,
1) create a folder with name "pentaho"
2) navigate to that folder
    for example : home/pentaho
3) Give the below command at pentaho folder
wget  http://ncu.dl.sourceforge.net/project/pentaho/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip

4) Unzip this the downloaded .zip file
Command : unzip biserver-ce-5.0.1-stable.zip

5) You will find bi-server folder after unzipping.

3)Downloading MySQL 5.5 Community
In Ubuntu 
Go through the below post of mine to configure MySQL in Ubuntu machine

http://pentaho-bi-suite.blogspot.in/2014/01/mysql-installation-in-remote-ubuntu.html

In Windows
In Windows it's just to run and configure the .exe file and a general like software installation
MySQL down link : Clickbelow(You need to have oracle account)

http://dev.mysql.com/downloads/file.php?id=450445

4) Executing MySQL .sql scripting files for Pentaho Integration
From here I'm going to explain only on Ubuntu server as it is simple to do the same in windows machine.

File you need to execute are :
1) create_jcr_mysql.sql
2) create_quartz_mysql.sql
3) create_repository_mysql.sql

Connect to mysql 
root@SAD-AKAR-POC167:/usr/bin# mysql -u root -p
Enter password:password
  
Exectuing .sql files 
1) mysql>source   home/pentaho/biserver-ce/data/mysql5/create_jcr_mysql.sql

2) mysql>source   home/pentaho/biserver-ce/data/mysql5/create_quartz_mysql.sql

3) mysql>source   home/pentaho/biserver-ce/data/mysql5/create_repository_mysql.sql

Once you execute the .sql files check the databases using show databases command.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hibernate          |
| jackrabbit         |
| mysql              |
| performance_schema |
| quartz             |
| test               |
+--------------------+
7 rows in set (0.01 sec)

5) Modifying various configuration files to build the communication of Pentaho Server with MySQL db(Preparing MySQL BA Repository Database)

I)  Configure Quartz on MySQL BA Repository Database
     (quartz.properties)

II) Configure Hibernate Settings for MySQL
     ( applicationContext-spring-security-hibernate.properties
       hibernate-settings.xml
       mysql5.hibernate.cfg.xml )

III)  Modify JDBC Connection Information in the Tomcat context.xmlFile
     (context.xml)
     Delete/Remove pentaho.xml file in  Tomcat directory 
    (pentaho.xml)

IV) Modify Jackrabbit Repository Information for MySQL 
    (repository.xml)

V)  Stop HSQL db start up with pentaho start up
    (web.xml)
 

I)  Configure Quartz on MySQL BA Repository Database
     (quartz.properties)

Location of the file : 
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/quartz$nano quartz.properties



Original Code:
  org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Modify above Code like below:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

NOTE:
In nano editor use Ctrl+W to find the line of code

II) Configure Hibernate Settings for MySQL
     (  applicationContext-spring-security-hibernate.properties

        hibernate-settings.xml

       mysql5.hibernate.cfg.xml )

applicationContext-spring-security-hibernate.properties
Location of the file :  

sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system$nano applicationContext-spring-security-hibernate.properties


Comment/delete the exiting hsqldb code
Actual code
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

Code after Modification

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

Location of the file :  
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/hibernate$nano hibernate-settings.xml


Actual code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Code after Modification

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

mysql5.hibernate.cfg.xml
You need NOT to change any code in this file, but you just need to confirm every thing is proper as per your database settings.
Location of the file : 
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/hibernate$nano mysql5.hibernate.cfg.xml


Actual code

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>

<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

<property name="connection.username">hibuser</property>

<property name="connection.password">password</property>

Code after Modification
NO need to change any thing from actual code until and unless you have any modified database details at the time of MySQL installation such as host,port. 

III)  Modify JDBC Connection Information in the Tomcat context.xml file
      (context.xml)
      Delete/Remove pentaho.xml file in  Tomcat directory 
      (pentaho.xml)

context.xml
Location of the file
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/webapps/pentaho/META_INF$nano context.xml
Actual Code:

<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"

maxWait="10000" username="pentaho_user" password="password"

driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

Code after Modification

<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"

maxWait="10000" username="pentaho_user" password="password"

driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"

validationQuery="select 1"/>

pentaho.xml

Remove/delete this file
Location of this file
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/conf/Catalina/localhost$ls
pentaho.xml

Use rm command to delete this file
i.e.,
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/conf/Catalina/localhost$rm pentaho.xml

Reason for removing/deleting this file
Pentaho BA server  creates a copy of context.xml file with  the name of pentaho.xml when we start the server.
Now when the server starts the newly generated pentaho.xml file consists of the modified code of context.xml
(In simple words context.xml=pentaho.xml and pentaho.xml file should have the modifications done in context.xml file, that's the reason we are deleting pentaho.xml file)

IV) Modify Jackrabbit Repository Information for MySQL 

    (repository.xml)

You need to disable/comment some code and need to enable some code to work with MySQL
You need to disable and enable the code in the categorized list of locations in the file.
Location of the file
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/jackrabbit$nano repository.xml

 i)  FileSystem part
ii) data store configuration part
iii) security configuration part
iv) persistence manager of the workspace part
v) Configures the versioning
vi) PersistenceManager Versioning part

i)  FileSystem part
Comment this code
 <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
     <param name="path" value="${rep.home}/repository"/>
  </FileSystem>

Activate this code
 <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    <param name="driver" value="com.mysql.jdbc.Driver"/>
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="fs_repos_"/>
  </FileSystem>

ii) data store configuration part
Comment this code
   <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>

Activate this code
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="databaseType" value="mysql"/>
    <param name="driver" value="com.mysql.jdbc.Driver"/>
    <param name="minRecordLength" value="1024"/>
    <param name="maxConnections" value="3"/>
    <param name="copyWhenReading" value="true"/>
    <param name="tablePrefix" value=""/>
    <param name="schemaObjectPrefix" value="ds_repos_"/>
  </DataStore>

iii) security configuration part
Comment this code(line number 225 to 227(5.0.1 CE pentaho - it may vary depending on release)
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${wsp.home}"/>
    </FileSystem>
Activate this code( 197-204 lines)
 <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user"/>
      <param name="password" value="password"/>
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="fs_ws_"/>
    </FileSystem>

iv) persistence manager of the workspace part
Comment this code
 <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">
      <param name="url" value="jdbc:h2:${wsp.home}/db"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
    </PersistenceManager>

Activate this code
 <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user" />
      <param name="password" value="password" />
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
    </PersistenceManager>

v)Configures the versioning 
Comment this code
  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${rep.home}/version" />
    </FileSystem>
Active this code 
 <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user"/>
      <param name="password" value="password"/>
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="fs_ver_"/>
    </FileSystem>

vi) PersistenceManager Versioning part
Comment this code
 <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">
      <param name="url" value="jdbc:h2:${rep.home}/version/db"/>
      <param name="schemaObjectPrefix" value="version_"/>
    </PersistenceManager>
Active this code
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user" />
      <param name="password" value="password" />
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="pm_ver_"/>
    </PersistenceManager>

V)  Stop HSQL db start up with pentaho start up

    (web.xml)

 Comment or delete the code from below code from web.xml file so that you can stop the start up hsqldb when you start the pentaho server, at the same time instead of hsqldb pentaho server will pick the connections of MySQL .

Location of the file

sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/webappas/pentaho/WEB-INF$nano web.xml

Commenting code at 1st place - Comment the below code

 <!-- [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] -->

Commenting code at 2nd place - Comment the below code

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



You have done with integrating Pentaho 5.0.1 with MySQL database.

NOTE:
1) Check logs  for errors and pentaho server log for startup with MySQL
2)  Login with Admin/password credential.
3) You will find no example will work with this installation as all the pentaho given examples with work with hsqldb.
4) You need to execute the sample database scripts for that and you need to give jdbc connections in the BA server. 

Sadakar

I believe in "Learning never exhausts the mind"



 

MySQL installation in remote Ubuntu 12.04 server using PuTTY and accessing 3306 port in local mysql client by Tunneling

In this post you will learn how you can install MySQL in remote Ubuntu server as well how you can access the installed database from server to your local machine using PuTTY Tunneling.

Environment:
Client location 
1) Ubuntu Server 12.04
Development location
1) Windows 7 64 bit OS ( DEV location)
2) PuTTY installed in Windows.
3) DBVisulalizer(OR any MySQL client tool installed for connecting to remote servers)
4) MySQL installed in Windows and service running state.

This post is divided into two sections.
PART-I : MySQL installation in remote Ubuntu 12.04 Server using PuTTY
PART-II : How to by pass 3306 port number by Tunneling concept to access the MySQL database.
( Even though if 3306 port number is not opened in the Ubuntu server we can access the mySQL database)

Connect to Ubuntu server using given credentials(Username, password and ppk file)

PART-I : MySQL installation in remote Ubuntu 12.04 Server using PuTTY

1)root@SAD-AKAR-LLM167: aptitude update

2) Download and install using below command
     root@SAD-AKAR-LLM167:aptitude install mysql-server

3) The installer should ask you to set a root password : set it as "password"  and click on OK.

4) Installation will complete after clicking on OK.

5) MySQL Service status command after installation completed
    root@SAD-AKAR-LLM167:/# service mysql status
    mysql start/running, process 28645

6) MySQL Start command :
     root@SAD-AKAR-LLM167:/# service mysql start
   mysql start/running, process 29040

7) MySQL Stop command:
      root@SAD-AKAR-LLM167:/# service mysql stop
      mysql stop/waiting


8) MySQL installed location:
root@SAD-AKAR-LLM167:/# which mysql
/usr/bin/mysql
 
9) Connect to MySQL
root@SAD-AKAR-LLM167:/# /usr/bin/mysql -u root -p
Enter password:password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

10) Find mySQL version using below command

mysql>SHOW VARIABLES LIKE "%version%";
 
PART-II : How to by pass 3306 port number by Tunneling concept to access the MySQL database

 1. On your PuTTY window which will drop down the options in a list shown in below figure.
    (Assume that you have already working with PuTTY for MySQL installation and not closed it).

2. Click on Change Settings
  i) On the left side click on "Connections"
  ii) Expand SSH then click on Tunnels
  iii) Give destination as : 127.0.0.1:3306 or localhost:3306
  iv) Give source port as : 3306
  v) Now, click on Add

3. Once you click on Add you should find  added host in the box.

4. Impotently note that in your local machine(Windows) MySQL server is running. 

    In Destination you are giving localhost or 127.0.0.1 so you should run MySQL server in your local machine. And note that from the PuTTY(let's say Ubuntu server) you are by passing 3306 port number to get the actual MySQL database installed on the Ubuntu server. 

5. Now test whether it is working or not. 

   Open MySQL Query browser or any client tool where you can access remote MySQL server.

In this example I have taken DBVisualizer 

 

 

 Give all the database deatils

Database server : 127.0.0.1 or localhost (remember that it is not the IP of  Ubuntu server).

Database Port : 3306

Username and password : root/password

Find OS version
Find which Ubuntu version number using this command
root@SAD-AKAR-LLM167:/usr/bin#  cat /etc/*-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=12.04
DISTRIB_CODENAME=precise
DISTRIB_DESCRIPTION="Ubuntu 12.04.3 LTS"
NAME="Ubuntu"
VERSION="12.04.3 LTS, Precise Pangolin"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu precise (12.04.3 LTS)"
VERSION_ID="12.04"

MongoDB installation in Windows 7 64 bit OS

PART-I : MongoDB Introduction
PART-II : Mongo DB Installation in Windows -7 64 bit OS

PART-I : MongoDB Introduction

MongoDB is an open-source document database that provides high performance, high availability, and automatic scaling.  


It is a NoSQL database


MongoDB  structure in favor of JSON(JavaScript Object Notation - http://www.json.org/)-like documents with dynamic schemas (MongoDB calls the format BSON- http://bsonspec.org/). 

Learn MongoDB online here: http://try.mongodb.org/

Keywords we can exchange from SQL to NoSQL

RDBMS	MongoDB
Database	Database
Table	Collection
Tuple/Row	Document
column	Field
Table Join	Embedded Documents
Primary Key	Primary Key (Default key _id provided by mongodb itself)

 PART-II : MongoDB Installation in Windows 7 64 bit OS

1. Download MongoDB from here

2. Create a folder mongodb in C drive (i.e., C:\mongodb)

3. Extract(unzip) the downloaded zip file to this folder(i.e., C:\mongodb\mongodb-win32-x86_64-2008plus-2.4.8)

4. Create a folder data either in C drive or D drive(Which ever drive you want to). C drive is preferable.(i.e, C:\data)

5. Create a sub folder db in data folder. i.e, (C:\data\db)

6. Open command prompt in Admin mode.

7. Navigate up to bin  from the mongodb installed folder(i.e., C:\mongodb\mongodb-win32-x86_64-2008plus-2.4.8\bin).

8 . Issue mongod.exe command (i.e., C:\mongodb\mongodb-win32-x86_64-2008plus-2.4.8\bin\mongod.exe)

 9. Open another command prompt in Admin mode 

 Issue mongo.exe command

NOTE:

1) test is a database which is default for MongoDB

2) Default port number for MongoDB is :   

     27017: the default port accessed by the various MongoDB drivers. 

     28017: a port that handles HTTP requests and provides some general monitoring.

 

Reference :  

http://docs.mongodb.org/manual/core/introduction/

http://docs.mongodb.org/manual/tutorial/