Oracle Coherence Pivot Table Queries

by · June 12, 2012

This blog post is about how to perform queries in Oracle Coherence that replicate the sort of data you get from an Excel pivot table. The post was prompted by a question on the Coherence forum here “more on the pivot table processing in coherence”. The original poster had started down the right route using a GroupAggregator and I was quite intrigued by the requirement so I thought I would give it a go and write it up. In the post I am going to cover how to manually write the required Java code to represent steadily more complex pivot table queries. After we have seen how to do that I will show a generic method that can produce any pivot table query aggregator. Finally we will look at a simple structure we can use to hold the data returned from the aggregator.

Pivot Tables

Anyone who has used Excel in any sort of anger will know what a pivot table is. If you don’t then I am not going to explain them, but you can find plenty of documentation on the Microsoft site, for example: PivotTable reports 101.

Oracle Coherence Pivot Table

If we look at a Pivot Table we can see that it is just multiple nested Group By queries and luckily for us Coherence comes
with a GroupAggregator out of the box. A GroupAggregator just wraps another Aggregator and groups the returned values by the value extracted by a specified ValueExtractor. See the GroupAggregator JavaDocs

The examples in this article, both in Excel and the Java code all use the same Transaction data from a CSV file pivot-test.csv. The Excel spreadsheet used can also be downloaded from here pivot.xlsx the file was saved in .xlsx format using Office 2011 for the Mac. The screenshots of Excel are also from Office 2011 so they may look a little different to other versions of Office. For those of you yet to come over from the dark side that is Windows and embrace OSX there is a normal MS Office version of the spreadsheet here pivot.xls

The code example use the same domain model I have pinched in previous blog posts from the Coherence book – less code is always good and this saves me writing code. As I have said before, if you have not read the Coherence book you really should, it is excellent; you can get it from the publisher’s web site. Although the book is titled Coherence 3.5 and we are now on 3.7, and soon Oracle will bring out the next version, the book is still very relevant to anyone building Coherence applications. In this case we populate a cache with transactions created using the CSV file already mentioned. What this also means though is that these examples use a couple of special valueExtractor classes to extract BigDecimal values from the domain model, but they are just ValueExtractors and if you use any of the techniques described here in your own code any ValueExtractor will work that is appropriate to your own domain model.

Before we start, the code for populating the Transaction cache from the CSV file is here.

  1. private void loadTransactionsFromFile() throws Exception
  2. {
  3.     NamedCache transactions = CacheFactory.getCache("transactions");
  4.     transactions.clear();
  5.  
  6.     URL testDataURL = getClass().getResource("/pivot-test.csv");
  7.     BufferedReader reader = new BufferedReader(new FileReader(new File(testDataURL.toURI())));
  8.  
  9.     // skip the first line as it is headings
  10.     reader.readLine();
  11.  
  12.     DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy hh:mm");
  13.     String line = reader.readLine();
  14.     while (line != null)
  15.     {
  16.         String[] fields = line.split(",");
  17.         Transaction.Id id = new Transaction.Id(Long.parseLong(fields[0]), Long.parseLong(fields[1]));
  18.         Transaction transaction = new Transaction(id,
  19.                                                   TransactionType.valueOf(fields[2]),
  20.                                                   dateFormat.parse(fields[8]),
  21.                                                   fields[7],
  22.                                                   new Money(fields[4], fields[3]),
  23.                                                   new Money(fields[6], fields[5])
  24.         );
  25.         transactions.put(id, transaction);
  26.         line = reader.readLine();
  27.     }
  28. }
private void loadTransactionsFromFile() throws Exception
{
    NamedCache transactions = CacheFactory.getCache("transactions");
    transactions.clear();

    URL testDataURL = getClass().getResource("/pivot-test.csv");
    BufferedReader reader = new BufferedReader(new FileReader(new File(testDataURL.toURI())));

    // skip the first line as it is headings
    reader.readLine();

    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy hh:mm");
    String line = reader.readLine();
    while (line != null)
    {
        String[] fields = line.split(",");
        Transaction.Id id = new Transaction.Id(Long.parseLong(fields[0]), Long.parseLong(fields[1]));
        Transaction transaction = new Transaction(id,
                                                  TransactionType.valueOf(fields[2]),
                                                  dateFormat.parse(fields[8]),
                                                  fields[7],
                                                  new Money(fields[4], fields[3]),
                                                  new Money(fields[6], fields[5])
        );
        transactions.put(id, transaction);
        line = reader.readLine();
    }
}

Simple Single “Row”, Single Value Pivot Table Query

We will start with the most basic of Pivot Tables – one with a single row value and aggregating a single value from our data. We will create a pivot table that has the Account ID as the row and we will sum up the monetary amounts of the transactions.
In Excel using our example CSV file the pivot table would look like this…

…as you can see, it is very simple and it is also simple to create in Coherence

  1. // Row - Account ID
  2. ValueExtractor row = new PofExtractor(null, new SimplePofPath(new int[]{0, 0}));
  3.  
  4. // Value - Sum Transaction Amount
  5. InvocableMap.EntryAggregator value = new BigDecimalSum(new MoneyAmountExtractor(4));
  6.  
  7. // Create the Pivot Query
  8. InvocableMap.EntryAggregator pivotAggregator = GroupAggregator.createInstance(row, value);
  9.  
  10. // Do the query
  11. NamedCache transactions = CacheFactory.getCache("transactions");
  12. Map<Object, Object> pivotResults = (Map<Object, Object>) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
// Row - Account ID
ValueExtractor row = new PofExtractor(null, new SimplePofPath(new int[]{0, 0}));

// Value - Sum Transaction Amount
InvocableMap.EntryAggregator value = new BigDecimalSum(new MoneyAmountExtractor(4));

// Create the Pivot Query
InvocableMap.EntryAggregator pivotAggregator = GroupAggregator.createInstance(row, value);

// Do the query
NamedCache transactions = CacheFactory.getCache("transactions");
Map<Object, Object> pivotResults = (Map<Object, Object>) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);

A pivot table like this can be done in Coherence as a single GroupAggregator. The result returned from a GroupAggregator is a Map where the key is the equivalent of the pivot table “row” values (Account ID) and the map value is the corresponding pivot table value (Sum of Transaction Amount)
Looking at the values returned in the map we see…

0 = 13118.00
1 = 15701.00
100 = 11596.00
101 = 17810.00

…which matches the values in our simple pivot table in Excel.

Simple Single “Row”, Multiple Value Pivot Table Query

The next level of complexity is to keep the single row but now add another value, in this case we will add a count of transactions.
In Excel our new pivot table looks like this…

excel-pivot-one-row-two-values

