|
OLAP and spreadsheets — friends or foes? |
THIS PAGE REPRESENTS ONLY A VERY SHORT EXTRACT FROM THE FULL REVIEW.
TO VIEW THE FULL REVIEW YOU CAN PURCHASE THE REVIEW INDIVIDUALLY OR PURCHASE AN ANNUAL SUBSCRIPTION TO THE OLAP REPORT WHICH ALLOWS ACCESS TO ALL OLAP REPORT CONTENT.
An in-depth exploration of the long but uneasy relationship between OLAP servers and spreadsheets
Screenshots |
| This report is copiously illustrated with dozens of screenshots showing Excel add-ins in action. Most were specially captured by vendors at our request to demonstrate particular capabilities of their products. We hope that these not only illustrate specific features, but will give readers ideas for how to make the most of their OLAP applications. The products represented include:
We would like to thank the vendors who assisted in this way — they often had to capture screens multiple times before we were satisfied, and we were only able to use a small subset of the huge number of screens they captured for us.As is usual in The OLAP Report, the screenshots are, if necessary, reduced in size to a width of not more than 640 pixels. We have to do this to ensure that they fit on typical screen sizes and the printed page. This reduction of resolution inevitably reduces their quality. But in all such cases, you can click on the image to see the screen in its original size and quality. |
In many cases, spreadsheet and OLAP tools are two alternate ways of implementing the same application. Many OLAP deployments replace spreadsheet systems; indeed most vendors of OLAP-based budgeting and planning systems see home-grown applications built using spreadsheets, rather than other OLAP vendors, as their main competition. Yet spreadsheet vendors such as Microsoft and IBM do not see OLAP tools as competitors; indeed, both also market OLAP servers.
Despite this apparent competition, most OLAP servers also offer a spreadsheet interface in which the spreadsheet acts as an intelligent window into the data stored and managed by the OLAP server. So, even though OLAP products and spreadsheets seem to compete, they also seem to work together on many occasions. This report examines the bitter-sweet OLAP-spreadsheet relationship.
Spreadsheets and what are now called OLAP servers go back a long way — they have co-existed since the late 1970s. To many people’s surprise, OLAP products pre-date even the earliest spreadsheet, and the earliest OLAP has outlasted most spreadsheet products.
The OLAP term may date only from 1993, but the products it describes have been around for much longer — almost a decade longer, in fact, than spreadsheets themselves. The first OLAP product was Express, launched in 1970, while the first spreadsheet, VisiCalc, was not released until late 1979. Though well past its sales peak, Express remains in use and on sale today, whereas VisiCalc disappeared many years ago.
Neither was an instant hit, as both were ahead of their times. VisiCalc, in particular, had to work on the 40-character Apple II monochrome screen. Amazingly, VisiCalc actually had hardly more on-screen rows and columns than today’s Pocket Excel, which is seen more as a toy than a useful tool. But VisiCalc contributed a lot to the success of the Apple II, which was sometimes called a “VisiCalc machine”. VisiCalc sold 100,000 copies its first year on the market and by October 1980, it topped the first published best sellers’ list for that early personal computer.
Soon, however, the IBM PC appeared and Lotus 1-2-3 Release 1A took off in 1983. This did for the IBM PC what VisiCalc had done for the Apple II. But unlike the Apple II, the PC was soon seen as a business machine, particularly from the release of the hard disk XT version, and 1-2-3 was quickly recognized as a useful business tool. Mitch Kapor, the visionary behind Lotus, made the apparently bullish prediction that 1-2-3 would do between two and three million dollars in its first year. In fact, it did $53m in its first year, and $156m the next year. No stand-alone OLAP product has ever achieved this kind of take-off, and yet Lotus, as a start-up, did it with its first product. Furthermore, 1-2-3 was a relatively new and unfamiliar type of product, running on a new machine with only a small installed base. Clearly, there was an immense latent demand for products like VisiCalc and Lotus 1-2-3, an important lesson for OLAP vendors, which most missed. Even today, many fail to understand the pressure for ease of use and end-user flexibility.
Today, no one is sure quite how many spreadsheet users there are. One estimate is that there are 200 million licensed users of Microsoft Excel — and perhaps the same number again of users running unlicensed copies. Add to this the smaller numbers using 1-2-3, Quatro Pro and Sun StarOffice, and there might be anywhere between a quarter and half a billion spreadsheet users in the world. The number of OLAP users, though growing well, is still tiny compared to such numbers.
In the mid 1980s, there seemed little comparison between the heavyweight mainframe DSS products, such as Express, FCS, IFPS and System W, and Lotus 1-2-3 running on what were still very low-powered PCs. The vendors of the established mainframe products were contemptuous of the limited capabilities of PC spreadsheets, and they never understood the attraction of the WYSIWYG interface. Spreadsheets were cheap, easy to learn and very fast: in fact, Lotus did so well because it bravely optimized 1-2-3 only for the IBM PC, not generic MS-DOS PCs (which were not then the fully-compatible clones they later became). This demand for speed was another lesson that many OLAP vendors have repeatedly missed and continue to overlook. In The OLAP Survey 2, published in October 2002, poor query performance remained by far the biggest product-related gripe, followed by lack of ease-of-use.
Learning Curves
|
Despite the fact that the early mainframe DSS vendors ignored them, spreadsheets soon started to be used for the same sort of financial reporting and planning models that were typically implemented on mainframes. Though their capacity was more limited, they were still surprisingly capable, and they remained much cheaper, easier and faster. For a few years, the early OLAPs and the early spreadsheets continued on parallel paths, with little mutual awareness. Neither had evolved from or learned from the other. Nor could either set of vendors see any reason to cooperate.
Spreadsheets may have been a roaring success for over two decades, but they are not without their problems, particularly when it comes to larger, corporate applications.
The most obvious limitation of all spreadsheets is their size limits. Even today’s Excel only allows 65,536 rows by 256 columns, the latter being a particular constraint for many corporate applications. In any case, spreadsheets that approach anywhere near these and the many other memory-linked limits become painfully slow and unmanageable.
Large spreadsheet applications therefore have to be broken into multiple smaller worksheets that are then linked and cross-referenced. But this has the unfortunate effect that changes in one cannot be dynamically propagated to the others that depend on it. For example, Excel takes care of updating all affected formulas if you insert or delete cells in one spreadsheet. But, if other spreadsheets refer to cells in that spreadsheet, their absolute cell references will not be updated, and the results will be incorrect. Sometimes this will be obvious if the link points to an empty or text cell instead of the expected numerical value, but all too often you may just get a wrong (but not obviously wrong) result which may never be spotted.
Indeed, apart from their limited capacity, spreadsheets soon earned a more serious notoriety for the ease with which mistakes can be made. Because the formulas are distributed throughout the sheet and mixed with the data, it is hard to follow the logic in a complex spreadsheet, but all too easy to overtype a formula with data or to make a mistake when inserting new cells. It is possible to protect or hide formulas, but of course, most people don’t bother, meaning to come back later and tidy up, but then failing to do so.
The mixing of formulas and data makes testing very difficult and can have fatal consequences: most serious spreadsheet users have experienced the sinking feeling that comes when you realize that an important report or presentation was based on a broken spreadsheet application. In fact, the anecdotal evidence almost certainly understates the problem, as has been found in many academic and systematic studies.
It is particularly hard for teams to collaborate successfully on developing and cross-checking large spreadsheet applications, and yet this technique is known to be one of the best ways of detecting conventional programming errors. Spotting your own errors, whether in word-processed documents or programs, is much harder and less likely than if someone else has to read, understand and correct them. But the whole spreadsheet culture is against this.
The need for multiple people to work on a project inevitably leads to everyone taking their own working copy of the master spreadsheets. In no time at all, these diverge, with each user being convinced that they have most up to date, accurate version. Nobody keeps track of the changes, and it’s near impossible later to merge all the updates and enhancements back into one clean, tested, master version.
Although it is possible to document spreadsheets, very few people bother. It is hard enough to check or correctly update a spreadsheet you developed yourself just a few days earlier, but much harder to do this successfully with one you inherited from someone who has moved on. One problem is that spreadsheet formulas usually refer to individual cells, rather than named entities like accounts, time periods, products, etc, which makes them harder to read and understand, so documentation is more important. But despite the need being greater, spreadsheets do not have a good way of storing comments without messing up the display format, which means that they are less likely to be used.
One of the big advantages of spreadsheets is that they are a true WYSIWYG environment. But this also means that display formats are tied to the data and formulas: you cannot have multiple reports containing the same data without duplicating or linking to it. This hardly matters with simple two-dimensional applications, but becomes much more serious with multidimensional applications where there are many possible, and equally valid, ways of presenting information. Users will want more than one report, and they will want to be able to add or change display formats without risk of breaking formulas or accidentally changing data. Indeed, different groups may be responsible for the formulas and the reports.
Many spreadsheet problems are a consequence of mixing data, formulas and formatting together, and of spreadsheets being two rather than multidimensional. In a properly multidimensional system, calculation rules should be kept separate from data and formatting, so each can be stored, managed, updated and viewed independently. And for proper maintainability, rules need to be defined in a more readable form than the cell-based notation used in spreadsheets.
One issue with inherently multidimensional applications is that variants of the same formula occur many thousands of time. For example, if you want to calculate the year-on-year percentage growth, the same calculation will be used for every product, for every customer, in every region. In a spreadsheet you cannot just enter a one-line formula once and use it everywhere. Instead, the formula will first be entered in one cell and then copied to all the other cells that need to use it. In a simple table this is not a problem, but in a large, complex application, the formula may be used in many parts of each of numerous linked spreadsheets. Simply copying it to all the destinations the first time is tedious and error-prone enough, but what happens when someone decides to change the formula logic?
The new version must first be tested with a variety of representative data sets, and then copied to all the other cells that use it. But perhaps some formulas were already customized to work in a different way, because of different local circumstances. This won’t be shown as a clear, readable rule with clearly-indicated exceptions, but simply manifested as modified formulas dotted around the spreadsheets. Will the person making the change know all the places to copy it to, including the customizations? In a less-than-ideal world, this is how many errors get introduced.
Not surprisingly, many IT professionals are very wary about using spreadsheets in any guise in a corporate application. They are all too aware of the gigabytes of undocumented Excel spreadsheets that clog up the corporate file servers, with perhaps only one person knowing what each contains and how to use it — and that person may no longer work for the organization. They know about the data duplication and the errors that can be assumed to exist in the majority of spreadsheet applications, which they may be asked to clean up. They know they can’t get rid of Excel altogether, but would be happy to restrict its use just to small, personal applications.
But this could be a big mistake, as, used properly, Excel can have a very valuable role to play that builds on its strengths while avoiding the pitfalls. In any case, end-users will not give up on their beloved spreadsheets, so it is better to bring Excel into the architecture in a sensible, controlled and supported way, rather than have it lurk in undocumented chaos on the fringes.
OLAP servers avoid all the problems of spreadsheets, but have a different set of their own. They are much more structured, keeping data, calculation rules and display formats separate. But this also destroys the popular WYSIWYG style that makes spreadsheets so simple to learn and so approachable. And their greater structural integrity also reduces their flexibility.
You need quite a lot of training and computer skills before you can set up even the simplest OLAP server application, and some of the tools also demand DBA skills. This automatically excludes the tens of millions of competent spreadsheet authors.
As a result, it is almost always easier to prototype any application in a spreadsheet than in an OLAP server, and most simple applications work well in spreadsheets. Spreadsheets also have the advantage of being unstructured: changing a spreadsheet is fast and easy (if unreliable), whereas changing an application built on an OLAP server is always more complex. This means that it is seductively easy to start every application in Excel, and by the time that it has become unwieldy, it is hard to switch to the more structured OLAP world. Usually, despite the problems that may be obvious with the spreadsheet solution, there is neither the time nor budget to start again with a different approach.
Sadly, in recent years, OLAP servers have become even less approachable by business users. This is partly because this market is being invaded by database and ERP vendors, whose server products are always aimed more at DBAs and specialist consultants than end-users, and partly because of the Web. Web-deployed applications demand more technical expertise to deploy than do desktop or simple, departmental client/server applications, both because of the need to set up and administer application and Web servers and the more complex security requirements.
Not only do applications based on OLAP servers usually demand professional IT resources to build, but in many cases, all modifications also have to be carried out by specialists. This is very frustrating for end-users who may need a quick change to an existing calculation, or a new business ratio to be calculated, or a report to be modified, and they cannot wait for help from an OLAP expert or external consultant. Even if it is accepted that the core multidimensional structures, data warehouse links and security must be professionally managed, end-users must have the flexibility to create new reports or add simple calculations without needing professional help. And they want to do it using skills they already have, rather than needing to be trained specially. Without this, they are likely to complain about, or perhaps reject altogether, the server-based solution, and revert to spreadsheet freedom and anarchy.
OLAP front-end viewers are usually quite easy to use, but they almost always fall well short of the report layout and formatting capabilities of a spreadsheet. Most business users could quickly put together excellent presentation-quality reports in Excel that would take days of professional programming in tools like Crystal Reports or Actuate. Not only is it easy to do in Excel, but the process is actually quite enjoyable. This means that, all too often, output from an OLAP report is manually copied (or, even worse, re-typed) into Excel for production of the final executive reports. This is slow and error prone, and defeats much of the purpose of using a dedicated OLAP viewer.
|
|
Spreadsheets are also ‘free’, because most users of any business application already have a spreadsheet installed on their machines. By contrast, most OLAP servers are relatively expensive. If an organization does not already have one, the cost, borne by the first OLAP application, is normally at least several tens of thousands of dollars. The one exception is Microsoft Analysis Services, which is much cheaper than all the other OLAP servers, which may partly explain its rapid growth.
In the long run, this very visible cost may be an excellent investment, but it may be hard to justify when an easy-to-use, standard and very familiar tool is already deployed on every desktop. And unlike spreadsheets, there is no industry standard, widely accepted OLAP server suitable for all applications. Buying one usually means doing an evaluation, an extra and unwanted step that is not required if an existing tool is used — and there is always the risk of picking the wrong server. Everyone knows what is possible in a spreadsheet, but it is much harder to be sure in advance exactly what is practical in a particular OLAP server.
Another issue is the language support. Some OLAP products are available only in English, while the larger vendors usually support a few additional languages as well, such as the major European languages plus, perhaps, Japanese. But even then, the application building tools are often available only in English, with the local language only supported for end-user features. In contrast Excel is available in over 40 languages, far more than any OLAP tool. So, in almost any business environment around the world, Excel is a viable tool for business users, but this is not true of any OLAP product.
End-users are sometimes wary of having centrally controlled, IT-implemented applications. They fear that they will lose precious control of their access to information, which will be slow to come and impossible to change. Their reaction, all too often, is “just give me my data and let me work with it”. This usually means dumping large quantities of data into Excel, in which form it is cumbersome and difficult to use, a solution that meets neither business nor IT requirements. But there is a better way, that combines the maintainability and scalability of a proper server application, with the flexibility and familiarity of a spreadsheet.
Most people implementing an OLAP application assume that there are, essentially, two types of front-end:
Dedicated Windows clients provide rich functionality and the best query performance, and some can also be used offline. But they are proprietary and have to be installed on each client machine, which is both a logistical and a licensing issue, particularly with large deployments. They also do not allow data from multiple (OLAP and non-OLAP) applications to be brought together in a single system. Consequently, proprietary Windows clients are typically now deployed for only a minority of the user population.
But Web interfaces are also problematical. They are usually less functional and are much slower. For example, there is no local data caching and charts have to be rendered on the server and downloaded as some form of bit map. This puts more load on the server and the network, which increases the hidden costs of Web deployment. They need an extra server tier that is either not needed at all, or much less heavily loaded, in a client/server deployment. The slow, compromised interface is often unacceptable to heavy users who therefore demand a full Windows client, either installed locally, or provided via a terminal server such as Citrix.
But there is another way: to use Excel as a form of intelligent browser connected to a server application. This does require an Excel add-in to be downloaded and installed (which can often be done automatically), but, as with a Web solution, the application itself is largely server-based and zero-maintenance locally. The add-in does not stop Excel being used in the normal way, and it is usually possible to have several add-ins installed without interfering with each other. Most add-ins have their own main menu entry and relevant, right-click context menus. Many also have one or more tool bars, which can be turned off when not required.
Thereafter, there are more similarities than are at first apparent and there is no need to regard this technique as in any way less professional than a Web deployment.
Ubiquity |
In the normal business world, despite hype about network computers, just about every potential user of an OLAP application uses a Windows PC with Internet Explorer and Excel already loaded. Internet Explorer is free and bundled and Excel is not — but every organization regards spreadsheets as an essential tool on knowledge workers’ desks, and these days, Excel is the tool that almost all organizations have chosen. Thus, both IE and Excel can now be treated as de facto standards and, effectively, ‘free’. The key point is that both can be assumed to be already installed, something that was not true a few years ago when there were still significant 1-2-3 and Netscape populations. |
Familiarity |
Almost all knowledge workers use IE and Excel regularly, though the same cannot be said of any proprietary OLAP client. End-users cannot be assumed to be competent at designing and building Excel applications any more than they can be expected to be competent Web designers — but neither skill is required if they are just going to ‘consume’ a pre-built analytical application delivered through either IE or Excel. The limited level of knowledge that can be assumed will take the average user much further with an Excel front-end than they could go with a Web front-end or even an unfamiliar Windows OLAP tool. Put simply, they can do more, with less training, with an Excel front-end than they could with either a Web or proprietary Windows OLAP client. This advantage is even greater with financial applications, whose users are likely to be even more comfortable with Excel than are other business professionals. |
Displaying objects |
Both Excel and IE are capable of displaying text, grids and charts as well as other objects, such as bit maps. In both cases, the objects can be placed anywhere on the page. |
Rendering objects |
Excel renders most output locally, whereas IE requires the almost-finished version to be created on the server and downloaded. Even if a user just wants to change the font on a column of numbers in a Web page, this requires a JavaScripted user interface running in the browser page, one or more server interactions, re-creation of the page and a fresh download. This basic feature, if available, has to be implemented as part of the server-based OLAP application. At best it is slow, clumsy and non-intuitive, but it is not even available in most Web front-ends. Conversely, it is fast, simple and intuitive in Excel, using skills that everyone already possesses, and imposing no load on the server, network or the OLAP application. Similarly, Excel just needs a few numbers — usually much less than a kilobyte of data — to draw a chart (which may already have been downloaded as part of a report) whereas IE requires a much larger — perhaps tens of kilobytes — GIF or JPEG to be downloaded. And if the user wants to see the chart in even a slightly different form, IE needs the server to repeat the whole process, whereas Excel can redraw it with no additional download. Excel also provides a much wider range of charting capabilities than do most Web OLAP tools. |
Scripting |
Both IE and Excel include scripting capabilities, JavaScript and VBA, respectively. The latter is a much more complete application building environment, and allows sophisticated workflow applications to be implemented, whereas JavaScript is used largely to partially replicate the intelligence found in any basic Windows application. However, VBA applications are more error-prone. |
Varying screen sizes |
Simple HTML pages can adapt automatically to different screen resolutions, but more complex, scripted layouts using cascading style sheets (CSS) are often hard-coded for a fixed resolution, with fixed size fonts. If the users have a variety of PCs with different screen sizes and resolutions, fixed formatted Web screens will be a poor compromise for many of the users. Excel has no such problems, automatically resizing the display to suit the window in which it is running, and allowing the whole display to be zoomed in or out without disrupting anything. This also puts no load on the server. |
Extending the functionality |
IE’s functionality can be extended using applets, plug-ins and ActiveX controls, which can be downloaded and installed automatically. This is not recommended on security grounds for open Internet use, but works well within the corporate firewall. Excel is the same, and add-ins can be automatically installed and loaded. In the OLAP context, these provide an intelligent application link between the OLAP server and the Excel client. The Excel interface provides an excellent solution for intranets, but not public Internet deployment. |
Connection protocol |
IE can connect to the server via HTTP, and so can many modern Excel add-ins. |
Internet deployment |
Zero footprint Web applications are suitable for open Internet (as opposed to intranet) use, whereas Excel applications are best regarded as suitable for client/server or intranet deployments only. |
Off-line use |
Web applications can only be used on-line, whereas once the data has been downloaded, Excel applications remain fully functional. Reports can continue to be formatted and distributed without a live link to the OLAP server, and in some cases, data can be entered into the spreadsheet for subsequent upload to the server. |
Merging OLAP and non-OLAP data |
As an OLAP deployment platform, IE can show both OLAP and non-OLAP data, and in a portal context, can show both on the same screen, though rarely in the same report. To integrate heterogeneous data in a single report would require complex changes to the OLAP application and could not be accomplished purely in the browser. Excel can also merge OLAP and non-OLAP data, but it can go further and merge them in the same report or even display results calculated using both OLAP and non-OLAP data, without even having to touch the applications providing the data. |
|
This screen from OutlookSoft EAP certainly looks like Excel, but
also illustrates that it is connected to the application server using
HTTP, just like a Web browser. This sort of formatted financial report
is not easy to produce in most proprietary OLAP viewers: |
As can be seen, Excel as an application display platform is not only surprisingly comparable to a Web browser, but in many respects is actually superior. In fact, Excel could be regarded as an enhanced browser, with greater intelligence and performance than IE. If using Excel in this way is such a good idea, why don’t all OLAP deployments use it?
THIS PAGE REPRESENTS ONLY A VERY SHORT EXTRACT FROM THE FULL REVIEW.
TO VIEW THE FULL REVIEW YOU CAN PURCHASE THE REVIEW INDIVIDUALLY OR PURCHASE AN ANNUAL SUBSCRIPTION TO THE OLAP REPORT WHICH ALLOWS ACCESS TO ALL OLAP REPORT CONTENT.
