site site-navbranch.xsl site

Skip to main content.

Looking for PowerPoint and/or Google Analytics solutions? Please follow the above links to

section no_callout.xsl no_callout

page static_html.xsl b2003_4_8

Technical Backgrounder

Before getting started with XML/A, you need to establish the requisite technology foundation. Basically, you need to download and install some free Microsoft software upon which all of the articles in this series will depend. Relevant links are included inline. And a link to this articles source code can be found at the end of the article.


In order to run these samples, it is assumed that you have at a minimum an installation somewhere on your network of SQL Server Analysis Services and XML for Analysis. If you are more of an end user, you may request from your IT services group that they setup XML/A on one of your servers. If you are a developer, you can setup your own testbed environment. It is assumed that you are somewhat familiar with OLAP and with Microsoft's implementation. Also assumed is that you are or are becoming familiar with the MDX query language. I will strive to explain my MDX, but I would recommend that you also review the MDX tutorial which is part of SQL Books Online. In addition, you may want to get a copy of "MDX Solutions" by George Spofford. Also look at the resources pages on this site.

Note: these articles and the provided source have been updated to XML/A 1.1. You will need to update the 1.1 SDK to run the samples in these articles.

Other prerequisites for this first series of articles includes:

  • Internet Explorer 5.0 or above
  • MSXML4
  • Windows Scripting Host (WSH) 5.6

The MSXML4 library can be downloaded from here

The Windows Scripting Host can be downloaded from here

Declarative Analytics

XML/A is an important step towards what I refer to as "declarative analytics". Declarative programming is in my opinion the most important development in software technology since the first compilers. Declarative programming makes extensive use of XML and XSLT, and so will I in my articles. So again in terms of background prerequisites for readers, you should start or continue building you understanding of XML technologies. I believe strongly that these technologies are approachable by non-developers, but I guess that you non-developers in the audience will have to be the judge of that.

I will demonstrate how to build OLAP reports using XML technologies and declarative analytics. These reports will be created in many formats, including text, Excel, PowerPoint, and HTML. For graphical reports, I will be using my Intrasight toolkit, SVG, and Microsoft's Chartspace Office Web Control.

Microsoft is lately using the phrase "democratization of business intelligence". By this they mean that business intelligence and analytics should be available when and where it can be of assistance. To me it means that those people needing to answer an important business question will have the tools, access privedges, and other resources needed to generate their own reports. This is one reason for my early samples run on the desktop under Window Scripting Host (WSH). Using declarative logic is, in my opinion, a requirement for the widespread adoption of OLAP analytics. I hope to demonstrate the validity of this statement through the result that you, my audience, achieve in using this approach.

Installing XML/A

The first step to declarative analytics nirvana is to install and configure XML for Analysis. The Microsoft SDK can be downloaded from here

You should also consider reading the XML for Analysis 1.1 Specification, on the web site.

The SDK will by default install to C:\Program Files\Microsoft XML For Analysis SDK. You will need to install the SDK on a machine running IIS. Also, you need to enable non-SSL communications during the install. To expose the XML/A web services, create a virtual directory named "xmla", give it execute privilages, and point it to the isapi folder in the SDK. You should set the security to anonymous or basic.

The SDK can be installed on the same machines running Analysis Services. For such an installation, the default datasources.xml (in the config folder) will expose the local instance of Analysis Services. If you want XML/A on a separate machine or you want to expose Analysis Services instances running on different machines, then you merely need to add additional DataSource nodes to the datasources.xml file. This OLAP data source needs to be accessible by the XML/A machine. XML/A has a not-so-well-documented capability to expose SQL Server through the XML/A web service. This is accomplished with the appropriate entries in datasources.xml. I will demonstrate this capability in one of my articles.

You can test your installation of XML/A using one of two Visual Basic applications which come with the SDK. The program "sample" partially recreates the "MDX sample application" which installs with Analysis Services. The program "simple" is more of a test harness to exercise the different methods and capabilities in the SDK. I would recommend to the serious developer to review these code samples. But for the "desktop" developers I am targeting in my early articles, such a review is not necessary.

You can use these programs to connect to the Analysis Services catalogues at the datasources specified for this XML/A installation. Hopefully your installation has the FoodMart 2000 sample, as this is typically used for demonstration and learning. I will be using FoodMart 2000 in my samples. If possible, have you system administrator install this sample as it will be immensely useful in learning MDX.

I'll also point out the excellent technical resource available on-line for XML/A at newsgroup "". You can access this newsgroup either using a news reader or using the Google groups page. You will find many common XML/A question and problems discussed in that group. Look for postings or responses by Akshai Mirchandani, Chris Webb, and Mosha Pasumansky.