Install Pentaho BI Server 5 Enterprise Edition with PostgreSQL repository


Reblog from:

Install Pentaho BI Server 5 Enterprise Edition with PostgreSQL repository.

Pentaho provides different ways to install Pentaho BI server. Each method has its own flexibility in installation.
1. Installer mode – Easy to install BA & DI server & tools in one flow with default PostgreSQL repo & default Tomcat server. (New Postgres installed on port 5432.)
2. Archive mode – BA server installed with own BA repository & default Tomcat server. Necessary tools need to be installed manually.
3. Manual mode – BA server installed with own BA repository & own application server (Tomcat or JBoss). Necessary tools need to installed manually.

We have a Postgres instance running on our server and are good with Tomcat as application server so Archive mode of installation is suitable for us. Pentaho installation requires two things be installed before starting with Pentaho installation.

  • Java 7
  • PostgreSQL

Archive mode installation files can be accessible only to license purchased users. Download biserver-ee-5.x-dist.zip from Pentaho customer portal with account credentials here: https://support.pentaho.com/forums/20413716-Downloads

Unzip the archive file and you can see the installation files inside extracted directory.

1
2
3
$ unzip biserver-ee-5.x-dist.zip
$ cd biserver-ee-5.x;ls
install.bat  installer.jar  install.sh  license.txt  README.txt

In remote servers Pentaho can be installed on console mode with ‘-console’. Accept the license and provide the installation path to install Pentaho BI server.

1
$ java -jar installer.jar -console

Find biserver-ee directory under the installation path and set sh files to executable mode.

1
2
$ cd biserver-ee;
$ find . -type f -iname '*.sh' -exec chmod a+x {} \;

Let’s create repository databases by running queries in SQL files located at biserver-ee/data/postgresql.
quartz, hibernate and jackrabbit databases will be created by executing these SQL files. Database names, usernames and password can be changed by modifying in SQL files if required.

1
2
3
4
5
$ cd biserver-ee/data/postgresql
$ psql -U postgres -p 5432 -a -f create_jcr_postgresql.sql
$ psql -U postgres -p 5432 -a -f create_quartz_postgresql.sql
$ psql -U postgres -p 5432 -a -f create_repository_postgresql.sql
$ psql -U postgres -p 5432 -a -f pentaho_mart_postgresql.sql

Pentaho uses postgresql as default database and files are configured to use postgresql. So just verify the database_name, username and password to work with installed postgresql and databases created.

– biserver-ee/pentaho-solutions/system/quartz/quartz.properties

1
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

– biserver-ee/pentaho-solutions/system/hibernate/hibernate-settings.xml

1
    <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

– biserver-ee/pentaho-solutions/system/hibernate/postgresql.hibernate.cfg.xml

1
2
3
4
5
    <property name="connection.driver_class">org.postgresql.Driver</property>
    <property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
    <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
    <property name="connection.username">pentaho_user</property>
    <property name="connection.password">password</property>

There are more occurrences in this file. Carefully do the necessary changes in all the places.
– biserver-ee/pentaho-solutions/system/jackrabbit/repository.xml
– biserver-ee/pentaho-solutions/system/jackrabbit/repository/workspaces/default/workspace.xml

– biserver-ee/tomcat/webapps/pentaho/META-INF/context.xml

1
2
3
4
5
<Resource name="jdbc/PDI_Operations_Mart" 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.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
            validationQuery="select 1"/>
1
Download postgresql and h2 drivers then place it under biserver-ee/tomcat/lib

postgresql-9.x.jdbc4.jar
h2-1.2.x.jar

Change Tomcat port on these two files to run Pentaho on different port.
Specify the Pentaho solutions path, server URL and port in web.xml of Tomcat webapp.

biserver-ee/tomcat/webapps/pentaho/WEB-INF/web.xml

1
2
3
4
5
6
7
8
9
        <context-param>
                <param-name>solution-path</param-name>
                <param-value>$INSTALLATION_PATH/biserver-ee/pentaho-solutions</param-value>
        </context-param>
        <context-param>
                <param-name>fully-qualified-server-url</param-name>
                <param-value>http://localhost:8080/pentaho/</param-value>
        </context-param>

Pentaho can be configured to run on different ports by changing ports on Tomcat server.xml and web.xml
– biserver-ee/tomcat/biserver-ee/tomcat/server.xml

