Version 4: Filtering Capabilities

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

Screenshot of the sample application showing a filtered query and filter editor.
The logic used in previous versions to generate MDX expressions for query axis (ROWS, COLUMNS, CHAPTERS, etc.) cannot be used against the FILTER axis. One of the reasons for this is that it's based on the drill state of displayed members. Furthermore the semantics associated to those axes are completely different from the semantics of the slicer axis. Specifically the operator argument to include and exclude methods in the QueryHierarchy class has no sense in a slicer axis, as including a MEMBER will aggregate the measures of its DESCENDANTS, and is equivalent to aggregate all of its CHILDREN.
So I've decide to change the behavior of some QueryHierarchy's methods when the instance lives in a slicer axis (hierarchy.geAxis().getLocation() == Axis.FILTER):
  • include(Member m, Operator op), exclude(Member m, Operator op)Both methods ignore the op argument and assume Operator.DESCENDANTS instead.
  • isIncluded(Member m)Returns true if and only if m and all of its descendants are included.
  • isExcluded(Member m)
    Returns true if and only if m and all of its descendants are excluded. Equivalente to !isIncluded(m) for instances living in query axes.
I've also changed the <olap:queryHierarchyEditor> faces component, changing its appearance and behavior when editing a query hierarchy living in a slicer axis:
  • The operator dropdown menu disappears
  • The include/exclude buttons are replaced by tri-state checkbox buttons, checked when the member is included, unchecked when it's excluded, or mixed if neither isIncluded(m)
    nor isExcluded(m) return true.
I've created a new faces composite component to show the contents of the slicer axis (active filters for the current query) and allow them to remove hierarchies from that axe. It' a table with a row for each hierarchy showing the hierarchy's caption in the first column and a descriptive text of the filter in the second column. This text lists the included members for simple query expressions and shows an informative label to indicate a filter too complex to be detailed.

MDX Generation for WHERE clause

