Building a Web-Based Analysis System

A real-world look at using the Analysis Services Thin Web Client Browser

When working with analytical databases, analysts organize data into common groups and try to determine what would happen if things were different.

For example, would increasing a product's price—which would increase profit per unit but probably reduce number of units sold—yield a higher or lower overall profit? Or how would a drop in the federal discount rate affect the yield of real estate loans? To help analysts make educated projections based on historical trends, Microsoft provides Analysis Services in SQL Server 2000 and OLAP Services in SQL Server 7.0. These services provide OLAP capability and can process data stored in SQL Server (or any other OLE DB—compatible data source) into multidimensional data structures called cubes. Data cubes simplify the process of analyzing trends and correlating the way entities interact with one another.

For example, real estate investors use cash-flow modeling to isolate a group of loans that have common characteristics (e.g., types of properties, geographic area, range of interest rates) and project the effects of different kinds of events. What will happen if loans mature more rapidly than expected or if the borrowers default? And how might such unpredictable events affect the yield of bonds that the loans secure?

Selecting from lists that can include hundreds of loans and isolating the loans that have the characteristic that you're analyzing can be tricky. Analysis Services and OLAP Services can help correlate these groups of loans so that analysts can model loan assumptions. To help a client's real estate analysts project the performance of commercial mortgage-backed securities, our development team needed to devise a system that simplified the grouping of loans in different ways—such as by their interest rate, term to maturity, or property location. The interface needed to be easy to learn and use. And the system we developed needed to be securely deployed through the Internet. To meet these criteria, the development team chose Analysis Services.

Using OWC to Deploy Office on the Web

Having settled on a back-end technology, the development team began working on a plan for implementing the front-end interface. Most financial analysts use Microsoft Excel and are familiar and comfortable with its interface. Excel includes PivotTable Service, which lets analysts connect to Analysis Services databases. Excel's drag-and-drop interface provides simple, intuitive access to multidimensional data without requiring users to have extensive training. And by using Excel's graphing capabilities, users can present data in graphs and charts. So for the front-end interface, the team's first choice was Excel 2002, which is part of Microsoft Office XP. Figure 1 shows Excel's PivotTable Service exploring an Analysis Services OLAP cube.

Excel would have been a fine choice—if all the client's users worked together in the same building and could access the Analysis server through the same LAN. But because the users needed to share the application from a variety of organizations whose offices are scattered around the world, the team needed a component similar to Excel that users could access through the Internet. The team found the solution to this challenge in Office Web Components. OWC is a set of ActiveX controls that you can use on Web pages to provide Office functionality. The OWC PivotTable component is a Web version of Excel's PivotTable Service; PivotTable uses PivotTable Service and requires that PivotTable Service be installed before it will run. But the OWC PivotTable works without Excel.

PivotTable can retrieve multidimensional data from an Analysis server and present the data in an interactive, drag-and-drop interface. Users who have Microsoft Internet Explorer (IE) 4.01 or later can use OWC to analyze Analysis Services data without installing additional component software. Figure 2 shows the OWC PivotTable client interface, which looks and works like the familiar Excel interface. The OWC PivotTable also provides intelligent caching, which improves performance by reducing the number of trips PivotTable makes through the network to the server. So by actively working with Analysis Services, PivotTable can reduce data transfer and work faster.

Although OWC provided everything our development team's project needed, we encountered problems when we tried to deploy OWC across the Internet. The first problem was the platform that OWC runs on. The Office XP version of OWC requires Microsoft Data Access Components (MDAC) 2.6 or later. Many of the service subscribers use Windows NT Workstation 4.0 as their OS, and to install MDAC 2.6, they also had to install Service Pack 6 (SP6). One of the primary attractions of using OWC was that we thought deployment would be seamless. We discovered that although we could automate the process of installing service packs, the process requires reboots and is intrusive. Microsoft later provided a revised version of the OWC component that works with SP4, but at the time we were developing our application, the deployment of service packs in the tightly controlled client network of a financial institution was a significant barrier. Thus, a solution that required a specific service pack for the OS wasn't a viable option.

The second problem that our team encountered was connectivity. OWC requires a direct connection to the Analysis Services data source. OWC communicates directly with the Analysis server through the default port, 2725, which is a problem for organizations with firewalls. (For details about connecting to Analysis Services, see the Microsoft article "INF: TCP Ports Used by OLAP Services when Connecting Through a Firewall" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q301901.)

First, we tried to solve the connectivity problem by using an HTTP connection to connect to the server through port 80. This connection provides access through the same port that the Web browser uses. For added security, Analysis Services can alternatively connect through port 443 by using Secure Sockets Layer (SSL). Most organizations open both port 80 and port 443 to let users access the Internet. (For more information about using HTTP, see the Microsoft article "INF: How to Connect to Analysis Services 2000 By Using HTTP Connection" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q279489.) Figure 3 illustrates the challenges involved in using OWC to connect to the Analysis server.

