Adding Treemap to JPivot

Corwin Joy
  • Corwin Joy
    Corwin Joy

    So, I want to add TreeMap visualization to the JPivot tool.  Here is a link to what TreeMap can do:

    Basically, TreeMap is a very nice way of quickly visualizing the data in a section of a hierarchical tree. The way I want it to work similarly to the Drill Through feature.  Basically when you click a Tree Drill Through Button it will write a file with the data from the drill through query for Mondrian and then launch the Treemap Java Applet.  (Hopefully this should be fairly easy to code). The idea is that at any point on the tree you can click for a quick visualization of the data below that node, something that will be especially useful for some of the more awkward to naviagate hierarchies.  The file format is fairly easy, I could write the data in either a tab delimited format ( or I may go with the more generic XML format they support (

    1. I should be able to just rework some of the code from either the chart or drillthrough to do this fairly easily, but I'm having a bit of trouble understanding it - any hints would be welcome here.

    2. TreeMap gives a lot of slice and dice stuff, but is only free for non-commercial use.  For commercial use I guess they charge something.  There is a very basic open source treemap
    but it does not have all the UI features and would involve much more coding by hand so I want to initially leverage off of the work from the University of Maryland.

    Any thoughts or suggestions?

    • Corwin Joy
      Corwin Joy

      I guess I should also mention that commercial versions of things like treemap are also increasingly used in financial applications - here is an example:

      As far as I know, though, this kind of viewer has not been integrated with a pivot table which in my mind will give an even more powerful kind of viewer.

    • Corwin Joy
      Corwin Joy

      Finally! I have an initial version of this up and running. The result is pretty nice. I placed the resulting mondrian.war on my BIOLAP site where I am working on extending JPivot at

      To use: Open the testpage.jsp as usual from the main index.jsp page. Drillthrough on a cell in the pivot table.  Below the drillthrough table you will see a cell that says "Get Drillthrough TreeMap".  This will launch an applet that shows a treemap of the drill through data.  By default, we set up a hierarchy starting with the first dimension in the cube.  Click on the Hierarchy tab to change the fields used for the heirarchy. Click on the "Legend" tab to choose what measures to use in plotting the data.

      -Get more fields than just the one measure in the drill through so that we can display additional measures.
      -Maybe try to integrate the Treemap more deeply into the pivot table so it could pick a starting dimension layout more intelligently.  I don't think this is all that big a deal since the applet makes it easy to pick alternate dimensions, but it would be nice to be able to click on one of the "+" signs in the pivot table and get an intelligent treemap display.

      I'll also need to co-ordinate as to posting my changes?

      • Sherman Wood
        Sherman Wood

        This is a very effective UI feature. The problems I see in including it in base JPivot are:

        1. The contribution has to use the same license as JPivot, namely CPL. The licensing of Treemap from the U of Maryland site will not work.

        2. Is it optional or is it part of the core JPivot feature set?

        3. Should this be a completely alternate UI compared to the JPivot WCF component/XSL generated one? ie. Is the use of Treemap broader than the drill through, replacing TableComponent?

        4. Should you include it as part of BIOLAP instead? Hmmm, you are not explicit on the licensing of BIOLAP - what open source license do you want to use?


    • Corwin Joy
      Corwin Joy

      Lets see
      1. Regarding the licensing.  I agree. The licensing of TreeMap is not CPL.  Instead it is free for non-commercial use and can be freely distributed.  (Which is usable for academic instituions like where I work). It might be nice to tie in the sourceforge GPLd treemap shown above, but this is very primitive and I'm not sure I want to put in a bunch of work bringing it up to speed.  Anyway, I can certainly publish code to link to the U of M treemap, make that CPL, and then others can decide if this is a useful add-on for them or not.

      2. Probably it should be optional.

      3. In principle it could be an alternative to the drill through.  I like tying it in to the JPivot display for a couple reasons though:

      a. Treemaps do a great job of displaying an overview of a wide hierarchy to about 3-4 levels deep.  Beyond that I think they get less understandable.  Hence I think that linking a treemap onto nodes in a JPiviot as a kind of drillthrough makes a lot of sense in terms of getting the best of both worlds: numeric overviews but also treemaps to quickly visualize a large part of the hierarchy rather than having to slowly navigate it.  I suppose one could have an equivalent kind of treemap drilldown that always went three levels deep and when you click on a square in the map it would then "zoom in" to show 3 levels down from that square.

      b. Having Treemap be able to leverage off of existing hierarchy and database tables makes sense to me.

      c. There is some value to having the numeric and graphical view side by side.  Just like the pivot table and the chart that comes with it.

      4. I plan to include this as part of BIOLAP, but I also think that a JPivot link to a treemap display makes sense.  Right now I am still kicking the tires some more on this tool, trying to see how it can best complement the existing JPivot view of things.  I am currently putting the finishing touches on having it pick up the "tms" or format file so that when you design a page you can specify a more sophisticated default tree format in terms of colors, hierarchy to display and so on.  Also I should mention that BIOLAP and these additions are all CPL - I picked the same license as JPivot uses for compatibility.


    • Andreas Voss
      Andreas Voss

      Glad to hear that you choose CPL for the BIOLAP code, so it can be easyly combined with Mondrian and JPivot. And I'm also glad that you consider to contribute the Treemap feature to JPivot. The TreeMap visualisation looks very interesting, I never heard of that type of UI before. I would appreciate to see it as a part of JPivot but I agree with Sherman that

      - the license has to be compatible with CPL so JPivot can be easily used in commercial applications. It seems to me that the MIT license of the project is compatible with CPL. So if your code supports the implementation it would make sense to add it to JPivot. You could add your "glue" code to the JPivot source tree and the applet jar to the binary distribution for example.

      - the feature should be optional, the exsisting functionality should not be changed by the addition, e.g. the new code should only run if the user clicks on the new link. The link you described below the drill-through table sounds good to me (although I could not try it out because I had trouble installing the mondrian.war).

      Because this is your first contribution I'd like to review the code before check in, can you please send me a diff? After that you will get CVS write access to check in the code.


      • Ed Burnette
        Ed Burnette

        Better than GPL, but CPL has some patent language in it that my companie is not comfortable with. Dual CPL/EPL would be better. CDDL looks interesting but the lawyers haven't let me know if that's acceptable or not. We have picky lawyers but I imagine we're not alone. Maybe a tri LGPL/CPL/EPL would cover all the bases.

    • Corwin Joy
      Corwin Joy

      O.K. I've done more research on open source treemap implementations so here are my thoughts:
      1. I've just about got the UMd treemap integration done so I will put out a zip of this source just so folks can see where I am going with this.

      2. I agree that the UMd license is not compatible.  The project looks very hard to use.  But, there is another sourceforge project that looks quite easy to use called  JTreemap
      Which can build a treemap from an XML file.  The problem is that it is a swing component so that takes work to make it an applet.

      3. Yes, I want to make it optional, in a way like the chart button where it would show you a graphical view of the drilldown.  Right now I've done this as a page link, but once I get further along probably it can be hooked up more elegantly by someone that understands the JPivot TableComponent class  better than I do so as to auto-generate a link as needed.

    • Julian Hyde
      Julian Hyde

      I only just noticed this discussion. I am excited to have another visualization technique for Mondrian/JPivot users!

      Treemaps seem to avoid the 'double accounting' problem which affects some charting techniques, in particular, pie charts.

      A pie chart commits the crime of double accounting if it displays an OLAP dataset which contains parent and child members. If you were to display a chart with 3 segments, 2005 = 17, 2005.Q1 = 10, 2005.Q2 = 7, then you will notice that the 2005 segment is always the same size as the combined Q1 and Q2 segments. The problem is that any fact contibutes to two segments (2005, and one of the quarters), and breaks principle that the charts that the segments add up to 100%.

      Treemaps don't suffer from this problem, because their data model isn't flat: it understands that some elements are nested inside parent elements.

      In principle, a treemap should be able to do all of the operations that a jpivot grid can:
      * expand (that is, show the children but keep the parent displayed),
      * collapse (the opposite of expand),
      * drill down (replace the parent with the children or with just one child),
      * drill through (show the relational data) and
      * sort.

      Do you intend to support these?

      Finally, it would be great if other Mondrian user-interfaces, such as Rubik, supported treemaps too. Any interest, Rubik guys?

    • Corwin Joy
      Corwin Joy

      You're absolutely right that in principle a treemap can do all the operations that a grid can.  In fact, if you download the UMd treemap tool ( ) this is exactly the way it works. Left click on a square to expand, right click to collapse / zoom back out. I'm not sure I'm up for that amount of work but plan to release a simpler version tomorrow.  Here is what I have:

      1. I went ahead and did another round of code to tie JPivot into JTreemap so it could use an open source tool.  The author (Laurent) was nice enough to provide an applet example.

      2. Due to laziness I have tied the JTreemap into the drillthrough for JPivot.  This allows JPivot to handle the navigation and then the treemap will display a 4 level deep hierarchy of the data.  The displayed hierarchy can be chosen by using the "E" option in JPivot to show/hide columns.

      This gets the job done but I'd like to do better.
      Where I want to take this next:
      1. For some goofy reason, JPivot only grabs one measure for the drillthrough.  A treemap intrinsically displays two measures at a time, one measure is shown by size, the other by color.  I'm planning to take a harder look at the JPivot code to get it to grab more measures.

      To go further, I'll probably need some help. Specifically:

      2. The treemap only shows a tree 4 levels deep because beyond that their visualization power does not work so well.  What I'm doing now is taking the drill through data and applying a default aggregation rule of "sum" to collapse information below the 4th level.  What I really want to do is modify the JPivot tree so as to add an "M" next to the "+" sign on the tree to expand nodes.  This "M" would then execute a mondrian query that expanded 4 levels deep along the selected dimension (something like select [This Dimension].[All Levels].[All Levels].[All Levels].[All Levels] ).
      The advantage of this would be:
      a. Nicer navigation.  No intermediate drill through table needed. Just show the graph immediately.
      b. Take advantage of the power of the different mondrian aggregation functions so that cells of the treemap can represent aggregates calculated in different ways.
      Sadly, I don't know how to do this part 2 since I don't understand the JPivot control very deeply or how to auto-generate the needed mondrian query.

      So, that's an update.  I plan to post a link tomorrow with source + binary with the initial version.

    • Corwin Joy
      Corwin Joy

      O.K. After some difficulties, I finally made a first release connecting JPivot to JTreeMap.
      Here is a screenshot:

      Mouse over the chart to see details for each square in the graph.

      The release can be found here
      under jpivot_jtreemap.
      The .war file is just the war of the resulting web application.  The file is the zipped
      source code with both mondrian & jpivot & jtreemap source.  All of the changed code can be found
      in the jpivot folder, the original jpivot code is in jpivot_backup.  I also had to enhance jtreemap
      a bit to get it to generate a mapped image file.

      Execute a drillthrough from the JPivot table.  At the bottom of the drill-through table you will
      see a button that says "export to Treemap".  Click the button to export the data to a treemap graph
      and display the graph at the bottom of the table. (You may have to click refresh from your browser
      to get the graph to show - still don't quite understand the JPivot paint mechanism).
      You can change what hierarchy will be used in the treemap by clicking the "E" button on the
      drillthrough and hiding all the columns you don't want used in the hierarchy.  I have made
      hidden column formats "persistant" subsequent drillthroughs in the page will hide the same

      Mouse over the treemap graph.  It will display information about the hierarchy used to get to
      each node, and the (value, weight) of the square.  Right now value=weight since drillthroughs
      only give one measure - until I can figure out how to change that.  The image is also a mapped
      image file.  I'm hoping to figure out how to hook web request links to the jpivot tool so
      that one can then just drill visually.

      - Figure out how to request drill-down actions from JTreemap.

      - Possibly move the JTreemap code from drillthrough to a node-expansion type icon in the
      top JPivot table.  This would allow me to auto-select the dimension used in the graph
      and might make it easier to write a query that drills down and grabs multiple measures.

      - Figure out how to get all measures in a row when drilling instead of just one measure.

    • Andreas Voss
      Andreas Voss

      Hey this looks great!

      Would you like to see your code to become part of JPivot? If so I'd be glad to help you to do so.

      There is a major change in the jpivot code base in that wcf has become a stand alone web gui library that can not import jpivot classes. So I think we should have a class, say DrillThroughTableComponent with its own tag that is derived from wcf TableComponent and contains the Treemap related code.


    • Corwin Joy
      Corwin Joy

      Yes!  I would very much like to make this part of JPivot since I think number and graphics representations side by side is a good way view information.  In terms of where to put the chart, it is fairly independent so it can go anywhere.  All it needs is
      1. A set of row data to display.  (Currently it uses a subset of the TableModel interface to grab rows).
      2. Optionally, a set of columns to hide. (I only did this as a cheap way of controlling the hierarchy columns to show from the drill down).

      Really, my ideal implementation would be
      1. To add a button like is done for chart, which would then add a decorator next to the "+" drill-down.  When clicked, this would display a treemap showing data 4 levels deep beneath that node (as per above message dated 2005-11-01 21:39, item 2).

      2. Understand if there is a way to use the mapped image that I have generated to get graphical drill-down.  I.e. is there a way to have an HTML link like testpage.jsp?drill=(cell location)  to drive drill down events on JPivot.

      This would be a nice elegant interface and in priciple fairly modular to the JPivot code.  I'm very open for suggestions on the best way to tie this in since you know the code base and I don't really.

    • Hello,

      I'm the one who develop JTreeMap.
      There is a new beta release here :

      If it can help you...

      I'm also open for suggestions.


    • Andreas Voss
      Andreas Voss

      Hi, finally I managed to review the sources you provided. I'd really like to see the treemap's in JPivot but I think it should be done in another way.

      I think treemaps should be another chart type. You plugged it on the drill-through data, but then you will only have a single measure. As far as I understood a treemap needs two measures, one for the size and one for the color. This is similar to a bar chart where you have red bars and green bars corresponding to different measures. In the chart package you have access to the result of the query and can - for example - pick the first two columns for color and size.

      What do you think?


      • Corwin Joy
        Corwin Joy

        I agree. I think that having the TreeMap be a chart type would make a lot more sense.  I only plugged it into the drilldown since that was an easy way for me to get at the data and I'm not that familiar with the code / don't know how to create a new chart type. Also, I'd ideally like a way to be able to have the html image map be able to drive drill-through events, so that folks could click on the image map and have that drive a query to expand down that dimension.  I can see how to do this with a mapped image <input> tag, but I would like to do it with an html image map since that allows one to hover the mouse over the chart and get additional information about the individual cells. Anyway, I don't really understand the existing code well enough to know how to take it further.  The treemap stuff is pretty modular so it just needs a set of data rows to hook to and can pretty much render a mapped image from that and go anywhere.  So, any direction you can give is much appreciated.


        • Andreas Voss
          Andreas Voss

          I will continue the discussion in the jpivot-devel mailing list.