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.