A newer version is available at https://doc.cuba-platform.com/bi-latest.

Preface

This manual describes the Business Intelligence integration add-on which is designed to integrate CUBA applications with Pentaho Platform.

We assume that the reader is familiar with the CUBA Platform Developer’s Manual, which is available at https://www.cuba-platform.com/manual.

If you have any suggestions for improving this manual, feel free to report issues in the source repository on GitHub. If you see a spelling or wording mistake, a bug or inconsistency, don’t hesitate to fork the repo and fix it. Thank you!

1. Overview

The Add-on provides the following functionality:

  • Generic screens for registering and running Pentaho reports. The access to registered reports can be restricted using CUBA security roles.

  • A visual component for embedding Pentaho reports to any application screen.

  • Single Sign-On between your CUBA application and Pentaho. It works as follows:

    • Users should have the same login names in the CUBA application and Pentaho.

    • When a user opens a report, the application generates a ticket for the user and pins username and the ticket to the Pentaho request. The ticket is stored in the user session.

    • CUBA plugin on the Pentaho server detects the ticket coming with the request and calls back the application to check if the ticket is valid.

    • If the ticket is valid, the plugin authorizes the user on the Pentaho server with roles that configured for the user in Pentaho.

2. Setting Up Pentaho

  1. Download and install Pentaho Community Edition.

  2. Download Saiku Analytics plugin (Meteorite BI) from http://www.pentaho.com/marketplace/ and install it.

  3. Move the saiku folder to the $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system directory, where $PENTAHO_HOME is the directory where Pentaho is installed.

  4. The Saiku plugin doesn’t work with the latest version of Pentaho Server. Do the following to fix the problem:

    • Remove the cpf-core-6.0.0.0-353.jar and cpf-pentaho5-6.0.0.0-353.jar libraries from $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system/saiku/lib folder.

    • Copy the newer versions of these libraries from any other folder of your Pentaho distribution (for example, $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system/sparkl/lib or …​/cgg/lib) to $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system/saiku/lib folder:

      cpf-core-7.1.0.0-12.jar
      
      cpf-pentaho-7.1.0.0-12.jar
      
      cpk-core-7.1.0.0-12.jar
      
      cpk-pentaho5-7.1.0.0-12.jar
      Tip

      The cpk-core…​ and cpk-pentaho…​ libraries may not exist in your Pentaho version. In this case, use only the cpf-core and cpf-pentaho libraries.

  5. Access the http://licensing.meteorite.bi and sign up for a new account. Once you have validated your account, create a new company and generate the community license for it:

    • Login to the system and click the CREATE NEW LICENSE button.

    • On the new license page, set the license type to COMMUNITY_EDITION.

    • Save and download the license. Rename the file to license.lic and then copy it to the $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system/saiku

  6. Download and install Pentaho Data Integration tool.

  7. Change Pentaho default port to 18081:

    • Navigate to $PENTAHO_HOME/pentaho-server/tomcat/conf.

    • Change default Tomcat port to 18081 in the server.xml:

    <Connector URIEncoding="UTF-8" port="18081" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="18443" />
  8. Change the Tomcat shutdown port to 8015 in the server.xml to avoid collision with CUBA default shutdown port:

    <Server port="8015" shutdown="SHUTDOWN">
        ...
    </Server>
  9. Setup authentication of CUBA users in Pentaho:

    • Download cuba-bi-pentaho-1.5.0.jar and copy it to $PENTAHO_HOME$/pentaho-server/tomcat/webapps/pentaho/WEB-INF/lib.

    • Download cuba-bi-pentaho-1.5.0-plugin.zip, unpack it and copy the saiku-cuba-bi folder to $PENTAHO_HOME/pentaho-server/pentaho-solutions/system.

    • Create new cuba-pentaho-community-authentication.xml file in the $PENTAHO_HOME/pentaho-server/pentaho-solutions/system folder with the following content:

      <?xml version="1.0" encoding="UTF-8"?>
      
      <!--+ | Application context containing FilterChainProxy. +-->
      <beans xmlns="http://www.springframework.org/schema/beans"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns:util="http://www.springframework.org/schema/util"
             xmlns:sec="http://www.springframework.org/schema/security"
             xmlns:pen="http://www.pentaho.com/schema/pentaho-system"
             xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-4.1.xsd http://www.pentaho.com/schema/pentaho-system http://www.pentaho.com/schema/pentaho-system.xsd"
             default-lazy-init="true">
      
          <!-- ======================== FILTER CHAIN ======================= -->
          <!-- if you wish to use channel security, add "channelProcessingFilter," in front of "httpSessionContextIntegrationFilter" in the list below -->
          <bean id="filterChainProxy" class="org.springframework.security.web.FilterChainProxy">
              <constructor-arg>
                  <util:list>
                      <sec:filter-chain pattern="/webservices/**" filters="securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,cubaAuthenticationFilter,basicProcessingFilter,anonymousProcessingFilter,sessionMgmtFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS" />
                      <sec:filter-chain pattern="/api/repos/**" filters="securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,cubaAuthenticationFilter,basicProcessingFilter,requestParameterProcessingFilter,anonymousProcessingFilter,sessionMgmtFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS,preFlightFilter" />
                      <sec:filter-chain pattern="/api/**" filters="securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,cubaAuthenticationFilter,basicProcessingFilter,requestParameterProcessingFilter,anonymousProcessingFilter,sessionMgmtFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS" />
                      <sec:filter-chain pattern="/plugin/reporting/api/jobs/**" filters="securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,basicProcessingFilter,requestParameterProcessingFilter,anonymousProcessingFilter,sessionMgmtFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS,preFlightFilter" />
                      <sec:filter-chain pattern="/plugin/**" filters="securityContextHolderAwareRequestFilterForWS,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,basicProcessingFilter,requestParameterProcessingFilter,anonymousProcessingFilter,sessionMgmtFilter,exceptionTranslationFilterForWS,filterInvocationInterceptorForWS" />
                      <sec:filter-chain pattern="/**" filters="securityContextHolderAwareRequestFilter,httpSessionPentahoSessionContextIntegrationFilter,httpSessionContextIntegrationFilter,httpSessionReuseDetectionFilter,logoutFilter,authenticationProcessingFilter,cubaAuthenticationFilter,basicProcessingFilter,requestParameterProcessingFilter,anonymousProcessingFilter,sessionMgmtFilter,exceptionTranslationFilter,filterInvocationInterceptor" />
                  </util:list>
              </constructor-arg>
          </bean>
      
          <bean id="cubaAuthenticationProvider" class="com.haulmont.addon.bi.pentaho.CubaAuthenticationProvider"/>
      
          <!-- ======================== AUTHENTICATION ======================= -->
          <bean id="authenticationManager" class="org.springframework.security.authentication.ProviderManager">
              <constructor-arg>
                  <util:list>
                      <ref bean="cubaAuthenticationProvider"/>
                      <pen:bean class="org.springframework.security.authentication.AuthenticationProvider"/>
                      <ref bean="anonymousAuthenticationProvider" />
                  </util:list>
              </constructor-arg>
              <property name="eraseCredentialsAfterAuthentication" value="false" />
              <property name="authenticationEventPublisher">
                  <ref bean="defaultAuthenticationEventPublisher" />
              </property>
          </bean>
      
          <bean id="cubaAuthenticationFilter" class="com.haulmont.addon.bi.pentaho.CubaPentahoAuthenticationFilter">
              <property name="userRoleDao">
                  <ref bean="userRoleDaoTxn" />
              </property>
              <property name="authenticationManager">
                  <ref bean="authenticationManager" />
              </property>
              <property name="extraRoles" ref="extraRoles" />
              <property name="cubaConnectionUrl" value="http://localhost:8080/app"/>
          </bean>
      </beans>
      Tip

      Specify URL of your CUBA application in the cubaConnectionUrl property of the cubaAuthenticationFilter bean:

      <property name="cubaConnectionUrl" value="http://localhost:8080/app"/>
    • Edit pentaho-spring-beans.xml in pentaho-solutions/system and add the line <import resource="cuba-pentaho-community-authentication.xml" /> after the <import resource="applicationContext-spring-security.xml" /> line:

      <import resource="applicationContext-spring-security.xml" />
      <import resource="cuba-pentaho-community-authentication.xml" />
  10. Start Pentaho Server:

    • Navigate to the $PENTAHO_HOME/pentaho-server directory.

    • Run start-pentaho.bat