However, the implementation of HTTP connectivity created some insurmountable obstacles. Connections through port 80 were significantly slower than direct connections in our tests. Because the multidimensional cube has to provide large amounts of data to the client, the performance degradation made using OWC impractical.

Looking for Alternatives

Next, our team considered using ADO-MD and MDX queries to create a custom interface. You can query the Analysis Services cube directly through the OPENROWSET command. (For information about querying Analysis Services, see the Microsoft article "HOWTO: SQL Server 7 Distributed Query with OLAP Server" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q218592.) OPENROWSET lets you query data from any OLE DB source, including Analysis Services. This flexibility would let us use ADO to query Analysis Services. The OLE DB provider for Analysis Services, MSOLAP, converts the multidimensional data into a standard rowset that ADO can use to communicate the data to the front-end application. The problem with this custom solution was the complexity of creating an interface that provided the intuitive, interactive look and feel of OWC and Excel. Although the team was capable of creating such an interface, the time, expense, and ongoing maintenance of such a solution made it prohibitive.

The team also investigated several third-party solutions. Many third-party solutions help users build a query and execute it to see the results. Such an approach uses system resources efficiently but isn't as interactive as the Excel and OWC drag-and-drop interfaces. So although these solutions offered unique advantages, none met the exact requirements of the project. When our team added custom development costs to the overall cost of the third-party software, we decided to look elsewhere.

Using the Thin Web Client to Access Multidimensional Data

The development team members finally went to the Microsoft SQL Server Resource Kit and found the solution to our dilemma: the Analysis Services Thin Web Client Browser. (To access the Thin Web Client on the resource kit CD-ROM, see the reference in chapter 39 of the resource kit at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part11/c3961.asp.) The Thin Web Client uses Active Server Pages (ASP) to connect to the Analysis server, converts the multidimensional data to HTML, and passes the data to the client. Figure 4 shows the Thin Web Client displaying a subset of loan data.

The Thin Web Client requires IE 5.0 or later. Because the client doesn't connect directly to the Analysis Services computer, the client doesn't need MDAC 2.6. Most of our client's subscribers have IE 5.0, so we didn't have to deploy service packs to the subscribers' PCs. Figure 5 shows the architecture that the Thin Web Client uses. The Thin Web Client uses ASP to query the cube from the Microsoft IIS server rather than from the client. The Thin Web Client comes with ASP pages, which you can deploy on an IIS server. Only the IIS server that's running ASP can connect to the Analysis Services database. Thus, you can use one connection to secure cube access through the firewall, and you can limit that connection to just the Web servers and the database server. The arrangement creates a high-performance, easily secured connection. The only connection to the client is a standard HTML connection, which alleviates the firewall concerns.

The Thin Web Client puts the queried data in a gridlike HTML table structure and sends the data to the browser. The user interacts with the data through JavaScript. By using transparent layers, users can drag dimensions into the cube to manipulate the data, drill down through dimensions, and display and analyze the data they need.

Using the Thin Web Client requires some compromises, as most design decisions do. Because a trust relationship can't exist between the Web server domain and the back-end domain, you can't extend Active Directory (AD) as your authentication service through the system. Such a trust relationship could provide a means for an intruder to violate security on the back-end domain. So the system must set up one anonymous user to access the data on the Analysis server. If access to the data depends on the user, you can't use Analysis Services role-based security to control access to the data. In a system that lets all users access the data through one security context, however, this arrangement using the Thin Web Client is secure and easy to deploy.

The Thin Web Client also has other drawbacks. OWC uses PivotTable's intelligent caching to minimize trips from the Web browser to the OLAP Web server to get more data. But the Thin Web Client draws data from the server side, and because data isn't cached at the Web browser, each change to the data requires the browser to make a new request from the Web server. When you're working with large amounts of data, this process can be sluggish. Also, OWC has a rich object model against which you can program custom code. The Thin Web Client uses JavaScript and can be more difficult to customize. Because the Thin Web Client is "free code" included in the resource kit, Microsoft doesn't provide the same level of support for the Thin Web Client as it does for Excel or OWC. And the Thin Web Client requires client-side scripting, which can produce errors if the Web browser is updated or changes. In addition, the Thin Web Client doesn't directly support write-back to the server. Fortunately, our team's application didn't require write-back, so this missing element didn't deter the team from using the Thin Web Client. After evaluating the advantages and drawbacks of the Thin Web Client, we decided to deploy it. The solution works well in the production environment and has been well received by subscribers.

Choosing the Right Client for the Task

The lessons our development team learned from this project can provide a valuable guide for deploying OLAP solutions in an enterprise. Excel provides a capable, familiar client that you can deploy in a LAN but requires realtime connectivity to the OLAP server. OWC works well for deploying an Analysis Services client in an intranet because you can easily control the client platform and open ports securely in an intranet. The Thin Web Client provides a good Internet solution when firewalls are in place and you want minimal impact on the user OS. For any development project, you need to understand the business requirements and needs of the people who will use the products you develop. By outlining requirements and weighing all the options, you can discover the right solution to satisfy your client's requirements.