Leveraging Bootstrap and Angular

The driving force behind my current Javascript experiment was a need to modernize the UI for the Pivot Table. Clearly the old good JPivot-like UI doesn't match modern users expectations: context menus, drag & drop items, etc. So, once my Javascript cellset fulfilled basic functionality (drill up/down and add/remove hierarchies) I've started my work to rejuvenate the UI into a more stylish one.

An Experiment: Using Javascript to Display CellSet Data

I've just started to use Javascript professionally so I've decided to apply my new knowledge to experiment on using this technology to display MDX data. My goal was restricted to display a CellSetAxis from a fixed query, allowing the user to drill down/drill up on any member.

Live Demo!!

I've just set installed a live demo running the current SNAPSHOT version of the project.

http://demo.desarrolloagil.es/olap-faces/test.jsf

Version 5: Refining Hierarchy Selection UI and Query Axis Editing

You can download this version of the sample, and an improved version of the component library from here.




Sample page including the <olap:queryAxisEditor> component

This version replaces the previous listbox and buttons used to add hierarchies to the query axes with a new composite component that:

• Displays the current cube's hierarchies organized by the axis where they are used. Unused hierarchies are displayed in an "Unused"
  axis.
• Allows for hierarchy sorting (within the ROWS and COLUMN axes).
• Allows hierarchy removal from an axis.
• Allows moving used hierarchies to other query axes.
• Keeps a selected hierarchy (in the sample page this selected hierarchy is the one displayed by the query hierarchy editor)
• Includes a button to swap rows and columns axes.
• Allows selecting of the non-empty state for ROWS and COLUMNS axes.

All this functionality is added to the sample page with the following facelet snippet

<olap:queryAxisEditor
id="axisEditor"
query="#{queryBean}"
value="#{queryBean.selectedHierarchyName}">

<f:ajax
event="change"
execute=":form:axisEditor"
render=":form:axisEditor :form:hierarchyEditor"/>

<f:ajax
event="edit"
render="@all"/>

</olap:queryAxisEditor>

<olap:queryAxisEditor> Composite Component Internals

The queryAxisEditor composite component takes the following parameters:

• query: AbstractQueryBean derived managed bean keeping the query state
• value: backing bean property used to keep the current selected hierarchy name

This component can fire two client side events:

• change: when the user changes the currently selected hierarchy
  
• edit: when the user clicks on any action button (remove, add to rows, add to columns, add to filter, push up or push down) changing the current query.
  

This is the facelet used to implement this composite component

<composite:interface>

<composite:attribute
name="query"
/>

<composite:attribute
name="value"
/>

<composite:attribute
name="styleClass"
/>

<composite:attribute
name="headerClass"
/>

<composite:attribute
name="rowClasses"
/>

<composite:clientBehavior
name="change"
default="true"
event="click"
targets="columns rows filter unused"
/>

<composite:clientBehavior
name="edit"
event="edit"
targets="columns rows filter unused"
/>

<composite:clientBehavior
name="edit"
targets="swap"
/>

</composite:interface>

<composite:implementation>

<div
id="#{cc.attrs.clientId}">

<h:commandButton
id="swap"
styleClass="cgaofHierarchyListSwapBtn"
value="Swap Axes"
action="#{queryAxisEditorBean.swapAxes(cc.attrs.query)}"/>

<h:inputHidden
id="selected"
value="#{cc.attrs.value}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
axisName="COLUMNS"
id="columns"

styleClass="#{cc.attrs.styleClass}"
headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
axisName="ROWS"
id="rows"

styleClass="#{cc.attrs.styleClass}"
headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
axisName="FILTER"
id="filter"

rendered="#{cc.attrs.query.slicerHierarchies.size() > 0}"
styleClass="#{cc.attrs.styleClass}"

headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
id="unused"

styleClass="#{cc.attrs.styleClass}"
headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

</div>

</composite:implementation>

I'm using an inputHidden control to keep the selected hierarchy name: its value is changed by the inner queryAxisEditorBase controls, responsible for displaying the contents of a single axis. Those controls receive the query bean, the current selected hierarchy name, the client id of the hidden input field and the name of the axis they are displaying. Each axis is displayed using a table:

