olappentahomondriandata-presentationolap4j

Presentation of data from Mondrian OLAP engine + Olap4j


I'm doing a little bit of planning of an application that uses Mondrian OLAP engine with Olap4j and should present/display data to user. I understand all the back-end stuff, but I'm not sure how should I display the data in the view layer.

For example olap4j has a formatter that prints the SELECT nicely into the console.

How is the data that I get from olap4j displayed in view layer ? I just went through the olap4j API, and there doesn't seem to be anything for getting the result in a form that can be somehow further processed and displayed. Is this process part of the Pentaho solution ? So that otherwise it is really not easy to present data just from Mondrian OLAP engine and olap4j ?

EDIT: I'm used to traditionally get some data from a database into my DTO and display it in view layer. But how do I create DTOs for such a complicated result set ?


Solution

  • You can create your own view layer it's just a little bit tricky.

    OlapStatement.executeOlapQuery() returns a CellSet, you will have to work with that. Also read the specifications, it's a good source of information.

    Here is an example, that creates List<List<MyCell>> (not the best representation but it's easy to undarstand how it works). This creates a table similar to http://www.olap4j.org/api/index.html?org/olap4j/Position.html (without the "Gender" and "Product" labels).

    private final static int COLUMNS = 0; //see Cellset javadoc
    private final static int ROWS= 1; //see Cellset javadoc
    /**
    * Outer list: rows, inner list: elements in a row
    */
    private List<List<MyCell>> getListFromCellSet(CellSet cellSet) {
        List<List<MyCell>> toReturn= new ArrayList<List<MyCell>>();
        //Column header
        //See http://www.olap4j.org/api/index.html?org/olap4j/Position.html on how Position works, it helps a lot
        //Every position will be a column in the header
        for (Position pos : cellSet.getAxes().get(COLUMNS).getPositions()) {
            for (int i = 0; i < pos.getMembers().size(); i++) {
                if (toReturn.size() <= i) {
                    toReturn.add(i, new ArrayList<MyCell>());
                }
                Member m = pos.getMembers().get(i);
                MyCell myCell = new MyCell(m); //use m.getCaption() for display
                toReturn.get(i).add(myCell );
            }
        }
        //Put empty elements to the beginning of the list, so there will be place for the rows header
        if (cellSet.getAxes().get(ROWS).getPositions().size() > 0) {
            for (int count=0; count < cellSet.getAxes().get(1).getPositions().get(0).getMembers().size(); count++) {
                for (int i = 0; i < toReturn.size(); i++) {
                    toReturn.get(i).add(0, new MyCell());
                }
            }
        }
        //Content + row header
        for(int i = 0; i < cellSet.getAxes().get(ROWS).getPositionCount(); i++) {
            List<MyCell> row = new ArrayList<MyCell>();
            //Header
            for (org.olap4j.metadata.Member m : cellSet.getAxes().get(ROWS).getPositions().get(i).getMembers()) {
                row.add(new MyCell(m));
            }
            //Content
            for (int j = 0; j < cellSet.getAxes().get(COLUMNS).getPositionCount(); j++) {
                ArrayList<Integer> list = new ArrayList<Integer>();
                list.add(j); //coordinte
                list.add(i); //coordinte
                row.add(new MyCell(cellSet.getCell(list))); //use cell.getFormattedValue() for display
            }
            toReturn.add(row);
        }
        return toReturn;
    }
    

    Create the MyCell class with these constructors:

    public class MyCell {   
        ...
        public MyCell(){...}
        public MyCell(Member m){...}
        public MyCell(Cell c){...}  
    }
    

    Don't forget to display the filters, use Cellset.getFilterAxis() for that.

    You can also check the Rectangular formatter on SourceForge, but it's a bit longer.