on’t you sometimes wish that accessing database data were easier, and you didn’t have to deal with such things as OLE DB, ODBC, and JDBC?that you could use just one uniform approach to access the data regardless of the location and type? Well, guess what? You won’t have to wait too much longer to see this dream become reality. All major vendors are not only working on this issue seriously but have already started releasing SDKs for exactly this purpose. The point of commonality between them is XML. Like it or not, XML is becoming a standard part of programmers’ lives because it’s simple and easy to understand structure make it a suitable format for transferring data between disparate applications and platforms. But the real power of XML lies in its ability to not only extend itself, but also define itself.

Microsoft is leading this charge, and has made almost all its products XML-aware. For example, in SQL Server 2000 you can retrieve records formatted as XML documents. A complementary server called Analysis Server adds data warehousing and data mining capabilities to SQL Server. Not long ago, Microsoft released its XML/A SDK which lets you retrieve Analysis Server data over the Web in XML format. In this article, I’ll show you what XML/A is, and provide an ASP.NET example showing how you can use it.

What Is XML/A?
XML for Analysis or XML/A is a SOAP-based API, intended to standardize data access interactions between a client application and an OLAP data provider working over the Web. XML/A is not a Microsoft product, XML/A is a specification on which many vendors are working jointly. Thus far, they have released the XML/A 1.1 specification and are working on 1.2, which will offer some improvements. The benefit of these specifications is that all vendors will have a single standard to follow, making it possible for any client to access any database functionality made available on the Web.

Describing the XML/A specification thoroughly is impossible in an article of this size, but I will briefly touch the most important aspects.

XML/A is based on two methods: Discover and Execute:

You use Discover to obtain metadata such as what data sources are available, and to retrieve more detailed information about those data sources.

You use Execute to run Multidimensional Expressions (MDX), which are a way to query data cubes using the SQL for Analysis Services.

Using XML/A, all the information passed between the server and client is encapsulated in SOAP packets, which are also a W3C standard. Figure 1 (from the Microsoft XML/A specification), shows a clearer view of how XML/A works.

Figure 1: This figure shows a high-level view of the XML/A concept and architecture

Obtaining and Installing the XML/A SDK
Microsoft has released the first version of the XML/A SDK for Analysis Server. This entire SDK consists of one DLL, msxisapi.dll, and several configuration files. The msxisapi.dll sends Discover and Execute requests encapsulated in SOAP packets, wraps the response XML in a SOAP packet and sends it back to the client. To use the DLL, you need to create a Web site, map it to the msxisapi.dll and start sending and receiving SOAP packets (see Figure 2).

Figure 2: The msxisapi.dll handles sending and receiving SOAP packets to and from clients.

Now that you have some idea of what XML/A is and how it works, it’s time to set it up on your computer. Here are the steps to install XML/A:

1. Download the XML/A SDK.
2. Install it in the desired directory. I recommend that you accept the default installation path.
3. Open IIS and create a virtual directory named “xmla” and map it to the directory that contains the msxisapi.dll file. Typically, you’ll find that file in the ISAPI subdirectory of your XML for Analysis SDK installation.

After installing you can use the local URL http://localhost/xmla/msxisapi.dll (substitute your server’s domain name for localhost if you didn’t install XML/A on your local machine) to accept SOAP packets and run queries

Using the Sample VB6 Applications
You can test your installation easily by compiling a small Visual Basic 6.0 (VB6) project called Simple.vbp, installed with the XML/A SDK. You’ll find it in the Samples subdirectory. The installation doesn’t include an EXE file, because you may need to change some server- and- location-specific settings. Therefore, you need the VB IDE to run the application. Here are the steps to run the program and test the SDK:

1. Open the Simple.vbp project in VB6
2. Locate the constant WEB_SERVER in the frmSimple form, and replace the value localhost with the name of the server where you created the xmla virtual directory (see Figure 3). If you’re running your Web server locally, you can skip this step. .
3. Run the program.
4. On the form that appears, select the XML option, and then click the Execute button. That contacts the server, which generates the MDX result set.
5. Try again, this time selecting the HTML option. You’ll see the same result, but this time in tabular form (see Figure 4) resulting from an XSL transformation of the XML returned by the server. The SDK download includes the XSL file, named xamd.xsl. You’ll find it very useful for displaying OLAP data.

