Programación Bizarra: Herramientas Libres para OLAP: Mondrian y JPivot

Presentación de Mondrian y JPivot, dos herramientas libres para OLAP en Java

Copyright (c) 2009 Héctor Francisco Hernández <hectorfh@gmail.com>.
Se otorga permiso para copiar, distribuir y/o modificar este documento bajo los términos de la Licencia de Documentación Libre de GNU, Versión 1.3 o cualquier otra versión posterior publicada por la Free Software Foundation; sin Secciones Invariantes ni Textos de Cubierta Delantera ni Textos de Cubierta Trasera. Una copia de la licencia se encuentra en http://www.gnu.org/copyleft/fdl.html.

I. ¿Qué es OLAP?

OLAP suele ser el primer paso en el campo de la inteligencia de negocio (Business Intelligence) en toda organización.

Repasemos brevemente el concepto de sistema transaccional con el fin de introducir OLAP.

En informática, los sistemas transaccionales son aquellos que están hechos para registrar y soportar las transacciones de negocio, por ejemplo operaciones empresariales como la venta, la compra, la adquisición de créditos, el pago, entre otras.

Se suele llamar a esta clase de uso de sistemas informáticos OLTP, "On Line Transaction Processing".

Estos sistemas deben estar preparados para soportar un gran número de usuarios en línea y distintos tipos de operaciones de manera concurrente. Y suelen tener la característica de que la interacción entre el usuario y el sistema es relativamente corta: menos de un segundo en muchos casos. La operación de negocio se llevará a cabo completamente a través de varias interacciones cortas que exigirán una respuesta inmediata. Además deben proveer datos actualizados hasta la última transacción; deben estar disponibles de forma continua; deben ser rápidos; y deben brindar la protección e integridad de los datos.

Sin embargo el universo informático dentro de una organización no termina en las aplicaciones OLTP. Existen otras aplicaciones, generalmente destinadas a soportar la toma de decisiones, que consisten en analizar una gran cantidad de datos para generar informes de negocio de ventas, mercadotecnia, informes de dirección y áreas similares.

Esta aplicación de sistemas informáticos se suele denominar OLAP, "On Line Analytical Processing".

OLAP (online analytical processing o procesamiento analítico en línea) es un término acuñado por EF Codd & Associates, que publicó un libro blanco en 1993, encargado por Arbor Software (en la actualidad Hyperion Solutions), titulado "Prestación de OLAP (Procesamiento Analítico On-Line) para análisis de usuario: Un mandato de las TI".

"En Línea" implica que aún cuando grandes cantidades de datos están involucradas en las consultas, el sistema debe responder lo suficientemente rápido como para permitir ser usado de manera interactiva.

Hay que remarcar que Codd cobró para escribir este libro blanco de apoyo a Essbase (producto comercial de Arbor). El documento de Codd fue visto como propaganda parcial.

Estos sistemas recolectan y procesan información registrada por los sistemas transaccionales de la organización y ponen los resultados a disposición del usuario a través de reportes que se generan de manera instantanea. Estos datos son utilizados generalmente para tomar decisiones y por lo general no necesitan estar actualizados al día de la fecha. Por ejemplo, el usuario requiere un informe de ventas del último cuatrimestre, o se precisa una comparación de los gastos en los últimos años.

La siguiente captura de pantalla muestra un reporte OLAP clásico.

(Reporte OLAP generado con JPivot)

Bases de datos OLAP

Aunque existen muchas herramientas de generación de informes que operan sobre las bases de datos de los sistemas transaccionales, suelen ser lentas.

Los diseños de estas bases de datos están pensados para realizar altas, bajas y modificaciones de la forma más óptima, teniendo un mínimo grado de redundancia de información posible. Esto significa que se tiende a la normalización.

Sin embargo la normalización puede hacer lentas las consultas complejas, ya que al estar la información distribuida en muchas tablas es necesario calcular productos cartesianos complejos (en sql "inner join", "outer join", el operador coma) y en gran cantidad. Este tipo de operaciones es de los más costosos para el motor de base de datos.

Cuando las consultas implican cruzar varias tablas de decenas o cientos de miles de registros, el sistema no responde satisfactoriamente. Además de tardar varios minutos en presentar la información, se consumen recursos haciendo más lento y degradando el servicio prestado por el sistema a los otros usuarios en línea.

