Web-enable Your Business Intelligence Using XML/A and ASP.NET

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