In Coherence this is still a simple aggregator…

  1. // Row - Account ID
  2. ValueExtractor row = new PofExtractor(null, new SimplePofPath(new int[]{0, 0}));
  3.  
  4. // Values
  5. InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
  6.         {
  7.                 // Count of Transactions
  8.                 new Count(),
  9.                 // Sum Transaction Amount
  10.                 new BigDecimalSum(new MoneyAmountExtractor(4))
  11.         };
  12.  
  13. // Create the Pivot Query
  14. InvocableMap.EntryAggregator pivotAggregator =
  15.         GroupAggregator.createInstance(row, CompositeAggregator.createInstance(values));
  16.  
  17. // Do the query
  18. NamedCache transactions = CacheFactory.getCache("transactions");
  19. Map<Object, List> pivotResults = (Map<Object, List>) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
// Row - Account ID
ValueExtractor row = new PofExtractor(null, new SimplePofPath(new int[]{0, 0}));

// Values
InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
        {
                // Count of Transactions
                new Count(),
                // Sum Transaction Amount
                new BigDecimalSum(new MoneyAmountExtractor(4))
        };

// Create the Pivot Query
InvocableMap.EntryAggregator pivotAggregator =
        GroupAggregator.createInstance(row, CompositeAggregator.createInstance(values));

// Do the query
NamedCache transactions = CacheFactory.getCache("transactions");
Map<Object, List> pivotResults = (Map<Object, List>) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);

As you can see, this time we have used a CompositeAggregator as the inner aggregator of the GroupAggregator (see the CompositeAggregator Java Docs). The CompositeAggregator wraps all the aggregators that we want to use for the values of our pivot table. Because the outer aggregator is still a GroupAggregator the return of the aggregate call still returns a Map but this time the values in the map are lists of the corresponding results from our values aggregators.
We now have Account ID for the row and the values are Transaction Count and Sum of Transaction Amount. Looking at the contents of the Map we see this…

0   = [20, 13118.00]
1   = [28, 15701.00]
100 = [18, 11596.00]
101 = [27, 17810.00]

…which again matches the contents of our Excel pivot table.

We can obviously take this as far as we like by continuing to add values to the values CompositeAggregator and these will then be returned in the list of results.

Multiple “Row” Query

OK, now another level of complexity, we will add another row to the pivot table. In a pivot table each subsequent row is like a nested group by inside the first row. For the second row of our pivot table we will use the Transaction Type (this is either DEPOSIT or WITHDRAWAL). In Excel the pivot table looks like this.

Excel also keeps totals for the outer rows which you can see if you collapse the rows as shown below (note: other versions of Excel may show all the rows and totals without any collapsing).

You can see that the first two rows have been collapsed for Account ID 0 and Account ID 1 and show the totals for those rows.
We now have Account ID and Transaction Type as rows and the values are Transaction Count and Sum of Transaction Amount. In Coherence the code now gets a little more complex

  1. // Row
  2. ValueExtractor[] rows = new ValueExtractor[]
  3.         {
  4.                 // Account ID
  5.                 new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
  6.                 // Transaction Type
  7.                 new PofExtractor(null, 1),
  8.         };
  9.  
  10. // Values
  11. InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
  12.         {
  13.                 // Count of Transactions
  14.                 new Count(),
  15.                 // Sum Transaction Amount
  16.                 new BigDecimalSum(new MoneyAmountExtractor(4))
  17.         };
  18.  
  19. // Combine the values into a CompositeAggregator
  20. InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  21.  
  22. // Create the Pivot Query
  23. InvocableMap.EntryAggregator pivotAggregator =
  24.     GroupAggregator.createInstance(rows[0],
  25.            CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  26.                    valuesAggregator,
  27.                    GroupAggregator.createInstance(rows[1], valuesAggregator),
  28.            }));
  29.  
  30. // Do the query
  31. NamedCache transactions = CacheFactory.getCache("transactions");
  32. Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
// Row
ValueExtractor[] rows = new ValueExtractor[]
        {
                // Account ID
                new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
                // Transaction Type
                new PofExtractor(null, 1),
        };

// Values
InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
        {
                // Count of Transactions
                new Count(),
                // Sum Transaction Amount
                new BigDecimalSum(new MoneyAmountExtractor(4))
        };

// Combine the values into a CompositeAggregator
InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);

// Create the Pivot Query
InvocableMap.EntryAggregator pivotAggregator =
    GroupAggregator.createInstance(rows[0],
           CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                   valuesAggregator,
                   GroupAggregator.createInstance(rows[1], valuesAggregator),
           }));

// Do the query
NamedCache transactions = CacheFactory.getCache("transactions");
Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);

We now store our rows in a ValueExtractor array and values in an InvocableMap.EntryAggregator array. This is to make things easier later on. We still create a CompositeAggregator to hold the multiple values as we did in the last example. We still have a GroupAggregator for the first row but now it contains a CompositeAggregator that wraps both the second row’s GroupAggregator and the values aggregator.

If we look at the map returned by the aggregate call we see that for each outer row value the map contains a list. This list contains another list that is the values for the row and also contains a map which is the second inner row. The map for the second row has keys that are the second row values (Transaction Type) and a list of corresponding values.

0 = [
        [20, 13118.00],
        {
            WITHDRAWAL = [10, 5708.00]
            DEPOSIT = [10, 7410.00]
        }
    ],
1 = [
        [28, 15701.00],
        {
            WITHDRAWAL = [13, 8351.00]
            DEPOSIT = [15, 7350.00]
        }
    ],
100 = [
        [18, 11596.00],
        {
            WITHDRAWAL = [9, 6098.00]
            DEPOSIT = [9, 5498.00]
        }
    ],
101 = [
        [27, 17810.00],
        {
            WITHDRAWAL = [14, 10240.00]
            DEPOSIT = [13, 7570.00]
        }
    ]

Again we can see that the values returned from the aggregate call match the values in the Excel pivot table – phew! (yes I know they are not sorted in the same order but that is a minor thing).

