OLAP: A Spreadsheet on Steroids

If you’re an accountant, you probably love your spreadsheets, and if you’re like me, you have on occasion taken them to their limits. Even with a state-of-the-art, function-rich package like Excel, sooner or later you simply run out of tools to model all the facets of your business.

Take the challenge of presenting information with more than a couple of dimensions. Spreadsheets are great for presenting information in 2 dimensions – sales by rep, for example, and even 3 dimensions – sales by rep by territory using multiple linked worksheets – but once you get into much more than 3 dimensions, things start to get messy. All those links become progressively harder to manage, and errors take longer and longer to track down, if they get tracked down at all.

This is where OLAP (online analytical processing) comes in. OLAP tools allow you to store information with many dimensions, and then recall it easily by referring to the subset of dimensions you want to see. As an example, take the case where sales numbers have a rep, but they also have a geography, a product and associated quantity, a quarter, and may be provided free as a demo. You can present this sort of information on a spreadsheet, but it’s tricky. An OLAP tool allows you to store all of this information against a sales number for quick and easy recall.

There are a lot a lot of these tools on the market at a wide range of price points and degrees of complexity – Cognos, from Ottawa, is a dominant player. One I came across lately is a product from Jedox (www.jedox.com) called PALO (that’s OLAP backwards – took me a while to figure that one out). It’s a Microsoft Excel add-in, a compact piece of software that is easy to install and use. Best of all, the basic PALO engine is free and open-source. It’s remarkably powerful for freeware, though you should consider buying the optional .pdf manual (19 Euro). Jedox was founded by a German, Kristian Raue, who’s brother, Peter Raue, founded MIS AG, one of the early leaders in OLAP technology. Needless to say, Jedox has more sophisticated products that they don’t give away...