The package private method toOlap4j in the QueryAxis class delegates in the new toOlap4jFilter() method when generating the MDX expression for a slicer axis. This method generates a cross join of the expressions returned by the olap4jFilter() method for each hierarchy in the axis:
    private AxisNode toOlap4jFilter() {

        CrossJoinBuilder xJoin = new CrossJoinBuilder();

        for(QueryHierarchy h : hierarchies) {



new AxisNode(null, false, axis, null, xJoin.getJoinNode());


The MDX expression for a silcer QueryHierarchy is computed recursively based on the tree of included/excluded members:
  • Simple case: current node has no overriding children.The resulting expression is the MemberNode for the current member if it's included, void expression if it's not included.
  • Recursive case: current node has overriding children.
    • If the current node is excluded, return the union of the recursive expressions for every overriding children
    • If the current node is included, return the union of
      • computing the set of non-overriding children as EXCEPT(<currentNode>.CHILDREN, <set of overriding children>)
      • computing the union of the recursive expression for every overriding children
This is the current implementation; with an immersion parameter to carry the current descendants include/exclude sign.
ParseTreeNode toOlap4jFilter(SelectionTree selectionNode,

            Sign defaultSign) {

        Sign selectionSign = selectionNode.getStatus().getEffectiveSign(

                Operator.DESCENDANTS, defaultSign);

        if (!selectionNode.hasOverridingChildren()) {

            // Current node has no overriding children, its filter expression is

            // the corresponding MemberNode if the member is included, void in

            // other case.

            if (selectionSign == Sign.INCLUDE)

                return Mdx.member(selectionNode.getMember());



        } else {

            // Current node has overriding children

            UnionBuilder finalExpression = new UnionBuilder();

            if (selectionSign == Sign.INCLUDE) {

                // Current node is included, so overriding children are excluded

                // or have excluded descendants.

                UnionBuilder overridingChildren = new UnionBuilder();

                for (SelectionTree overriding : selectionNode

                        .getOverridingChildren()) {





                // Return the set of non overriding children plus recursive

                // expression evaluations




            } else {

                // Current node is excluded, returns the union of recursive

                // evaluation for overriding children.

                for (SelectionTree overriding : selectionNode

                        .getOverridingChildren()) {





            return finalExpression.getUnionNode();



FilterAxisInfo Composite Faces Component

The component to display the filter axis for the current query hides a standard <h:dataTable> and relays on a managed bean to compute the filter expression to generate the textual description of the filter and adapt the call to remove a hierarchy from the filter axis. This component has the following attributes:
  • value (instance of AbstractQueryBean)
    The query containing the axis to display (used to remove a hierarchy from the axis)
  • style, styleClass
    CSS style and CSS style class to be applied to the component
  • headerClassCSS style class to be applied to the column showing the hierarchy captions.
  • expressionClassCSS style class to be applied to the column showing the filter descriptions.
It generates a client event when the user removes a hierarchy from the filter axis. This code snippet is from the sample test page:


render=":form:filterAxisInfo :form:table :form:hierarchyEditor"/>



It uses default style classes and uses the client event to re-render itself, the result set table and the query hierarchy editor after filter hierarchy removal.


Version 3: Part II, Struggling with the Editing Hierarchy User Interface

Wednesday, January 18, 2012
You can download this version of the sample, and an improved version of the component library from here.
After redesigning QueryDimension to allow grater member inclusion functionality, I had to design a user interface to allow the user to exploit that functionality. I started by implementing a generic (non olap related) tree faces component to display the hierarchy and allow member navigation. I considered using a RichFaces or MyFaces Tomahawk tree component, but I've decided to implement a simple one to keep the project as selfcontained as possible. I'm not going to detail its design, suffice to say that it acts as <h:dataTable> but with a hierarchical data model implemented by a nested <treeItems> component, responsible of finding the children of a given data item.
My problem was providing an easy to use interface to let the user execute, at least, six actions (include or exclude for each of the basic operators: MEMBER, CHILDREN, DESCENDANTS) on every member of the hierarchy. Those were considered options:
  1. A contextual menu for each member.
    Probably the best solution but too complex, it will delay higher priority features (e.g.: filtering)
  2. Three buttons to toggle inclusion for each operator.The simplest solution to implement, but so many buttons would clutter the UI.
  3. An operator selector, global for the whole component, plus one button to toggle each member inclusion/exclusion.The chosen solution. It allows every inclusion/exclusion operation allowed by QueryHierarchy and is "window-savvy".
This is a screenshot of the resulting solution:

The color and font weight of the members label indicate if the member is actually included or not in the query hierarchy. The operator menu over the hierarchy tree allows the user to select the operator of the exclusion/inclusion and, finally, the button beside the members label allows the user to toggle inclusion/exclusion of that member using the selected operator. The image in the button tells the user if clicking the button will result in an inclusion or exclusion; and the small label for the button provides a reminder of the selected operator.


Version 3: Part I, Drilling CellSet Encapsulation

Friday, January 13, 2012

First of all: Happy New Year to everybody!!

During this holidays I've working on implementing capabilities visually edit a query: adding/removing of hierarchies and inclusion/exclusion of members of those hierarchies. I'll explain you my achievements in two blogs entries: this one explains the AbstractQueryBean and its usage to provide an easy to use drillable cellset table, the next one will explain components allowing selection of hierarchies and inclusion/exclusion of its members.

Anyway, for the impatient among us, this is a screenshot of "version 3", and a link to the source code.

An Abstract Managed Bean to Handle Query State

The version in my previous blog entry used a helper class to serialize/deserialize the Query instance, and put it explicitly into the ViewState. This strategy works but has two main drawbacks: it's not easily extensible, and the name used to store the serialized query into the view state can silently clash with the names of other objects.
After some refactoring cycles I've ended up with a better solution: use a ViewScoped managed bean to manage the state of the query, and use the standard Java serialization mechanism to save and restore that state. This bean will handle connection management, needed to support the query deserialization; and caching for the query CellSet result.
To accomplish the vision of an easily pluggable component library I've added an abstract class AbstractQueryBean intended to serve as a base class for concrete view
scoped managed beans. This class defines two abstract methods to be implemented by deriving classes:
  • OlapConnection initConnection()Classes derived from AbstractQueryBean must implement this method to return a connection to the olap4j provider.
  • Query initQuery()Classes derived from AbstractQueryBean must implement this method to return newly a initialized query.
This abstract class, included in the olap4j-faces jar, implements writeObject and readObject to serialize the query state. The implementations of those members parallel those of the previous helper class, with an important difference: after serializing the query state, writeObject tears down the connection with the olap4j provider.
As I've pointed before, this class provides also with caching for the resulting CellSet from the query. The method getCellSet() tests for a cached CellSet before executing the current query. Convenience methods included in this class altering the query state invalidate this cache, to force a query execution in the next call to getCellSet(). And there is a invalidateCellSet() method to force this cache invalidation if the user of the class modifies externally the state of the query.
I'll change this manual cache invalidation mechanism and replace it with another based on QueryListener as the one used in the original query package.
Follows a minimal example of how to use this class to create a managed bean to serve as backing bean for drillable cellset:
package olaptest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;

import org.olap4j.Axis;
import org.olap4j.OlapConnection;
import org.olap4j.OlapException;
import org.olap4j.mdx.IdentifierNode;
import org.olap4j.metadata.Cube;

import es.cgalesanco.faces.olap4j.managedBeans.AbstractQueryBean;
import es.cgalesanco.olap4j.query.Query;
import es.cgalesanco.olap4j.query.QueryAxis;
import es.cgalesanco.olap4j.query.QueryHierarchy;
import es.cgalesanco.olap4j.query.Selection.Operator;


@ViewScoped // View scoped to save the state of the query between requests

class SampleQueryBean extends AbstractQueryBean{

    private static final long serialVersionUID = 1L;
    protected OlapConnection initConnection() throws OlapException {
        Connection jdbcCn = DriverManager.getConnection();

        return jdbcCn.unwrap(OlapConnection.class);

    protected Query initQuery() throws OlapException {
        Cube c = getConnection().getOlapSchema().getCubes().get("Sales");
        Query query = new Query("MyQuery", c);
        QueryAxis columnsAxis = query.getAxis(Axis.COLUMNS);
        return query;

Composite Control To Encapsulate Drilling CellSet

Insisting on easy construction of olap4j/JSF applications I've encapsulated the previous sample of a drillable cellset into a composite component using the AbstractQueryBean. This version of the composite components adds functionality to remove a hierarchy from the query by clicking on a close button in the hierarchy header cell.
So, the following code snippet, includes a drillable cellset table using the previous sample query bean:
        <olap:queryCellSet id="table" query="#{sampleQueryBean}">

Yeah, that's all folks. And, a slightly more complicated version using AJAX to update the table on drills and hierarchy removals
    <h:form id="form">

        <olap:queryCellSet id="table" query="#{queryBean}">

            <f:ajax event="drill" render=":form:table"/>

            <f:ajax event="axisChange" render=":form:table"/>