As with adding values we can continue to add multiple rows by continuing to nest GroupAggregator instances. For example if we now wanted three rows so that we had Transaction Time for the third row we would do this.

  1.     // Row
  2.     ValueExtractor[] rows = new ValueExtractor[]
  3.             {
  4.                     // Account ID
  5.                     new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
  6.                     // Transaction Type
  7.                     new PofExtractor(null, 1),
  8.                     // Transaction Time - Just the Date part
  9.                     new DateWithoutTimeExtractor(2),
  10.             };
  11.  
  12.     // Values
  13.     InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
  14.             {
  15.                     // Count of Transactions
  16.                     new Count(),
  17.                     // Sum Transaction Amount
  18.                     new BigDecimalSum(new MoneyAmountExtractor(4))
  19.             };
  20.  
  21.     // Combine the values into a CompositeAggregator
  22.     InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  23.  
  24.     // Create the Pivot Query
  25.     InvocableMap.EntryAggregator pivotAggregator =
  26.             GroupAggregator.createInstance(rows[0],
  27.                    CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  28.                            valuesAggregator,
  29.                            GroupAggregator.createInstance(rows[1],
  30.                                       CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  31.                                                   valuesAggregator,
  32.                                                   GroupAggregator.createInstance(rows[2], valuesAggregator),
  33.                                       })),
  34.             }));
  35.  
  36.     // Do the query
  37.     NamedCache transactions = CacheFactory.getCache("transactions");
  38.     Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
    // Row
    ValueExtractor[] rows = new ValueExtractor[]
            {
                    // Account ID
                    new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
                    // Transaction Type
                    new PofExtractor(null, 1),
                    // Transaction Time - Just the Date part
                    new DateWithoutTimeExtractor(2),
            };

    // Values
    InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
            {
                    // Count of Transactions
                    new Count(),
                    // Sum Transaction Amount
                    new BigDecimalSum(new MoneyAmountExtractor(4))
            };

    // Combine the values into a CompositeAggregator
    InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);

    // Create the Pivot Query
    InvocableMap.EntryAggregator pivotAggregator =
            GroupAggregator.createInstance(rows[0],
                   CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                           valuesAggregator,
                           GroupAggregator.createInstance(rows[1],
                                      CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                                                  valuesAggregator,
                                                  GroupAggregator.createInstance(rows[2], valuesAggregator),
                                      })),
            }));

    // Do the query
    NamedCache transactions = CacheFactory.getCache("transactions");
    Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);

The results map will continue to contain nested lists and maps for row.

Adding Columns

The final level of complexity is to add columns to our pivot table. Columns are just more nested group queries so that is how we can implement them in Coherence. For the column example we will have two rows, Account ID and Transaction Time. For the values we will keep Transaction Count and Sum of transaction Amount. For the column we will use Transaction Type. In Excel our pivot table looks like this…

In Coherence we code it with more nested GroupAggregators but this time we nest the columns in a CompositeAggregator along with the values…

  1. // Row
  2. ValueExtractor[] rows = new ValueExtractor[]
  3.         {
  4.                 // Account ID
  5.                 new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
  6.                 // Transaction Time - Just the Date part
  7.                 new DateWithoutTimeExtractor(2),
  8.         };
  9.  
  10. // Values
  11. InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
  12.         {
  13.                 // Count of Transactions
  14.                 new Count(),
  15.                 // Sum Transaction Amount
  16.                 new BigDecimalSum(new MoneyAmountExtractor(4))
  17.         };
  18.  
  19. // Column - Transaction Type
  20. ValueExtractor column = new PofExtractor(null, 1);
  21.  
  22. // Combine the values into a CompositeAggregator
  23. InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  24.  
  25. // Create the columns Group By Aggregator wrapping the values aggregator
  26. InvocableMap.EntryAggregator columnsAggregator = GroupAggregator.createInstance(column, valuesAggregator);
  27.  
  28. // Combine the columns and values aggregators together
  29. InvocableMap.EntryAggregator columnsAndValuesAggregator =
  30.         CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]
  31.                                            {
  32.                                                valuesAggregator,
  33.                                                columnsAggregator,
  34.                                            });
  35.  
  36. // Create the Pivot Query
  37. InvocableMap.EntryAggregator pivotAggregator =
  38.     GroupAggregator.createInstance(rows[0],
  39.            CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  40.                    columnsAndValuesAggregator,
  41.                    GroupAggregator.createInstance(rows[1], columnsAndValuesAggregator),
  42.            }));
  43.  
  44. // Do the query
  45. NamedCache transactions = CacheFactory.getCache("transactions");
  46. Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
// Row
ValueExtractor[] rows = new ValueExtractor[]
        {
                // Account ID
                new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
                // Transaction Time - Just the Date part
                new DateWithoutTimeExtractor(2),
        };

// Values
InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
        {
                // Count of Transactions
                new Count(),
                // Sum Transaction Amount
                new BigDecimalSum(new MoneyAmountExtractor(4))
        };

// Column - Transaction Type
ValueExtractor column = new PofExtractor(null, 1);

// Combine the values into a CompositeAggregator
InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);

// Create the columns Group By Aggregator wrapping the values aggregator
InvocableMap.EntryAggregator columnsAggregator = GroupAggregator.createInstance(column, valuesAggregator);

// Combine the columns and values aggregators together
InvocableMap.EntryAggregator columnsAndValuesAggregator =
        CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]
                                           {
                                               valuesAggregator,
                                               columnsAggregator,
                                           });

// Create the Pivot Query
InvocableMap.EntryAggregator pivotAggregator =
    GroupAggregator.createInstance(rows[0],
           CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                   columnsAndValuesAggregator,
                   GroupAggregator.createInstance(rows[1], columnsAndValuesAggregator),
           }));

// Do the query
NamedCache transactions = CacheFactory.getCache("transactions");
Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);

On line 26 we create the GroupAggregator that represents our Columns.
On line 29 that we have created a CompositeAggregator that wraps the Columns GroupAggregator and the Values CompositeAggregator.
On line 37 where we put all this together you can see we now use the combined columns and values CompositeAggregator inside the GroupAggregators where we previously just used the values CompositeAggregator.

Our result map is getting a little large now so below is just the entry for the row of Account ID = 0.

0 = [
        [
            [20, 13118.00],
            {
                DEPOSIT = 10, 7410.00
                WITHDRAWAL = 10, 5708.00
            }
        ],
        {
            20/06/2012 = [
                            [4, 3620.00],
                            {
                                DEPOSIT = 2 1825.00
                                WITHDRAWAL = 2 1795.00
                            }

            21/06/2012 = [
                            [8, 5676.00],
                            {
                                DEPOSIT = 4 3248.00
                                WITHDRAWAL = 4 2428.00
                            }
            22/06/2012 = [
                            [4, 1439.00],
                            {
                                DEPOSIT = 2 885.00
                                WITHDRAWAL = 2 554.00
                            }
            23/06/2012 = [
                            [4, 2383.00],
                            {
                                DEPOSIT = 2 1452.00
                                WITHDRAWAL = 2 931.00
                            }
        }
    ]

For each entry in the map the value is a list.
The first entry in the list is another list and the first value in the nested list is the values for the row as a whole.
The second entry in the nested list is a Map representing the column values for the row.
The second entry is the outer list is the nested map for the next inner row.
The pattern is then repeated for each subsequent inner row.
We can see that these values again match the Excel pivot table – pretty cool.

Multiple Columns

To finish off we will look at multiple columns. Like multiple rows that are nested inside each other, columns work the same way and are also nested inside each other.

And here is the Coherence code…

  1. // Row
  2. ValueExtractor[] rows = new ValueExtractor[]
  3.         {
  4.                 // Account ID
  5.                 new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
  6.                 // Transaction Time - Just the Date part
  7.                 new DateWithoutTimeExtractor(2),
  8.         };
  9.  
  10. // Values
  11. InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
  12.         {
  13.                 // Count of Transactions
  14.                 new Count(),
  15.                 // Sum Transaction Amount
  16.                 new BigDecimalSum(new MoneyAmountExtractor(4))
  17.         };
  18.  
  19. // Columns
  20. ValueExtractor[] columns = new ValueExtractor[]
  21.         {
  22.                 // Transaction Time - Just the Date part
  23.                 new PofExtractor(null, 1),
  24.                 // Transaction Amount Currency
  25.                 new PofExtractor(null, new SimplePofPath(new int[]{4, 2}))
  26.         };
  27.  
  28. // Combine the values into a CompositeAggregator
  29. InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  30.  
  31. // Create the columns Group By Aggregator which now needs to be a CompositeAggregator
  32. InvocableMap.EntryAggregator columnsAggregator =
  33.         GroupAggregator.createInstance(columns[0],
  34.                                        CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  35.                                                valuesAggregator,
  36.                                                GroupAggregator.createInstance(columns[1], valuesAggregator),
  37.                                        }));
  38.  
  39. // Combine the columns and values aggregators together
  40. InvocableMap.EntryAggregator columnsAndValuesAggregator =
  41.         CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]
  42.                                            {
  43.                                                    columnsAggregator,
  44.                                                    valuesAggregator
  45.                                            });
  46.  
  47. // Create the Pivot Query
  48. InvocableMap.EntryAggregator pivotAggregator =
  49.         GroupAggregator.createInstance(rows[0],
  50.                                        CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  51.                                                columnsAndValuesAggregator,
  52.                                                GroupAggregator.createInstance(rows[1], columnsAndValuesAggregator),
  53.                                        }));
  54.  
  55. // Do the query
  56. NamedCache transactions = CacheFactory.getCache("transactions");
  57. Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
