page static_html.xsl b2003_4_10

Calling XML/A With WSH

This articles continues to incrementally build up a toolkit for declarative analytics using XML/A. There are two options for making SOAP calls from WSH. The first was demonstrated in the Amazon example, where I opened a SOAP request template as an XML file. The Second approach is to use a SOAP client library.

As is described in the XML for Analysis Specification, there are two SOAP functions exposed by this web service. The first function is Execute, which accepts an MDX query and returns a hierarchical, multidimensional result set (basically a subcube). Execute is the workhouse of XML/A since this is the command which runs an analysis and returns results which will be incorporated into a report. The other function provided is Discover. Discover queries the "metadata" of one or more OLAP servers. Through proper setting of Discover's arguments, you can ask what datasources (servers) are present, what catalogs are present on these servers, what cubes are present on these catalogs, what dimensions are present in the cubes, etc. I will demonstrate the specifics of using both Execute and Discover in these articles. You can also look at the two sample programs which came with the SDK to see further examples of their use.

Calling XML/A with a SOAP template

The code in execute.wsf makes an Execute call to XML/A. Following the model of the Amazon sample, the file xmla-execute.xml containing a template for the SOAP request is loaded and modified before making a Web Service method call. The modification made is to insert an MDX query, a Datasource, and a Catalog into an Execute template. In this example, the HTTP call is made synchronously so the result is available as soon as the send() function returns. The XML resultset is then loaded into an XML document and saved. Finally, an Internet Explorer brower instance is started and this XML file is loaded for viewing.

This and future wsf scripts include a file named transform.js which contains a library of reused JavaScript functions involved with XML processing.

The results obtained from a call to XML/A are not exactly in a human consumable form. This is the nature of Web Services - they are meant for computer consumption. Later in this article I show to transform this XML into a tablular report.

Transforming the XML returned by XML/A

The next sample introduces a key programming idiom to our declarative analytics toolkit. In this example, I use XSLT (eXtensible Stylesheet Language for Transformation) to generate a tabular report from the XML returned from the Execute SOAP method. XSLT is the workhorse of the XML world. It is used to transform XML to and from other formats - including other XML formats. In execute-xform.wsf, I have extended execute.wsf to add a transformation which creates an HTML table from the XML/A resultset.

The XSLT which I used for this transformation is given in xamd.xsl. This stylesheet has been just slightly modified from the "optimized" stylesheet which Microsoft has posted several times on the XML/A newsgroup.

The Discover method

The Execute method runs an MDX query and gets an XML result set. This is the fundamental operation in OLAP reporting with XML/A. But a complete OLAP API requires the ability to query the "metadata" from OLAP servers. The Discover method is a highly parameterized method for OLAP metadata discovery. Access to this metadata is necessary if you want to build a user interface from which the user can construct queries without knowing MDX. This isn't my immediate objective in presenting XML/A use cases, so my samples don't present the full richness of the Discover method. The sample script discover.wsf makes a call to list the catalogs at the datasource "Local Analysis Server". Discover methods return a tabular resultset. The stylesheet xars.xsl is used to render these tables as HTML.

Finally, a word on debugging. Since things don't always go as planned (or work as intended), it is often necessary to debug one's code. You can run these scripts in the script debugger (or in Interdev if installed) by running the script like:

cscript //d //x discover.wsf

If things are setup properly, you should see prompt windows asking you if you want to debug the script. Answer yes and the debugger should open. If it asks if you want to open an existing project, say no. What exactly happends depends upon which debugger you have associated with scripts.

downloads: b2003_4_10.zip (17K)