Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
8 out of 15 rated this helpful - Rate this topic

Implementing the XML for Analysis Provider for SQL Server 2000 Analysis Services

SQL Server 2000
 

John Mikesell
Microsoft Corporation

January 2004

Applies to:
   Microsoft® XML for Analysis SDK 1.1
   Microsoft® SQL Server™ 2000 Analysis Services
   Microsoft® XML Core Services (MSXML)

Summary: See how to install the XML for Analysis Provider for SQL Server 2000 and implement XML for Analysis client applications. (12 printed pages)

Contents

Introduction
Installing the XML for Analysis Provider
Creating the Virtual Directory
Enabling the XML for Analysis Web Service Extension on Windows Server 2003
Setting Up Data Sources
Configuring Security for the Provider
Testing and Debugging the Installation
Implementing XML for Analysis Client Applications
Using the XML for Analysis Web Service
Frequently Asked Questions
Troubleshooting
For More Information

Note   This article is being published to the Web before the final release of the XML for Analysis SDK 1.1, so references to version 1.1 of the XML for Analysis Provider (included in the SDK) refer to the Beta version. Instructions that do not mention version 1.1 apply equally to version 1.0 of the provider.

Introduction

The Microsoft® XML for Analysis Provider is a Web service providing access to SQL Server 2000 Analysis Services. It implements the industry standard XML for Analysis Specification, which defines communications between client applications and analytical data providers (OLAP and data mining), regardless of the language used to write the application. Leveraging the benefits of XML and SOAP, it allows client applications to access Analysis Services databases through firewalls and across the Internet, making creation of the client applications much simpler.

The XML for Analysis Service Provider is designed for use within an n-tier application. A typical design pattern would consist of three tiers: a client computer running an analytical application, a Web server (running Internet Information Server (IIS), the Pivot Table Service, and XML for Analysis), and a database server running the Analysis Services server component. The client application would use SOAP to communicate with XML for Analysis. The Web server would use TCP/IP or HTTP to communicate with the databases server running Analysis Services.

The result is a highly scalable architecture. The client-to-Web server protocol is usually stateless, so no database connection is required for each client. The Web server pools the database connections and reuses them for each client request. This minimizes the number of active sessions and minimizes the overhead to handle large numbers of clients. No special software (ADO or OLEDB database drivers) is required, just SOAP and XML. In this article, we'll dive into the details of installing the provider and implementing a sample client application that uses it.

Installing the XML for Analysis Provider

To start working with the XML for Analysis Provider, you first need to install the XML for Analysis SDK from the Microsoft Download Center. Unless you are working with applications that specifically require an earlier version, download the latest version of the SDK—currently XML for Analysis Software Development Kit version 1.1. If you are an end user, request that your IT Services group install XML for Analysis on a server.

Important   Before you install the XML for Analysis SDK, you must first install the correct version of Microsoft XML Core Services (MSXML). Use MSXML 4.0 for the 1.1 version of the XML for Analysis SDK or MSXML 3.0 for the 1.0 version of the SDK. For instructions to download and install MSXML, see the Knowledge Base article, 324460 HOW TO: Upgrade the Microsoft XML Parser. For additional MSXML information and downloads, go to the MSDN XML Developer Center. For more information about the system requirements for XML for Analysis, see the Readme file for the XML for Analysis SDK.

Install the 1.1 version of the SDK by running the Xmlasdk.exe installation file downloaded from the Microsoft Download Center. The default install location for the SDK is "C:\Program Files\Microsoft XML for Analysis SDK." Normally you will want to install the SDK on the same computer that is running SQL Server 2000 Analysis Services (for remote installations, see "Can I use XML for Analysis Services with a remote server" in the FAQ section). The installation program will replace any earlier version of the SDK, as you cannot use two versions of the SDK on the same computer.

By default, Setup for the XML for Analysis SDK 1.1 installs with the requirement that all requests to the provider use the HTTPS protocol—the HTTP protocol is disallowed. On the Connection Encryption Settings page of Setup, you can choose to Enable HTTP Protocol connections. For client connections to a remote server over the Internet, we recommend that you do not enable client connections using the HTTP protocol. If you do not enable HTTP connections and later find that you require them, see "How do I enable HTTP connections to the XML for Analysis Provider?" in the FAQ section.

