Let's Drill: Inner Workings

Saturday, December 10, 2011

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.