Develop multidimensional analysis reports using Jpalo engine in SpagoBI

Introduction

SpagoBI allows multidimensional analysis through OLAP engines, i.e. JPalo which are more flexible and user-friendly, compared to structured reports.

The users can monitor data on different detail levels and from different perspectives, through drill-down, drill-across, slice-and-dice, drill-through processes.

Configurations
The Jpalo engine comes configured with latest version of SpagoBI i.e. v3.4, but we would need to go through data source and related configurations. Please follow the step below to configure it properly:

  1. Mondrian Configuration
    a. In order to configure Mondrian to use a datasource as a JNDI resource, define resource in server.xml.b. Then configure it in SpagoBIJPaloEngine/META-INF/context.xml.c. Suppose your JNDI datasource resource is named “jdbc/foodmartmondrian”, then you need to configure DataSourceInfo tag in SpagoBIJPaloEngine/WEB-INF/datasources.xml
    to use the correct JNDI resource.

    <DataSourceInfo>Provider=mondrian;DataSource=java:comp/env/jdbc/foodmartmondrian;</DataSourceInfo>

    Use the following in case of jdbc (mysql) resource
    <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:mysql://localhost/<dbname>?user=root&password=<password>;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>

    d. Remember that you can even put multiple catalogs and datasources under Mondrian control, by setting them in datasources.xml file.
    e. Further you have to place database schema FoodMart.xml (or the one you have prepared with all cubes defined), under SpagoBI resources folder.

    f. To be accessed by Mondrian server, change Definition tag in SpagoBIJPaloEngine/WEB-INF/datasources.xml.

    <Definition>file:./../resources/Olap/foodmart.xml</Definition> 

  2. Palo Pivot Configuration
    a. Change settings in SpagoBIJPaloEngine/WEB-INF/classes/deploy_it.properties. By default JPalo uses Hsql db to store its data, but it could eventually use Mysql. You can specify it by setting use.mysql=true otherwise you can set use.mysql=false. By default JPalo uses http protocol to connect to Mondrian. If you want to change protocol to https set is.ssl=true otherwise set is.ssl=false.More customization can be done:

    • jpalo.admin.user=[jpalo_username]
    • jpalo.admin.password=[jpalo_password]
    • jpalo.mondrian.connection.url=[mondrian_host]:[mondrian_port]
    • jpalo.mondrian.connection.service=SpagoBIJPaloEngine/xmla
    • jpalo.mondrian.connection.name=[name_of_mondrian_connection]

    b. Once you have decided what kind of database JPalo is going to use to store views and connections, you need to edit the proper settings.

    Case JPalo uses HSQL DB, change Hsql db settings in SpagoBIJPaloEngine/WEB-INF/classes/sql/hsql/credentials

    • jdbcDriver = org.hsqldb.jdbcDriver
    • jdbcURL = jdbc:hsqldb:file:[absolute_path_to_your_hsql_files]
    • jdbcPort = [port]
    • userName = [username]
    • userPassword = [password]

    Case JPalo uses Mysql DB, change Mysql db settings in SpagoBIJPaloEngineWEB-INFclassessqlmysqlcredentials

    • jdbcDriver =com.mysql.jdbc.Driver
    • userPassword = [password]
    • userName =[username]
    • databaseName =[schema_name]
    • jdbcURL =jdbc:mysql://[database_host]:[port]
  3. Please make sure the engine instance is created in SpagoBI server. Go to Resources->Engines Management and define/check SpagoBIJPaloEngine:
    • Document type : On-line analytical processing
    • Url : [protocol]://[host]:[port]/SpagoBIJPaloEngine/com.tensegrity.wpalo.SpagoBIJPaloEngine/JPaloEngineStartServlet
    • Driver : it.eng.spagobi.engines.drivers.jpalo.JPaloDriver

How to create Modrian Schema?
This is a  .xml file where you define your olap cubes. The Jpalo engine read it and process it’s information to show the multidimensional reports.

There is an open source tool named “Schema-Workbench” where you can design your multidimensional cubes and save it as .xml file. As per above instruction, we store this file inside resource path of SpagoBI server.

We can download it from http://sourceforge.net/projects/mondrian/files/schema%20workbench/

Other links for schema-workbench and modrian:

http://mondrian.pentaho.com/documentation/schema_workbench.pdf
https://docs.google.com/a/paxcel.net/gview?url=http://jasperserver.sourceforge.net/docs/3-7-0/Mondrian-3.0-Technical-Guide.pdf&chrome=true

You can work on other tools in the market also to develop mondrian schema.

Create Document in SpagoBI and run the report

a. Create new On-line analytical processing Document in spagoBI, using SpagoBIJPaloEngine. Go to “Analytical model->Documents development” and click on the Insert icon from the top left and fill the following information:

Type: on-line analytical processing
Engine: JPalo Engine
State: Development

e.g.
http://paxcel.net/blog/wp-content/uploads/2012/05/document.jpg

b. Save the document, and click on “Template build” button to create the view and execute the document just created.

c. In the next screen, click on the “Create new views” button from the left navigation area,

d. Choose the account, select the mondrian cube and click on Create button.

e. The view is created, now drag and drop the dimensions and save your view.

Once it is saved, it can be excuted directly from “Analytical Model->Documents development” page.

This is a detailed document which will help you to build Jpalo document with SpagoBI, please let me know in case you face any issue so that I can try to help you there. You can reach me through your comments.

Comments are closed.