// Row
ValueExtractor[] rows = new ValueExtractor[]
        {
                // Account ID
                new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
                // Transaction Time - Just the Date part
                new DateWithoutTimeExtractor(2),
        };

// Values
InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
        {
                // Count of Transactions
                new Count(),
                // Sum Transaction Amount
                new BigDecimalSum(new MoneyAmountExtractor(4))
        };

// Columns
ValueExtractor[] columns = new ValueExtractor[]
        {
                // Transaction Time - Just the Date part
                new PofExtractor(null, 1),
                // Transaction Amount Currency
                new PofExtractor(null, new SimplePofPath(new int[]{4, 2}))
        };

// Combine the values into a CompositeAggregator
InvocableMap.EntryAggregator valuesAggregator = CompositeAggregator.createInstance(values);

// Create the columns Group By Aggregator which now needs to be a CompositeAggregator
InvocableMap.EntryAggregator columnsAggregator =
        GroupAggregator.createInstance(columns[0],
                                       CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                                               valuesAggregator,
                                               GroupAggregator.createInstance(columns[1], valuesAggregator),
                                       }));

// Combine the columns and values aggregators together
InvocableMap.EntryAggregator columnsAndValuesAggregator =
        CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]
                                           {
                                                   columnsAggregator,
                                                   valuesAggregator
                                           });

// Create the Pivot Query
InvocableMap.EntryAggregator pivotAggregator =
        GroupAggregator.createInstance(rows[0],
                                       CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                                               columnsAndValuesAggregator,
                                               GroupAggregator.createInstance(rows[1], columnsAndValuesAggregator),
                                       }));

// Do the query
NamedCache transactions = CacheFactory.getCache("transactions");
Map pivotResults = (Map) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);

Applying Filters

Pivot Tables allow results to be filtered and so does Coherence. In all of the examples above we have just used an AlwaysFilter.INSTANCE value for the filter in the aggregate call on the Transactions cache. If we wanted to filter the results to only pivot on certain transactions then we could use any normal Coherence Filter. Using our most basic pivot table from Excel we can filter it to only include Transactions where the type is DEPOSIT.

We can do the same in Coherence with a Coherence Filter

  1. // Row - Account ID
  2. ValueExtractor row = new PofExtractor(null, new SimplePofPath(new int[]{0, 0}));
  3.  
  4. // Value - Sum Transaction Amount
  5. InvocableMap.EntryAggregator value = new BigDecimalSum(new MoneyAmountExtractor(4));
  6.  
  7. // Create the Pivot Query
  8. InvocableMap.EntryAggregator pivotAggregator = GroupAggregator.createInstance(row, value);
  9.  
  10. // Create the Filter on Transaction Type = DEPOSIT
  11. EqualsFilter filter = new EqualsFilter(new PofExtractor(null, 1), TransactionType.DEPOSIT);
  12.  
  13. // Do the query
  14. NamedCache transactions = CacheFactory.getCache("transactions");
  15. Map<Object, Object> pivotResults = (Map<Object, Object>) transactions.aggregate(filter, pivotAggregator);
// Row - Account ID
ValueExtractor row = new PofExtractor(null, new SimplePofPath(new int[]{0, 0}));

// Value - Sum Transaction Amount
InvocableMap.EntryAggregator value = new BigDecimalSum(new MoneyAmountExtractor(4));

// Create the Pivot Query
InvocableMap.EntryAggregator pivotAggregator = GroupAggregator.createInstance(row, value);

// Create the Filter on Transaction Type = DEPOSIT
EqualsFilter filter = new EqualsFilter(new PofExtractor(null, 1), TransactionType.DEPOSIT);

// Do the query
NamedCache transactions = CacheFactory.getCache("transactions");
Map<Object, Object> pivotResults = (Map<Object, Object>) transactions.aggregate(filter, pivotAggregator);

We have created an EqualsFilter to match Transactions with Type equal to DEPOSIT and use this in the aggregate call. And the results we see from the map are…

0 = 7410.00
1 = 7350.00
100 = 5498.00
101 = 7570.00

which, of course, yet again, match the values shown in Excel.

Performance

At this point a quick word about performance. The code in this blog has been written using the standard Coherence GroupAggregator and CompositeAggregator all nested inside each other. For a complex pivot table this will result in multiple nested aggregators and extractors and is nowhere near as efficient as writing a custom aggregator that can produce a pivot table structure. A custom pivot table aggregator is probably something for another blog post though otherwise I would never get this one out the door.

A Generic Method to Build the Pivot Table Aggregator

In the examples above we have always hand coded the GroupAggregator based on the scenario at the time. It would be better if we could write a single method that took the required rows, values, columns and filter and built the GroupAggregator. So to start with the basic signature of our method will look like this…

  1. private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
  2.     ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
  3. {
  4.     return null;
  5. }
private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
    ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
{
    return null;
}

…where we pass in the information required to create our pivot table aggregator.

Now lets think about what our aggregator will need to do. We can do this by looking back at the examples above.

  • Create the CompositeAggregator that wraps up all of the values.
  • Create the nested GroupAggregator for any columns
  • Create the nested GroupAggregator for any rows

The first part is very simple

  1. private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
  2.     ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
  3. {
  4.     // Create Values Aggregator
  5.     CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  6.  
  7.     return null;
  8. }
private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
    ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
{
    // Create Values Aggregator
    CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);

    return null;
}

