Archive for the 'platform' Category

Mondrian 4, OSGi in Pentaho 5.1 CE

During the development of 5.1, Pentaho has taken steps to integrate Mondrian 4 into our business analytics platform.  This article goes over what we have accomplished so far, where we are headed, and also instructions for getting Mondrian 4 working with Pentaho 5.1 Community Edition.

Pentaho Enterprise Edition has Mondrian 4 bundled for a specific reason - we’ve now introduced native MongoDB support as a plugin to Mondrian 4.  This use case allows customers to slice and dice data from MongoDB collections in Pentaho Analyzer.  You can learn more about the capability here:  http://www.pentaho.com/request-analyzer-mongodb

As we continue to evolve the Pentaho Platform, we need a more flexible plugin architecture for driving innovation.  To allow both Mondrian 3 and Mondrian 4 runtime environments, we’ve introduced OSGi as a core part of the platform.  Mondrian 4 is our first use case, but we’ll be introducing many others in future versions.

Once Mondrian 4 is installed as an OSGi bundle, it is available as an OLAP4J resource to the platform via Pentaho’s proxy system Driver aptly named “PentahoSystemDriver”.  The following steps below walk you through getting Pentaho Mondrian up and running within Pentaho CE 5.1.  Note that these instructions won’t work against previous versions of Pentaho, and these instructions are not necessary in Pentaho EE 5.1, as Mondrian 4 is already configured and installed.

Download Pentaho 5.1 CE

You can download Pentaho 5.1 from sourceforge here: http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/5.1/

Make sure it is working normally before continuing with these instructions.

Deploy Mondrian 4’s required OSGi bundles

We first need to add Mondrian 4 and its dependencies as OSGi Bundles.  Copy the following JARs, which are now OSGi compatible, to pentaho-solutions/system/osgi/bundles:

Also, you may copy the Mondrian properties file to “mondrian.cfg” in the same folder to customize various Mondrian 4 properties.

Install the CDA Plugin via the Marketplace

Go to the Marketplace perspective and install Community Data Access, this plugin allows you to query various data sources including Mondrian 4 via OLAP4J.

Setup a Mondrian 4 Database and Schema