Creating the Virtual Directory

To make the provider available to Web clients, use IIS Manager on the same computer as the provider to create one or more virtual directories. In IIS Manager, right-click the Web site you want to use, point to New, and then click Virtual Directory. This opens the Virtual Directory Creation Wizard, which prompts you for the following information:

  • For the directory containing the content to publish, enter the path to the folder containing Msxisapi.dll. For simplicity, use the path to the installation folder that contains Msxisapi.dll. The default location for Msxisapi.dll is C:\Program Files\Microsoft XML for Analysis SDK\Isapi. If you specify a different folder for the content directory, you must then copy Msxisapi.dll from the installation folder to the specified folder.
  • The access permissions for the virtual directory should be set to allow Read, Run Scripts, and Execute access.

Use IIS Manager to configure security for the XML for Analysis Provider. Right-click the virtual directory in IIS Manager, click Properties, and then click the Directory Security tab to enable anonymous access and authentication, IP and domain name restrictions, or secure communications.

Enabling the XML for Analysis Web Service Extension on Windows Server 2003

On Microsoft Windows® Server 2003, IIS is installed by default in a highly secure and "locked" mode. Before you can use XML for Analysis on Windows Server 2003, you must enable XML for Analysis as a Web service extension. If you do not enable this functionality, IIS returns a 404 (file or directory not found) error. To do this, right-click the Web Service Extensions folder for the computer you are administering in IIS Manager, and click Add a new Web service extension. In the New Web Service Extension dialog box, specify a friendly name for the Web service extension. For required files, add the complete path name for the Msxisapi.dll file. Select the Set extension status to Allowed check box.

Setting Up Data Sources

To make data sources available to client applications, you need to specify data sources in the Datasources.xml file. The XML for Analysis Provider sends this document to client applications so they can select a data source. The default location for this file is C:\Program Files\Microsoft XML For Analysis SDK\Config. Datasources.xml initially exposes Analysis Services on the same computer that is running the provider. You can edit this file to change the settings for the local server or to expose Analysis Services on a remote computer.

The file is structured as an XML document with the root element <DataSources>. Each <DataSource> element within the root element specifies a different instance of Analysis Services. To add an additional data source, merely add an additional <DataSource> element to this file. Any data source specified in this file needs to be accessible to the provider. The syntax for the file is detailed in "Setting Up Data Sources" in the XML for Analysis online Help.

Configuring Security for the Provider

The XML for Analysis Provider always runs with the credentials chosen by IIS based on the security you set on the virtual directory.

Security SettingDescription
Anonymous authenticationIIS always runs the provider as the IUSR_computername (when Application Protection is set to Low) or IWAM_computername user (when Application Protection is set to Medium or High).

For anonymous authentication, the IUSR_computername or IWAM_computername user must be added to a cube role for the provider to access the cube.

Integrated Windows authentication turned on and anonymous access turned offIIS attempts to impersonate the client user and runs the XML for Analysis Provider as that user. If, however, you have multi-machine delegation in your scenario, then security will not be able to delegate credentials and the XML for Analysis Provider may again run as the IUSR_computername or IWAM_computername user.
Integrated Windows authentication turned on and anonymous access turned onRuns as anonymous authentication
Basic authentication (over HTTPS highly recommended)IIS impersonates the client using the username and password provided by the client. The XML for Analysis Provider runs as the client user.
Client certificatesClient certificates are electronic documents that contain information about clients. These certificates contain encryption keys that facilitate encryption and decryption of transmitted data over an open network. Client certificates are worth investigating for installations that require mapping from client users to domain users.

For more information, see the "Certificates" topics in the IIS Documentation or search for "Client Certificates" in the MSDN Library.

Testing and Debugging the Installation