La solución adoptada consiste es extraer, tranformar y cargar (ETL: Extract, Transform and Load) los datos a una base exclusiva para OLAP. Si bien las bases de datos relacionales, u orientadas a objetos en los sistemas más recientes, son las utilizadas comunmente en las aplicaciones OLTP, en el mundo OLAP las bases de datos impuestas son las multidimensionales.

Conceptualmente, una base de datos multidimensional utiliza la idea de "cubos". Un cubo posee N dimensiones y en él están registrados "hechos" de un determinado tipo. Los hechos podrían ser, por ejemplo, desde las ventas realizadas por una empresa (según estudios la utilización de OLAP para el analisis de ventas es la aplicación más popular) hasta los defectos que reportan los usuarios de un sistema informático, según cuál fuese la finalidad de la aplicación.

Cada "hecho" tiene asociado un elemento de cada dimensión definida para el cubo. Por ejemplo, si el cubo registra ventas, cada venta tiene asociado un lugar geográfico (dimensión geográfica), el día en el que ocurrió (dimensión temporal), el producto que se vendió (dimensión de tipo), etc.

Además el "hecho" tiene valores asociados, por ejemplo el importe por el cual se realizó la venta o la cantidad de unidades vendidas.

Poseyendo estos tres elementos, a saber, un tipo de hecho, un conjunto de dimensiones y un conjunto de valores, queda conformado un cubo capaz de responder preguntas como cuántas ventas de determinado artículo se realizaron en el mes de febrero, o cuál es el producto más vendido del trimestre pasado en la ciudad de Córdoba.

Para consultar estos datos al cubo se necesita una herramienta fundamental. Así como existen lenguajes de consultas en los otros paradigmas de bases de datos (SQL, HQL), para las bases multidimensionales el lenguaje es MDX: MultiDimensional eXpressions.

MDX fue presentado como parte de la especificación OLE DB for OLAP (ODBO) en 1997 por Microsoft, con Mosha Pasumansky siendo uno de los arquitectos del lenguaje. Luego de la especificación siguió el lanzamiento comercial de Microsoft OLAP Services 7.0 en 1998 y más tarde Microsoft Analysis Services. La última versión de la especificación OLE DB for OLAP (ODBO) fue publicada por Microsoft en 1999.

Aunque no es un estándar, sino más bien una especificación propietaria de Microsoft, ha sido adoptada por una amplia gama de compañías en diversos productos, entre los que se incluyen Applix, Microstrategy, SAS, SAP, Whitelight, NCR, Panorama Software, Proclarity, AppSource, Cognos, Business Objects, Brio Technology, Crystal Reports, Microsoft Excel, Microsoft Reporting Services, etc.

Un ejemplo de consulta MDX es la siguiente:

SELECT
 { [Measures].[Importe] } ON COLUMNS,
 { [Tiempo].[2002], [Tiempo].[2003] } ON ROWS
FROM Ventas
WHERE ( [Sucursal].[Zona Oeste].[Tablada] )

Esta consulta obtiene como resultado un reporte OLAP, de un aspecto similar a la captura de pantalla mostrada más arriba, con los totales de las ventas realizadas por una sucursal llamada "Tablada" de una cadena de mercados mostrando los años 2002 y 2003 en el eje vertical. No tienen mucha importancia por el momento los detalles de qué es lo que se consigue con la consulta, sólo qué aspecto tiene el lenguaje MDX. Nótese una fuerte inspiración en el lenguaje SQL, el "select-from-where".

II. Herramientas Libres para OLAP: Mondrian y JPivot

Existe más de una solución OLAP en el mercado. Entre las herramientas disponibles se incluyen motores para implementar bases de datos multidimensionales nativas (solución MOLAP) o para emularlas sobre una implementación relacional (solución ROLAP), diseñadores de cubos, navegadores, generadores de reportes, graficadores, programas para realizar la extracción, transformación y carga de datos (ETL) desde bases transaccionales.