3. Quick Start

We have created a small project to demonstrate how to enrich your CUBA application with BI reports. This demo application for customers and orders management contains an embedded Saiku report running on Pentaho server.

3.1. Setting Up Sample CUBA Application

  1. Clone or download the sample project from https://github.com/cuba-platform/cuba-bi-demo.git

  2. Open the project in an IDE or in CUBA Studio, run the createDb command and start the application server.

3.2. Loading Data to Star Schema

In our Pentaho report we will use aggregated data from multiple database tables. This data will be stored in additional tables and then loaded to the Star Schema. In our case, the Star Schema will consist of one fact table (Orders) and two dimension tables (Customer and Product), providing the ability to drill into the report hierarchy.

3.2.1. Loading Data For the Impatient

You can use the ready star schema delivered with the demo project.

  1. Make sure you have the demo project opened in Studio . The import procedure explained below will access the project’s HSQL database.

  2. Run Pentaho Data Integration tool:

    • Navigate to the directory where Pentaho Data Integration is installed.

    • Run spoon.bat.

  3. Open $BI_DEMO_PROJECT/demo/kettle/bidemo.kjb, where $BI_DEMO_PROJECT is the directory where the demo project is located.

  4. Click Run to update the Star Schema.

    bi star schema
    Figure 1. Star Schema

