Version 1: Drill-Enabled CellSet Table

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:cellSetAxis
forAxis="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.
<span
style="padding-left: #{m.member.depth}ex">

<h:commandButton

rendered="#{m.member.childMemberCount > 0}"

value="#{olapSample.isDrilled(component,m.position) ? '-' : '+'}"
/>

        action="#{olapSample.toggleDrill(component,m.position)}"
    <h:outputText
value="#{m.member.caption}"
/>

</span>


To support this markup we'll add two methods to our managed bean:

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

This is the code snippet for toggleDrill
    public
void toggleDrill(UIComponent c, List<Member> position)
            throws OlapException, SQLException {
        // Find the UICellSetAxis within the ancestors of 'c'
        while (c != null && !(c instanceof UICellSetAxis)) {
            c = c.getParent();
        }
        if (c == null)
            return;


        // Get the query axis based on the UICellSetAxis information
        final UICellSetAxis axisComponent = (UICellSetAxis) c;
        QueryAxis queryAxis = getQuery().getAxis(
                axisComponent.getCellSetAxis().getAxisOrdinal());


        // Toggle drill
        Member[] members = position.toArray(new Member[position.size()]);
        if (queryAxis.isDrilled(members))
            queryAxis.undrill(members);
        else
            queryAxis.drill(members);


        // Invalidate the CellSet caché
        cs = null;


    }


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.

So, the getQuery method is something like:
private Query getQuery() throws OlapException {

    if (query != null)

        return
query;



    Map<String, Object> viewMap = FacesContext.getCurrentInstance()

        .getViewRoot().getViewMap();

    Object savedQuery = viewMap.get("SavedQuery");

    if (savedQuery == null) {

            // There is no saved query, so we create the query used to
            // show our initial CellSet.

        query = initQuery();

    } else {

        query = QuerySaver.restoreQuery(getConnection(), savedQuery);

    }



    return
query;

}



I'm going to save the query state just before rendering the cellset. To make this I'll use attach a listener method to the preRenderComponent event for the <olap:cellSetTable>


<olap:cellSetTable
value="#{olapSample2.sampleCellSet}" …>

<f:event
type="preRenderComponent"
listener="#{olapSample.saveQuery}"/>

…

<olap:cellSetTable/>