SQL Server Magazine

XML for Analysis (XMLA)—a Web-service standard proposed and supported by Microsoft and leading OLAP companies—brings together Web services and OLAP technologies by providing an XML schema for OLAP and data-mining applications. Essentially, XMLA lets you explore and query multidimensional data through Web services, which means analytical applications can move away from their expensive and difficult-to-maintain client/ server roots toward a more flexible, Web-based architecture.

XML Web services architectures connect applications and components by using standard Internet protocols such as HTTP, XML, and Simple Object Access Protocol (SOAP). These architectures offer the promise of interoperable distributed applications that can be shared between and within enterprises. Amazon.com, for example, uses Web services to support associate programs that let third parties sell from its catalog, and Microsoft's MapPoint Web service integrates location-based services into a variety of applications. Web services are becoming crucial pieces of enterprise application architecture by letting you loosely couple services from disparate applications in a way that's easy to maintain as business processes change.

The XMLA specification, available at http://www.xmla.org, describes the following design goals:

• Provide to remote data-access providers a standard data-access API that application developers can use universally across the Internet or a corporate intranet to access multidimensional data.
• Optimize a stateless architecture that requires no client components for the Web and minimal round-trips between client and server.
• Support technologically independent implementations of XMLA providers that work with any tool, programming language, technology, hardware platform, or device.
• Build on open Internet standards such as SOAP, XML, and HTTP.
• Leverage and reuse successful OLE DB design concepts so that application developers can easily enable OLE DB for OLAP applications and OLE DB providers for XMLA.
• Work efficiently with standard data sources such as relational OLAP databases and data-mining applications.

By fulfilling these design goals, XMLA provides an open, industry-standard way to access multidimensional data from many different sources through Web services—with support from multiple vendors.

XMLA is based on SOAP, and you can use it from any application-programming language that can call SOAP methods, such as Visual Basic .NET, Perl, or Java. SOAP is a lightweight, XML-based protocol for exchanging structured and type information over the Web. Structured information contains content and an indication of what that content means. For example, a SOAP message might have an XML tag in it called CustomerName that contains customer name information. A SOAP message is an XML document that consists of a SOAP envelope (the root XML element that provides a container for the message), an optional SOAP header containing application-specific information (e.g., custom-authentication information), and a SOAP body, which contains the message you're sending. Calling SOAP methods is simply a matter of wrapping the arguments for the SOAP method in XML and sending the request to the server. Because SOAP's overall goal is simplicity, the protocol is modular and easy to extend to new types of applications that can benefit from Web services. You can use Internet standards to integrate SOAP with your existing systems. Most mainstream development platforms offer some support for calling SOAP-based Web services. Both Java 2 Enterprise Edition (J2EE) and the Microsoft .NET Framework have strong support for Web services, making the invocation of remote services almost transparent to the developer.

Besides working with XMLA directly, you can use the Microsoft .NET-based ADO MD.NET library to build .NET applications that use XMLA. ADO MD.NET is the successor to the OLE DB for OLAP—based ADO MD. However, I don't cover ADO MD.NET in this article. Instead, I show you how to use the underlying XMLA protocol to build an analytic application on any device or platform or in any language that supports XML. I assume you have some knowledge of OLAP fundamentals, at least a passing familiarity with MDX, and some exposure to XML. For an introduction to XML Web services, see Roger Wolter's Microsoft article "XML Web Services Basics' at http://msdn.microsoft.com/library/en-us/dnwebsrv/html/webservbasics.asp. You'll find an even more basic and technology-neutral introduction in Venu Vasudevan's Web services article "A Web Service Primer" at http://webservices.xml.com/pub/a/ws/2001/04/04/webservices/index.html.

Installing XMLA
To use XMLA with SQL Server 2000, download the XML for Analysis Software Development Kit (SDK), available at http://www.microsoft.com/downloads/details.aspx?familyid=7564a3fd-4729-4b09-9ee7-5e71140186ee&displaylang=en, and install it on a Web server that can access your Analysis Services data source through OLE DB for OLAP. (You can simply use the server that has Analysis Services installed on it.) SQL Server 2005 Analysis Services will support XMLA as a native protocol, so you won't have to separately install XMLA. But for now, this step is necessary.

Installing the SDK is straightforward, but to run the installer, you must be logged on as an Administrator to the machine on which you're performing the installation. When you double-click the XMLADSK.msi installation package, the installer walks you through the process. Unless you have a Secure Sockets Layer (SSL) certificate configured on your Web server, you need to select Enable HTTP and HTTPS during the Connection Encryption Settings step to allow your SQL Server unsecured communication with the XMLA Provider through HTTP. Note that using the XMLA Provider in unsecured mode isn't a good idea for a production system because the provider will pass your data across the network in plain text for anyone to intercept. But for just learning about XMLA in a non-production environment, you're probably OK using unsecured communication.

After installing the SDK, you need to set up the data sources that you're going to connect to through XMLA and make the server available to clients by creating a virtual directory for the XMLA Provider. To set up the data sources, you edit the datasources.xml file in the Config subfolder of the installation folder you selected when installing the provider. The default path for installation is C:\Program Files\Microsoft XML for Analysis SDK\. The datasources.xml file contains a preconfigured example connection for the Local Analysis Server that you can copy to set up your own data sources. Figure 1 shows part of the datasources.xml file. The most important parts of this file are the required elements that facilitate the connection to the OLAP data source: DataSourceName for naming the data source; DataSourceDescription for adding a text description of the data source; URL, which provides the URL for the XMLA Provider; DataSourceInfo, which describes the OLE DB for OLAP connection to the Analysis Servers; ProviderType, which enumerates the type or types of provider being referenced—tabular data provider (TDP), multidimensional data provider (MDP), data-mining provider (DMP); and AuthenticationMode (Unauthenticated, Authenticated, or Integrated), which describes how the Web service will authenticate connections to the provider. The XML for Analysis Help file (which you installed with the SDK at \Microsoft XML for Analysis SDK\Help\1033\smla11.chm) contains complete information about all these configuration options.