Saiku Analytics (BI) Integration with Pentaho and Liferay

Objective:

Recently I had integrated Saiku Analytics tool with Pentaho to implement the Ad hoc report feature in a Java based business application. The basic requirement was, users should be able to define the report layout and generate report based on dynamic dimensions, cubes and field selection. Also user should be able to view reports in graphical format and export it into excel or pdf.

Saiku is an open source tool which is used for multidimensional analysis where you can slice and dice data. This tool is freely available for download from www.analytical-labs.com. Organisations can use the solution internally without having to pay license fees.This provides a user friendly, web based analytics solution that helps users to quickly and easily analyze application/system data. The solution connects to a range of OLAP Servers including Mondrian, Oracle Hyperion. This tool easily integrates with Pentaho, as this was developed with the purpose of the GWT based wrapper around the OLAP4J library. We can integrate this tool with other business applications using Iframe, SSO. In this blog, I have explained how we can integrate this tool with Pentaho and Liferay using Iframe Portlet.

We have integrated Saiku with an application having the following technology stack

• Liferay 5.2.3
• Spring framework 3.0.5
• Ifaces 1.8
• Mysql 5.1

Saiku provides Pentaho plugin, you can download it from http://analytical-labs.com/downloads.php site. We need to extract the Saiku.zip file into [drive name]liferay-portal-5.2.3pentaho-solutionssystem folder and map corresponding datasource in [drive name]liferay-portal-5.2.3pentaho-solutionssystemolapdatasources.xml file. This step will integrate Saiku with Pentaho. Using this datasources.xml file Saiku pickup dimensions and cubes. Sample datasource definition in datasource.xml file -

<DataSource>
<DataSourceName>Provider=Mondrian;DataSource=Pentaho</DataSourceName>
<DataSourceDescription>Pentaho BI Platform Datasources</DataSourceDescription>
<URL>http://localhost:9080/pentaho/Xmla?userid=joe&amp;password=password</URL>
<DataSourceInfo>Provider=mondrian</DataSourceInfo>
<ProviderName>PentahoXMLA</ProviderName>
<ProviderType>MDP</ProviderType>
<AuthenticationMode>Unauthenticated</AuthenticationMode>
<Catalogs>
<Catalog name="Repair Order">
<DataSourceInfo>Provider=mondrian;DataSource=Liferay</DataSourceInfo>
<Definition>solution:Liferay/Repair_Order.mondrian.xml</Definition>
</Catalog>
</Catalogs>
</DataSource>
</DataSources>

In the above xml snippetsLiferay/Repair_Order.mondrian.xml is a cube and dimension definition file which is called as mondrian schema file and it can have multiple cubes defined in it, this file is generated through Pentaho and Liferay is the name of the folder in which this file is present. We can create cube using Pentaho schema workbench.

Pentaho should connect to MySQL database, for that purpose we have configured the database username, password and driver information in mysql5.hibernate.cfg.xml, hibernate-settings.xml file from the pentaho-solutionssystemhibernate folder and to configure datasource in Pentaho, we have used the following steps :

1. Connect to Pentaho admin interface using http://localhost:9080/pentaho/ URL.
2. Use username=joe and password=password to login in Pentaho
3. Go to file ->New -> Data source..
4. Select source type = “SQL Query”, you can see Liferay as listed data source
5. Select it and edit it to connect to correct database. [Click on the pencil icon to edit the data source.] Provide hostname, database name, port number, user name and password and click on ‘Test’ to test the connection. It should show successfully connected message.

Integrating Saiku Reporting Tool in Liferay

Saiku Analytics is an independent Web application, and requires authorization to access the tool. Liferay community edition comes bundled with an Iframe Portlet which enables integration of web application inside the Liferay UI as a Portlet.

• Source URL = /pentaho/Login?unique=new Date()
• Authenticate=true
• User name (field name=j_username) and (value=joe)
• Password (field name=j_password) and (value=password)
• Hidden variables= locale=en;redirectTo=/pentaho/content/saiku-ui/index.html?biplugin=true;autoLoginSiku=true;unique=new Date()
• HTML attributes= no change in this. Keep it as it is.