The next part involves creating the nested columns aggregators. We need to create a GroupAggregator for each column, where we nest that column aggregator inside any previous columns in our list – so we need some sort of reverse iteration.

  1. private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
  2.     ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
  3. {
  4.     // Create Values Aggregator
  5.     CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  6.  
  7.     // Create the first Column Aggregator - this is from the last entry in the columns array
  8.     int columnIndex = columns.length - 1;
  9.     InvocableMap.EntryAggregator columnsAggregator =
  10.             GroupAggregator.createInstance(columns[columnIndex--], valuesAggregator);
  11.  
  12.     // Iterate backwards over any remaining columns wrapping each prior aggregator
  13.     for (; columnIndex >= 0; columnIndex--)
  14.     {
  15.         columnsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  16.                 valuesAggregator,
  17.                 GroupAggregator.createInstance(columns[columnIndex], columnsAggregator)
  18.         });
  19.     }
  20.  
  21.     // Combine the columns and values aggregators together
  22.     InvocableMap.EntryAggregator columnsAndValuesAggregator =
  23.             CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]
  24.                                                {
  25.                                                        valuesAggregator,
  26.                                                        columnsAggregator
  27.                                                });
  28.  
  29.     return null;
  30. }
private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
    ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
{
    // Create Values Aggregator
    CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);

    // Create the first Column Aggregator - this is from the last entry in the columns array
    int columnIndex = columns.length - 1;
    InvocableMap.EntryAggregator columnsAggregator =
            GroupAggregator.createInstance(columns[columnIndex--], valuesAggregator);

    // Iterate backwards over any remaining columns wrapping each prior aggregator
    for (; columnIndex >= 0; columnIndex--)
    {
        columnsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                valuesAggregator,
                GroupAggregator.createInstance(columns[columnIndex], columnsAggregator)
        });
    }

    // Combine the columns and values aggregators together
    InvocableMap.EntryAggregator columnsAndValuesAggregator =
            CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]
                                               {
                                                       valuesAggregator,
                                                       columnsAggregator
                                               });

    return null;
}

On line 8 we create the first column GroupAggregator using the last column ValueExtractor from the columns array (using the columnIndex we initialised on the previous line).
On line 12 we iterate backwards through any remaining entries in the columns ValueExtractor array creating a GroupAggregator for that column wrapping the previous GroupAggregator then combining that in a CompositeAggregator with the values aggregator.
Finally on line 21 we combine top level column GroupAggregator and the values together in a CompositeAggregator.

Now we have our values and columns we need to do the rows. If you remember, rows are nested GroupAggregators just like the columns so we can use the same reverse iterator technique to create them. This time though the inner aggregator used by the rows will be either the combined columns and values aggregator we just created, or if there are no columns then it will just be the values aggregator.
First we will change the code above to create the correct inner aggregator depending on whether there are columns or not.

  1. private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
  2.     ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
  3. {
  4.     // Create Values Aggregator
  5.     CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  6.  
  7.     // This is the aggregator that will be used as the inner aggregator by the rows
  8.     InvocableMap.EntryAggregator columnsAndValuesAggregator;
  9.  
  10.     if (columns.length > 0)
  11.     {
  12.         InvocableMap.EntryAggregator columnsAggregator = valuesAggregator;
  13.  
  14.         // Iterate backwards over any columns wrapping each prior aggregator
  15.         // but stop at column[1] - that is, do not do column[0]
  16.         for (int columnIndex = columns.length - 1; columnIndex > 0; columnIndex--)
  17.         {
  18.             columnsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
  19.                     valuesAggregator,
  20.                     GroupAggregator.createInstance(columns[columnIndex], columnsAggregator)
  21.             });
  22.         }
  23.  
  24.         // Create a GroupAggregator for column[0]
  25.         columnsAggregator =
  26.                 GroupAggregator.createInstance(columns[0], columnsAggregator);
  27.  
  28.         // Combine the columns and values aggregators together
  29.         columnsAndValuesAggregator =
  30.                 CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
  31.                                                            valuesAggregator,
  32.                                                            columnsAggregator
  33.                                                    });
  34.     }
  35.     else
  36.     {
  37.         // We have no columns so the rows will just use the values aggregator
  38.         columnsAndValuesAggregator = valuesAggregator;
  39.     }
  40.  
  41.     return null;
  42. }
private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
    ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
{
    // Create Values Aggregator
    CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);

    // This is the aggregator that will be used as the inner aggregator by the rows
    InvocableMap.EntryAggregator columnsAndValuesAggregator;

    if (columns.length > 0)
    {
        InvocableMap.EntryAggregator columnsAggregator = valuesAggregator;

        // Iterate backwards over any columns wrapping each prior aggregator
        // but stop at column[1] - that is, do not do column[0]
        for (int columnIndex = columns.length - 1; columnIndex > 0; columnIndex--)
        {
            columnsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
                    valuesAggregator,
                    GroupAggregator.createInstance(columns[columnIndex], columnsAggregator)
            });
        }

        // Create a GroupAggregator for column[0]
        columnsAggregator =
                GroupAggregator.createInstance(columns[0], columnsAggregator);

        // Combine the columns and values aggregators together
        columnsAndValuesAggregator =
                CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
                                                           valuesAggregator,
                                                           columnsAggregator
                                                   });
    }
    else
    {
        // We have no columns so the rows will just use the values aggregator
        columnsAndValuesAggregator = valuesAggregator;
    }

    return null;
}

you can see we now wrap the columns code inside an if statement on line 9 as there is nothing to do if we have no columns. The columnsAndValuesAggregator declared on line 7 is the aggregator that will be used inside the rows and is set either to the combined columns and values aggregator on line 28 or if we have no columns it is set to the values aggregator on line 37.

Now we can add the code to do the rows; as we have already said this works the same way as the columns code.

  1. private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
  2.     ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
  3. {
  4.     // Create Values Aggregator
  5.     CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);
  6.  
  7.     // This is the aggregator that will be used as the inner aggregator by the rows
  8.     InvocableMap.EntryAggregator columnsAndValuesAggregator;
  9.  
  10.     if (columns.length > 0)
  11.     {
  12.         InvocableMap.EntryAggregator columnsAggregator = valuesAggregator;
  13.  
  14.         // Iterate backwards over any columns wrapping each prior aggregator
  15.         // but stop at column[1] - that is, do not do column[0]
  16.         for (int columnIndex = columns.length - 1; columnIndex > 0; columnIndex--)
  17.         {
  18.             columnsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
  19.                     valuesAggregator,
  20.                     GroupAggregator.createInstance(columns[columnIndex], columnsAggregator)
  21.             });
  22.         }
  23.  
  24.         // Create a GroupAggregator for column[0]
  25.         columnsAggregator =
  26.                 GroupAggregator.createInstance(columns[0], columnsAggregator);
  27.  
  28.         // Combine the columns and values aggregators together
  29.         columnsAndValuesAggregator =
  30.                 CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
  31.                                                            valuesAggregator,
  32.                                                            columnsAggregator
  33.                                                    });
  34.     }
  35.     else
  36.     {
  37.         // We have no columns so the rows will just use the values aggregator
  38.         columnsAndValuesAggregator = valuesAggregator;
  39.     }
  40.  
  41.     InvocableMap.EntryAggregator rowsAggregator = columnsAndValuesAggregator;
  42.  
  43.     // Iterate backwards over the rows wrapping each prior aggregator
  44.     // but stop at row[1] - that is, do not do row[0]
  45.     for (int rowIndex = rows.length - 1; rowIndex > 0; rowIndex--)
  46.     {
  47.         rowsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
  48.                 columnsAndValuesAggregator,
  49.                 GroupAggregator.createInstance(rows[rowIndex], rowsAggregator)
  50.         });
  51.  
  52.     }
  53.  
  54.     // Create a GroupAggregator for row[0]
  55.     return GroupAggregator.createInstance(rows[0], rowsAggregator);
  56. }
