1 Reply Latest reply on Jun 12, 2012 8:36 PM by Jonathan.Knight

    more on the pivot table processing in coherence

      hello, all:

      I posted a question regarding how to create a pivot table in coherence (coherence and pivot table

      Now I am on a project that my mgr wanted me to query a coherence cache and return a subset in "pivot" structure.

      Suppose I send a request from the client with these info: String[] rows, String[] columns, String[] data. Each of them represent rows area, columns area and data area as in a Excel table. The aggregation is limited to sum for now.

      I managed to create some code like this:
      public Map<Object, Object> sum(Filter filter, String properties, String[] targets){
                EntryAggregator[] entryAggregators = new EntryAggregator[targets.length];
                for(int idex = 0;idex<targets.length;idex++){
                     entryAggregators[idex] = new DoubleSum(targets[idex]);
                MultiExtractor me = new MultiExtractor(properties);
                EntryAggregator ca = CompositeAggregator.createInstance(entryAggregators);
                GroupAggregator ga = GroupAggregator.createInstance(me,ca);
                return aggregate(filter, ga);
      here properties represent rows: "getX,getY,getZ" ; targets represent data to aggreated on: targets[] {"getA","getB"}

      There are two relevant questions:
      1. I am not able to put the columns info into the code;
      2. the return type above is Map<Object, Object> , printing map looks like
      +{[Parts, Appliance, Trash compactor],[214.5, 1.0]}...+
      with 3 rows (X,Y,Z) in the first segment, and aggreated 2 values (A,B) in the 2nd segment.
      how can I exact these values from a map structure (it seems the key/value are map themselves), and design a "pivot" structure to host them?

      I hope I've made myself clear.

        • 1. Re: more on the pivot table processing in coherence
          Hi Johnny,

          Your question intrigued me so I thought I would do a bit of digging into how you might replicate a pivot table using aggregators. Well, it has taken me a few days and as it is quit a long subject I wrote it up on my blog rather than post it all in the forum http://thegridman.com/coherence/oracle-coherence-pivot-table-queries/

          You were on the right track with the GroupAggregator but there is a bit more to do to allow all the different combinations of rows, columns and values as well as filtering.

          I hope what I have written up is clear enough, just ask if you have more question.