Vector Space Inc.

TM1 the origins — Part I

I wish I had a penny for every person who has asked me what Applix TM1 is and why we like it so much. Even enthusiastic users often know little of its origins. I thought readers of this website might want to know a little more –. I meant to write a short piece, but it just grew — so I’ve split it into parts. Let me know what you think. . . . David

In the beginning there was the spreadsheet

TM1 began as a part of the spreadsheet “revolution” in 1984. Dan Bricklin had invented the spreadsheet with VisiCalc in 1979; Lotus 1-2-3 followed in 1983. This new type of computer application exponentially increased the ability of users to apply computer power to problems of their choice without expert intervention. Modelled on a simple grid of paper, the spreadsheet was easy to grasp and easy to use. It could be applied to models large and small, to all manner of reports, fair-sized databases and large calculations involving many variables and formulas.

Unlike many key innovations in computing, the spreadsheet had no roots or theory from the academy, or from the research labs of AT&T, IBM, Xerox or the likes. It had no direct predecessors. It used the unusual but easy declarative programming model, replacing thousands of lines of FORTRAN with a series of formulas that a high school student could understand.

Success on the bleeding edge

On the bleeding edge of technology, the spreadsheet could easily have faded back to wait for its moment like many other brilliant ideas. But it had some unique advantages. It was extremely accessible to users, immediately useful, and – perhaps most important — appeared just as as microcomputers were coming on the scene. Bricklin’s website tells the story well.

The spreadsheet became the tool of choice for a wide variety of bright and creative people in many fields – financial managers, of course, but also planners, business analysts, economists and scientists.

Trouble in Paradise

But as spreadsheet use expanded, and people started to use it for more and bigger projects, the simple paradigm of the ruled sheet of paper became unwieldy.

• Large sheets were slow, and pushed the limits of memory. To get around this, users split single spreadsheets into galaxies of sheets linked by external formulas and macros. But this sacrificed transparency; complex systems became large and bug prone.
• The classic spreadsheet has two dimensions but often the data it represents often has three or more. A simple table showing accounts and months is easy. But how does a two-dimensional spreadsheet show a book of income statements for many companies that transact business with each other – or break down a statement by product and region?
• The spreadsheet combines data and program elements in a single object. How can the user (or the auditor) easily tell the difference, and how to find errors buried deep in the sea of formulas? All of these problems have solutions — but they require labor and user discipline.
• Spreadsheets are usually used by a team, passed around from one to the other, with changes being made in different places.  This easily results in a situation where everybody has a slightly different version of the sheet, and the truth is hard to find.

The next step

Many developers at the time looked for a logical next step for the spreadsheet.

Manuel (Manny) Perez, the “father” of TM1 — now CTO at Applix — came up with a unique combination of features that addressed all of these problems.

Manny created a product that integrated a database into the spreadsheet, keeping an extremely tight, formula-driven link between the two. The database was not a conventional relational database, but a special kind of multidimensional database designed for the types of things spreadshets often do. Each value in the database was identified by a set of strings, one from each of its user-defined dimensions. For example, a budget number was identified as the intersection of “2006”, “January”, “Sales”, “Striped paint”, and “Boston”. The dimensions could specify how to “consolidate” numbers. Using this information, if the user asked for “Eastern Region,” the database would calculate a total of the numbers for “Boston”, “New York” and “Washington”, for instance.

These things may seem routine to OLAP users today, but at the time the term OLAP had not been coined and only a few products did anything similar. A good industry history can be found here.

Already in its early versions, TM1 included a multitude of useful features for its user-developers, including the ability to work with more than one “cube” (or multidimensional table), the ability to store strings in cubes, and the “process” command, which uses a spreadsheet to import ASCII data files into the database.

Spreadsheet OLAP

Manny’s TM1 spreadsheet had all the usual functionality, but additionally included a set of special functions to retrieve data from and send it to the database. These formulas worked with strings, so a value could be retrieved based upon the text contained elsewhere in the spreadsheet. And they worked just like all other spreadsheet formulas, so that new data could be brought in by simply changing the value of a cell and recalculating.

This innovation solved most of the problems with spreadsheets until then. It separated data from formulas. It made many very large spreadsheets unnecessary by storing their data in the database. And of course it was multidimensional from the ground up.

Additionally, Manny consistently implemented all aspects of use and administration to work from spreadsheets, making the product very friendly to spreadsheet users.

A move to client-server technology

