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 {

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 Madd 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.

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

Screenshot of the version 1 sample webapp. Using an standard color scheme and
image buttons for drilling.

This entry describes how to leverage olap4j, JSF standard components and the drilling capability presented in my previous entry to create a drillable cellset table alla JPivot. First I explain how to add the necessary drill buttons, and then I describe the strategy I've chosen to save the query state between requests.﻿﻿

Adding Drill Buttons

Let's start modifying the contents in the <olap:cellSetAxisforAxis="rows"/>. I'll add a <h:commandButton/> to let the user drill/undrill a member in the cellset table. This button must be rendered only if the member has children, and will show a '-' if the member is already drilled or a '+' otherwise. This is the corresponding Facelet markup. <spanstyle="padding-left: #{m.member.depth}ex">

boolean isDrilled(UIComponent source, List<Member> position);This method receives a component and a positioned member, and returns a boolean value indicating if that member is drilled or not.

boolean toggleDrill(UIComponent source, List<Member> position);This method receives a component and a positioned member, and modifies the current query to change the drill status of the positioned member.

The isDrilled method has a similar structure. The only point remaining to be explained is the getQuery call, it's related to the query state saving strategy.

Query State Saving Strategy

The OlapSample managed bean is a request-scoped bean, so it doesn't keep state between requests. But, to make the table functional, we need to keep the drill state of current query between requests. So I'll save the query state in the ViewState. This would be the code:Map<String, Object> viewState = FacesContext.getCurrentInstance().getViewRoot().getViewMap();

// Put the query in the View State

viewState.put("SavedQuery", query);

// Get the query from the View State

query = (Query)viewState.get("SavedQuery");

Unfortunately it won't work… Query instances cannot be serialized (mainly because they contain references to database connections), so they can't be added to the ViewState; I need helper class to create a serializable object from a Query instance and to reconstruct the original query from that object. That class, QuerySaver, has to static public methods:

Object saveQuery(Query q)Generates the serializable object from the query. It will be an array of objects containing the names of the query, the source cube, axis dimensions, drilled members, etc.

Query restoreQuery(OlapConnection cn, Object state)This method receives an object produced by saveQuery and an OlapConnection and recreates the original query.

This is the first of two
entries documenting the process of adding drilling capabilities to OLAP-Faces. This
entry starts explaining my reasons to rewritethe org.olap4j.query
package and later describes the drilling capability added to that rewrite.

In the following entry I will apply this
new package to add drilling capabilities to the <olap:cellSet> JavaServer Faces
component and will provide you with a working example.

A Hard Decision: Rewrite the query Package

The current version of the specification provides
the class DrillDownOnPositionTransform to drill a positioned member within a CellSet, it should work like:

// Create a query

Query q = new Query("myquery",
salesCube);

QueryDimension productDim =
q.getDimension("Product");

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

q.getAxis(Axis.ROWS).addDimension(productDim);

q.getAxis(Axis.COLUMNS).addDimension(measuresDim);

q.validate();

CellSet cs = q.execute();

// Generate the MDX to for drilling

DrillDownOnPositionTransform
drillTransform =

new DrillDownOnPositionTransform(Axis.ROWS, 0, 0, cs);

SelectNode drilledMdx =
drillTransform.apply(q.getSelect());

// Execute new query to get the
drilled CellSet

OlapStatement stmt =
salesCube.getSchema().getCatalog().getMetaData()

.getConnection().createStatement();

CellSet drilledCs = stmt.executeOlapQuery(drilledMdx);

Unfortunately that class is not implemented
in the released version of olap4j (1.0.0.445) and I’ve not been able to find a
simple enough implementation; mainly because such an implementation must accept
as input a generic MDX expression.

I think that drilling in a cell set is a must,
so my proposal is: adding drilling support into the QueryAxis class. I’ll rewrite a
highly simplified version of the org.olap4j.query
package to produce a proof of concept for this
approach.

In this code all the classes in org.olap4j.query
package have been replaced by classes in the es.cgalesanco.olap4j.query package.

Translating QueryAxis.drill() into MDX

Drilling a one-dimensional axis is easy:
just use DrilldownMember MDX function passing the initial member set for the dimension as
the first parameter, the set of drilled members as the second parameter and
request for recursive drill resolution.

For example:

DrillDownMember(

{[Store].[All Stores]},

{[Store].[All
Stores],[Store].[USA],[Store].[USA].[OR]},

RECURSIVE)

Produces an axis with the following
structure

|-All Stores

|-Canada

|-Mexico

|-USA

|-CA

|-OR

| |-Portland

| |- Salem

|-WA

An easy way to extend this to
multi-dimensional axes is

1.generate a DrillDonwMember call,
grouping all the drills having the same prefix (drill specifications with the
same length differing only in the last element)

2.put these expressions in CrossJoins to generate a set of tuples as required for the axis

3.compute the union of all the
previously generated cross joins and hierarchize

So for an axis with two dimensions ([Store]
and [Store Type]) and the following sequence of drills

1.[Store].[All Stores]

2.[Store].[USA], [Store
Type].[All Store Types]

3.[Store].[USA]

4.[Store].[All Stores].[Store Type].[All
Store Types]

The generated MDX will be

Hierarchize(

Union(

CrossJoin(

DrillDownMember(

{[Store].[All Stores]},

{[Store].[All Stores],[Store].[USA]},

RECURSIVE

),

[Store Type].[All Store Types]

),

CrossJoin(

{[Store].[USA]},

DrillDownMember(

{[Store Type].[All Store Types]},

{[Store Type].[All Store Types]},

RECURSIVE

)

),

CrossJoin(

{[Store].[All Stores]},

DrillDownMember(

{[Store Type].[All Store Types]},

{[Store Type].[All Store Types]},

RECURSIVE

)

)

)

)

This is a basic
algorithm and clearly optimizable, but it will do for my proof of concept.

QueryAxis’ New Methods

So our revamped QueryAxis will contain an additional
list of drilled positions, supported with these new methods

void drill(Member[]
drilledPos);

This method adds drilledPos to the
list of drilled positions. If that position was already drilled it’s a no-op.

void
undrill(Member[] drilledPos);

This method removes
drilledPos from the list of drilled positions.

boolean
isDrilled(Member[] drilledPos);

This method
returns a boolean value indicating if drilledPosis in the list of drilled positions.

You can download the source code for the v0 component library and the sample web project here.

The focus for this initial version is about
rendering an arbitrary cellset result. In the previous post I proposed a component
structure with a main UICellSet component acting as a container of two UICellSetAxis component instances,
one for each CellSetAxis, and a UICellSetCells component to handle the data cells.

I will use the delegated implementation rendering model for those components. So,
following the pattern used in the JSF API, I’ll extend those component classes
to handle HTML specific properties and methods. The resulting classes will be

·HtmlCellSet, extending UICellSet

·HtmlCellSetAxis,
extending UICellSetAxis, and

·HtmlCellSetCells, extending UICellSetCells

These classes will have an associated set
of renderer classes: HtmlCellSetRenderer, HtmlCellSetCellsRenderer, HtmlColumnsAxisRenderer and HtmlRowsAxisRenderer. The last two renderers both render a HtmlCellSetAxis, but are specialized
to render a columns (ordinal 0) axis and a rows (ordinal 1) axis. This is
achived overriding the HtmlCellSetAxis.getRenderType() method to return a different type of renderer based in the type of
CellSetAxis its bound to

@Override

public String
getRendererType() {

if (isFor(TableArea.columnAxis))

return"es.cgalesanco.faces.olap4j.columnsCellSetAxis";

else

return"es.cgalesanco.faces.olap4j.rowsCellSetAxis";

}

HtmlCellSetRenderer, drives the global rendering of a CellSet.

·encodeBegin() renders the starting <table> element, the upper-left corner
cell, and delegates (indirectly) on HtmlColumnsAxisRenderer the rendering of the <tr>
tags for the rows containing the columns axis. In
the last row of the columns axis, it delegates on HtmlRowsAxisRenderer to render the
header cells for the rows axis.

·encodeChildren() renders the cell set rows containing the rows axis and the data
cells. Renders the <tr> elements and computes the first cell of the rows axis to be
rendered in the row (taking into account previous cells rows spans); delegates
the rendering of the cells on HtmlRowsAxisRenderer
and HTmlCellSetCellsRenderer.

The following colored HTML shows which
renderers renders which tag:

<table>

<colgroup>

<col/>

</colgroup>

<tr>

<th>&nbsp;</th><th colspan=”2”>Measures</th>

</tr>

<tr>

<th>Store</th><th>Unit
Sales</th></th>Store Cost</th>

</tr>

<tr>

<th>All Stores</th><td>266,733</td><td>225,627.23</td>

</tr>

<tr>

<th>USA</th><td><td>266,733</td><td>225,627.23</td>

</tr>

</table>

HtmlCellSetRenderer

HtmlRowsAxisRenderer

HtmlColumnsAxisRenderer

HtmlCellSetCellsRenderer

Styling the CellSet

This distribution of responsibilities
rendering the table is used to style the table using the properties of the HTML
components. The properties of these components are

HtmlCellSet

·styleClass. The HTML style class passed through to the class attribute
of the main <table> element.

·cornerClass. The HTML style class passed through to the <th> element
rendering the corner cell.

·alternateClass. The HTML style class passed through to the <tr> elements
starting the odd rows rendered by the HtmlCellSetRenderer (the gray rows
in the previous colored HTML)

HtmlCellSetAxis

·styleClass. The HTML style class passed through to the <tr>
element rendered by the HtmlColumnsAxisRenderer or the <col> element rendered by the HtmlRowsAxisRenderer.

·headerClass. The HTML style class passed through to the <th>
elements containing the hierarchy headers.

Wrapping It Up

First, the @ManagedBean backing our sample CellSetTable;
just change the getConnection() method to fit your olap4j provider and connection string. Caching
the resulting CellSet is important, as the method getSampleCellSet() can be invoked
repeatedly within the faces components.

It has the structure discussed in my
previous post; I’ve added the class attributes to allow styling and you can see
how I’ve implemented member indentation in the rows axis using the Member.getDepth()method.

And this is the CSS styles I’ve used to
render the table. They pretend to be as pedagogical as possible, so I beg your
pardon about the color scheme.