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.

10 thoughts on “Develop multidimensional analysis reports using Jpalo engine in SpagoBI

  1. hello Rakesh nice of meet you and congratulations for your enhanced practice at SpagoBi, we are an consulting group at Mexico City developing opensource project based on Spago.
    we are working on BI-Cube configuration and their dimensions views for multidimensional report.
    we are following your posted articule for developing multidimensional analysis reports using Jpalo engine we have a specif problem for know how we can generate the MBX views; so, really we dont know if any step of the cube configuration process are in fault or if any process of dimension charge are in fault, did you have some full exercise or example can you share or provide us in order of track and check our case?, let us know how you can assist us on scripts and configuration revew , thanks in advance

  2. Thanks Hector.

    I do have example but it is for our company’s project and I won’t be able to share it with you. It would be better if you could describe your issue properly so that we can discuss it further.

    Is it related to dimension tables, developing mondrian schema, spagobi server configurations, or what? What is the error you are getting?

    Regards,
    Rakesh

  3. txs Rakesh very appreciated, let us arm a detailed description of config and scripts for send you asap one hour , can you tell me an e-mail for send this descriptions?

  4. Hi Rakesh;
    my cube olap ans mdx work fine, i saved the file .xml in

    Administrator\Desktop\projet\all-in-one\SpagoBI-DEMO-3.6.0\resources\Olap
    and in workbench schema

    in the step “Create Document in SpagoBI and run the report” .d.
    when i try him she just displayed me Foodmart view;

    Can you resoleve me this problem.

  5. I think, you didn’t follow the section “Configurations” above, you would need to configure your data source and other settings.

    Regards,
    Rakesh

  6. Hello Rakesh

    First of all thank you for such a precise description of JPalo configuration.
    I successfully configured it , following your post.

    I need your help on following :
    =============================
    I want to create jasper reports using this view/cube creted by JPalo engine.

    I can successfully generate reports in ireport by using database type XMLA server.

    But when i upload tyhe .jrxml/.jasper file to SpagoBI , it shows blank report.

    ( According to me , I need to configure datasource in SpagoBI as well to the XMLA server. )

    Can you please help me on it in anyway ASAP.

  7. Hai rakesh

    I created analytical document and saved in a folder…then I click “template build icon” Jpalo document opened…and I create a cube..drag and drop dimensions and saved….

    But when I executed cube it gives following error “You are trying to execute document with no template or view defined.”

    I dont know where the problem is exactly…..

    so please help me to resolve this error….its very urgent for me…

    Thanks in Advance….

Leave a Reply

Your email address will not be published. Required fields are marked *


4 × = twenty four

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>