You build and run any of the three Visual Basic applications—Sample, Sample.NET, or Simple—that come with the SDK to test your installation. The Sample program is a Visual Basic® 6.0 program that issues MDX queries against the XML for Analysis Web Service and displays the results as XML or in grid format. Sample .NET is similar to Sample, but is a managed code application written using Visual Basic .NET. The Simple application is a Visual Basic 6.0 application that demonstrates the basic functionality of XML for Analysis exercising the methods and capabilities in the SDK. You can use any of these programs to connect to catalogues in the any of the data sources specified in Datasources.xml file. Instructions for installing the samples can be found in the topic, "Samples for XML for Analysis" in the online Help for the XML for Analysis SDK.

If you have general problems connecting to the provider, check the following:

  • Try browsing to the URL for the ISAPI DLL (http://localhost/xmla/msxisapi.dll). This should display an XML documented generated by the DLL indicating, "The Web Service supports only the POST operation." If this fails, perform the following checks.
    • Check that IIS virtual directory has been configured correctly to point to the folder containing the Msxisapi.dll library.
    • If the installation is on the Windows Server 2003 server, check that the XMLA Web service extension is enabled in IIS Manager.
    • Make sure that you have not disabled GET/POST commands for the virtual directory in the IIS configuration.
    • Make sure that execution of scripts and executables is allowed for the virtual directory in the IIS configuration.
  • Try executing the Simple application provided with the XML for Analysis SDK and see if it is able to discover catalogs from the SQL Server 2000 Analysis Services server. If this fails, perform the following checks.
    • Check that IIS virtual directory has been configured correctly to point to the folder containing the Msxisapi.dll library.
    • If the installation is on the Windows Server 2003 server, check that the XMLA Web service extension is enabled in IIS Manager.
    • Check that your data source description and its connection string is correctly configured in the Datasources.xml file.
    • Check the IIS security settings for the virtual directory and for your Analysis Services databases. For example, the IIS virtual directory may have anonymous authentication turned on, but OLAP security is configured to not allow anonymous access to your database.

If you have less general problems connecting, you can access the microsoft.public.data.xmlanalysis newsgroup on the msnews.microsoft.com news server to ask questions and participate in discussions about the Microsoft XML for Analysis Provider and to get peer-based support.

Implementing XML for Analysis Client Applications

XML for Analysis is a SOAP Web service. An application communicates with this service by sending XML-encoded messages and getting XML-encoded information back in response. There are two SOAP methods exposed by the XML for Analysis Web service, Discover and Execute.

The Discover method queries metadata from an Analysis Services server. The Discover method is a highly parameterized method for OLAP metadata discovery. Access to the metadata is required to build a user interface with which the user can construct queries without knowing MDX. You use arguments on the Discover method to find data sources (servers) available to the provider, catalogs on a server, and information about objects in a catalog, such as cubes and dimensions in a cube.

The Discover method has three input parameters: RequestType, Restrictions, and Properties. The Discover parameters are packaged inside XML tags. The first parameter, RequestType, identifies the type of discovery operation that you are requesting. The Restrictions parameter uses a list of XML name and value pairs to restrict the number of the rows returned by the result set. The Properties parameter identifies the context under which the application will make the Discover call. Because XML for Analysis is usually stateless, this parameter passes information that the server would otherwise establish and maintain with the connection.

To connect with a data source, first use the DISCOVER command with the DISCOVER_DATASOURCES <RequestType> to get the information specified in the Datasources.xml file. Then, with each subsequent request, use the <DataSourceInfo> property on DISCOVER or EXECUTE set to the exact string value that is returned in the DataSourceInfo column of the DISCOVER_DATASOURCES rowset obtained by the first use of the DISCOVER command. This requirement is mandatory in the 1.1. version of the XML for Analysis Specification.

Security   In version 1.1 of the XML for Analysis SDK, the value for DataSourceInfo returned by the DISCOVER_DATASOURCES rowset is actually the value for <DataSourceName> element in Datasources.xml. For security purposes, the value for the <DataSourceInfo> element should not be exposed to client applications.

The Execute method accepts an MDX command or query and returns a result set. Execute has two input parameters, Command and Properties. The Command parameter is an MDX string, and the Properties parameter is the same information that you passed in the Discover method. The result set is typically in a hierarchical multidimensional format, although you can also specify tabular format or native format (for which the provider determines the appropriate format and identifies it by the namespace of the result). Execute is used to run analyses and return the results incorporated in reports and displayed to users.

XML for Analysis does not by default maintain connection or state information between method calls. Therefore, you must provide all the connection information with every XML for Analysis method call. Because XML for Analysis is stateless, it can share resources among clients and scale to support many clients.

However, in some situations, maintaining a session is important—for example, when you want to create a calculated member or set, and then use the member or set with subsequent MDX queries. XML for Analysis supports stateful operation to handle these situations. Use the BeginSession SOAP header to begin a session and get back a session header containing a SessionID. You then send back that session header with each subsequent request to that session. End the session with the EndSession tag.

Possibly an even more compelling scenario happens during a write-back to a cube. The default mode for UPDATE CUBE is autocommit, so if you do not open a transaction, every UPDATE CUBE will automatically open one and (if the statement succeeds) commit. Several write-back commands might together make one transaction, so you need to group them together with one commit operation. If you are making multiple UPDATEs when doing write backs of cube cells, you need to do the following:

  • Begin the new session and obtain a session ID.
  • Issue the BEGIN TRANSACTION statement using the session ID obtained in step 1.
  • Issue one or more UPDATE CUBE statements using the session ID obtained in step 1.
  • Issue the COMMIT TRANSACTION (or ROLLBACK TRANSACTION) statement using session ID obtained in step 1.
  • End the session.

Sessions are not the same thing as transactions. A transaction is begun within a session, so you should never use transactions without having a session. Otherwise, you may see unexpected results when multiple users are connected to an Analysis server.

For more information about sessions, see "Statefulness and Sessions Support" in the online Help for the XML for Analysis SDK.

Using the XML for Analysis Web Service

The XML for Analysis Provider exposes a Web service that can be used by client applications to query OLAP cubes. You will need a SOAP client API and an XML parser. Microsoft encapsulates the XML for Analysis service description in a Web service Description Language (WDSL) file that you can use to create a proxy class. The proxy class makes the process of sending and receiving SOAP packets to and from Analysis Services transparent. In order to present XML results on the Web, you can use style sheets.

The XML for Analysis SDK 1.1 WSDL file, Vs.wsdl, is located with the Sample.Net program files in the Web Reference folder (by default, C:\Program Files\Microsoft XML For Analysis SDK\Samples\Sample.NET\Web References\MsXmlAnalysis). If you are using XMLA 1.0, you can download its WSDL file from the Microsoft Download Center (search for "XML for Analysis").

The easiest way to use the WSDL file in Visual Studio is to go through the "Add Web Reference" wizard. The opening screen allows you to enter the URL of the WSDL file and then you can add the Web reference to your project. The Web reference generates the file "Msxmlanalysis.cs" (if you are using C#) and you can examine this file to find out how to instantiate and invoke the generated class.

A more customizable method is to use the XML Web Services Description Language Tool (Wsdl.exe) to generate an XML for Analysis Web service client proxy class. To generate the C# class Msxmlanalysis.cs, use the following command line (specifying a path if necessary):

Wsdl vs.wsdl

You can use the /language:vb argument to generate the Visual Basic class, Msxmlanalysis.vb. Additional optional command-line arguments let you specify a namespace, authentication parameters, and other information. For additional information about the Wsdl.exe command syntax, refer the MSDN documentation or type wsdl at the command prompt, and then press Enter.

Note   The Wsdl.exe utility ships with both Visual Studio and the .NET Framework SDK. The utility is typically located in the folder /ProgramFiles/Microsoft.NET/FrameworkSDK/bin on the drive where you installed the SDK.

That command creates the class file in the same folder as the source WSDL file. After you add the Msxmlanalysis.cs class to a Visual C# project, you will see that it provides the Discover and Execute methods.

Frequently Asked Questions

Can I use the XML for Analysis Provider with a remote server?
You can use XML for Analysis with a remote server if you are using Basic Authentication over HTTPS or you do not require a secure connection (anonymous access over HTTP). However, it is better not to use XML for Analysis with a remote server. Apparently it causes all requests to be serialized. This occurs due to behavior of the WinInet component used by the Pivot Table Service (the SQL Server 2000 Analysis Services OLE DB for OLAP Provider). IIS (and Msxisapi.dll) should be running on the same server as Analysis Services for users accessing data from a Web page.

One alternative is to use Basic authentication over HTTPS on the first domain and regular domain security to connect with Integrated Authentication to the remote Analysis Services server. However, users of client applications will have to manually enter their user name and password.

Another alternative is to give all your users the same roles. Delegation of their credentials is then not a problem if the Web service is running as a user with permissions to connect to the Analysis Services server. However, this works only if you do not need user-specific authentication.

How do I get levels in a dimension?
The Microsoft XMLA DISCOVER command supports OLE DB for OLAP schema rowsets. Use MDSCHEMA_LEVELS with the DISCOVER method. The Microsoft XML for Analysis Provider supports MDSCHEMA_LEVELS as an extension to the XML for Analysis Specification. You may want to discover hierarchies (with MDSCHEMA_HIERARCHIES) before you discover levels.
How do I recognize a measures dimension or level?
MDSCHEMA_DIMENSIONS contains the DIMENSION_TYPE column, which you can use to differentiate measures.
If two users are using different IDs, would the Pivot Table Services cache be kept for each one?
If sessions are used, then the cache is never reused. If sessions are not used, if the different users belong to exactly the same roles, and there is no dynamic security (that depends on Username function), then the connection is reused.
Is there a way to control the size of the cache stored by Pivot Table Services on IIS?
Use the "Client Cache Size" property in the connection string in Datasources.xml. Refer to the OLEDB documentation for Client Cache Size to implement this correctly.
How do I enable HTTP connections to the XML for Analysis Provider?
For secure communications with the XML for Analysis Provider on a remote server, we recommend that you do not enable client connections using the HTTP protocol. If HTTP connections to the provider are not enabled during Setup, and you later find that you require the HTTP protocol, you can enable it in either one of two ways:
  • Run the XML for Analysis SDK 1.1 Setup program first to uninstall and then to reinstall the SDK. When you reinstall the SDK, make sure on the Connection Encryption Settings page of Setup that you select the Enable HTTP Protocol (unencrypted communication) check box.
  • Edit the Datasources.xml file and set the AllowInsecureTransportFlag attribute on <DataSources> to 1. The default location for this file is "C:\Program Files\Microsoft XML For Analysis SDK\Config\datasources.xml."
How do I delete connections from the connection pool when using stateless sessions?
If you are using stateless sessions, you can use the following steps to drop any existing connections in the pooled connection.
  1. Edit Datasources.xml and change the value of UnnamedSessionsTimeout to 0.
  2. Send a Discover request for DBSCHEMA_CATALOGS.
  3. Edit Datasources.xml and change UnnamedSessionsTimeout back to its original value.
  4. Send another Discover request for DBSCHEMA_CATALOGS.
Can I hard-code DataSourceInfo instead of using the DISCOVER_DATASOURCES to get it?
You may find that you can hard-code DataSourceInfo. However, as described in the XML for Analysis Specification 1.1, you should never hard-code DataSourceInfo. This allows your code to work against other XML for Analysis Providers and against different versions of the Microsoft provider.
Can I install the XML for Analysis 1.1 SDK side-by-side with the 1.0 SDK?
Only one version of the provider can be installed on a single server. The COM objects are registered the same, so only one will work.
Are multiple hierarchies in a single dimension supported by the XML for Analysis Provider?
If you have multiple hierarchies in your dimensions, you may find that the MDSCHEMA Members rowset does not return the hierarchy name for dimension members when the dimension contains multiple hierarchies. You are therefore unable to distinguish between hierarchies in the dimension. A hotfix that resolves this issue is available. Knowledge Base article 819606 FIX: Hierarchy Name Is Not Returned in MDSCHEMA Rowset has information on how to install the hotfix for this issue.

Troubleshooting

404 File not found or "The page cannot be found" "The page cannot be displayed"
Make sure that you have set up your IIS Web server correctly to specify the location of Msxisapi.dll for the XMLA virtual directory. Try browsing to the URL for the ISAPI DLL (http://localhost/xmla/msxisapi.dll) and make sure that you see a SOAP Fault XML document generated by the DLL indicating "The Web Service supports only the POST operation". If you do not see this document, then you need to reconfigure IIS to point to the Msxisapi.dll library, or you need to reinstall the XML for Analysis SDK to locate Msxisapi.dll correctly. On the Windows Server 2003 server, you must enable the XML for Analysis Web Service Extension.
404 file or directory not found for XML for Analysis installed on Microsoft Windows Server 2003
On Microsoft Windows Server 2003, IIS is installed by default in a highly secure and "locked" mode. To use XML for Analysis, you must enable XML for Analysis as a Web service extension. If you do not enable this functionality, IIS returns a 404 (file or directory not found) error. For more information, see Enabling the XML for Analysis Web Service Extension on Windows Server 2003 earlier in this article.
Unable to process the request, because the DataSourceInfo property was missing or not correctly specified
First make sure that the data source is correctly configured in the Datasources.xml file (see Setup Data Sources). If it is correctly configured, you may not be using the correct string for the DataSourcesInfo property. A client should not construct the contents of the DataSourceInfo property to send to the server. Instead use the Discover method to find the data sources supported by the provider and send back the same value for the DataSourceInfo property as received with the DISCOVER_DATASOURCES rowset.
I cannot view some of the databases on the Analysis Services.
In IIS Manager, check the security settings for the XML for Analysis virtual directory. If the security is set to the default security (anonymous access), access for the IUSR_servername or IWAM_computername user must be added to cube roles. You can send an MDX Query to return the "username" and easily check if it is the IUSR_servername or IWAM_computername user.
I get an error "Class not registered" or "Library not found" when sending my first request to the XML for Analysis Provider.
Check if MSXML 4.0 is installed (for the XML for Analysis 1.1) or if MSXML 3.0 (for XML for Analysis 1.0). Setup for XML for Analysis SDK does not install MSXML, so you must install it separately. For more information and downloads for MSXML, go to the MSDN XML Developer Center.

For More Information

This article discussed installing the XML for Analysis provider and implementing XML for Analysis client applications. In addition, here are more resources available to you:

  • Download ADOMD.Net—Microsoft® ActiveX® Data Objects (Multidimensional)—and use it to access the XML for Analysis Provider 1.1. ADOMD.Net is the .NET successor to the ADO (COM) object model used to build client components. For many applications, ADO MD provides easy access to multidimensional data from common programming languages without accessing XML for Analysis directly. (The forthcoming SQL Server 2005 release includes the next major version of Analysis Services. XML for Analysis is the native protocol for Analysis Services, and in SQL Server 2005, Web services are natively supported by the server. ADOMD in the SQL Server 2005 release will provide easy access to multidimensional data from common programming languages.)
  • To view the online Help for XML for Analysis, click Start, point to All Programs, point to Microsoft XML for Analysis SDK, and then click Books Online. This online help file documents methods, properties, data types, schema rowsets, and error handling for the XML for Analysis Provider.
  • Find the XML for Analysis 1.1 SDK at the Microsoft Download Center. Search for "XML for Analysis SDK." ADO MD.Net can also be accessed with a hyperlink on the XML for Analysis SDK 1.1 download page.
  • You can find additional technical resources for XML for Analysis in the topic "Additional Resources" in the online Help for the Microsoft XML for Analysis SDK.
  • If you have questions or support, you can access the microsoft.public.data.xmlanalysis newsgroup on the msnews.microsoft.com news server to participate in discussions about the Microsoft XML for Analysis Provider and to get peer-based support. You can access this news group either with a news reader or from the Google Groups page.
  • The XML for Analysis Specification is available for download at the XML for Analysis (XMLA) Advisory Council Web site (XMLA.org).
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.