|
Commentary: OLAP benchmarks |
You can contact Nigel Pendse, the author of this section, by e-mail on NigelP@olapreport.com if you have any comments, observations or user experiences to add. Last updated on December 30, 2003.
After a gap of almost five years, Hyperion finally published a new set of APB-1 results in December 2003. The previous three published APB-1 benchmarks had all been from Oracle, and it was looking like Hyperion had dropped out of this private race with Oracle.
The latest Essbase run was performed in November 2003 with the same benchmark paramenters as Oracle had used for its 9i OLAP run in December 2002, so they are comparable, but Hyperion achieved better performance on lower priced hardware than Oracle had used. This does seem to confirm our suspicions that Oracle’s figures, when dissected, appeared to show that Oracle9i OLAP is indeed slower than Express or Essbase on the same hardware. Click here for a more detailed analysis of this benchmark, including questions about the lack of impartial auditing.
Microsoft, Unisys, EMC and Knosys published first details of their T3 project on February 7, 2001. This was designed to show that Microsoft Analysis Services running on Windows 2000 could build, update and query MOLAP cubes that were far larger than what was commonly believed to be the upper limit for MOLAP, and indeed well into high-end ROLAP territory. It was not a competitive benchmark between OLAP products, nor based on any agreed, ‘industry standard’ benchmark.
The system used modified, extended data standard syndicated market research data, with a total of 7.7 billion rows in eight fact tables (representing data of differing granularity), the largest of which contained 4.9 billion rows. The relational database size was 1.2 terabytes. Because of the pure MOLAP architecture, the fact tables did not need indexing, and no relational summary tables were created. Had this been a typical ROLAP implementation, both would have been needed, and the database would have been perhaps three or four terabytes. These figures can be compared with MicroStrategy’s earlier benchmark with four billion rows of detailed sales history data in a 157Gb base fact table, in a 200+ Gb database. In effect, the largest published MOLAP benchmark is much larger than the largest published ROLAP benchmark!
The eight fact tables were mapped to eight MOLAP cubes, which were then joined to produce a single virtual cube. Even with aggregates and indexes, the total MOLAP data space was only 471Gb, compared to 1.2Tb of input data (with only partial aggregates and no indexes). This illustrates that MOLAP mode cubes are very compact, and far from suffering from database explosion, benefit from database compression. Indeed, the entire MOLAP data space is likely to be much smaller than the summary table and index space required in a large ROLAP solution, so the overall disk space consumption is much reduced. Audited query responses, even for complex queries, remained very fast, proving that, regardless of size, a good MOLAP solution can be lightning fast and should always beat a ROLAP solution. However, Microsoft only ran a small number of simulated concurrent users, so this demonstration did not test the configuration’s ability to support large numbers of users.
We have a detailed analysis of this demonstration on-line, including details of the queries not available in Microsoft’s technical own white paper.
Previously, on March 16, 1999, Microsoft and HP performed the first audited terabyte OLAP "benchmark" demonstration on the Web. This was based on Query 5 from the TPC-D, but was far from being a full TPC-D test. It was also not a full OLAP benchmark as it did not perform a realistic range of potential analyses. But it was nevertheless interesting because there are probably fewer than a dozen OLAP applications in production anywhere in the world analyzing much over the terabyte of raw data used here.
This very narrowly focused demonstration was in response to Larry Ellison’s million dollar challenge, made at Comdex in mid November 1998, when he offered anyone in the audience $1m if they could run a specific (TPC-D query 5) query better than 100 times slower than Oracle 8i. Ellison’s apparently casual challenge was nothing of the sort: Oracle was well aware that SQL Server 7.0 lacked a key feature (materialized views) that would allow it to handle this particular query in the same way that Oracle8i could, so Oracle was not actually risking the humiliation of paying Microsoft a $1m prize.
However, Microsoft subsequently realized that it could use SQL Server OLAP Services to perform a version of the TPC-D Query 5, but did not comply with all the technical requirements. OLAP Services can take full advantage of stored aggregates, but this was still not enough to meet all of Oracle’s (now expired and very specific) challenge requirements — so Oracle was in no danger of having to pay Microsoft the famous $1m prize. But the test nevertheless demonstrated that Microsoft OLAP Services could work successfully in ROLAP mode (at least in this highly artificial benchmark situation) with a terabyte of raw data stored in a 6 billion row fact table.
Microsoft and HP used a 4x450 MHz Pentium II Xeon server with 4Gb RAM — impressive by the standards of typical NT servers, but far less powerful and some 40 times (ignoring the disks) cheaper than the exotic Sun Starfire Ultra Enterprise 10000 server used by Oracle. Despite being limited to lower powered NT hardware, the Microsoft query times averaged about 1.1 seconds, compared to the 71.5 seconds posted by Oracle in November (which had, however, dropped to 0.7 seconds by February 1999, largely through database improvements such as fully materialized views). Microsoft’s 1.1 seconds query time was achieved with all caching turned off; with caching allowed, the query times stabilized at 0.05 seconds in the best case.
As with MicroStrategy before it, Microsoft was slow to publish the white paper describing the demonstration and it was thin on details when it did come out. The auditor’s report indicated that each of the aggregate partitions took about six hours to build separately. Had all eight partitions have been built in parallel on the four processor server, the total elapsed time would presumably have been well over 12 hours (but somewhat less than 48), but this was not measured as it was not part of the challenge. In a real-world implementation, more aggregates would surely be needed than the very restricted set required to satisfy this benchmark, but they would also not all be needed to be created in one go, so it might be possible to fit in a routine data load into a nightly refresh cycle.
As we show in our expanded benchmarks section, there were no real losers in this latest skirmish in the seemingly endless Oracle vs Microsoft war, but there were some unexpected real winners. However, despite mounting anecdotal evidence that Microsoft OLAP significantly outperforms Essbase, at least in some applications, it seems that Microsoft has no plans to publish APB-1 figures; it appears to believe that the APB-1 rules prevent Microsoft OLAP from running the benchmark. But other Microsoft benchmarks are available, such as that performed by Unisys.
At the time, Hyperion derided Microsoft’s ‘terabyte’ demonstration, but in November 1999, it and IBM staged a very similar, though less functional, demonstration with Essbase. A claim that: "Hyperion and IBM simulated a real-world demand management application based on one terabyte of data .... Hyperion Essbase OLAP Server .... consistently delivered sub-second analytical queries against the application", notably failed to point out that only about a 0.1 percent summary of the total data was actually loaded into Essbase and available for analysis. The remaining 99.9 percent could be drilled to, but not subjected to multidimensional analysis. In contrast, in Microsoft’s version, the whole database was, in principle, available for multidimensional analysis.
Both of these runs show that queries on summary data can be serviced very quickly by multidimensional engines, regardless of the amount of underlying data, but neither gave any indication of how quickly large volumes of data could be loaded, pre-calculated or accessed.
MicroStrategy started this series of published OLAP performance results with its so-called ‘Stress Test’ which purported to show that it had "demonstrated real-world scalability for enterprise data warehouse access by simulating the workload and connectivity of over 40,000 users in a Web-enabled decision support environment". We disputed the many extrapolations and assumptions behind that headline figures, and in our view, the detailed data actually indicates a realistic workload of no more than 150 concurrent users (and a very much smaller number of concurrent queries) rather than MicroStrategy’s hyperbolic headline. MicroStrategy has never published full details of the test, so it would not be possible to replicate it or to check its realism.
Shortly afterwards, the then Arbor Software became the first vendor to run the OLAP Council’s APB-1 benchmark with its Essbase 4.1 software, which demonstrated far better performance and the ability to handle many more users on much cheaper hardware, but with very much less raw data. Arbor’s results were also a graphic demonstration of database explosion in action.
Then, Oracle published excellent APB-1 figures for Express 6.1 in October 1997, using the same benchmark parameters as the Arbor June benchmark, running on an equivalent server, but with a much faster network, although it is very unlikely that the network performance influenced the results. Arbor has also highlighted the number and the complexity of the stored procedures required to implement the Express server calculations and query processing.
Applix followed in December 1997 with figures for TM1 Server 6.0, running the same benchmark but on much cheaper hardware than Oracle or Arbor. The TM1 overall AQT figures were just behind Oracle’s, but Applix proved its claims that TM1 can start answering queries much quicker after a data update — it was in action after just 13 minutes, whereas Express took 83 minutes and Essbase 4.1 took a mammoth 304 minutes. As with the previous Oracle Express benchmark, Arbor vigorously attacked Applix’s implementation of the benchmark, which contravenes some of the OLAP Council’s rules, despite being audited by the NSTL. In particular, some parts of the benchmark were executed on the client, as they would be in a real-world application, but the benchmark rules do not permit this. While these errors were probably accidental, and it is not known how much they improved the timings, they do raise question marks about the usefulness of the results.
On March 16, 1998, Arbor (now Hyperion Solutions) released bombshell figures for Essbase 5.0. There was no doubt that Essbase 5, by not requiring a full pre-calculation, would avoid database explosion and the lengthy data load/calculation phase that had dogged Essbase prior to version 5.0. This was almost certain to improve the Analytical Query Time (AQT), but the scale of the improvement was extraordinary: Essbase 5 had an AQT that was almost 14 times better than before — and more than three times better than Express 6.1.
On May 18, Oracle released audited figures for Express 6.1, this time running on Sun Solaris rather than Intel NT. The new server had four 300 MHz Ultra-Sparc processors rather than four 200MHz Pentium Pros and the client machines generating the queries were also about 50 percent faster, so the improved performance that resulted was no surprise. In addition, Oracle re-coded the Express benchmark to pre-calculate less than it had done previously, though still more than Arbor and Applix did. The net result of these changes was that the AQM was 8072 compared to 5291 for Essbase 5.0 running on a quad Pentium Pro server and 1563 for TM1 6.0 running on a dual Pentium Pro server. All three took roughly the same time to start producing query results, but the big differences were in query rates. As the Sun machine was probably well over 50 percent faster than the NT server previously used by Arbor, the Express figures are probably still somewhat slower than those for Essbase 5 on a ‘like for like’ basis. But the gap is not large enough to be significant. Undoubtedly, if Oracle had figures that beat Arbor’s on the same hardware, it would not have held back from publishing them. Subsequently, in November 1998, Oracle and Intel produced figures showing that Express 6.2 running on a 4x400MHz Intel Xeon server was significantly faster than Express 6.1 running on the 4x300MHz Sun server.
We welcomed the fact that Oracle also ran a version of the benchmark with the same dimension limits, but 50 times denser data (786Mb input data), which is a much more useful size to benchmark. For the larger run, the Sun server’s RAM was increased to 4Gb, but Oracle was at pains to point out that it used exactly the same application code for this larger benchmark as for the less dense version, thus demonstrating painless application scalability. In this case, the partially pre-calculated database size rose from 616Mb to 11.5Gb. This was only 18.6 rather than 50 times bigger, confirming that database explosion is less severe with denser data. This phenomenon is explained in more detail in the database explosion section. The AQM was 852, almost ten (rather than 50) times slower than for the smaller data volume. While the difference in server memory makes it hard to compare this performance directly with the smaller case, it nevertheless still demonstrates that a near-instant query response can be delivered even with moderately large data volumes. In February 2001, Microsoft showed that it is possible to handle ROLAP-style terabyte plus databases using MOLAP technology, with sub-second query responses and database compression rather than explosion.
On November 13, 1998, Hyperion published figures for Essbase also running on Sun servers, though with faster processor speeds than Oracle had used. Across the board, the figures were about twice as fast as Oracle’s (on server hardware perhaps 25-30 percent faster), thus putting Hyperion back into the lead. That run was the final one under the old rules and figures under the new rules will not be directly comparable.
All the current benchmarks are analyzed in detail and compared in the 50 page expanded benchmarks section.
This page is part of the free content of The OLAP Report, but ten times more information is available only to subscribers, including reviews of dozens of products, case studies and in-depth analyses. You can register for access to a preview of some of the subscriber-only material in The OLAP Report or subscribe on-line.
|
All information copyright ©2003, Business Application Research Center, all rights reserved.