• The axis name and the non-empty toggle button are displayed within the table's header.
• Each hierarchy within the axis is displayed in a row with three columns:
  • A column for the remove button.
  • A column for the hierarchy's caption. The caption includes javascript to change the selected hierarchy on clicking.
  • A column for the other action buttons: rows, columns, filter, push up and push down.

Both composite components (queryAxisEditor and queryAxisEditorBase) relay on the QueryAxisEditorBean managed bean to execute actions and simplify facelets.

Required Query Package Improvements

The improvements implemented in the query package have been straightforward and all of them consisted in implementing methods already defined by the org.olap4j.query package:

• Query.swapAxes to allow axes swapping
• QueryHierarchy.isNonEmpty(), QueryHierarchy.setNonEmpty() to check/set the flag controlling the generation of NON EMPTY axis.
  
• QueryHierarchy.pushUp(), QueryHierarchy.pushDown()to allow moving a hierarchy within an axis.

Version 2: Redesigning QueryDimension

You can download this version of the sample, and an improved version of the component library from here.

Once I've added drilling capabilities to QueryAxis, my next task has been redesigning QueryDimension member selection capabilities. My design goal was to improve the integration of this class with the UI components used to allow the user selecting the set of members to be included in the dimension. This goal pushed me to change the semantics associated to the include/exclude methods. The original olap4j implementation executes first all the includes and then all the excludes. My implementation executes the includes and excludes in the order they were invoked, so an hypothetical UI can include and exclude members as instructed by the user and get the resulting selection state immediately. For example, the following selection sequence:
    QueryDimension dim;

  

    dim.include(

        Operator.DESCENDANTS,

        IdentifierNode.ofNames("Time","2000").getSegmentList());

    dim.exclude(

        Operator.DESCENDANTS,

        IdentifierNode.ofNames("Time","2000","Q1").getSegmentList());

    dim.include(

        Operator.MEMBER,

        IdentifierNode.ofNames("Time","2000","Q1","April").getSegmentList());



will produce a different set of selected members in my implementation (April to December) than in the olap4j QueryDimension (May to December).

Usage Sample

This is the code used to initialize the query in the sample web app. It selects only the states of USA for the Store hierarchy, all the members of the Gender hierarchy and shows only Unit Sales and Measures.
    private Query initQuery() throws OlapException {

        Cube c = getConnection().getOlapSchema().getCubes().get("Sales");

        Query q = new Query("MyQuery", c);

        QueryAxis columnsAxis = q.getAxis(Axis.COLUMNS);

        columnsAxis.setNonEmpty(true);

        columnsAxis.addDimension(selectAll(q, "Gender"));

        

        QueryHierarchy measuresDim = q.getDimension("Measures");

        columnsAxis.addDimension(measuresDim);

        measuresDim.include(

                Operator.MEMBER,

                IdentifierNode.ofNames("Measures","Unit Sales").getSegmentList());

        measuresDim.include(

                Operator.MEMBER,

                IdentifierNode.ofNames("Measures","Store Cost").getSegmentList());

        

  

        QueryAxis rowsAxis = q.getAxis(Axis.ROWS);

        rowsAxis.setNonEmpty(true);

        QueryHierarchy storeDim = q.getDimension("Store");

        storeDim.include(Operator.CHILDREN, IdentifierNode.ofNames("Store","USA").getSegmentList());

        rowsAxis.addDimension(storeDim);

        rowsAxis.addDimension(selectAll(q, "Store Type"));

  

        return q;

    }



    private QueryHierarchy selectAll(Query q, String dimension) throws OlapException {

        QueryHierarchy dim = q.getDimension(dimension);

        dim.include(Operator.DESCENDANTS, dim.getHierarchy().getRootMembers().get(0));

        return dim;

    }

And this is the resulting output, after a pair of drills. Note that the drills controls at CA, OR and WA are a bug, they don't drill anything as those members have no children in this query hierarchy

Another design goal, suggested in a comment by Julian Hyde, was to define the selection mechanism in terms of hierarchies instead of dimensions, allowing selections on non-default hierarchies. So I renamed my QueryDimension to QueryHierarchy. My initial implementation supports only member selections using the operators MEMBER, CHILDREN, INCLUDE_CHILDREN and DESCENDANTS. The remaining member selections: ANCESTOR and SIBLING, can be implemented in terms of the previous ones and I decided to postpone implementation of level selections.

Implementation: Select as you Drill

