INTALIGN blog

Numerous reports and surveys by the big analyst groups such as Forrester[1],  IDC, Ventana Research[2] and Oracle[3] have proven time and time again that spreadsheets, particularly Microsoft Excel, are still the most widely used business intelligence tool.

Still, many times over the past 12 years, I have been confronted by ‘professional' BI vendors who insist that "Excel is just for Mickey Mouse Business Intelligence".  However, more than a 100 BI projects across the globe later, I am yet to find a BI initiative where Excel hasn't played some kind of front end role.

I don't mean cutting and pasting screen dumps, or loading text file extracts statically into a spreadsheet - surprisingly (sadly!) still the norm in many organisations. I'm talking about using Excel as a container for dynamically displaying and writing back data to professional analytical database solutions (in particular, multidimensional or "OLAP") that include effective multi user management (access right restrictions, concurrent use, etc.).

Admittedly, this approach has had some shortcomings, such as nonexistent web functionality. The 97 version of Excel ("Internet Assistant Wizard") introduced a limited approach that enabled users to save spreadsheets in static html code. Since then, a number of new approaches and solutions have been developed to combine the power of the web with the flexibility of a spreadsheet and over time, things have come a long way. This analysis aims to give you overview of options for publishing dynamic, data driven Excel reports to the web.

Why make the change?

Some of you may be asking why I would want to swap the familiar full client spreadsheet environment for a web browser that offers far less usability. The answer is, for that very reason. Given the requirement of audit ability and control, users need to be restricted so that the structure of spreadsheets can't be modified. The web offers this. In addition, the Meta information (who, when, etc.) of any input has to be recorded.

Another key factor is the protection of Intellectual Property. Often, a lot of effort has been invested in spreadsheet based calculation models so the author wants to ensure they are secure. This has led to strong solutions that combine Excel based design with professional publishing in a web browser - not the least two major applications from the Seattle behemoth itself.

Below we will cover the approach, strengths, weaknesses and distinctive marks of the different solutions available. In particular, we will look at Microsoft Office SharePoint Server, Jedox Worksheet Server and XLCubed Web.

Microsoft Office SharePoint Server / Excel Web Services

Figure 1 Publishing of Workbook to Excel Services

In 2006 Microsoft has introduced a new, dynamic way to make spreadsheets available over a web browser. Excel Web Service is not part of an Office, edition but is a part of the SharePoint family. It is included in the Microsoft Office SharePoint Server (MOSS). MOSS offers a scalable way to publish Excel workbooks on the web, including dynamic, formula based references to MS Analysis Services cubes.

Figure 2 Excel Services Web Interface

The technology allows business users to design reports with spreadsheet flexibility while making them dynamically available (i.e. users can change a point of view and data will automatically update) in a controlled fashion to recipients over the web. However, there are a few caveats, the major ones being:

• No ability to writeback to the sheet. Inputs can be made, though, only in a separate parameter box. Those can't be stored when the web client is used.
• The sheet can't include pictures, Shapes, Text Boxes, WordArt, Clip Art.
• Comments are not supported.
• No programming (VBA code) or other active content i.e. ActiveX controls on the sheet.
• No support for query tables (i.e. the dynamic integration of relational queries).

The configuration of an Excel services environment can be a bit complex with the setup of trusted data connections, trusted locations and document libraries is not a task that you would assign to a typical business user in the finance department.

MOSS requires the latest version of Excel 2007 and carries a price tag of around $8,000k for the server and $166 per user (CAL).

XLCubed Web

Figure 3 Dashboard in XLCubed Web

XLCubed Web evolved as an add-on to one of the initial Analysis Services spreadsheet add-ins so users could create reports and do ad hoc analysis of multi-dimensional cubes in Excel. Since 2007, XLCubed has also offered a web service component for publishing reports. More than this, it allows you to create new reports, ad hoc analysis and dynamic charting capabilities in a pure html environment (i.e. no active components like ActiveX).

Figure 4 XLCubed Web Report Design and Ad Hoc Analysis

Particularly impressive is that you can do drill throughs to RDBMS sources and direct spreadsheet cell based data entry. Also, XLCubed includes professional data visualisations like spark lines and bullet graphs. The latter technology was included after the purchase of the German specialist software developer, Bonavista Solutions.

One of the shortcomings of XLCubed is the missing integration with Microsoft's SharePoint technologies which have achieved a substantial market share over the last few years. The advantage of a SharePoint integration is that document management functionality and collaborative applications (discussion boards etc.) can be integrated with the published reports.

XLCubed Web's entry package contains a 5 port concurrent server and 5 Designers for $16,000. Additional clients are around $700 per designer or $1500 per 5 concurrent port web server pack.

Jedox Worksheet Server (WSS)

Figure 5 Jedox Worksheet Server Web Interface

Worksheet Server is part of the Jedox Open Source Business Intelligence suite. It transforms Microsoft Excel spreadsheets into server-based web applications (Excel-to-Web), allowing Excel spreadsheets to be accessed via a web browser. Although the WSS component is commercial software, its main use is as a front end for the free, open source PALO project.

PALO is a real time multi-dimensional OLAP database with an Excel add-in that offers similar functionality to TM1 and ALEA tools widely used in the finance space. Like XLCubed, WSS also supports writeback and spreading (i.e. entering data on aggregated levels) into the underlying data sources.

Figure 6 Data entry in WSS

The big differentiator to MOSS and XLCUbed is that Jedox WSS can be deployed in a non Microsoft environment and supports sources other than Microsoft SQL Server. Compared to the other two solutions, the current user interface is a bit antiquated. However, Jedox have big plans for the upcoming 3.0 version where it aims to emulate nearly the entire functionality of the Excel client on the web. The benefit of this is that designers won't need an Excel client installed on their machines anymore. Jedox WSS starts at around $15,000 for a 10 concurrent user package.

Conclusion

About Martin Kratky: After completing a business degree at the University of Economics Vienna and studies at the London School of Economics Martin has worked in the business intelligence industry for more than a decade. He is currently the managing director for INTALIGN a solution provider specialising in business intelligence and information design.

[1] http://www.forrester.com/Research/Document/Excerpt/0,7211,41687,00.html

[2] http://findarticles.com/p/articles/mi_m0EIN/is_2005_Oct_24/ai_n15729234

[3] http://download-uk.oracle.com/docs/cd/B25221_04/core.1013/b25781/odg_bi.htm