In 1989, Manny took a further leap, implementing the database side as a server for spreadsheet clients, thus making TM1 a multi-user tool for group collaboration protected by security. This solved the last spreadsheet problem mentioned above — the multiple versions of the truth contained in differet documents. While early releases included the proprietary spreadsheet, TM1 soon made the leap to enabling Lotus and then Excel spreadsheets to serve as clients. Following this came a series of enhancements and improvements, each remarkable in its own way. Manny kept a very close line of communication with TM1’s key power users and seemed to be exactly on time and in tune with the evolving needs of the product’s base.

Spreadsheet roots

TM1 continued to be closely connected to its spreadsheet roots.

• Like the spreadsheet from which it sprang, TM1 is a productivity tool for power users – in some ways, a quintessential “horizontal” tool useful in a wide variety of applications.
• Like the spreadsheet, TM1 found an enthusiastic home in financial applications.
• Like the spreadsheet, TM1 enabled clever users to produce one-off, applications of tremendous power.
• Finally, like the spreadsheet, TM1 embodied new concepts which were not originally pioneered by the academies or research institutes.

TM1 quickly caught on with a small group of sophisticated spreadsheet programmers, and developed a very loyal following. Many thought TM1 was the “next big thing”; the logical successor to Lotus 1-2-3, but time was to prove them wrong.

– In the next section, we will discuss some of the further history of TM1 and current developments. We will also reflect on some of TM1’s obstacles and successes, and muse on the many ways in which it was ahead of its time.

Add comment August 8th, 2006

Raue talks about PALO

Following our post on the new PALO open source package, Jedox president Kristian Raue agreed to answer a few questions about the company’s plans for the product.

How is PALO planning to relate to the broader world of spreadsheet OLAP?

By July 2006 more than 10,000 people had downloaded Palo and we have 50 new downloads each day. So I think we have already had a large impact in the spreadsheet OLAP market. With the availability of a 32- and 64-bit version and with the availability of a Linux version for Palo our target market is potentially bigger than the market of other well-known players in the spreadsheet OLAP field. With Palo 1.5 later this year, we will have access rights, element attributes, an Embedded Transaction Engine and also an advanced engine that promises even more speed. And after 1.5 we will have a 2.0 release in 2007, hopefully 1st Quarter 2007.

What is your corporate outlook? Do you intend to make most of your money on the client software? Are you planning an “enterprise” Excel client or something of the kind?

Jedox makes money by selling Worksheet-Server, which is our OLAP-enabled multi-user Excel-to-Web solution for corporate use. We also earn money by selling support and consultancy work regarding Palo and Worksheet-Server implementations. At this time we don’t plan on making money by selling some advanced or enterprise version of the Palo Excel-Client or Palo Server. With this business model closely bound to Open-Source technologies we managed to grow more than 100% in revenue each year.

What is your relation to the German spreadsheet OLAP maker MIS-AG?

There a basically two relationships to MIS AG. In 2002 I sold my shares of Intellicube AG, which was the initial developer of OnVision, to MIS AG. And second, Peter Raue, formerly president of MIS AG, was my brother. He unfortunately passed away in 2004.

Is the codebase of PALO completely new, or does it derive from Alea?

It is completely new, we have never seen or touched the code base of ALEA (or Applix TM1).

Do you intend to relate in some way to Microsoft beyond Excel? What about Analysis Services?

I loved Excel from the very first day that it appeared back in 1987. Apart from that, there is not much relation to Microsoft. You can connect Palo and MS AS using Cubeware.

Can we expect to see some unique features in the product?

Yes. In some ways, Palo is unique already today for example with its Linux version. In September we will have the ETE (Embedded Transaction Engine), which will allow the server to trigger other processes when server events occur. Such processes could include, for example, PHP scripts or user programs. It will be very interesting to see where clever users and developers go with such unprecedented capabilities. With 15 years of intensive experience in the spreadsheet OLAP market we will deliver more unique features in future releases.

What about the clients you are producing? Are you planning to develop for Open Office too?

For Palo there a APIs available in all directions (C, C++, .NET, PHP and Java at www.jpalo.net). We used the .NET API to build our freeware Excel client. So far, we are not supporting Open Office yet, but that might change in the future or somebody else volunteers to do it.

How will Jedox respond if a group decides to produce a free web client that competes with your commercial one?

We would love to see such a development, which would expand the visibility of Palo. By the way, we also like Google Spreedsheets and Excel 12 Server. These products help us develop the market for Excel-to-Web solutions.

How hard is it for a developer to put a web client together using open source tools like PHP?

Easy. Have a look at the demo source code for PHP that you can download with the Palo SDK.

What about involving a community of developers? Are you intending to involve others outside of the company, or do you intend to do the development in-house?

MOLAP is about speed. So we currently decided to develop the core engine of Palo ourselves to make sure it is fast. For all other aspects of Palo (clients, ETL, Reporting tools, etc) we are very open and supportive to others outside the company. Look at the jpalo project for example, it is developed by Tensegrity.