private InvocableMap.EntryAggregator createPivotAggregator(ValueExtractor[] rows,
    ValueExtractor[] columns, InvocableMap.EntryAggregator[] values)
{
    // Create Values Aggregator
    CompositeAggregator valuesAggregator = CompositeAggregator.createInstance(values);

    // This is the aggregator that will be used as the inner aggregator by the rows
    InvocableMap.EntryAggregator columnsAndValuesAggregator;

    if (columns.length > 0)
    {
        InvocableMap.EntryAggregator columnsAggregator = valuesAggregator;

        // Iterate backwards over any columns wrapping each prior aggregator
        // but stop at column[1] - that is, do not do column[0]
        for (int columnIndex = columns.length - 1; columnIndex > 0; columnIndex--)
        {
            columnsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
                    valuesAggregator,
                    GroupAggregator.createInstance(columns[columnIndex], columnsAggregator)
            });
        }

        // Create a GroupAggregator for column[0]
        columnsAggregator =
                GroupAggregator.createInstance(columns[0], columnsAggregator);

        // Combine the columns and values aggregators together
        columnsAndValuesAggregator =
                CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[] {
                                                           valuesAggregator,
                                                           columnsAggregator
                                                   });
    }
    else
    {
        // We have no columns so the rows will just use the values aggregator
        columnsAndValuesAggregator = valuesAggregator;
    }

    InvocableMap.EntryAggregator rowsAggregator = columnsAndValuesAggregator;

    // Iterate backwards over the rows wrapping each prior aggregator
    // but stop at row[1] - that is, do not do row[0]
    for (int rowIndex = rows.length - 1; rowIndex > 0; rowIndex--)
    {
        rowsAggregator = CompositeAggregator.createInstance(new InvocableMap.EntryAggregator[]{
                columnsAndValuesAggregator,
                GroupAggregator.createInstance(rows[rowIndex], rowsAggregator)
        });

    }

    // Create a GroupAggregator for row[0]
    return GroupAggregator.createInstance(rows[0], rowsAggregator);
}

So that is our completed method; we return the rowsAggregator, which is what we want to execute aginst the cache. We now have a generic method that can create a suitable aggregator for any rows, columns and values we want. You might want to add some error checking into make sure we have at least one row and at least one value as the pivot query is a bit usless without them.

A Pivot Table Structure

One last thing to sort out is the data returned from the aggregate call. At the moment this is a nested map of maps and lists which can be a bit awkward. It might be better to create a class that can hold the pivot data and allow us to use it in a structured way.
Lets think about what our structure is; we basically need something to represent each row of the pivot table – lets call it PivotTableEntry. If the class represents a row in a pivot table, what attributes can it have? There is an Object to hold the identifier for the row and a list of the aggregated results for that row. What else can we have? We can have nested rows, so we will use a Map of PivotTableEntry instances keyed on their ID to represent the child rows. Finally we can have columns – if we think back these look just like rows so we will use another Map of PivotTableEntry instances keyed on their ID. Because a PivotTableEntry can be a row or a column I am going to add a type field to identify which it is.

  1. public class PivotTableEntry
  2. {
  3.     public static enum Type {ROW, COLUMN}
  4.  
  5.     private Object id;
  6.  
  7.     private Type type;
  8.  
  9.     private List<Object> values;
  10.  
  11.     private Map<Object,PivotTableEntry> rows;
  12.  
  13.     private Map<Object,PivotTableEntry> columns;
  14.  
  15.     public PivotTableEntry(Object id, Type type)
  16.     {
  17.         this.id = id;
  18.         this.type = type;
  19.         this.values = new ArrayList<Object>();
  20.         this.rows = new LinkedHashMap<Object,PivotTableEntry>();
  21.         this.columns = new LinkedHashMap<Object,PivotTableEntry>();
  22.     }
  23. }
public class PivotTableEntry
{
    public static enum Type {ROW, COLUMN}

    private Object id;

    private Type type;

    private List<Object> values;

    private Map<Object,PivotTableEntry> rows;

    private Map<Object,PivotTableEntry> columns;

    public PivotTableEntry(Object id, Type type)
    {
        this.id = id;
        this.type = type;
        this.values = new ArrayList<Object>();
        this.rows = new LinkedHashMap<Object,PivotTableEntry>();
        this.columns = new LinkedHashMap<Object,PivotTableEntry>();
    }
}

So there is the start of our class. We can obviously add appropriate getter methods for the various fields.