La mayoría de estas herramientas son privativas y requieren de la adquisición de una licencia para poder usarlas. Sin embargo ya comienzan a haber productos libres y de calidad aceptable que podemos utilizar en un entorno de producción. Entre estos hablaremos de dos para JEE que se pueden descargar de la internet: Mondrian, un motor ROLAP, y JPivot un explorador OLAP web basado en Mondrian que muestra la información en tablas interactivas que el usuario puede navegar.

Mondrian

La última versión de Mondrian y su documentación se encuentra en http://mondrian.pentaho.org/.

De una manera similar a un ORM (por ejemplo Hibernate en Java), que permite emular una base de datos orientada a objetos a partir de una relacional, Mondrian nos permite emular una base multidimensional. Permitiéndonos así realizar consultas en lenguaje MDX, pensando en términos de cubos, hechos, dimensiones y valores en lugar de tablas, registros y campos.

Mondrian está compuesto por un conjunto de archivos "jar", que proveen una API inspirada en JDBC pero diseñada para trabajar con una base multidimensional. El sistema necesita que escribamos en un archivo "xml" cómo se corresponden los elementos de esta base con la base relacional.

La base relacional debe ser diseñada cuidadosamente de modo que sobre ella se pueda hacer el mapeo. Las estructuras comunmente usadas son tres:

Una única tabla para el cubo donde existe un registro por cada hecho (por ejemplo, un registro por cada venta). El registro tendrá un código único, un campo o más por cada dimensión y un campo por cada valor del hecho (un campo con el importe de la venta, otro con la cantidad de artículos vendidos, etc.).

Una estructura de estrella. Con una tabla central de hechos donde está el código único del mismo y los valores correspondientes, y varias tablas periféricas, una por cada dimensión. Los registros de la tabla central se relacionan con los registros de las tablas periféricas de un modo "muchos a uno", por lo tanto la tabla central posee además una clave foránea por dimensión.
Una estructura de copo de nieve. No muy alejada de la estrella, pero busca quitar relaciones de la tabla central para ahorrar espacio.

Para mostrar un ejemplo de mapeo, supongamos un cubo llamado "Ventas" que posee todas las ventas de una pequeña cadena de mercados. Este cubo tendrá tres dimensiones: "Rubro", "Sucursal" y "Tiempo", y también un valor, el "Importe" de la venta. Previamente crearíamos una base de datos en algún servidor que tenga conectividad con JDBC, por ejemplo MySQL. Nuestra arquitectura será la típica estrella, con la tabla de hechos "venta" y una tabla adicional por cada dimensión.

Así las sentencias que crearán la base serán las siguientes:

create database olapmercado;
create table venta (id integer primary key, rubro_id integer,

        sucursal_id integer, tiempo_id integer, importe double);
create table rubro (id integer primary key, nombre char(20));
create table sucursal (id integer primary key, nombre char(20));
create table tiempo (id integer primary key, anio integer, mes integer);

Estas tablas deben ser llenadas. Probablemente queramos hacerlo con datos que se encuentran en algún otro sistema. Por eso debemos crear un script o programita que extraiga, transforme y cargue los datos (ETL), y que lo haga periodicamente, por ejemplo todas las semanas o todos los meses.

Para mapear el cubo con la tabla, creamos un archivo llamado "olapmercado.xml" con el siguiente contenido:

<?xml version="1.0"?>
<Schema name="olapmercado">
  <Dimension name="Rubro">
    <Hierarchy hasAll="true" allMemberName="Todos"

        primaryKey="id">
      <Table name="rubro"/>
      <Level name="Rubro" column="nombre"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Sucursal">
    <Hierarchy hasAll="true" allMemberName="Todas"

        primaryKey="id">
      <Table name="sucursal"/>
      <Level name="Sucursal" column="nombre"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Tiempo">
    <Hierarchy hasAll="true" allMemberName="Total"

        primaryKey="id">
      <Table name="tiempo"/>
      <Level name="Anio" column="anio"/>
      <Level name="Mes" column="mes"/>
    </Hierarchy>
  </Dimension>

  <Cube name="Ventas">
    <Table name="venta"/>

    <DimensionUsage name="Rubro" source="Rubro"

        foreignKey="rubro_id"/>
    <DimensionUsage name="Sucursal" source="Sucursal"

        foreignKey="sucursal_id"/>
    <DimensionUsage name="Tiempo" source="Tiempo"

        foreignKey="tiempo_id"/>

    <Measure name="Importe" column="importe"

        aggregator="sum" formatString="#,##0.00"/>
  </Cube>