Figure 3. If you aren’t running a local server, replace the WEB_SERVER constant value “localhost” in the file Simple.frm with the name of your server.

Figure 4: Here’s the XML transformed by XSL using the stylesheet in the file xamd.xsl.

You should experiment with this application by trying different options and operations, and also review the code to understand it in more detail.

Creating an ASP.NET Web Services Client
Now that you know the XML/A SDK is working correctly, you can create an ASP.NET application that accesses it.

Creating Web Services:
To create a Web service, you need to know the service description, which is typically encapsulated in a Web service Description Language (WDSL) file. Microsoft provides a WSDL file suitable for creating a proxy class. The proxy class makes the process of sending and receiving SOAP packets to and from Analysis Services transparent.

The sample code contains a C# class named MsXMLAnalysis.cs which was generated automatically from the VS7MsXMLAnalysis.wsdl file. See the sidebar Generating Classes from WSDL Files for the procedure to generate class files from WSDL files automatically.

The sample code that accompanies this article contains two ASP.NET applications. The first application, OLAPWebServices, implements a Web service named “FoodMart2000,” which exposes an ExecuteMDX method. The method accepts any valid MDX query within the domain of the FoodMart2000 database. The second application, OLAPWebClient, calls the FoodMart2000 Web service, and displays the returned data on a Web form.

To test the sample:

1. Open Visual Studio.NET
2. Create a blank solution called TestOLAP
3. Add both the OLAPWebClient and OLAPWebservices projects to the TestOLAP solution using the “Add Existing Project” option (see Figure 5).
4. Rebuild both projects. You should see no compilation errors.
5. Select the OLAPWebClient, set it as your Startup project, and then press F5 to run the application.

Figure 5: Add the OLAPWebClient and OLAPWebservices to the TestOLAP solution.

On the Web page that appears in your browser, paste the following MDX query into the MDX Query box and then click the Execute button.

   SELECT CrossJoin(   {[Measures].Members}, CrossJoin(    {[Gender].[Gender].Members},   {[Marital Status].[Marital       Status].Members} )) on    columns,{[Time].Members} on rows    FROM Sales

Figure 6: Here’s a typical response from the server when you enter a valid MDX query and click the Execute button.

The OLAPWebservices Project
The sample OLAPWebservices project contains two files of interest: MsXmlAnalysis.cs and FoodMart2000.asmx.. The MsXmlAnalysis.cs class file is the proxy class that the wsdl.exe utility generates from the WSDL file VS7msxmlanalysis.wsdl (see Sidebar 1 for more information). The class provides SOAP wrapper services that make calling the Web service transparent to programmers. The code in the MsXmlAnalysis.cs file contains two methods?Execute and Discover? that perform the functions as defined by the XML/A specification.

The Execute Method

