PALO – spreadsheet OLAP goes public domain?
I mentioned the new open source OLAP server PALO (OLAP backwards) in an earlier post. This TM1 workalike is still at an early stage, but any new offering in this area - especially if it is free - is of great interest to spreadsheet OLAP users.
In this post; I will give readers some idea of what this software offers, what it promises and what it lacks. I’ve put together some screenshots and added some thoughts on this entry.
Some of the people behind PALO were earlier involved with MIS AG’s spreadsheet product Alea, and PALO looks quite similar. There’s a long history here which may be worth telling elsewhere, but what matters in this context is that the designers of this software “get it” about how OLAP and spreadsheets should work together.
What’s more, the product is not cheap, it’s actually free - released under the orthodox Gnu Public License (GPL) which restricts distribution only by forbidding the recipients to restrict further redistribution, including source code.
But don’t plan to replace your corporate TM1 applications with a free replacement anytime soon - PALO is missing many key TM1 features, including security, advanced calculation engine, fast imports, views, subsets and others. Some of the missing features are in the Roadmap, others may take a long time to come.
And while the server is available under the GPL, clients come under a different set of licenses and restrictions, including the free but proprietary Excel client and several completely commercial clients as well, including a web server called the Worksheet Server.
Still, this is a very interesting entry in the public domain world. The server runs on Linux and Windows; the most “native” client, of course, is Excel, but it comes with a Linux Eclipse add-in to browse tables as well. You can download and install Palo from here.
Palo has a few flashy screen shots on its web site, but real spreadsheet OLAP users will want to know a bit more. I’ve put together a few screen shots to show how it looks.
PALO is a server-based system, with an Excel front end on the model pioneered by Applix TM1. The server holds the data; spreadsheets connect to the server over the network and bring in data as needed.
Here are some screenshot showing a simple session:
Connecting to a local server:
Selecting a view:
Simply selecting “paste” provides the following excel spreadsheet.
The “PALO.DATAC” formula for the value contains a reference for the server and to the dimension elements that identify the correct intersection.
The string “Gross Profit” is also a reference to a database value, in this case a dimension element. It uses the “PALO.ENAME” formula, which pulls out a dimension element.
If we double-click on “Gross Profit” we get a “drill-down” as follows”
Similarly, if we double-click on “Variance” we get the following:
Looking at this view, we can see as well that the “title elements” - products, region, month and year are also defined by “PALO.ENAME” formulas.
Double-click on one of these, and we get a selection box like the following:
This is a normal outline view, and we can open it by clicking on the “+” signs, to produce a view as follows:
Selecting, for example, France, we get the following:
We can make “stacked slices by dragging the “Product” dimension to the “Column titles” area as follows:
This will give us a spreadsheet like this:
And double-clicking on “All products” will open that dimension out as follows:
Dragging and dropping a few other dimensions and elements around, we look at the same data in a completely different way something like the following:
This sort of an OLAP view is very effective in uncovering data problems - in this case, it seems the test database hasn’t been updated since 2004.
The nice thing about these spreadsheets is that they are not “magic” - we can save them and retrieve them, and on recalculation they will again retrieve the latest data from the server. We can insert a column, add a regular spreadsheet formula and format it, and everything will behave the way we expect, like this:
If we take every dimension down to the bottom level, we can type in new values which are then sent to the database. So if we want to look at budgets and enter actuals, we can work with a view like this:
If we put the cursor on January Actuals for Desktop L, we can type in the number “323″, press the Enter key, and get this screen:
The number has been sent to the server, and we are looking at a view of server data again. Of course, in the absence of security, there’s no way to make sure the user doesn’t “correct” actuals to values she likes better either 
These slice spreadsheets can be saved and retrieved, and when recalculated with the PALO add-in, they will reflect the current status of the database from the server.
Aside from spreadsheets, though, there is no way to store views. And there is no way to store subsets either, although subsets for views can be manipulated with some basic operations. Here’s an element selection window:
Shortcomings
In short, the product looks like it’s got the basics. But it has critical shortcomings too. Of course, are all those missing features. But looking at the roadmap, it seems they are well understood and at least some of them will be here soon. Other weaknesses concern me more.
- A key shortcoming is documentation. I tried to connect from my Excel client to a linux server without success — the connection software asks for a login and a port number, but there’s no documentation on how to set these. Indeed, while there’s documentation on the API and the source code is open, the user side is almost completely undocumented.
- There’s little evidence that the developers of PALO are taking advantage of the internet infrastructure for open source development, or trying to attract a community of contributors outside of their walls. There’s no site on Sourceforge or Savannah, which host much other GPL software. There is no wiki, no automated bug reporting such as Buzilla or launchpad; their linux software is not packaged for Red Hat or Debian. Prospective users may wonder whether the software will develop rapidly or have a long life if it depends on the fortunes of the Jedox company, and does not foster feedback and contributions from a broad community..
- There are unresolved questions about how the free software model of the server will blend with the commercial angles of the clients. Is anyone thinking of a client for Open Office Calc, for example? How will Jedox respond if a group decides to produce a free web client that competes with their commercial one
In sum, PALO has enough features to make it interesting. It probably won’t challenge the corporate sweet spot of its commercial competitors, and it hasn’t shown a deep involvement with the open source community. On the other hand, the availability of a free spreadsheet OLAP product may open up new horizons for this genre, especially in smaller and non-profit environments. And it might attract the interest of open source heavy hitters like Sun or IBM, which would turn a very interesting corner indeed. Barring that, the future of PALO will depend upon how the community of users, developers and implementors take to it, and how well they are able to take advantage of its open source paradigm. We will be following this with interest.
Add comment July 25th, 2006