1
2
3
    <Connector URIEncoding"UTF-8" port"9090" protocol"HTTP/1.1"
               connectionTimeout"20000"
               redirectPort"8443" />
1
2
3
4
5
- biserver-ee/tomcat/webapps/pentaho/WEB-INF/web.xml
         <context-param>
                <param-name>fully-qualified-server-url</param-name>
                <param-value>http://localhost:9090/pentaho/</param-value>
        </context-param>

Install license

A license needs to be installed to use Pentaho. Navigate to the license-installer directory in installation path. Feed license file to install_license.sh, separating more than one license file with spaces.

1
$ ./install_license.sh install ../license/Pentaho\ BI\ Platform\ Enterprise\ Edition.lic

Install plugins
Archive mode of installation installs only BI Server. Necessary plugins can to be installed manually. Here install the plugins for reporting, analyzer and dashboard. Plugins are available at the same place where download BI server. Download these three files and place at any path on server
Reporting – pir-plugin-ee-5.x-dist.zip
Analyzer – pdd-plugin-ee-5.0.0.1-dist.zip
Dashboard – paz-plugin-ee-5.0.0.1-dist.zip

All the plugins installed through same procedure
– Unzip the plugins and navigate to extracted directory
– run installer on console, accept the license and provide $INSTALLTION_PATH/biserver-ee/pentaho-solutions/system as location to install plugins

1
$ java -jar installer.jar -console

Lets start the BI server

1
biserver-ee$ ./start-pentaho.sh

Install the licenses for the plugins by login as admin user (default – Admin:password) or install through the command line
Administration -> License -> install licenses for plugin by click +

Troubleshooting:

1
2
biserver-ee$ tail -f tomcat/logs/catalina.out
biserver-ee$ tail -f tomcat/logs/pentaho.log

If the pentaho.xml is present at tomcat/conf/Catalina directory, delete it. It will be generated again when you start the BA Server.

Start and stop the BI server :

1
2
biserver-ee$ ./start-pentaho.sh
biserver-ee$ ./stop-pentaho.sh
Advertisements

Pentaho BI Server 5.0.1CE MySQL installation guide


How to install Pentaho BI Server 5.0.1 Community Edition with MySQL 5.x and Windows

Hi all this is David Fombella and I would like to share a way to deploy Pentaho Community last edition installing its security into a MySQL database server.

Help me keep the guides up to date and the posts flowing by donating, every small amount of money helps!

btn_donatecc_lg

Here is the download link to get the biserver 5.0.1 CE .

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip/download

Creation of Databases & Users

In our first stage we are going to create hibernate database and the user hibuser. To achieve this execute the SQL script  create_repository_mysql.sql included at biserver-ce\data\mysql5

SQL code

CREATE DATABASE IF NOT EXISTS `hibernate` DEFAULT CHARACTER SET latin1;
USE hibernate;
GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';
commit;

Next we create quartz database and the user pentaho_user. You only need to execute the SQL script  create_quartz_mysql.sql included at biserver-ce\data\mysql5

CREATE DATABASE IF NOT EXISTS `quartz` DEFAULT CHARACTER SET latin1;

grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';

USE `quartz`;

DROP TABLE IF EXISTS QRTZ5_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ5_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ5_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ5_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ5_LOCKS;
DROP TABLE IF EXISTS QRTZ5_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_TRIGGERS;
DROP TABLE IF EXISTS QRTZ5_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ5_CALENDARS;

