page static_html.xsl b2004_11_20

Porting command-line client to use 2005 B2 provider

In case you hadn't yet heard, XML/A is a pretty fundamental component of Analysis Services 2005. No longer an add-in SDK, XML/A is now the native provider. This is of course good news for us XML/A die-hards. I had wanted to demonstrate XML/A in 2005 during my sqlpass presentation but I ran out of prep time. I have finally found the motivation to return to this question, and want to share with you the results.

The goal of this exercise is basically to port the xmla2html program I presented in 11/1/2003 to the 2005 XML/A provider. I use this program regularly during my development work, so having it for 2005 is important. But it also provides a well-defined target for studying the migration.

Installing 2005 B2

I installed 2005 B2 on a fairly clean Server 2003 box which did not have SQL 2000 installed. I also installed and configured the Adventure Works sample database. I brought the FoodMart catalog onto this machine by migrating it from another machine.

Creating Adventure Works OLAP catalog

No AS catalog was installed by default and it was not clear to me how to go about getting one. After some hunting in the folder structure, I found the "BI solution" project in C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\awasdb. Always one to experiment, I opened it up and ran the menu command "Build->Process". It seemed to be doing stuff, and then presented a dialog with a "Run" button. Clicking that resulted in somewhat familiar cube processing messages - but then lots of error messages. The first implied it ran out of memory. I closed all my other programs, brought up Task Manager to observe memory, and ran it again. This time it finished without errors

I opened up SQL Server Management Studio and lo and behold I had a cube!

Setting up XML/A

This was not at all documented with the beta, so I made a posting to the SQL 2005 B2 newsgroup (a great resource BTW at http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us). I asked how to setup XML/A and got a response from the ever so helpful Akshai Mirchandani. Below are the steps he suggested, with my actions in italics.

1. Create a virtual directory pointing to the location of msmdpump.dll
   Note that you cannot just copy this dll into a new location since it depends on the resource files. I created the vdir with the same alias from the XML/A 1.1 SDK samples, namely "xmla".
   
2. Enable "Scripts and Executables" on the virtual directory
3. Setup security on the virtual directory
   I unckecked anonymous, leaving only integrated
   
4. Create a mapping from ".dll" to msmdpump.dll in the properties of the virtual directory
   (Configuration -> Application Mappings -> .dll = msmdpump.dll)
   I had to put the path to the dll in double quotes since it had spaces.
   
5. For Windows 2003 server, you may need to explicitly enable the msmdpump.dll as an "Allowed" ISAPI DLL.
   Since I am on 2003, I had to do this. In MMC, wen to Services and Applications -> Internet Information Service -> Web Service Extensions. Right click and "add new web serviceextension..."
   Extension name: xmla
   Required files: msmdpump.dll, xmlrwbin.dll
   
6. You may need to explicitly change the ACL for this folder: c:\Program Files\Microsoft SQL Server\MSSQL.1\Olap\Bin\isapi and for the msmdpump.dll file inside the folder. The updated ACL should allow Read permissions for the identity of the application pool that is loading the pump (by default, Network Service)
   I added "NETWORK SERVICES" with [read&exe, list, read] permissions to the folder C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin
   

Composing a SOAP message

The first thing I tried doing was running my existing xmla2html program against this new provided. It didn't work. But I was certain that it could work (meaning that Microsoft would not have provided a broken provider). The beauty of XML Web Services is the complete control and visibility you have around the message. Clearly my message was not in a format which the new provider could understand. And unlike the XML 1.1 SDK, no sample SOAP messages were provided. So back to the newsgroup.

This time Bogdan at Microsoft responded with explicit HTTP traces of SOAP messages to XML/A (exactly what I needed - thanks Bogdan).

The first thing I decided to do was to send exactly these messages - using XMLHTTP as I have done in my other articles.

C:\thinolap\xmla2html>cscript session.wsf
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <BeginSession xmlns="urn:schemas-microsoft-com:xml-analysis"/>
  </Header>
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command><Statement xmlns="urn:schemas-microsoft-com:xml-analysis"/></Command>
      <Properties>
        <PropertyList>
          <LocaleIdentifier>1033</LocaleIdentifier>
        </PropertyList>
      </Properties>
    </Execute>
  </Body>
</Envelope>

The response was:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <Session SessionId="2EC9D776-4CEC-4F7A-A5EA-E6B12D4F125D"/>
  </Header>
  <Body>
    <ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
      <return><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"/>
      </return>
    </ExecuteResponse>
  </Body>
</Envelope>

Yey, I have successfully queried 2005. Next, I tried his Discover example:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
    <Body>
  <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
      <RequestType>DISCOVER_PROPERTIES</RequestType>
      <Restrictions>
    <RestrictionList>
        <PropertyName>Catalog</PropertyName>
    </RestrictionList>
      </Restrictions>
      <Properties>
    <PropertyList>
        <DataSourceInfo>ACTIVE-GX150</DataSourceInfo>
        <Content>Data</Content>
        <LocaleIdentifier>1033</LocaleIdentifier>
    </PropertyList>
      </Properties>
  </Discover>
    </Body>
</Envelope>

The response was:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
  <DiscoverResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
    <return>
      <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" mlns:xsi="http://www.w3.org/2001/XMLSchema-instance" mlns:xsd="http://www.w3.org/2001/XMLSchema">
        <row>
          <PropertyName>Catalog</PropertyName> 
          <PropertyDescription>Catalog</PropertyDescription> 
          <PropertyType>string</PropertyType> 
          <PropertyAccessType>ReadWrite</PropertyAccessType> 
          <IsRequired>false</IsRequired> 
          <Value>FoodMart 2000</Value> 
        </row>
      </root>
    </return>
  </DiscoverResponse>
</Body>
</Envelope>

So why is only FoodMart showing up? Where is Adventure Works? Usually such a result involves security settings. But I don't know enough about AS 2005 security to figure this one out right now. But I may have to - can I query?

Back to generic client

Now I'll try using the script xmla.wsf used in the 11/1/2003 article against AS2005. I updated the SOAP templates xmla-discover.xml and xmla-execute.xml based upon the above findings. Also, I no longer default the DataSourceName to "Local Analysis Server".

The file xma.cmd has some examples of running this script. These examples are explained in the previous article, so I'll not repeat the explanations here. I've changed the parameters to use Adventure Works, All of the examples in this cmd file work for me.

When I run my list catalogs command, I see FoodMart and Adventure Works. Now I notice that the Discover call I did earlier was using DISCOVER_PROPERTIES instead of DBSCHEMA_CATALOGS. I guess I'll stick with the later. Perhaps someone can explain the discrepancy.

Summary

I am very excited to be querying the 2005 XML/A provider. When, after a couple hours of hacking on this, I finally saw values come back from an Execute request, I was ready to party! (yeah, it's pretty sad what gets me excited these days). My next project will be to fix the PowerPoint generator to work with 2005.

downloads: b2004_11_20.zip (14K)