Business Intelligence Integration

This document is still a work in progress. It may contain inaccuracies or errors.

Introduction

This document describes the integration of Openbravo ERP with a Pentaho Business Intelligence (BI) server. It will explain how to set up both Openbravo ERP and the BI server and a description of the included business intelligence analysis. The chosen BI server is Pentaho. Both systems, Openbravo ERP and Pentaho, run independently, even in separate servers if necessary, but you can view business intelligence information from Pentaho in dashboards embedded into Openbravo ERP.

Security Implementation

To enable Openbravo ERP and the Pentaho BI Server to communicate securely with each other, the following setup processes are required.

• The integration of the login process.
• The data model access.

Login and Single Sign On (SSO)

It is recommended that you set up a single sign on system when using Pentaho BI with Openbravo ERP.

SSO is more secure because it ensures that both applications are using the same user authentication. To enable SSO in Pentaho BI, you must be running the Enterprise Edition. If you are using the Community Edition, you cannot enable SSO, but should instead configure its ACEGI to authenticate against Openbravo ERP's user database.

Openbravo ERP already has a login handler for LAM, so we use it in companion of a CAS server. This CAS server has to be configured to authenticate against Openbravo ERP's user database. Openbravo ERP's passwords are hashed using SHA1 and coded in the database using base64. CAS does not handle this algorithm so it is necessary to build a custom handler. See the CAS and LAM configuration with Openbravo's users document for a detailed description of the configuration.

You can find information about how to configure the SSO in the Enterprise Edition in Pentaho's website.

Data model access

An important issue related to the data model is what data can be viewed by the authenticated users. Security constrains are included at the data level, for the accessed information of Openbravo users and roles by the Pentaho solutions.

The main concept is that all the facts belong to one organization and one client. The general schema of a Pentaho solution with a SQL query is to use the session parameter related with the user role, for including this role into the query. With the MDX querys, the cube definition have security constrains, and no parametrization is needed. Anyway, you have to include the session parameter (role), to the cube within the connection info.

Configuration

In this section all the needed actions to configure both Openbravo ERP and Pentaho to work with the available Dashboards and ETLs are defined. It is considered that there are already installed both Openbravo ERP and Pentaho.

For information about the installation of Openbravo ERP see the Openbravo_ERP_installation document on this wiki. For information about the installation of Pentaho plese refer to Pentaho's wiki. Specially the Getting Started With the BI Platform and Manual Deployment of Pentaho documents. Notice that the Pentaho version supported in this guide is the 1.7.1.

Downloading the sources

The necessary sources can be downloaded as a package from the downloads area of Openbravo's website or directly the sources from the subversion repository. The sources contain 4 folders: ETLs, pentaho-solutions, jsp and role-kettle-plugin.

The dashboards that are included in the package are:

• Revenue and Expenses analysis

The ETLs that are included in the package are:

• Security
• Financial cube

Openbravo ERP configuration

The dashboards included in the package are already defined in the application dictionary. It's necessary to reactivate them to be available from the application's menu in the Report and Process window.

It's also necessary to set the URL where the Pentaho server is deployed. To do so, configure the pentahoServer property in the Openbravo.properties file.

Pentaho configuration

Prerequisites:

• Postgresql 8.1 database or later, or Oracle 10g database.
• Tomcat servlets container or Jboss application server.
• Pentaho BI Suite 1.7 or later:
  • pentaho_src-1.7.1.GA.zip
  • pentaho_solutions-1.7.1.GA.zip
  • pentaho_j2ee_deployments-1.7.1.GA.zip

http://downloads.sourceforge.net/pentaho/

Some steps are necessary for the Pentaho configuration of solutions and dashboards:

Create knowledge database

Create the star structure of the knowledge database with the provided sql scripts in the sql directory. For executing the different sql scripts init_ob_etl_db.sql and create_ob*.sql please read README.txt

Execute ETL

For executing the ETL's process is necessary to download Data Integration Programs for the BI Suite (spoon application).

http://downloads.sourceforge.net/pentaho/pdi-open-3.0.4.zip?modtime=1214584463&big_mirror=0

The ETL transformation and job files (ktr and kbj files) are executed in the spoon application:

• Temporal dimension data: Execute ob-time-reference-normalized.ktr with spoon application