The implementation of member selection is centered in the idea of including/excluding nodes at drilling time. Every usage of the hierarchy in a query axis is translated into a MDX expression with the following structure
    Exclude(DrilldownMember(<include expression>,<drill expression>, RECURSIVE), <exclude expression>)
Those sets are generated with the following algorithm:
    Initialize the <include expression> with the "roots" of the QueryHierarchy

    for every drilled member M

        add M to the <drill expression>

        add to the <exclude expression> the excluded children of M
        add to the <include expression> the "orphans" of M
The roots of the query hierarchy are those selected members having no selected ancestors in the query hierarchy. And the orphans of a member are those members, descendants of that member, having no selected ancestor below that member.
Another key point of the implementation is the way I store selection state for the members. It's stored as a tree of MemberSelectionState (an implementation class) keeping the operator includes and excludes issued for the member. And the children of the node are the children members that: override the selection dictated by its ancestor, or have any descendant overriding such a selection. This way of storing selections allows improvements to the previous algorithm that produce MDX expressions proportional in length to the number of drills executed on that usage of the hierarchy (refer to the QueryHierarchy.updateDrillSets() method implementation for details.

Query adaptation for handling hierarchies instead of dimensions

Using query hierarchies instead of query dimensions has an impact on the class Query. I've renamed the methods referring to the dimensions: getDimension to getHierarchy and getDimensions to getHierarchies. I've added the method getAvailableHierarchies to list the hierarchies that can be added to the current query. For a QueryHierarchy to be available nor It nor any hierarchy in the same dimension can be used in any axis.

Next Steps

Augment QueryHierarchy with methods to expand levels, allowing presenting a pre-drilled hierarchy to the user, and implementing a method to test if a certain member is drillable in the query hierarchy (has selected descendants).
Implement Level, ANCESTOR and SIBLING selections
Add filtering capabilities to Query
Implement a faces component to allow member selection.

Antecedentes

Mi primer contacto con el lado Open Source de BI (ya había participado anteriormente en un proyecto con SAP BI) ha sido supervisando una implantación de Pentaho. Así que, cuando me enfrente al desarrollo de una pequeña utilidad que requería de funcionalidades BI, decidí utilizar algunos de los componentes básicos de esta suite. En concreto Mondrian como motor OLAP y JPivot para la representación de los datos.
Mientras que Mondrian me pareció muy sencillo de integrar, JPivot lo encontré bastante más engorroso y poco flexible y me puse a buscar alternativas. Dado que en ese mismo momento estaba esperimentando con la versión 2.0 de JSF, intenté encontrar alguna biblioteca de componentes JSF que proporcionase una funcionalidad similar a la de JPivot.
No la encontré y posteriormente el desarrollo de la citada utilidad acabó cancelándose.
De todos modos, personalmente la idea de tal bibloteca de componentes me parecía interesante y una buena forma de aprender el desarrollo de componentes JSF personalizados.Lo que me terminó por dar el empujon definitivo fue la liberación de la versión 1.0 de olap4j.
La idea de unos componentes de visualización BI, conectables a diversos proveedores OLAP mediante una interfase estandarizada (olap4j), y que pudiesen integrarse en un marco de desarrollo de Web tambien estandar (JSF 2.0) con solo añadir un archivo .jar al classpath del proyecto, cada vez me resultaba más atractiva.

---

Background

I initially got in touch with Open Source BI while managing a Pentaho deployment (previously I was involved in another project using SAP BI). So when I faced the development of a small utility requiring BI functionallity, I choose to use some of the foundations of this BI suite. Specifically Mondrian as the OLAP engine and JPivot for data visualization.

While Mondrian seemed very easy to integrate, JPivot was quite more cumbersome and unwieldy so I started looking for alternatives. I was experimenting with version 2.0 of JSF, at that moment, so I tried to find a JSF components library to provide JPivot like functionality.

I could not find it, and the utility development was finally cancelled.

Anyway, personally, the idea of such a component library seemed appealing, and was a nice path to learn about JSF custom components development. For me, the finall push was the release of version 1.0 of olap4j.

The vision of a suite of BI presentation-tier components, pluggable to several OLAP providers using an standarized interface (olap4j), and tha could be integrated into an also standard Web framework (JSF 2.0), by just dropping a .jar file into the project's classpath attracted me more and more.