</Schema>

Una vez hecho esto, ya estamos en condiciones de poder utilizar la API de Mondrian, basada en JDBC, para conectarnos a la base, ejecutar consultas MDX y obtener resultados.

Esta API tiene un aspecto similar al siguiente:

Connection connection = DriverManager.getConnection(
  "Provider=mondrian;" +
  "Jdbc=jdbc:mysql://localhost/olapmercado?" +

  "user=root&password=root1;" +
  "Catalog=olapmercado.xml;",
  null,
  false);

String mdxString = "select {(Sucursal, Rubro)} on rows, " +
  "{Tiempo} on columns from Ventas";

Query query = connection.parseQuery(mdxString);

Result result = connection.execute(query);

int[] coord = {0, 0};
Cell cell = result.getCell(coord);

etc...

Pero no diré más sobre el asunto. No es preciso utilizar esta API habiendo navegadores OLAP como JPivot que pueden ejecutar la consulta y mostrar los datos en pantalla sin necesidad de programar nada.

JPivot

La aplicación de Mondrian por sí misma parece estar destinada a la programación en un bajo nivel. Debemos ejecutar varios métodos de Java para establecer la conexión con la base, ejecutar la consulta y obtener los resultados, para después programar cientos de líneas según lo que necesitemos: mostrarlos en pantalla, pasarlos a alguna hoja de cálculo, imprimirlos o hacer algún gráfico.

Afortunadamente existen componentes y programas conocidos como "clientes OLAP", que trabajan con Mondrian, cuyo objetivo es proporcionar una interfaz ("front" en inglés) "prefabricada" donde, con sólo introducir la consulta MDX, se puede obtener los datos en una tabla navegable, imprimirlos, graficarlos y hasta exportarlos a una hoja de cálculo. Entre estos programas se encuentra JRubik, una aplicación de escritorio programada con Swing, y JPivot, que está implementada como un conjunto de tags JSP (tecnología web), por lo que es altamente configurable y se integra bien con nuestras aplicaciones web.

JPivot se puede descargar de la página del proyecto en SourceForge, http://jpivot.sourceforge.net/. La distribución binaria incluye el archivo ".war" listo para ser colocado en algún servidor JEE y funcionar.

Por defecto, en la distribución ya vienen algunas consultas hechas a modo de ejemplo. Se encuentran dentro de los archivos ".jsp" en el directorio "WEB-INF/queries". En los mismos archivos se puede ver cómo se configura la cadena de conexión con la base de datos. Y en el mismo directorio se halla un archivo ".xml" de mapeo para el Mondrian (recuérdese que JPivot utiliza Mondrian y que ya viene con los ".jar" incluídos).

Los pasos para tener JPivot andando son pocos, aunque pueden llevar algunas horitas la primera vez. Primero, descargar algún servidor web como Tomcat e instalar el ".war". Segundo, armar el archivo de mapeo para el Mondrian. El paso cero habría sido tener una base de datos ya armada. Tercero, armar los archivos con las consultas MDX y la cadena de conexión (en lugar de utilizar una cadena de conexión, también es posible especificar un "datasource"), para ello tomar como base los ejemplos que vienen. Si todo va bien ya tendrémos el navegador OLAP funcionando.

Luego JPivot se puede modificar según nuestras necesidades. Es fácil cambiar el aspecto de la interfaz del navegador.

Suponiendo el ejemplo de más arriba, del cubo "Ventas" de la cadena de mercados, con el mismo archivo de mapeo, veamos algunas capturas de pantallas:

select {(Sucursal, Rubro)} on rows, {Tiempo} on columns from Ventas
select {Sucursal} on rows, {Rubro} on columns from Ventas
select {(Rubro, Sucursal)} on rows, {Tiempo} on columns from Ventas

Hasta aquí Mondrian y JPivot, dos herramientas para OLAP libres. Si le ha interesado le sugiero que continue leyendo la documentación de Mondrian y JPivot en sus respectivos sitios y que se anime a experimentar instalando JPivot.