CREATE TABLE QRTZ5_JOB_DETAILS
  (
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
    IS_DURABLE VARCHAR(1) NOT NULL,
    IS_VOLATILE VARCHAR(1) NOT NULL,
    IS_STATEFUL VARCHAR(1) NOT NULL,
    REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ5_JOB_LISTENERS
  (
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    JOB_LISTENER VARCHAR(200) NOT NULL,
    PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER),
    FOREIGN KEY (JOB_NAME,JOB_GROUP)
        REFERENCES QRTZ5_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ5_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    IS_VOLATILE VARCHAR(1) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    NEXT_FIRE_TIME BIGINT(13) NULL,
    PREV_FIRE_TIME BIGINT(13) NULL,
    PRIORITY INTEGER NULL,
    TRIGGER_STATE VARCHAR(16) NOT NULL,
    TRIGGER_TYPE VARCHAR(8) NOT NULL,
    START_TIME BIGINT(13) NOT NULL,
    END_TIME BIGINT(13) NULL,
    CALENDAR_NAME VARCHAR(200) NULL,
    MISFIRE_INSTR SMALLINT(2) NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (JOB_NAME,JOB_GROUP)
        REFERENCES QRTZ5_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ5_SIMPLE_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    REPEAT_COUNT BIGINT(7) NOT NULL,
    REPEAT_INTERVAL BIGINT(12) NOT NULL,
    TIMES_TRIGGERED BIGINT(10) NOT NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ5_CRON_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    CRON_EXPRESSION VARCHAR(200) NOT NULL,
    TIME_ZONE_ID VARCHAR(80),
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ5_BLOB_TRIGGERS
  (
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    BLOB_DATA BLOB NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ5_TRIGGER_LISTENERS
  (
    TRIGGER_NAME  VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    TRIGGER_LISTENER VARCHAR(200) NOT NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ5_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ5_CALENDARS
  (
    CALENDAR_NAME  VARCHAR(200) NOT NULL,
    CALENDAR BLOB NOT NULL,
    PRIMARY KEY (CALENDAR_NAME)
);

CREATE TABLE QRTZ5_PAUSED_TRIGGER_GRPS
  (
    TRIGGER_GROUP  VARCHAR(200) NOT NULL,
    PRIMARY KEY (TRIGGER_GROUP)
);

CREATE TABLE QRTZ5_FIRED_TRIGGERS
  (
    ENTRY_ID VARCHAR(95) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    IS_VOLATILE VARCHAR(1) NOT NULL,
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    FIRED_TIME BIGINT(13) NOT NULL,
    PRIORITY INTEGER NOT NULL,
    STATE VARCHAR(16) NOT NULL,
    JOB_NAME VARCHAR(200) NULL,
    JOB_GROUP VARCHAR(200) NULL,
    IS_STATEFUL VARCHAR(1) NULL,
    REQUESTS_RECOVERY VARCHAR(1) NULL,
    PRIMARY KEY (ENTRY_ID)
);

CREATE TABLE QRTZ5_SCHEDULER_STATE
  (
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
    CHECKIN_INTERVAL BIGINT(13) NOT NULL,
    PRIMARY KEY (INSTANCE_NAME)
);

CREATE TABLE QRTZ5_LOCKS
  (
    LOCK_NAME  VARCHAR(40) NOT NULL,
    PRIMARY KEY (LOCK_NAME)
);

INSERT INTO QRTZ5_LOCKS values('TRIGGER_ACCESS');
INSERT INTO QRTZ5_LOCKS values('JOB_ACCESS');
INSERT INTO QRTZ5_LOCKS values('CALENDAR_ACCESS');
INSERT INTO QRTZ5_LOCKS values('STATE_ACCESS');
INSERT INTO QRTZ5_LOCKS values('MISFIRE_ACCESS');
commit;

Finally we create jackrabbit database and the user jcr_user. You only need to execute the SQL script  create_jcr_mysql.sql included at biserver-ce\data\mysql5

CREATE DATABASE IF NOT EXISTS `jackrabbit` DEFAULT CHARACTER SET latin1;
grant all on jackrabbit.* to 'jcr_user'@'localhost' identified by 'password';
commit;

Configuring JDBC Security

This section describes how to configure the Pentaho BI Platform JDBC security to use a MySQL server, this means the Pentaho BI Platform will now point to the hibernate database on the MySQL server instead of the packaged HSQL in memory database.

1. applicationContext-spring-security-hibernate.properties.

Edit the file pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

Original 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
 

Make the changes necessary to get the snippet of code below

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
 

    2. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

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

Make the changes necessary to get the snippet of code below

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

3. mysql5.hibernate.cfg.xml

Edit the file pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml .

You do not need to make any changes to this file if you would like to use the default user hibuser. However, if you would like to specify your custom user, change connection.username and password  properties.

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

4. quartz.properties

Edit the file pentaho-solutions\system\quartz\quartz.properties .

Original code

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

Make the changes necessary to get the snippet of code below

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

Configuring Hibernate and Quartz

Hibernate and Quartz need to specifically use the hibernate and quartz databases which were created on the MySQL server. To do so modifications need to be executed in context.xml file .

5. context.xml

Edit the file tomcat\webapps\pentaho\META-INF\context.xml.

Remember deleting tomcat\conf\Catalina\localhost\pentaho.xml , Pentaho creates  on startup pentaho.xml as a copy of context.xml.

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

Make the changes necessary to get the snippet of code below

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

6. repository.xml

Edit the file pentaho-solutions\system\jackrabbit\repository.xml.

One of the new features of pentaho 5 is the use of Jackrabbit content repository.

http://jackrabbit.apache.org/repository-server-howto.html

jlogo

Comment the original code in the FileSystem part

<!--
Replace the following "FileSystem" XML node to use supported databases as
the repository file system.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<FileSystem>
<param name="path" value="${rep.home}/repository"/>
</FileSystem>
 

Make this code active on FileSystem part of the code

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

Comment the original code in the DataStore part

<!--
data store configuration
-->
<!--
Replace the following "DataStore" XML node to use supported databases as the data
store for the repository.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
 

Make this code active on DataStore part of the 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>

Below the security part comment the original code in the FileSystem Workspace part

<!--
virtual file system of the workspace:
class: FQN of class implementing the FileSystem interface
-->
<!--
Replace the following "FileSystem" XML node to use supported databases as
the repository file system.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<FileSystem>
<param name="path" value="${wsp.home}"/>
</FileSystem>
 

Make this code active on FileSystem WorkSpace part of the code

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

Below  FileSystem Workspace part you will find the PersistenceManager part

<!--
persistence manager of the workspace:
class: FQN of class implementing the PersistenceManager interface
-->
<!--
Replace the following "PersistenceManager" XML node to use a supported database as the
persistenanceManager store.  Change the url, user, password and parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->

<PersistenceManager>
<param name="url" value="jdbc:h2:${wsp.home}/db"/>
<param name="schemaObjectPrefix" value="${wsp.name}_"/>
</PersistenceManager>

 

Make this code active on PersistenceManager part of the code

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

Below you will find FileSystem Versioning part

<!--
Configures the filesystem to use for versioning for the respective
persistence manager
-->
<!--
Replace the following "FileSystem" XML node to use a supported database as
the repository file system.  Change the url, user, password and other parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<FileSystem>
<param name="path" value="${rep.home}/version" />
</FileSystem>
 

Make this code active on FileSystem Versioning part

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

Below you will find PersistenceManager Versioning part

<!--
Configures the persistence manager to be used for persisting version state.
Please note that the current versioning implementation is based on
a 'normal' persistence manager, but this could change in future
implementations.
-->
<!--
Replace the following "PersistenceManager" XML node to use a supported database as the
persistenanceManager store.  Change the url, user, password and parameters
to suit your db installation.  The schemaObjectPrefix should
be a unique prefix that will be prepended to the table names.
NOTE: The database must be pre-created in and match the parameters.  See Jackrabbit
documentation for further explanation.
-->
<PersistenceManager>
<param name="url" value="jdbc:h2:${rep.home}/version/db"/>
<param name="schemaObjectPrefix" value="version_"/>
</PersistenceManager>

Make this code active on PersistenceManager Versioning part

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

Quit HSQL Hypersonic automatic startup

By default  Hypersonic database starts up automatically – to avoid this  comment or delete locate the following snippets of code from web.xml:

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

Second  section you need to comment or eliminate

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

Sign into the user console

Using this URL http://localhost:8080/pentaho you can reach pentaho login screen:

login biserver5Once you are into the user console, you will see  Home perspective with several buttons:

  • Browse Files: Navigate through Public and Home root directories and Cut, Copy, Share, Download, Schedule all contents included in Pentaho ( PRPT reports,  sequence actions, jobs, transformations, olap views, saiku views —)
  • Create New: (JPivot View , Add components via Marketplace)
  • Manage DataSources: Create, Edit and Delete  JDBC datasources, Mondrian metadata sources  and Metadata.xmi reporting ad hoc datasources
  • Documentation: Documentation for Pentaho 5.0

PUC biserver5

One of the main changes of Pentaho 5 is the fact that there isn’t an external Administration Console and administration capabilities are included in the User Console. In this version our “ancient”  friend joe has been replaced with the admin user. Besides the new roles available are the following:

  • Administrator
  • Power User
  • Report Author
  • Business Analyst

You can access in the administration perspective in the user console using the dropdown and selecting Administration:

administration biserver5Then you will reach the administration perspective

administration roles biserver5

Pentaho 5 Restful Web Services


How are you friends today I reblog for us this amazing post including new Pentaho 5 features from : http://ivy-is.blogspot.co.uk/2013/11/pentaho-5-restful-web-services-101.html

With the latest release of Pentaho V5 comes some very cool new features. One that we were really interested in had to be the new Restful Webservices.

Lets take a look at some of the core Repository features.

  • Navigate Repository Contents
  • Manipulate Repository Contents
  • Edit User and Group Permissions

Manipulating the Pentaho Repository

A quick word about the syntax of solution directories. With the new Restful API, solution directories are broken up using the : character. So for example, if we wanted to render a report called sales.prpt in the steel-wheels/reports/sales-reports directory the url would look a little like this:

steel-wheels:reports:sales-reports:sales.prpt

LISTING REPOSITORY CONTENTS

There are two types of repository listing requests. One gets the list of files and folders (including child folders to a certain depth if specified) and the other returns properties of a certain file or folder.
We can start by listing all the files and folders in the public solution folder:
If we wanted to return the content of the public/cde directory we would execute:
By default the children request lists all files and folders including nested files and folders (not including hidden files and folders). Its possible to pass in some argument to filter our results

depth

the depth argument defines the depth of folders we wish to query. by default the depth is -1. This returns ALL folders. If we set it to 0 we get only the contents of the currect folder. if we set it to 1 or more we get the contents of the child folders to that depth

showHidden

By default hidden files are not returned. to show hidden files in the results we simple pass in the argument showHidden=true

filter

It is possible to filter if we want to return Files or Folders. We do this with the filter argument. For example, to filter only solution files we would add the following argument: filter=*|FILES. To list onlt folder we would do filter=*|FOLDERS

Getting Solution folder / file properties

If we want to get the details for a simple solution file or folder we can use the properties webservice. For example, to get the properties of a solution file called sales.prpt in the public/mysolution/reports folder we would do something like this

Creating new Folders

Lets assume we want to create a new folder in the public solution directory called myreports. To create the new folder we simple execute the following request

http://localhost:8080/pentaho/api/repo/dirs/:public:myreports

Deleting Solution Files and Folders

Lets assume we now want to delete the myreports folder in the repository. before we can delete a file or folder we need to get its ID. We can use the properties request method to get a file or folder id.Once we have the id of the file(s) or folder(s) we want to delete we can execute the delete and pass in the list of ID into the response header as a comma separated list

http://localhost:8080/pentaho/api/repo/files/delete

Listing File or Folder ACLs

We can get the permissions for a particular solution file or folder using the acl service. For example, if we wanted to get the permissions for the public folder we could execute the following request:
This will return the ACL descriptor for the public directory

Setting ACLs for Files or Folders Summary

If we want to set new permissions to a certain solution file or folder we can use the same service but pass in a response header with the new ACL definition using PUT.

Web Services to interact with Mondrian

Here are the webservice calls for getting a Mondrian schema file into the repository and out of the repository. Note at this time you need to have admin permissions to perform these actions, but in future releases we’ll be able to control which users can import and export schemas.

http://localhost:8080/pentaho/plugin/data-access/api/mondrian/postAnalysis
Multipart POST request with post params:
uploadAnalysis – Mondrian schema
catalogName – name of the catalog (optional?)
parameters – parameter list

http://localhost:8080/pentaho/plugin/data-access/api/datasource/SteelWheels/getAnalysisDatasourceInfo
GET request to get the parameters of a Mondrian schema definition
SteelWheels should be the name attribute that appears in the Mondrian.xml schema

http://localhost:8080/pentaho/plugin/data-access/api/datasource/analysis/SteelWheels/download
GET request to download the Mondrian xml schema

Most of the datasource UIs call REST services for these behaviours, you can look at the http traffic in Chrome or Firefox to determine the API calls for other datasource types like Pentaho Metadata.

Summary

So this is just a quick overview of some of the Pentaho BI Server Restful Web Services but it should be enough to get you started with integrating Pentaho into your own applications
Help me keep the guides up to date and the posts flowing by donating, every small amount of money helps!
btn_donatecc_lg