Archive for the 'mondrian' 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

Agile BI Milestone 1 is now available

We’ve been working hard at Pentaho to deliver the first milestone of Agile BI.  A little over a month ago, James Dixon, our CTO, presented to the community the initial concept, which includes integrating dimensional modeling and visualization within Spoon, our ETL environment.  Since that time, James and the engineering team at Pentaho have been sprinting towards this release, making the source code available in the open, along with adding additional capabilities including the ability to persist models and visualizations.

You can download the first milestone and begin experimenting with the beginnings of what we are considering phase 1 of the Agile BI initiative, which includes the ability to quickly model and visualize a single fact table metadata and olap model.  Feedback is always welcome, check out the Agile BI forum for more discussion.

More than just Analyzer

There has been a lot of buzz recently about Pentaho’s acquisition of LucidEra’s ClearView technology, now known as Pentaho Analyzer Enterprise Edition, including this detailed writeup by Julian Hyde. But one
important aspect of this acquisition that has gone unmentioned, which I am most excited about, is the addition of Benny Chow to the Pentaho engineering team.

At LucidEra, Benny was the Director of Engineering and was responsible for the development of ClearView. In his short time here at Pentaho, he’s already had a great impact on our engineering efforts especially with the integration work necessary to make Analyzer an integral part of Pentaho’s User Console. Let me be the first to publicly welcome Benny to the Pentaho team!

Aggregate Table Naming in Aggregation Designer

This week, Nick Goodman and I worked together on Aggregation Designer’s approach to naming aggregate tables and their columns.    The team’s initial approach to table naming was to use Mondrian’s AggName convention, where you must explicitly define aggregate tables in Mondrian’s schema.  Our decision was made to guarantee that the tables would be recognized.

After working with Agg Designer in the wild, Nick created PAD-22, a feature request asking for a change in naming aggregate tables to match Mondrian’s default recognition rules.

When developing Agg Designer originally, we used Spring, which makes this type of change very easy to implement and configure.  To implement this change, we first started off by implementing an AggregateNamingService thatnames the tables to match Mondrian’s default pattern, agg_.+_${fact_table_name}.  We then implemented an OutputFactory which generates the appropriate names for the Aggregate Table columns.  These classes are now checked in to the core and ui projects, named RuleBasedAggregateTableOutputFactory and RuleBasedAggregateNamingServiceImpl.  To configure Agg Designer to use these classes, simply modify the Spring applicationContext.xml and the plugin.xml files.

I’ve provided compiled versions of these classes and instructions in the feature request, until we release a new version of Pentaho Aggregation Designer.  Thanks Nick for the help, hopefully this will make it easier to use the Aggregation Designer to increase Mondrian performance!

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.

MySQL Conference Highlights

I just got back from spending the week in Santa Clara, California at the MySQL Conference. I really enjoyed spreading the word about Open Source Business Intelligence. Many folks attending the conference were new to Business Intelligence and Pentaho, so I enjoyed demoing our products, showing off Adhoc Querying with Metadata and building Transformations with Kettle.

I was very impressed with Marten Mickos and Jonathan Schwartz’s keynotes. Both focused on the value and benefits of Open Source technology. Marten celebrated the MySQL community by recognizing some of the top community contributors of MySQL, and Jonathan discussed the big picture of how Sun powered by Open Source can change the world.

Other great highlights included Lance Walter giving a talk on Operational Business Intelligence, and Julian Hyde discussing Interactive OLAP, which included a demo of Halogen. To cap off the week, I visited my good friend Dan Morrill at the Googleplex and got a tour of their wonderfully creative environment, including pin ball machines and space ships!

Sprinting At Pentaho - Mondrian Platform Sprint

For the past couple of months, Pentaho’s Orlando developers have been using the Scrum methodology for managing our projects.  For the past two weeks, I’ve been working part time on the Mondrian Platform Sprint.  We’re delivering some exciting new features into open source.  The first story we’re tackling is the ability to create new analysis views within Pentaho’s BI Platform.  What I’ve enjoyed most about this story is working closely with Mat Lowery and Nick Baker.  We’re all working together on the various components, and things are coming together nicely.

The second story we’re delivering on this sprint is the ability to publish Mondrian Schemas to the BI Platform from Mondrian’s Schema Workbench.  I’ve taken the publishing dialog source code from Pentaho’s Report Designer and bended it to fit within the Workbench.  It’s been a while since I’ve had the pleasure of writing Swing UI code, I’m enjoying every minute.

Both of these stories will make Mondrian more usable within Pentaho’s BI Platform.  What is great about sprinting is that we know we’re focused on high priority items that make our products easier to use.