Have you had feedback or reviews so far?

Very positive feedback from our customers so far. In October the first book about Palo will appear. Palo is already used successfully in large companies and organizations, for example check this.

What about documentation?

We are continuously improving the documentation and welcome suggestions about missing parts. Also the book - which will probably be in German and probably also in English and French - will help a lot. If you have a problem with missing information, simply use the Palo Forum. This forum closely monitored by our team. If you think you have found a bug, please report it to our bug tracker.

In our review, we cited an apparent lack of open source infrastructure for PALO, noting that there is no site on Savannah or Sourceforge, or automated bug tracking. Do you have any comments on this?

Both the forum and the public bug tracker are available since the very first day that Palo came out.

Add comment August 3rd, 2006

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

More on Applix & Temtec

Since I had more questions than answers about Temtec and Applix (see my last post below), I decided to take the bull by the horns and call Dave Menninger, Applix’s Vice President for Marketing to get a little perspective on what Applix is thinking. Here’s what he told me:

While TM1 continues to be the king of the Excel-OLAP connection, experience with customers has shown that not all business users of TM1 data are Excel users or like to work in Excel. There are many who prefer a full-featured GUI user interface. Applix had already moved to address this need with in the design of its web client, TM1 Web, but Temtec’s Executive Viewer offers a mature, elegant and powerful GUI interface tested and proven with “non-Excel” users.

In the future, Menninger said, Applix will integrate some of the features of Temtec’s product into TM1 Web as well, making it more friendly to a wider variety of users while continuing to support its customer base of Excel users.

I noted that Temtec today accesses other OLAP databases, including SAP BW, Microsoft Analysis Services and Hyperion. I asked Menninger how that plays with TM1’s server model and what Applix’s plans might be for the other OLAP technologies.

Menninger noted that the two companies have had parallel activities with many of the same relationships – Applix, like Temtec is Microsoft Gold Certified, and SAP certified as well. The big exception, of course is Hyperion, which has been competitive in many situations. Menninger said he hopes that Temtec’s Hyperion relationship will continue, and perhaps even increase. Hyperion and Applix, he noted, co-exist at many accounts, so there may well be a basis for a closer relationship.

Finally, as a hands-on integrator, I asked a strictly technical question. Does Temtec support writeback?

Yes, Menninger responded, it does write back, but only through the ODBO interface, which is somewhat limited. This, he said, may be an area of further development going forward.

Stay tuned . . .

Add comment June 16th, 2006

Open Office and corporate standards . . .

Reading the entry below about web spreadsheets, a friend writes:

The web spreadsheets are interesting, but it will be very difficult to unseat Excel even with a free product. Look at Open Office - they have a very capable product, but they’ve barely dented Excel\’s customer base. For corporate customers, it’s all about standards. Excel is the de facto spreadsheet standard, and large companies don’t like to take risks with non-standard software. And then there’s the training issue - “everyone” knows Excel.

That’s interesting, and it’s certainly what Microsoft would like to believe. Organizations are conservative by nature. Perhaps they need to be, since one ill-considered jump can destroy everything they have.

On the other hand, all organizations — and especially commercial ones — are acutely sensitive to the bottom line. After all, Open Office is actually free, that is, it costs nothing, zilch, the great Goose Egg, it has very similar functionality and it reads and writes the Office files that everyone else is using.

It’s not just price

Price aside the most interesting advantage of open source for any organization is that the file formats are open, meaning that they can not be held hostage by a software company. Also, their files will not turn to binary mush if the software du jour changes. Have you tried to read your old WordPerfect files with a recent version of Word?

A significant advantage that Microsoft’s products retains is the market for add-on products like Applix TM1, which actually transform the spreadsheet into quite a different animal.

But most users use Excel for its native features. In the end, unless Excel offers a compelling technical advantage, the point may come where a tiny trickle turns to a flood.

(but price does count)

N.B. Although the FLOSS gurus insist that they mean their software is “free as in speech, not free as in beer,” free beer is certainly received with full honors in suites where free speech may not be so welcome. “Beer freedom” may be the decisive factor propelling free software to dominance in the commercial arena.

Add comment June 14th, 2006

Study confirms spreadsheet errors

Everyone who has created a sizeable spreadsheet data system can testify to the problems of data integrity. Now a study quantifies this, and gives us an idea of its scale:

Slashdot | Errors in Spreadsheets are Pandemic

Continue Reading Add comment June 5th, 2006

Applix TM1

This site will contain comments, coverage and resources related to Applix TM1, a remarkable OLAP tool that we have used for many projects.

Add comment April 7th, 2006