I already had Foodmart installed in a local MySQL instance, I also deployed the Mondrian 4 FoodMart.mondrian.xml schema (https://github.com/pentaho/mondrian/blob/lagunitas/demo/FoodMart.mondrian.xml) to the BA Server by uploading the file in the /public/Foodmart folder.

Create a new CDA file that queries Foodmart

I copied the olap4j example that comes with CDA.  Here are the important parts:

<DataSources>
<Connection id=”1″ type=”olap4j”>
<Driver>mondrian.olap4j.MondrianOlap4jDriver</Driver>
<Url>jdbc:mondrian4:</Url>
<Property name=”JdbcUser”>foodmart</Property>
<Property name=”JdbcPassword”>foodmart</Property>
<Property name=”Jdbc”>jdbc:mysql://localhost:3306/foodmart_mondrian_4</Property>
<Property name=”JdbcDrivers”>com.mysql.jdbc.Driver</Property>
<Property name=”Catalog”>solution:/public/Foodmart/FoodMart.mondrian.xml</Property>
</Connection>
</DataSources>

<Query>
select {[Measures].[Unit Sales]} ON COLUMNS,
NON EMPTY [Time].[Time].[1997].Children ON ROWS
from [Sales]
where ([Product].[${productfamily}])
</Query>

You can download the full CDA file I used here: mondrian4.cda

Upload the CDA file in the /public/Foodmart folder.

You can now run the CDA file and see the results come back from Mondrian 4!

Additional Info

For developers wanting to access Mondrian 4 metadata like they can access Mondrian 3 schemas by schema name in the Pentaho Platform, we’ve done some low level plumbing to get folks started.  If you define a Mondrian 4 connection in pentaho-solutions/system/olap4j.properties, you can gain access to that programmatically through code like the following:

import org.pentaho.platform.plugin.services.connections.mondrian.MDXOlap4jConnection;
import org.pentaho.platform.plugin.services.importexport.legacy.MondrianCatalogRepositoryHelper;
import org.pentaho.platform.plugin.services.importexport.legacy.MondrianCatalogRepositoryHelper.Olap4jServerInfo;
...
final MondrianCatalogRepositoryHelper helper = new MondrianCatalogRepositoryHelper( repo );
if ( helper.getOlap4jServers().contains( catalogName ) ) {
final Olap4jServerInfo serverInfo = helper.getOlap4jServerInfo( catalogName );
properties.setProperty( “url”, serverInfo.URL );
properties.setProperty( “driver”, serverInfo.className );
if ( serverInfo.user != null ) {
properties.setProperty( “user”, serverInfo.user );
}
if ( serverInfo.password != null ) {
properties.setProperty( “password”, serverInfo.password );
}
MDXOlap4jConnection connection =
(MDXOlap4jConnection) PentahoConnectionFactory.getConnection( IPentahoConnection.MDX_OLAP4J_DATASOURCE,
properties, PentahoSessionHolder.getSession(), null );
}

Using this utility code is nice because the MDXOlap4jConnection will manage mapping Pentaho’s roles to Mondrian’s.

So how does all of this work?

Pentaho has bundled Apache Felix into the Pentaho Platform.  Felix is an OSGi container which now manages Mondrian 4 and its dependencies.  The core bundles that make up Pentaho’s OSGi container can be found in pentaho-solutions/system/osgi/core_bundles, here you’ll find a number of utility OSGi jars including Gemini’s Blueprint, which we use for wiring OSGi components.  Blueprint is similar to the Spring Framework.  Also, the Mondrian 4 jar contains some metadata that registers it with the Pentaho platform as an available OLAP4J driver with the JDBC prefix name of “mondrian4″.  You can check out the metadata file OSGI-INF/blueprint/beans.xml to see the specific XML to declare the driver.  To see how the internal wiring is done, and how PentahoSystemDriver is involved, you can check out the pentaho-platform package org.pentaho.platform.osgi.

There is still a lot of work to do!

Here are some of the areas we will need to complete in future versions to make this a seamless experience:

  • Ship the Mondrian 4 binaries with the release!
  • Update Pentaho’s Datasource Manager to easily manage Mondrian 4 connections instead of editing olap4j.properties
  • Enable Mondrian 4 to access Pentaho Database Connections
  • Create an easy to use Mondrian 4 Schema Editor - Maybe Ivy Information System’s work will move in that direction?
  • Have projects such as Pivot4J and Saiku support Mondrian 4 connections via OSGi

If any of this work is of interest to you, folks in the Mondrian mailing list and at Pentaho would be happy to help point the way!

Thanks!

Will

Experimental Action Editor in GWT

I wanted to share with everyone a new open source project that I just released out to Google Code.  When using Pentaho’s BI Server, users create Action Sequences to pull their data from a warehouse and display it in a Report, etc.  Pentaho’s Action Sequences make it easy for business analysts to define their own process. Pentaho offers Action Components ranging from Analysis OLAP Views to XML Queries that can be combined to create an Action Sequence. Today, you can use one of our many wizards to generate an Action Sequence, or generate one with our Design Studio, a thick client application built on Eclipse.

To educate myself in Google’s Web Toolkit (GWT), I decided to attempt a port of our Design Studio over to thin client. I’ve made a lot of progress, including getting Dom4J running in GWT along with Pentaho’s Action Sequence DOM, an important library that parses and builds a document object model of an Action Sequence file. This week I’ve uploaded the first set of code, which implements input, resource and output parameter management, as well as the start of a couple Action Components, including SQL Lookup Rule and the JFreeReport Component. The project includes instructions to integrate into our latest BI Server, so you can right click, select “Edit Action”, and start editing your Action Sequence right in the browser!

If there are folks out there interested in contributing to this project, please feel free to check out the code, help out with requirements, graphics, you name it! I’ll be working on the project in my spare time.

DOM4J4GWT
This is a light DOM4J Implementation, designed to run within GWT’s client environment
http://code.google.com/p/dom4j4gwt

Penaho Action Sequence DOM 4 GWT
This project modifies Pentaho Action Sequence DOM so that it may run within GWT’s client environment
http://code.google.com/p/pentaho-actionsequence-dom-4gwt

Pentaho Action Editor
This is the main project, that allows you to edit Pentaho’s Action Sequences in the browser. It includes instructions for deploying into Pentaho’s 2.0 BI Platform
http://code.google.com/p/pentaho-actioneditor

Pentaho BI Server Community Edition, 2.0 RC1

This week, the Pentaho team released our first release candidate of our 2.0 Business Intelligence Server.  The entire Orlando team worked many months on the brand new features incorporated into this release.  I’m going to highlight some of the most exciting features that we’re all proud of at Pentaho:

A New User Console
In this release, we built from the ground up an entirely new user interface designed to greatly enhance the user experience of our Business Intelligence Server.  Taking advantage of Google’s Web Toolkit’s amazing technology, we were able to deliver a complete Web 2.0 dynamic experience to our business users.  We’ve modernized the look and feel of our analysis views and adhoc reporting tool, along with greatly simplifying permissions management.

Data Source and User Management
For the first time, our Community Edition Business Intelligence Server also comes shipped with an Administration console, for management of Users and Relational Data Sources.  In addition to our default support for direct JDBC and JNDI connections, we also have the ability to define a Pentaho Managed Data Source.  This makes it easy for business users to add their own data sources, without having to know the ins and outs of the Java Enterprise Container hosting Pentaho’s BI Server.

Metadata Row Level Security
Another important enhancement to our product offering is the ability to manage row level security from within our Metadata Layer.  In our first release of Pentaho Metadata, we implemented Model, View and Column Level Security.  In this release we extend our Security functionality to also include Row Level Constraints.  Check out the documentation!

There are countless other features that went into our Community Edition release.  At the same time, we’re also releasing our Enterprise Edition software, which includes features such as enhanced ETL Administration, along with many additional BI Server Administration and Configuration capabilities.  These are exciting times!

Pentaho iPhone Coverage, Post on YouTube

Today I saw that Pentaho’s Business Intelligence iPhone Softare made it into Network World’s 20 cool third-party iPhone apps list. Check out slide 11! Very cool. Also, I posted the video over to youtube for all to share:

log4j, Mondrian MDX and SQL Logging

I’ve always struggled to get log4j working perfectly within the Pentaho BI Platform.  Before the release of JBoss 4.2.1, there was a log4j.xml conflict between JBoss’s config and regular web applications.  Now that Pentaho 1.7 PCI by default runs in JBoss 4.2.1, we finally have a hastle free way to manage logging.

We can now manage all of Pentaho’s logging within the pentaho war’s WEB-INF/classes/log4j.xml file.  Another reason why this is important to note is that Mondrian’s SQL and MDX logging, something we’re always taking a look at, moved from a Mondrian debug property to a log4j category in Mondrian 3.0.  To get MDX and SQL logging out of Mondrian:

Add two new categories to the log4j.xml file:

<category name="mondrian.sql">
<priority value="DEBUG"/>
</category>

<category name="mondrian.mdx">
<priority value="DEBUG"/>
</category>

Also, you’ll want to remove the explicit Threshold element from within the appenders.  When a Threshold is defined in an Appender, it overrides the individual category thresholds.  log4j is a powerful logging tool, but sometimes it can be difficult to configure.