Now you can proceed to creating the analysis report.

3.2.2. Create Database Connection

If you want to create the star schema yourself, follow the steps below. For the detailed instructions consult the Pentaho wiki.

  1. Start Pentaho Data Integration using spoon.bat from $PENTAHO_HOME$/design-tools/data-integration.

  2. Create new transformation.

  3. Create new database connection for the transformation:

    • Enter the Connection Name

    • Connection Type: Hypersonic

    • Access: Native (JDBC)

    • Host Name: localhost

    • Database Name: bidemo

    • Port Number: 19001

    • User Name: sa

    • Leave the Password field empty

    star schema
    Figure 2. Database connection

3.2.3. Create Dimensions

For the dimensions, we will use Products and Customers. Each product refers to some product line, that is the type of product, e.g. Ford T belongs to the Vintage Cars product line.

Customers belong to certain cities, they, in turn, refer to some countries, the countries are grouped into territories.

  1. First, create the Product transformation. Drag and drop the Table input node onto the worksheet and define the fields we need for the report: product id, name and product_line_id.

    star schema 2
    Figure 3. Product table input
  2. Then create an Insert/Update node for products:

    star schema 3
    Figure 4. Insert/Update Product
  3. Create the transformation for product lines:

    star schema 4
    Figure 5. Product transformation
  4. Finalize the first transformation with the Update node:

    star schema 5
    Figure 6. Update Product
  5. Create the Customer transformation in the same way, including City and Territory levels, and add it to the Product one:

    star schema 6
    Figure 7. Customer transformation
  6. When the transformation is ready, wrap it in the corresponding job, using the START and Success nodes and the Abort job exit node in case of an error:

    star schema 12
    Figure 8. Dimensions job

3.2.4. Create Facts

For the Facts measure we will take the Orders and Order Lines.

  1. First, create the Order Line transformation. Drag and drop the Table input node onto the worksheet and define the fields we need for the report: id, product_id, quantity and order_id:

    star schema 7
    Figure 9. OrderLine table input
  2. Then create an Insert/Update node for order lines:

    star schema 8
    Figure 10. Insert/Update OrderLine
  3. Create the transformation for orders:

    star schema 9
    Figure 11. Order transformation
  4. Finally, update the customers IDs in the table:

    star schema 10
    Figure 12. Update customers
  5. The Facts transformation is now ready:

    star schema 11
    Figure 13. Facts transformation
  6. Wrap the transformation in the corresponding job:

    star schema 13
    Figure 14. Facts job

3.2.5. Create Star Schema

Now let’s assemble the dimensions and facts jobs into the complete star schema:

  1. Add the START node to start the job.

  2. Start the job with the Check Db connections condition.

  3. In case the DB is not connected, add the Abort job node for the job.

  4. Then add consequently Update Dimensions and Update Facts jobs that we have designed earlier.

  5. Finalize the job with the Success node and run the job:

    star schema 14
    Figure 15. Star Schema job
  6. Save all the job and transformation files in the project folder for further use.