We can also have a class to represent the pivot table as a whole. We will obviously call this PivotTable and it can contain a Map of the top level row PivotTableEntry instances, keyed on ID.

  1. public class PivotTable
  2. {
  3.     private Map<Object,PivotTableEntry> pivotEntries;
  4.     private ValueExtractor[] rows;
  5.     private ValueExtractor[] columns;
  6.     private InvocableMap.EntryAggregator[] values;
  7.  
  8.     public PivotTable(Map pivotData, ValueExtractor[] rows, ValueExtractor[] columns,
  9.         InvocableMap.EntryAggregator[] values)
  10.     {
  11.         this.rows = rows;
  12.         this.columns = columns;
  13.         this.values = values;
  14.         this.pivotEntries = new LinkedHashMap<Object,PivotTableEntry>();
  15.     }
public class PivotTable
{
    private Map<Object,PivotTableEntry> pivotEntries;
    private ValueExtractor[] rows;
    private ValueExtractor[] columns;
    private InvocableMap.EntryAggregator[] values;

    public PivotTable(Map pivotData, ValueExtractor[] rows, ValueExtractor[] columns,
        InvocableMap.EntryAggregator[] values)
    {
        this.rows = rows;
        this.columns = columns;
        this.values = values;
        this.pivotEntries = new LinkedHashMap<Object,PivotTableEntry>();
    }

In the constructor of the PivotTable we pass in the Map returned from the aggregate call on the cache, then the rows, columns and values used for the pivot.

OK, so we have a structure, now we need to populate it from the Map we get back from the aggregate call to the cache. We need to iterate over the entries of map and create a row (PivotTableEntry) for each entry in the map. The PivotTableEntry can then populate itself from the entry in the map.

Populating the PivotTableEntry

Lets think about how an entry will populate itself. The PivotTable is going to crate an entry that is of type “row” and pass the corresponding List from the map that contains the data for that row. But what does the list contain? That depends on the structure of the pivot table, that is how many rows, columns and values it has. Here is a list of the possibilities…

  • 1 row 1 value = List of values
  • 1 row many values = List of values
  • 1 row many values 1 column = List [List of values, Map columns]
    Map column: key = column ID, value = List of values
  • many rows many values = List [List of Values, Map rows]
  • many rows many values 1 column = List [List{List of values, Map of columns}, Map of sub-rows]
    Map of columns : key = column ID, value = List of values
  • many rows many values many columns = List [List{List of values, Map of columns}, Map of sub-rows]
    Map of column: key = column ID, value = List[List of values, Map of sub-columns]

So, to explain a bit better…
If we have one row and zero columns the List is always the list of values.
If we have one row and one or more columns the List contains two entries, the first is the List of values for the row, the second is the map of column data.
If we have many rows and no columns the List has two entries, the first is the list of values for the row, the second is the map of sub-rows.
If we have many rows and one or more columns the List has two entries, the first is a list with two further entries, the list of values and the map of column data, the second is the map of sub-rows.
…now that is clear we can write some code so that a PivotTableEntry can populate itself.

  1. public class PivotTableEntry
  2. {
  3.     public static enum Type {ROW, COLUMN}
  4.  
  5.     private Object id;
  6.  
  7.     private Type type;
  8.  
  9.     private List<Object> values;
  10.  
  11.     private Map<Object, PivotTableEntry> rows;
  12.  
  13.     private Map<Object, PivotTableEntry> columns;
  14.  
  15.     public PivotTableEntry(Object id, Type type)
  16.     {
  17.         this.id = id;
  18.         this.type = type;
  19.         this.values = new ArrayList<Object>();
  20.         this.rows = new LinkedHashMap<Object, PivotTableEntry>();
  21.         this.columns = new LinkedHashMap<Object, PivotTableEntry>();
  22.     }
  23.  
  24.     public void populate(List<Object> data, int rowCount, int columnCount)
  25.     {
  26.         this.clear();
  27.  
  28.         Map subRowData = null;
  29.         Map columnData = null;
  30.         if (columnCount == 0)
  31.         {
  32.             if (rowCount > 1)
  33.             {
  34.                 this.values.addAll((List) data.get(0));
  35.                 subRowData = (Map) data.get(1);
  36.             }
  37.             else
  38.             {
  39.                 this.values.addAll(data);
  40.             }
  41.         }
  42.         else
  43.         {
  44.             if (rowCount > 1)
  45.             {
  46.                 List list = (List) data.get(0);
  47.                 this.values.addAll((List) list.get(0));
  48.                 columnData = (Map) list.get(1);
  49.                 subRowData = (Map) data.get(1);
  50.             }
  51.             else
  52.             {
  53.                 this.values.addAll((List) data.get(0));
  54.                 columnData = (Map) data.get(1);
  55.             }
  56.         }
  57.  
  58.         if (subRowData != null)
  59.         {
  60.             for (Map.Entry entry : (Set<Map.Entry>) subRowData.entrySet())
  61.             {
  62.                 PivotTableEntry subRow = new PivotTableEntry(entry.getKey(), this.type);
  63.                 switch (type)
  64.                 {
  65.                     case ROW:
  66.                         subRow.populate((List) entry.getValue(), rowCount - 1, columnCount);
  67.                         this.rows.put(subRow.getId(), subRow);
  68.                         break;
  69.                     case COLUMN:
  70.                         subRow.populate((List) entry.getValue(), rowCount - 1, columnCount - 1);
  71.                         this.columns.put(subRow.getId(), subRow);
  72.                         break;
  73.                 }
  74.             }
  75.         }
  76.  
  77.         if (columnData != null)
  78.         {
  79.             for (Map.Entry entry : (Set<Map.Entry>) columnData.entrySet())
  80.             {
  81.                 PivotTableEntry column = new PivotTableEntry(entry.getKey(), Type.COLUMN);
  82.                 column.populate((List) entry.getValue(), rowCount - 1, columnCount - 1);
  83.                 this.columns.put(column.getId(), column);
  84.             }
  85.         }
  86.     }
  87.  
  88.     private void clear()
  89.     {
  90.         this.values.clear();
  91.         for (PivotTableEntry row : rows.values())
  92.         {
  93.             row.clear();
  94.         }
  95.         for (PivotTableEntry column : columns.values())
  96.         {
  97.             column.clear();
  98.         }
  99.     }
  100. }
public class PivotTableEntry
{
    public static enum Type {ROW, COLUMN}

    private Object id;

    private Type type;

    private List<Object> values;

    private Map<Object, PivotTableEntry> rows;

    private Map<Object, PivotTableEntry> columns;

    public PivotTableEntry(Object id, Type type)
    {
        this.id = id;
        this.type = type;
        this.values = new ArrayList<Object>();
        this.rows = new LinkedHashMap<Object, PivotTableEntry>();
        this.columns = new LinkedHashMap<Object, PivotTableEntry>();
    }

    public void populate(List<Object> data, int rowCount, int columnCount)
    {
        this.clear();

        Map subRowData = null;
        Map columnData = null;
        if (columnCount == 0)
        {
            if (rowCount > 1)
            {
                this.values.addAll((List) data.get(0));
                subRowData = (Map) data.get(1);
            }
            else
            {
                this.values.addAll(data);
            }
        }
        else
        {
            if (rowCount > 1)
            {
                List list = (List) data.get(0);
                this.values.addAll((List) list.get(0));
                columnData = (Map) list.get(1);
                subRowData = (Map) data.get(1);
            }
            else
            {
                this.values.addAll((List) data.get(0));
                columnData = (Map) data.get(1);
            }
        }

        if (subRowData != null)
        {
            for (Map.Entry entry : (Set<Map.Entry>) subRowData.entrySet())
            {
                PivotTableEntry subRow = new PivotTableEntry(entry.getKey(), this.type);
                switch (type)
                {
                    case ROW:
                        subRow.populate((List) entry.getValue(), rowCount - 1, columnCount);
                        this.rows.put(subRow.getId(), subRow);
                        break;
                    case COLUMN:
                        subRow.populate((List) entry.getValue(), rowCount - 1, columnCount - 1);
                        this.columns.put(subRow.getId(), subRow);
                        break;
                }
            }
        }

        if (columnData != null)
        {
            for (Map.Entry entry : (Set<Map.Entry>) columnData.entrySet())
            {
                PivotTableEntry column = new PivotTableEntry(entry.getKey(), Type.COLUMN);
                column.populate((List) entry.getValue(), rowCount - 1, columnCount - 1);
                this.columns.put(column.getId(), column);
            }
        }
    }

    private void clear()
    {
        this.values.clear();
        for (PivotTableEntry row : rows.values())
        {
            row.clear();
        }
        for (PivotTableEntry column : columns.values())
        {
            column.clear();
        }
    }
}

Now the PivotTableEntry populate method is done we can go back and add this to the PivotTableClass

  1. public class PivotTable
  2. {
  3.     private Map<Object,PivotTableEntry> pivotEntries;
  4.     private ValueExtractor[] rows;
  5.     private ValueExtractor[] columns;
  6.     private InvocableMap.EntryAggregator[] values;
  7.  
  8.     public PivotTable(Map pivotData, ValueExtractor[] rows, ValueExtractor[] columns,
  9.         InvocableMap.EntryAggregator[] values)
  10.     {
  11.         this.rows = rows;
  12.         this.columns = columns;
  13.         this.values = values;
  14.         this.pivotEntries = new LinkedHashMap<Object,PivotTableEntry>();
  15.         populate(pivotData);
  16.     }
  17.  
  18.     public void populate(Map<Object,List> pivotData)
  19.     {
  20.         for (Map.Entry<Object,List> rowEntry : pivotData.entrySet())
  21.         {
  22.             PivotTableEntry row = new PivotTableEntry(rowEntry.getKey(), PivotTableEntry.Type.ROW);
  23.             row.populate(rowEntry.getValue(), rows.length, columns.length);
  24.             this.pivotEntries.put(row.getId(), row);
  25.         }
  26.     }
  27. }
public class PivotTable
{
    private Map<Object,PivotTableEntry> pivotEntries;
    private ValueExtractor[] rows;
    private ValueExtractor[] columns;
    private InvocableMap.EntryAggregator[] values;

    public PivotTable(Map pivotData, ValueExtractor[] rows, ValueExtractor[] columns,
        InvocableMap.EntryAggregator[] values)
    {
        this.rows = rows;
        this.columns = columns;
        this.values = values;
        this.pivotEntries = new LinkedHashMap<Object,PivotTableEntry>();
        populate(pivotData);
    }

    public void populate(Map<Object,List> pivotData)
    {
        for (Map.Entry<Object,List> rowEntry : pivotData.entrySet())
        {
            PivotTableEntry row = new PivotTableEntry(rowEntry.getKey(), PivotTableEntry.Type.ROW);
            row.populate(rowEntry.getValue(), rows.length, columns.length);
            this.pivotEntries.put(row.getId(), row);
        }
    }
}

And thats it, now we have a class that represents a PivotTable. Obviously there is a bit missing; I have not included any accessor methods to get the data, but I think they are pretty obvious to write now we have a proper structure. You could even go as far as a fancy toString that prints out a nice pivot table, but I have not done this yet as I want to actually post the blog and not spend months tweaking the code – I’m sure all you readers are smart enough to work it out, or ask if you can’t.

A quick example of using all the above code…

  1. // Row
  2. ValueExtractor[] rows = new ValueExtractor[]
  3.         {
  4.                 // Account ID
  5.                 new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
  6.                 // Transaction Time - Just the Date part
  7.                 new DateWithoutTimeExtractor(2),
  8.         };
  9.  
  10. // Values
  11. InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
  12.         {
  13.                 // Count of Transactions
  14.                 new Count(),
  15.                 // Sum Transaction Amount
  16.                 new BigDecimalSum(new MoneyAmountExtractor(4))
  17.         };
  18.  
  19. // Columns
  20. ValueExtractor[] columns = new ValueExtractor[]
  21.         {
  22.                 // Transaction Type
  23.                 new PofExtractor(null, 1),
  24.                 // Transaction Amount Currency
  25.                 new PofExtractor(null, new SimplePofPath(new int[]{4, 2}))
  26.         };
  27.  
  28. InvocableMap.EntryAggregator pivotAggregator = createPivotAggregator(rows, columns, values);
  29.  
  30. NamedCache transactions = CacheFactory.getCache("transactions");
  31. Map<Object, Object> pivotResults = (Map<Object, Object>) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
  32. PivotTable table = new PivotTable(pivotResults, rows, columns, values);
// Row
ValueExtractor[] rows = new ValueExtractor[]
        {
                // Account ID
                new PofExtractor(null, new SimplePofPath(new int[]{0, 0})),
                // Transaction Time - Just the Date part
                new DateWithoutTimeExtractor(2),
        };

// Values
InvocableMap.EntryAggregator[] values = new InvocableMap.EntryAggregator[]
        {
                // Count of Transactions
                new Count(),
                // Sum Transaction Amount
                new BigDecimalSum(new MoneyAmountExtractor(4))
        };

// Columns
ValueExtractor[] columns = new ValueExtractor[]
        {
                // Transaction Type
                new PofExtractor(null, 1),
                // Transaction Amount Currency
                new PofExtractor(null, new SimplePofPath(new int[]{4, 2}))
        };

InvocableMap.EntryAggregator pivotAggregator = createPivotAggregator(rows, columns, values);

NamedCache transactions = CacheFactory.getCache("transactions");
Map<Object, Object> pivotResults = (Map<Object, Object>) transactions.aggregate(AlwaysFilter.INSTANCE, pivotAggregator);
PivotTable table = new PivotTable(pivotResults, rows, columns, values);

Further Thoughts

The code above might not be the most optimal in the world, but as I already touched on, I wanted to get the blog posted and the ideas out there rather than spend a long time on the code. The code works and you are free to do what you want with it.

Coherence aggregators are very powerful and at my current client we use them for a number of quite complex things, such as cross cache joins. This could easily be combined with the code above to give cross cache pivot tables. You can pivot on far more data in a Coherence cache than you could probably hold in an Excel spreadsheet.

I could try and brush up my C# and convert the code above which could then (if I knew how) even be integrated into Excel – or I might be talking rubbish here with me not being a Windows C#/Office developer :-)

Tags:

You may also like...

6 Responses

  1. Ashwin Jayaprakash says:
    June 13, 2012 at 00:35

    Very cool!

    Reply
  2. Andrew Wilson says:
    June 13, 2012 at 08:58

    Nice work mate!

    Reply
  3. Gaurav Tomar says:
    June 20, 2012 at 08:48

    Great post Mate…

    Reply
  4. Dave says:
    June 20, 2012 at 10:13

    Interesting post Jonathan

    Reply
  5. internship for electronics engineering students says:
    January 29, 2013 at 11:50

    I think this is one of the most vital info for me. And i’m glad reading your article. But should remark on some general things, The website style is great, the articles is really nice : D. Good job, cheers

    Reply
  1. Builder Patterns and Coherence Pivot Tables | KCL Blog
    April 18, 2013

    […] They way they can be used is within pivot tables within Coherence. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

Follow: