The definitive web-based OLAP information resource

Complimentary Sample

The OLAP Report is the most frequently consulted specialist source of information on OLAP products, suppliers, and trends as well as practical advice on selecting software and implementing applications. Totally independent of vendor influence, The OLAP Report is compiled by an editorial team lead by Nigel Pendse, the leading analyst on the OLAP market. Research shows that organizations who perform a formal multi-product evaluation are more likely to achieve their business goals, and much more likely to realize business benefits than those who do no. The OLAP report is the only dedicated on-line information resource where you can carry out a comparative analysis of all the leading OLAP products. An annual subscription to The OLAP Report gives you unlimited access to detailed reviews and critical evaluations of all the most important products plus exclusive analysis of OLAP market trends, practical advice on making the most of OLAP software and case studies. The following is an extract from one of over 40 product reviews available exclusively to subscribers of the OLAP Report. This sample illustrates the quality and authority of the product analysis you will find throughout the product reviews. Each review includes an exhaustive analysis under the content headings listed below - including the OLAP reports influential conclusion and product scores. Introduction Vendor overview Product commercial issues Pricing Database structure and capacity Architecture and platforms MOLAP storage Data access functionality Calculation functionality Reporting flexibility Automatic detection of exceptions Time series intelligence Currency support Security and DBMS issues End-user human factors Application building features Performance Conclusions and scores To order the OLAP report please visit www.olap-report.com or for further information, contact mschumacher@OLAPreport.com

Applix TM1 8.3

Product Review Extract TM1 quietly grows its market share at the expense of the BI giants
Product commercial issues Applix now has consistently numbered version numbers for its products. TM1 Server, Architect (the building tool) and Perspectives (the Excel client tool) now all share the same release number. Version 8 shipped in early 2003 and the release reviewed here is 8.3, released a couple of months later than expected, in November 2004. In July 2002 Applix released version 8.0 of the TM1 OLAP Server, which superseded version 7.0. TM1 Perspectives version 8.0 (superseding Perspectives 7.0) is the TM1 spreadsheet add-in for Excel. TM1 Perspectives supports versions of Excel up to Office 2003; Lotus 1-2-3 support has been dropped. TM1 Perspectives serves not only as a client to the server but also as a stand-alone OLAP engine (to be used with a spreadsheet). New in version 8.2 was TM1 Web. New since version 7.0 is TM1 Architect which is used to build dimensions, create cubes, load them with data and perform all aspects of server administration. Surprisingly, all this same functionality is also available in Perspectives, so that Architect is a complete subset of Perspectives. Applications developed with Perspectives can be readily loaded on to a TM1 Server since the file formats are identical. This capability puts standalone TM1 Perspectives somewhat in the same space as the desktop OLAPs, although the spreadsheet interface does not have the EIS-style interactive query capabilities. It should be noted, however, that the in-spreadsheet browser does provide more EIS style, interactive drill and pivot capabilities than were previously available. In terms of multidimensional capacity on the desktop, TM1 Perspectives would probably lead the desktop OLAP group, and it is superior to them in calculation functionality and performance as well. Version 7 was an ambitious release for Applix with considerable new functionality. Version 7 was the first version with a multi-threaded server which greatly improves multi-user performance. It should be noted that although each user has its own thread of execution, writes are queued to a single thread. Thus read-mostly applications will benefit more from the multi-threading than intensive read-write ones. It also now supports OLE DB for OLAP and has cube and dimension replication a powerful feature for enterprise-wide users of TM1. However, relational storage of data has been dropped as few customers used it. Another key component introduced with Server 7 is TurboIntegrator, which is an application development tool for loading data and building dimensions. It could be classified as a dedicated OLAP-focused Extract, Transform and Load (ETL) tool. Its programming language will be familiar to TM1 developers since it is extremely similar to the cube rules language in TM1. It offers considerable programming power, but lacks features such as user defined functions or calls to external routines (although it can launch external processes). TurboIntegrator runs on the server and greatly increases the speed of data loads, as well as allowing the fast building of large dimensions (Applix report one with more than a million members). TurboIntegrator also includes a scheduler. TurboIntegrator can pull directly from relational databases as well as ASCII files. this allows tight integration with modern ERP systems, including automatic metadata updates. Version 8 continued to build on the foundations laid by version 7. Refinements were made to the calculation engine which improved TM1s already impressive calculation performance. Improvements were made to the user interface, for example, complex rules based calculations can now be traced which greatly helps debugging. Many new functions were added to TurboIntegrator, greatly improving the ease with which application integration could be automated. Applix has developed several solutions for developing web based applications with TM1. The first was TM1.web which was a full Web-enabled multi-dimensional Java-based browser based on a licensed version of Databeacon. TM1.web was not successful so Applix next undertook an extremely ambitious development project to build an application development environment called Integra, based upon its (now sold) CRM product. This product has not gained market acceptance and has been de-emphasized by the new management. Applixs third generation Web product, TM1 Web is more successful than its two predecessors, although, since it instantiates an Excel session on the Web server for each connected user, is not suitable for large deployments. Most large Web deployments with TM1 therefore use third party tools. Applix also now offers 64-bit versions of TM1 for both Unix (HP-UX and Sun Solaris) and Windows. The 64-bit Unix versions have been available for longer, but as with the 32-bit versions, 64-bit Windows is likely to prove far more popular with TM1 users than 64-bit Unix.

Pricing Applixs TM1 starts at $25,000 for a TM1 Server which includes five read-write concurrent users ports. Additional users can be added for either $3500 per read-write concurrent user or $400 per read-only named user. Additional servers are $15,000 each and there is a single $25,000 uplift for Windows 64-bit support and $40,000 for Unix 64-bit support. Stand-alone Perspectives is priced at $3500 per copy. Other modules that can be added include TurboIntegrator for $10,000 and TM1 Web, also for $10,000. Newly introduced TM1 Planning Manager, which provides work flow for TM1 applications and within Excel, is $15,000 for the first server and $10,000 for additional servers. The SAP BW interface is $20,000 per server and requires TurboIntegrator. The TM1 API (Both C and Java) is free. Applications developed with the TM1 API require a valid TM1 license to run. Thus, the Applix TM1 price list now ranges from $25,000 for five users to $1.7m for 1500 users. These prices are for a server (including a license for TurboIntegrator) and a proportional number (one for each five ports) of bundled TM1 Perspectives clients. Applix also includes an unlimited license for a thin client. The thin client (thin here means reduced functionality, not Web based) can read and update server-based cubes but has no ability to create dimensions or cubes, or access local cubes. The TM1 Perspectives product can serve as both a client and a fully functional OLAP tool in its own right. Thus any desktops using the full TM1 Perspectives, in excess of 20 percent of the port count, would need to license separate copies. Software maintenance, which covers software upgrades and telephone support (most support is typically provided by the VAR or OEM), is 18 percent of the current list price. First year maintenance is no longer included in the list price. Applix supplied us with the following typical US configuration prices:

System Size

Total concurrent read-write users Total named read-only users Number of TM1 production servers Number of TM1 development servers
Small Medium Large Very Large

User Costs

TM1 production server base cost TM1 development server cost TurboIntegrator Web Server Total License Fee First year maintenance Training US list price (excluding consulting) Database structure and capacity TM1 uses a multicube data structure. A database is a collection of cubes (Applix now calls them cubes, having previously called them tables), with cubes defined as combinations of up to 16 independent dimensions each. As dimensions and cubes are all stored in separate disk files, there is no problem with multiple databases sharing dimension definitions. Dimensions can have up to two billion members. This is a theoretical limit most TM1 applications do not currently exceed 10,000 members per dimension, though a few customers have much larger dimension: the largest known dimension in production has about 6 million members. Complex hierarchical structures are allowed in each dimension, although there is no validation of the correctness of a hierarchy, unlike in some other products. Dimensions can be created and updated via TurboIntegrator (or the API) from most data sources, including relational and ASCII files. The preferred way of defining dimensions used to be through a spreadsheet interface, which is still an option. Perspectives and Architect include the $10,000 $60,500 $10,890 $4,000 $66,315 $25,000 $25,000 $40,000 $15,000 $15,000 $10,000 $20,000 $10,000 $20,000 $128,500 $507,500 $23,130 $91,350 $11,000 $20,000 $136,930 $415,850 $70,000 $30,000 $40,000 $40,000 $2,312,500 $416,250 $100,000 $1,672,500

Server Explorer which is a graphical interface, similar to the Microsoft Management Console used by SQL Server. Dimensions can now be created, viewed and edited in this much more intuitive graphical format. Users who are upgrading can still use spreadsheets if they prefer. Simple aggregations (including both partial consolidations and differences) are defined in the dimensions. More complex calculations are defined in cube rules. Applications can draw data from as many cubes as needed. As noted above cubes can comprise up to 16 dimensions. Applix, like most other OLAP vendors, rarely finds a need for more than nine or ten dimensions in a single cube (although a single application could contain dozens). As many cubes as required can share a dimension. Dimension names can be up to 255 characters long. Member or element names may also be up to 255 characters in length. Spaces and capitalization are ignored and element names must be unique within each dimension. Data must be entered at the lowest level as there is no provision to enter data at a sub-total level. However, from release 8.1 there is now a DataSpread feature that allows data to be entered at a sub-total level and then spread to leaf level. Microsoft Analysis Services has had this feature for much longer, but the TM1 version has more spreading options and operates much faster, which makes it much more usable in practice. In general, TM1 does not treat any dimension differently from any other and it has no special provision for the treatment of the time dimension (so that, for example, there is no provision to calculate cumulative totals or leads and lags except with the creative use of the rules language). When acting as an OLE DB for OLAP data source TM1 can, optionally, identify the measures and time dimensions since OLE DB for OLAP treats these specially. Cube cells can contain strings, as well as numeric data. Elements must be specifically defined to contain either string or numeric data and only a single dimension in a cube (the last to be defined) can define members as strings. TM1 users have made extensive use of this capability to use cubes to store member attribute information such as the sign of an account or a detailed product description. The capability to store attributes (known as member properties in Analysis Services) natively was included from version 7. TM1s ability to store strings in cubes, as well as use them for attributes, combined with its string manipulation capabilities in the cube rules is a powerful feature. Applixs customers often use the product for financial applications such as consolidation, budgeting and forecasting. These applications, while often containing reasonably large data volumes (tens of millions of numbers), typically have a data density in the one percent to 25 percent range. Often when revenue data is analyzed by product and market the number of dimensions increases to eight or more and the sparsity of the data typically increases drastically. The TM1 engine is particularly well optimized to deal with sparse applications and its calculate-on-demand approach avoids the pitfalls of the database explosion phenomenon. The TM1 Server supports all flavors of Windows (2003, 2000 and NT) as well 64-bit Windows and Unix. The TM1 server can take advantage of as much memory (virtual or physical) as the hardware provides (up to 2 or 3Gb on 32-bit operating systems). This is particularly important to TM1 since it is one of the few OLAP servers that cache the entire database in virtual RAM. Server 7 was the first version of TM1 to be multi-threaded which makes a significant difference in large applications that serve many users or involve particularly complex calculations. TM1s algorithm for dealing with multidimensional sparse data has evolved over the years. The technology is based on keeping all the active cubes (logically, if not physically) in RAM. Although this sounds ambitious, the algorithm succeeds in keeping a remarkably consistent 60,000 to 100,000 numbers per megabyte of RAM. This byte count of 10 to 15 bytes per populated cell can increase to 50 or more for very sparse (nine or more dimensions) applications or those with complex calculation rules. The largest TM1 database that Applix is aware of has a size of about 35 Gb in RAM, roughly two billion actual data cells. In memory, each cube uses arrays of pointers that point to other arrays of pointers, and eventually to data values. An array of pointers for the first dimension points to a series of arrays of pointers for the second and subsequent dimensions, for any elements that contain values. A null pointer indicates that there are no further values for that element. No run length encoding is used. This approach means that there is no requirement on the user to define dimensions as dense or sparse and the database requires little tuning and no defragmenting. Applix does recommend that dimensions be ordered from least to most dense to maximize efficiency. Version 8 introduced a tool which lets the developer optimize the dimension order of an existing cube. We believe that Applixs algorithms for both disk and memory storage are unusually efficient, so TM1 requires less hardware than other comparable solutions, and its ability to work entirely in RAM means that it is a good fit with battery operated notebook computers. TM1 uses a number of techniques to optimize query times. All calculated results are automatically cached if they take more than a certain time to calculate. Another technique (code-named Stargate) makes the Server calculate all the result cells in a slice of the cube in a single pass, regardless of which ones are requested. This trick, referred to by the development team as getting data wholesale as opposed to retail, imposes little overhead most of the time and can result in spectacularly faster queries in many cases. These views are also cached. Any views taking longer than a specified time are retained in the server cache on a most used basis. We applaud the elegant simplicity of these approaches, which allow the database to be tuned by non-technical personnel. One of the criticisms often leveled at TM1 by its competitors is its lack of database tuning tools, but in reality, although we have heard of rare examples of databases becoming inefficient if dimensions are specified in a particularly adverse fashion in the cube, TM1 databases typically need little tuning. In a client/server environment this means that once a cell has been calculated for one user, all other users will typically get it without recalculation (unless, of course, one of the underlying components has changed). TM1 is intelligent in which previously cached results to discard when input

data changes as long as they were defined through the aggregations defined within dimension structures (in other words, it only discards the affected ones). However, for the more complex rule-defined calculations, it plays safe and discards all rule-generated results if input data changes. In each case, it does not invoke a new bulk calculation to replace the discarded cells, so there is no delay, but subsequent attempts to read those cells will force their recalculation. This sparse recalculation technique can result in very fast aggregations on read-mostly cubes, although more complex calculations in constantly changing cubes will be slower. TM1s architecture is therefore most suited to applications that require fast shared read-write access to data from spreadsheet clients. Server 6s single- threaded server worked best with models containing less than ten million populated data input cells, but in this range it appeared to be one of the fastest OLAP servers available. Server 7 did not expand the database capacity but it allowed a single server to support more users by sharing the load over multiple processors. This now allows TM1 to share more easily larger cubes (100 million or more populated cells) such as those often found in marketing and sales analysis applications. Server 8 actually improved the capacity of the database since feeders (see Currency Translation) are now stored as a single bit in a bit map rather than taking 8 bytes. TM1 uses a different data storage structure on disk and now offers only a single format, TM1s native proprietary file format; the previouslyoffered relational storage option has been dropped as few customers used it. Because cubes are read into server memory when the server is loaded, and written back to disk only when the server process is ended or for back-up, there is no need to have an indexing structure to handle random file access. Instead in the proprietary format, data is stored as a series of sorted variable length records. Each record has a start word (two bytes), a four-byte key for each dimension and eight bytes for the data. To save space, only changes in the key are stored from one record to the next. As, in most cases, only one dimension will have changed from one cell to the next, there will be an average of only about 15 to 16 bytes per cell, regardless of the number of dimensions or the theoretical number of cells in the cube. This storage technique means that empty cells never directly occupy disk space, which makes for very compact data storage on disk. For example, using the same example as was quoted for Essbase 4 in the original OLAP Report, a cube with a theoretical size of 50 billion cells might actually only have perhaps one million input and four million stored consolidation cells. As TM1 would only store the input cells, the cubes would occupy about 13Mb on disk. By contrast, a fully pre-calculated approach might require in the region of 150200Mb for the same database on disk. Unusually, elements can be added to dimensions at any time (even when the server is up in the client/server model). Dimensions cannot be added to or removed from cubes without rebuilding the cube, but TM1 provides tools to move data from one cube to another, which makes adding extra dimensions relatively easy by defining a new cube and transferring the data from the old cube to the new.

Architecture and platforms The current TM1 product line supports Intel PCs and Sun and HP Unix as servers. The previous AIX support has been dropped. The then Sinper Corporations TM/1 Spreadsheet Connector was arguably the first client/server OLAP server to market when it was launched in 1989. It is a true LAN based client/server architecture where calculations are done on the server and only the results are communicated across the LAN to the client. It supports TCP/IP and, with version 8, HTTP protocols. Applix offers client software for Excel, 1-2-3 support having been dropped with version 8. The client interface appeals to spreadsheet users since it allows easy browsing of data in cubes and facilitates the building of spreadsheets which can become sophisticated reports. An API which can be accessed from Windows products such as Visual Basic, Delphi, JBuilder, Visual Caf and Java allows the development of custom interfaces. TM1 Perspectives consists of a spreadsheet add-in, which communicates with the multidimensional engine running on the server. The TM1 Server is the engine only, configured to run as a separate multi-threaded multidimensional application. An application can be distributed across multiple servers which can be automatically synchronized. TM1 uses an efficient client/server architecture. The (typically spreadsheet) client requests values from the server. These requests are bundled into packets and sent across the LAN or WAN to the server. The server retrieves the values, computing them on-the-fly if necessary, and then bundles them into packets to be sent back across the network. Thus the primary volume of traffic consists only of the data required to support the current analysis. This generally results in excellent performance. Even if the requested values are derived, TM1s real-time calculation capability is particularly efficient. TM1 has an API that allows full access to data in cubes (both read and write), as well as access to elements and data relating to them (their index position in a dimension or whether they are a base element or a calculation, for example). Applix has increased its focus on the API over the last few years to support ISVs such as arcplan, Hyperion, Comshare, Information Builders and VARs in creating a wider variety of non-spreadsheet clients and applications. The API was completely redesigned for Server 7 and is much richer than the Server 6 one (ironically much of the design input came from Hyperion Software, now part of Hyperion Solutions and Applixs main competitor) and provides comprehensive access to all features of the server.

Perspectives will run very comfortably on a desktop machine costing less than $1000, whereas some high-end applications are run on servers which are loaded with RAM and fault tolerance costing $20,000+. Unlike most OLAP products, adding RAM to a server running TM1 has a direct effect on total capacity and may in turn improve performance if it results in data being cached in real memory rather than virtual. Because of the RAM-based approach, TM1 has a mechanism of logging all changes to cubes shared on a server. This not only provides a recovery facility in the event of a server crash, but also provides a roll-back capability and a complete audit trail, including which user made each change, and when. By making this a comma delimited ASCII file, users are able to take advantage of the standard flat file import to re-import the data into the cubes and rebuild their application to the point immediately prior to the crash. This recovery happens automatically. With version 8, administration tools to view and search the transaction and server message logs were added. Previous versions had no interface for viewing logs and notifying users. In fact, live logs could not previously be viewed as the files were exclusively held open by the server process. The server also makes use of the transaction log to provide a two-way fine-grained replication capability. Thus a copy of a cube could be replicated to a local server on a notebook PC, allowing updates to the budget to be done while at 30,000 feet. The next morning the notebook could be reconnected to the network and the changes merged into the server cube on a last updated (regardless of where) basis. Replication is also useful for large enterprises who want the fast response time of a local server but want global synchronization. Replication can be performed on demand or scheduled and can be used to keep servers synchronized throughout the enterprise. Thus, in contrast to most OLAP products, the TM1 engine allows true multi-user read-write, with an audit trail and fast recalculation of results if data changes. As long as most data fits into memory, the server should outperform most others. TM1 Perspectives can easily run on typical notebook computers and it is common for TM1 users to distribute parts of cubes to individual department managers or marketing managers. A single client can connect to many OLAP servers. Each OLAP server is typically a fast processor running Windows Server and often dedicated to running a single instance of TM1 Server, although multiple servers can run on the same machine. The server should contain enough RAM to load all shared cubes into memory for optimal performance. The server runs as a service under Windows 2000 and 2003 so the database administrator does not need physical access to the server machine to bring the server up and take it down. All of the configuration parameters for the server are now specified in a configuration file rather than command line parameters. TM1 has only a basic server console so most changing of server parameters is performed by editing this configuration file.