3.3. Configure Pentaho Analysis Report

  1. Open Pentaho console: http://localhost:18081/pentaho and login as Admin/password.

  2. Click File → Manage Data Sources.

  3. Click the settings button and select New Connection:

    bi pentaho
    Figure 16. Database connection
  4. Create connection to HSQLDB:

    • Host Name: localhost

    • Database Name: bidemo

    • Port Number: 19001

    • User Name: sa

    bi pentaho 2
    Figure 17. Database connection

Now you can either use the analysis report delivered with the demo project or create it yourself following the steps below.

3.3.1. Using Demo Report

Below is the easiest way to see what the Saiku report looks like, you only have to import ZIP-files with the analysis and the report structure.

  1. Click Import Analysis.

  2. Select BIDemo Data Source and import $BI_DEMO_PROJECT/demo/pentaho/BiDemo.zip Mondrian File. The report structure will be imported.

    bi pentaho 3
    Figure 18. Import analysis
  3. Click New → Saiku Analytics → Create a new query. Select BiDemo cube and fill measure, columns, and rows as on the screen:

    bi pentaho 5
    Figure 19. Create query
  4. Save report in the directory /home/admin with the name ProductsByTypeAndLocation.

Now you can open the Saiku report in the CUBA application.

3.3.2. Create Data Source and Analysis Report Manually

Create Data Source
  1. Click New Data Source.

  2. Select the Source Type: Database Table(s).

  3. Select the new BIDemo connection in the list of available connections.

  4. Select Reporting and Analysis as the aim of this data source.

    pentaho console
    Figure 20. Data source wizard
  5. Select the dimensions and the fact tables we have created in Spoon: "PENTAHO_DIM_CUSTOMER", "PENTAHO_DIM_PRODUCT", "PENTAHO_FACT_ORDER_LINE":

    pentaho console 2
    Figure 21. Data source wizard
  6. Define Joins for selected tables:

    pentaho console 3
    Figure 22. Data source wizard
  7. Customize the dimensions hierarchy:

    pentaho console 5
    Figure 23. Data source model editor
  8. Save the data source. Select it in the list of available datasources and export the created analysis for the further use:

    pentaho console 4
    Figure 24. Export data source
Create Analysis Report
  1. Click New → Saiku Analytics → Create a new query. Select BiDemo cube and fill measure, columns, and rows as on the screen:

    bi pentaho 5
    Figure 25. Create query
  2. Save report in the directory /home/admin with the name ProductsByTypeAndLocation.

Now you can open the Pentaho report in the CUBA application.

3.4. Open BI Widget in CUBA Sample Application

  1. Go to http://localhost:8080/app

  2. Open Shop → BI Saiku main menu item:

saiku
Figure 26. BI Saiku screen

4. Adding BI to Your Application

  1. Install the add-on in your project.

    • Double-click Add-ons in the CUBA project tree.

      addons
      Figure 27. Add-ons in CUBA project tree
    • Select Marketplace tab and find Business Intelligence add-on.

      bi addon
      Figure 28. Marketplace window
    • Click Install button and then Apply & Close.

      addon install
      Figure 29. Apply & Close button
    • Click Continue in the dialog.

      addon continue
      Figure 30. Confirm dialog

      The add-on corresponding to the used platform version will be installed.

  2. Add cuba.web.mainTabSheetMode property to your web-app.properties file in order to keep report content while switching application tabs:

    cuba.web.mainTabSheetMode = MANAGED
  3. Register your BI report in the BI > BI Reports screen provided by the add-on. Click Create and set the path to your Pentaho report:

    bi create
    Figure 31. BI Reports screen

    The list of roles allows you to make the report available to certain users only. If the list is empty, the report is available to all.

    Now users can run the report in the BI > Run BI Reports screen.

  4. Another option is to embed BI reports to application screens using the biComponent visual component provided by the add-on. For example:

    <cubabi:biComponent id="biComponent"
                        height="100%"
                        reportPath=":home:admin:ProductsByTypeAndLocation.saiku"
                        width="100%"/>

    The reportPath attribute is the path to the report file in Pentaho User Console. Also, add the cubabi namespace to the xmlns attribute of the window element:

    xmlns:cubabi="http://schemas.company.com/cubabi/0.1/ui-component.xsd"
. . .