The Execute method executes an MDX query and returns the results as an XmlElement. The method accepts two parameters Command (String) and Properties (XmlElement). The FoodMart2000 Web service also contains an Execute method that accepts two parameters, both of type String. The Execute method from the FoodMart2000 Web service simply calls the Execute method in the MsXmlAnalysis.cs class to obtain the result:

   private static string Execute( _      string Command, string Properties)   {      // Create the Xml for Analysis proxy class       MsXmlAnalysis xa = new MsXmlAnalysis();     // This line is to make sure that      // we pass the IIS security checks     // without any problem. Theoretically, this      // should not be necessary, but sometimes,      // when you generate a proxy class using      // wsdl.exe, it creates a problem, so it's      // better to include the following line      xa.Credentials =          System.Net.CredentialCache.DefaultCredentials;                    //server url       xa.Url = "http://localhost/xmla/msxisapi.dll";      XmlDocument queryDom = new XmlDocument();      XmlDocument  restrictDom = new XmlDocument();      XmlElement  xelem1;      XmlElement  xelem2;      XmlElement  xelem3;         // Load the command and propertylist into a DOM       // and cast them to an element      queryDom.LoadXml(Command);      restrictDom.LoadXml(Properties);      xelem1 = (XmlElement) queryDom.FirstChild;      xelem2 = (XmlElement) restrictDom.FirstChild;         // Call the execute method      xelem3 = xa.Execute(xelem1, xelem2);                  // return the resulting XML      return xelem3.InnerXml;   }

The Discover Method

Although I won’t discuss the details in this article, you use the MsXmlAnalysis class’s Discover method to explore metadata exposed by OLAP data sources, such as the number of databases available, the cubes in a particular database, etc.

Using the MsXmlAnalysis Web Service
The Web form FoodMart2000.asmx consumes the MsXmlAnalysis Web service. It contains three main methods:

FoodMart2000(): The class constructor sets up the initial property values which it stores in the protected class-level variable Props. It sets properties for the MSOLAP data provider, DataSourceInfo and Format.

Execute(): This private method creates an instance of the MsXmlAnalysis class and calls its Execute method.

ExecuteMDX(): This is the public Web method. It accepts an MDX query string, which it then passes to the private Execute method. When that method returns, ExecuteMDX passes the resulting XML to the client, adding a tag to the returned XML to make it well formed.

Using the OLAPWebClient

The Web form MDX.aspx contains three main controls:

• a Textbox where users can enter an MDX query
• an Execute button
• an XML control that transforms the returned XML into HTML using the stylesheet in the OLAPGrid.xsl file.

The following code shows the Execute button’s onClick event handler. Note that the code strips the XML namespaces from the returned XML. The sample transformation will not run properly unless you strip the namespaces. In this case, you don’t need the. XML namespaces are used to uniquely identify elements in a scope; however as there’s no possibility of element name collisions in this application, you can eliminate them and simplify the transformation task.

   private void btnExecute_Click(      object sender, System.EventArgs e)   {      // Put user code to initialize the       // page here      localhost.FoodMart2000 olapService =          new localhost.FoodMart2000();      try      {         olapService.Credentials =             System.Net.CredentialCache.            DefaultCredentials;          strXML = olapService.ExecuteMDX            (txtMDX.Text);         strSearch = "xmlns="urn:schemas-"            + "microsoft-com:xml-analysis:"            + "mddataset"";         strXML = strXML.Replace(strSearch ,"");         Xml1.DocumentContent = strXML;      }      catch(Exception er)      {         Response.Write(er.Message.ToString());      }   }

Using the OLAPGrid.xsl Stylesheet
The stylesheet in the OLAPGrid.xsl file provides almost the same functionality as xamd.xsl. It’s not as robust as xamd.xsl but it has the advantage of being relatively easy to understand.

The MDX query returns not only the data but also an embedded schema which contains the number of columns, their data types, levels etc. The OLAPGrid.xsl stylesheet uses that information to determine the number of columns and rows required, and the levels of data available. Finally, it performs some calculations to transform the raw XML into an HTML grid containing the data.

Most of the stylesheet is straightforward; however some rows in the generated OLAP data are null. Therefore, you have to solve the problem of knowing that the value of a particular row is null. The stylesheet uses the value of the CellOrdinal attribute, which is not a cell sequence; it’s the cell location in the grid.

The code uses the same PrintCell template found in xamd.xsl to display cell values.

PrintCell is a recursive template called for each tuple. The stylesheet passes two parameters to the template: $varStartCell and $varEndCell. These two variables define the boundary of a tuple. The stylesheet calculates the value of $varStartCell by multiplying the tuple number by the total number of columns in Axis0. The stylesheet calculates the $varEndCell value by adding one to the tuple number, and then multiplying that by the total number of columns in Axis0.

The stylesheet calls the PrintCell template for each tuple, and the PrintCell template calls itself again recursively, adding 1 into $varStartCell each time. The recursive call continues to print cells until it reaches to the boundary of that tuple, which it determines by comparing the value of $varStartcell and $varEndCell value. When the value of $varStartCell becomes equal to the value of $varEndCell, the PrintCell template stops calling itself.

         0      0                                                                                                                                                     null                                                                                                

I hope this article introduced you to XML/A and how you can use it. Improvements to the XML/A specifications in the near future will make it an even more powerful and scalable solution.

1. Download the file VS7msxmlanalysis.wsdl here.
2. Generate the C# class MsXMLAnalysis.cs by using the wsdl.exe utility that ships with both Visual Studio and the .NET Framework SDK. Typically, you’ll find the utility in the folder /Program Files/Microsoft.NET/FrameworkSDK/bin on the drive where you installed the SDK. Run the utility using the following command, adding path information to the filenames if necessary. For help, see the MSDN documentation or type wsdl at the command prompt and then press Enter.