CUBA Platform. BI Tools Integration

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, please contact Technical Support at http://www.cuba-platform.com/support. When reporting errors in the documentation, please indicate the chapter and surrounding text to point the error.

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

Download and install Pentaho Community Edition from https://community.hds.com/community/products-and-solutions/pentaho/:
- Downloads → Main Downloads → Business Analytics Platform
Download Saiku Analytics plugin (Meteorite BI) from http://www.pentaho.com/marketplace/ and install it.
Move the saiku folder to the $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system directory, where $PENTAHO_HOME is the directory where Pentaho is installed.
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 following libraries from $PENTAHO_HOME$/pentaho-server/pentaho-solutions/system/sparkl/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
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
Download and install Pentaho Data Integration from https://community.hds.com/community/products-and-solutions/pentaho/:
- Downloads → Main Downloads → Data Integration

Change Pentaho default port to 8081:

Navigate to $PENTAHO_HOME/pentaho-server/tomcat/conf.
Change default Tomcat port to 8081 in the server.xml:

<Connector URIEncoding="UTF-8" port="8081" protocol="HTTP/1.1"
           connectionTimeout="20000"
           redirectPort="18443" />

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>

Setup authentication of CUBA users in Pentaho:

Download https://dl.bintray.com/cuba-platform/main/com/haulmont/addon/bi/cuba-bi-pentaho/1.2.1/cuba-bi-pentaho-1.2.1.jar and copy it to $PENTAHO_HOME$/pentaho-server/tomcat/webapps/pentaho/WEB-INF/lib.

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:18080/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" />
```

Start Pentaho Server:
- Navigate to the $PENTAHO_HOME$ 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

Clone or download the sample project from https://github.com/cuba-platform/cuba-bi-demo.git
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.

Make sure you have the demo project opened in Studio . The import procedure explained below will access the project’s HSQL database.
Run Pentaho Data Integration tool:
- Navigate to the directory where Pentaho Data Integration is installed.
- Run spoon.bat.
Open $BI_DEMO_PROJECT/demo/kettle/bidemo.kjb, where $BI_DEMO_PROJECT is the directory where the demo project is located.
Click Run to update the 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.

Start Pentaho Data Integration using spoon.bat from $PENTAHO_HOME$/design-tools/data-integration.
Create new transformation.
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

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.

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.
Then create an Insert/Update node for products:
Create the transformation for product lines:
Finalize the first transformation with the Update node:
Create the Customer transformation in the same way, including City and Territory levels, and add it to the Product one:
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:

3.2.4. Create Facts

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

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:
Then create an Insert/Update node for order lines:
Create the transformation for orders:
Finally, update the customers IDs in the table:
The Facts transformation is now ready:
Wrap the transformation in the corresponding job:

3.2.5. Create Star Schema

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

Add the START node to start the job.
Start the job with the Check Db connections condition.
In case the DB is not connected, add the Abort job node for the job.
Then add consequently Update Dimensions and Update Facts jobs that we have designed earlier.
Finalize the job with the Success node and run the job:
Save all the job and transformation files in the project folder for further use.

3.3. Configure Pentaho Analysis Report

Open Pentaho console: http://localhost:8081/pentaho and login as Admin/password.
Click File → Manage Data Sources.
Click the settings button and select New Connection:
Create connection to HSQLDB:
- Host Name: localhost
- Database Name: bidemo
- Port Number: 19001
- User Name: sa

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.

Click Import Analysis.
Select BIDemo Data Source and import $BI_DEMO_PROJECT/demo/pentaho/BiDemo.zip Mondrian File. The report structure will be imported.
Click New → Saiku Analytics → Create a new query. Select BiDemo cube and fill measure, columns, and rows as on the screen:
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

Click New Data Source.
Select the Source Type: Database Table(s).
Select the new BIDemo connection in the list of available connections.
Select Reporting and Analysis as the aim of this data source.
Select the dimensions and the fact tables we have created in Spoon: "PENTAHO_DIM_CUSTOMER", "PENTAHO_DIM_PRODUCT", "PENTAHO_FACT_ORDER_LINE":
Define Joins for selected tables:
Customize the dimensions hierarchy:
Save the data source. Select it in the list of available datasources and export the created analysis for the further use:

Create Analysis Report

Click New → Saiku Analytics → Create a new query. Select BiDemo cube and fill measure, columns, and rows as on the screen:
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

Go to http://localhost:8080/app
Open Shop → BI Saiku main menu item:

4. Adding BI to Your Application

Include the add-on in your project.

If your project does not use other CUBA Premium Add-ons, add the following lines to the buildscript.repositories section of your build.gradle:

buildscript {
    // ...
    repositories {
        // ...
        maven {
            url 'https://repo.cuba-platform.com/content/groups/premium'
            credentials {
                username(rootProject.hasProperty('premiumRepoUser') ?
                    rootProject['premiumRepoUser'] : System.getenv('CUBA_PREMIUM_USER'))
                password(rootProject.hasProperty('premiumRepoPass') ?
                    rootProject['premiumRepoPass'] : System.getenv('CUBA_PREMIUM_PASSWORD'))
            }
        }
    }

Open your project in CUBA Studio.
Edit Project properties and on the App components panel click the plus button next to Custom components.

In the Custom application component dialog, enter coordinates of the BI add-on:

Artifact group: com.haulmont.addon.bi
Artifact name: cuba-bi-global

Version: 1.2.1

Select the add-on version compatible with the CUBA platform version used in your project:

Platform Version	Add-on Version
6.5.x	1.1.1
6.6.x	1.2.1

Platform Version

Add-on Version

6.5.x

1.1.1

6.6.x

1.2.1

For example:

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
```
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:

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.

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"