• The other dimensions data is executed with ob-dimensions-normalized.ktr

• Roles for mondrian: Copy ob-mondrian-role-bin plugin (role kettle plugin) to pdi/plugins/steps/ and reboot spoon. Execute ob-mondrian-roles-job.kjb job

• Facts data table: Execute ob-fact-accts-job.kjb job

The generated data is placed in the table ob_fact_acct_normalizada of the ob_etl_db database.

Deploy solutions

The next steps are necessary to deploy the solutions:

• Creation of pool connections: It is neccesary to publish the data sources in the jndi tree of the solution. This depends on the application server. For example, for tomcat server edit the conf/server.xml and add a resource in the context of Pentaho application. The resource includes de jndi name, the user, password and url of the database. The jndi name of the resource is used in the solution xactions files. Then edit the web.xml for declarating the corresponding resource reference.

• Deploying the solutions: Just copy the openbravo folder from the solution repository or the downloaded pentaho-solutions/openbravo folder to pentaho-solutions/ folder. Each solution include an xaction file, a properties file and a mondrian schema xml file.

• Copy the jsp files of the dashboards and in webapps/pentaho/jsp and declare them in the web.xml

• Copy the modified Pivot.jsp in webapps/pentaho/jsp

• Refresh the repository solutions in the admin section of Pentaho server if necessary.

This is an example of resource definition in conf/server.xml for a postgresql database (driverClassName and url depends on database type):

<Resource name="jdbc/openbravoData" 
          auth="Container" type="javax.sql.DataSource" 
          maxActive="20" maxIdle="5" maxWait="10000" 
          username="ob_etl_user" password="password"
          factory="org.apache.commons.dbcp.BasicDataSourceFactory"
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://openbravo.mydomain.net:5432/ob_etl_db"/>

and the corresponding reference in web.xml

<resource-ref>
 <description>SampleData Connection</description>
 <res-ref-name>jdbc/openbravoData</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>

Schedule load data tasks

Data Integration Programs for the BI Suite contains kitchen script, that is used to execute job scheme in .kjb xml file form or from a repository. The first thing is to add the custom plugin to the $HOME/.kettle/plugin/steps/ and then test the tool.

Then execute from $KETTLE_HOME this command:

$KETTLE_HOME/kitchen.sh -file="PATH_TO_JOBS/ETLs/jobs/ob-mondrian-roles-job.kjb" -level=Rowlevel

http://wiki.pentaho.com/display/EAI/Kitchen+User+Documentation

You can try all the jobs and if success, you can schedule these tasks. To do this, create the scripts (.sh or .bat) that execute the previous command and fix the environment vars and add the task to the cron or windows scheduler. See the Pentaho manual to do this:

http://wiki.pentaho.com/display/EAI/Kitchen+User+Documentation#KitchenUserDocumentation-Scheduling

Dashboards

A dashboard is a set of deployed solutions in Pentaho BI server and coordinated for an agile management of the information data. The concept of Pentaho solution is the implementation of a solution for a business problem and it's composed of a functional-logical part (.xaction) and a visual part (widget.xml). The xaction defines the sequence and interaction between different actions (database query, message generation, log message, chart generation) and it corresponds to the most execution unit of Pentaho solutions engine.

For more information about Pentaho terminology:

http://wiki.pentaho.com/display/PentahoDoc/01.+Terminology

The dashboards included in this integration are jsp pages that serve Pentaho for the coordinations of the different solutions. These pages are built following the design of Openbravo ERP and can have several Pentaho actions to analyze the desired information. It is also possible to include links to jPivot cubes for multidimensional analysis.

Revenues and expenses dashboard

This dashboard shows the financial result of the enterprise in the given period of time. You can also compare the result to other period and view graphically the evolution of the last periods. It is possible to navigate to the financial cube to make more complex multidimensional analysis.

Revenue and Expenses analysis

ETLs

An ETL process consists of extract, transform and load data from different origins, in a convenience manner for the subsequent analysis.

Security

This ETL copies to the declarative mondrian schema all the data needed to enable the data access security in the dashboards. The copied data includes users, roles, organizations, clients and access levels.

Financial

This ETL copies all the financial data to the schema. This includes all the accounting entries difined in the Fact_Acct table and the corresponding description for the considered dimensions.