Using XMLA with BizTalk Server 2004.

 

I have been very busy in the past three weeks working for my Teched presentation. This was my first Teched experience and was fantastic. Then I decided to start blogging about the different topics of my session. Basically my session covers some of the most interesting topics to integrate BizTalk Server 2004 and SQL Server 2005. We presented a BizTalk adapter for SQL Server Service Broker a couple of SQL Servre Integration Services custom tasks to interact with BizTalk and a cool demo about how to use XMLA to query some BizTalk OLAP cubes.  I have plans to write a couple of article that covers the most relevant parts of my session. Anyway, the last part of my talk was around XML for Analysis (XMLA) and BizTalk Server 2004.

XMLA is an open Standard for client-server communications between OLAP Servers. XMLA describes its messages in a SOAP-based format designed to access any multidimensional data-store through the Web.

The XML for Analysis open standard describes two generally accessible methods: Discover and Execute. These methods use the loosely-coupled client and server architecture supported by XML to handle incoming and outgoing information on an instance of SSAS.

The Discover method obtains information and metadata from a Web service. This information can include a list of available data sources, as well as information about any of the data source providers. Properties define and shape the data that is obtained from a data source. The Discover method is a common method for defining the many types of information a client application may require from data sources on Analysis Services instances. The properties and the generic interface provide extensibility without requiring you to rewrite existing functions in a client application.

The Execute method allows applications to run provider-specific commands against XML for Analysis data sources. The following illustration shows how a business intelligence application sends Discover and Execute calls, using the SOAP and HTTP protocols, to an instance of SSAS.

XMLA 1.1 is natively supported in SQL Server 2005, in addition SQL Server 2000 developers can use the XMLA SDK to take advantages of the XMLA features on top of SQL Server 2000.

Our demo at Teched shows how to use the XMLA SDK for SQL Server 200 to query the BizTalk Analysis Database and specifically the Messages Metrics OLAP cube to get the statistics of the number of messages processed by the Service Broker, MSMQ and Submit Direct adapters.  

The first part of my demos does a Discover query using the XMLA Web Service.  The syntax of the Discover message is the following:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

          <RequestType>MDSCHEMA_CUBES</RequestType>

          <Restrictions>

                   <RestrictionList>

                             <CATALOG_NAME>BizTalkAnalysisDb</CATALOG_NAME>

                   </RestrictionList>

          </Restrictions>

          <Properties>

                   <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

                             <DataSourceInfo xmlns="urn:schemas-microsoft-com:xml-analysis">Local Analysis Server</DataSourceInfo>

                             <Catalog xmlns="urn:schemas-microsoft-com:xml-analysis">BizTalkAnalysisDb</Catalog>

                             <Format xmlns="urn:schemas-microsoft-com:xml-analysis">Multidimensional</Format>

                   </PropertyList>

          </Properties>

</Discover>

 

The next example shows the basic code to execute this query through the XMLA Web Service. In this code cPropList and cRestriction represents the PropertyList and Restriction elements fragments in the Discover query.

 

xmlaws.MsXmlAnalysis proxy= new xmlaws.MsXmlAnalysis();

xmlaws.Discover dscproxy= new xmlaws.Discover();

proxy.Url= “http://localhost/xmlaws/msxisapi.dll”;

proxy.Credentials= System.Net.CredentialCache.DefaultCredentials;

dscproxy.RequestType= "MDSCHEMA_CUBES";

XmlDocument xdoc= new XmlDocument();

XmlElement rnode= xdoc.CreateElement("", "RestrictionList", “urn:schemas-microsoft-com:xml-analysis”);

XmlElement propelem= xdoc.CreateElement("", "PropertyList", “urn:schemas-microsoft-com:xml-analysis”);

propelem.InnerXml= cPropList;

rnode.InnerXml= cRestriction;

//dscproxy.Restrictions= rnode;

dscproxy.Properties= propelem;

xmlaws.Session session= null;

xmlaws.DiscoverResponse dscresponse= proxy.Discover(dscproxy,  ref session, null, null);

 

This query returns the list of OLAP cubes that are present in the BizTalkAnalysisDb.

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:EX="urn:schemas-microsoft-com:xml-analysis:exception">

  Schema Info…

  <row>

    <CATALOG_NAME>BizTalkAnalysisDb</CATALOG_NAME>

    <CUBE_NAME>MessageMetrics</CUBE_NAME>

    <CUBE_TYPE>CUBE</CUBE_TYPE>

    <LAST_SCHEMA_UPDATE>2005-06-09T10:13:05</LAST_SCHEMA_UPDATE>

    <LAST_DATA_UPDATE>2005-06-09T10:13:05</LAST_DATA_UPDATE>

    <IS_DRILLTHROUGH_ENABLED>false</IS_DRILLTHROUGH_ENABLED>

    <IS_LINKABLE>true</IS_LINKABLE>

    <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>

    <IS_SQL_ENABLED>true</IS_SQL_ENABLED>

  </row>

  <row>

    <CATALOG_NAME>BizTalkAnalysisDb</CATALOG_NAME>

    <CUBE_NAME>ServiceMetrics</CUBE_NAME>

    <CUBE_TYPE>CUBE</CUBE_TYPE>

    <LAST_SCHEMA_UPDATE>2005-06-09T10:13:05</LAST_SCHEMA_UPDATE>

    <LAST_DATA_UPDATE>2005-06-09T10:13:05</LAST_DATA_UPDATE>

    <IS_DRILLTHROUGH_ENABLED>false</IS_DRILLTHROUGH_ENABLED>

    <IS_LINKABLE>true</IS_LINKABLE>

    <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>

    <IS_SQL_ENABLED>true</IS_SQL_ENABLED>

  </row>

</root>

 

Finally we show how to execute a DMX query over the MessageMetrics cube. The syntax of the query looks like the following.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">

   <Command>

      <Statement>

         select {[Measures].[Count]} on columns, {[Adapter].[All].[MSMQ], [Adapter].[All].[ssb], [Adapter].[All].[Submit]} on rows from MessageMetrics

      </Statement>

   </Command>

   <Properties>

      <PropertyList>

         <DataSourceInfo>Local Analysis Server</DataSourceInfo>

         <Catalog>BizTalkAnalysisDb/Catalog>

         <Format>Multidimensional</Format>

         <AxisFormat>ClusterFormat</AxisFormat>

      </PropertyList>

   </Properties>

</Execute>

 

The next code shows how to send an Execute message to the XMLA Web Service

xmlaws.MsXmlAnalysis proxy= new xmlaws.MsXmlAnalysis();

xmlaws.Execute execproxy= new xmlaws.Execute();

proxy.Url= "http://localhost/xmlaws/msxisapi.dll";

proxy.Credentials=  System.Net.CredentialCache.DefaultCredentials;

XmlDocument xdoc= new XmlDocument();

XmlElement cmdelem= xdoc.CreateElement("", "Statement", cns);

XmlElement propelem= xdoc.CreateElement("", "PropertyList", cns);

cmdelem.InnerText= cQuery;

propelem.InnerXml= cPropList;

execproxy.Command= cmdelem;

execproxy.Properties= propelem;

xmlaws.Session session= null;

xmlaws.ExecuteResponse ExecResponse= proxy.Execute(execproxy, ref session, null, null);

 

The following code shows the result of the Execute message.

<root xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:EX="urn:schemas-microsoft-com:xml-analysis:exception">

Schema Info…

  <OlapInfo>

    <AxesInfo>

      <AxisInfo name="Axis0">

        <HierarchyInfo name="Measures">

          <UName name="[Measures].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Measures].[MEMBER_CAPTION]" />

          <LName name="[Measures].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Measures].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Measures].[DISPLAY_INFO]" />

        </HierarchyInfo>

      </AxisInfo>

      <AxisInfo name="Axis1">

        <HierarchyInfo name="Adapter">

          <UName name="[Adapter].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Adapter].[MEMBER_CAPTION]" />

          <LName name="[Adapter].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Adapter].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Adapter].[DISPLAY_INFO]" />

        </HierarchyInfo>

      </AxisInfo>

      <AxisInfo name="SlicerAxis">

        <HierarchyInfo name="Time">

          <UName name="[Time].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Time].[MEMBER_CAPTION]" />

          <LName name="[Time].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Time].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Time].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Direction">

          <UName name="[Direction].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Direction].[MEMBER_CAPTION]" />

          <LName name="[Direction].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Direction].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Direction].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="URL">

          <UName name="[URL].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[URL].[MEMBER_CAPTION]" />

          <LName name="[URL].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[URL].[LEVEL_NUMBER]" />

          <DisplayInfo name="[URL].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Schema">

          <UName name="[Schema].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Schema].[MEMBER_CAPTION]" />

          <LName name="[Schema].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Schema].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Schema].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Decryption Subject">

          <UName name="[Decryption Subject].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Decryption Subject].[MEMBER_CAPTION]" />

          <LName name="[Decryption Subject].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Decryption Subject].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Decryption Subject].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Signing Subject">

          <UName name="[Signing Subject].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Signing Subject].[MEMBER_CAPTION]" />

          <LName name="[Signing Subject].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Signing Subject].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Signing Subject].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Services">

          <UName name="[Services].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Services].[MEMBER_CAPTION]" />

          <LName name="[Services].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Services].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Services].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="MessageBox">

          <UName name="[MessageBox].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[MessageBox].[MEMBER_CAPTION]" />

          <LName name="[MessageBox].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[MessageBox].[LEVEL_NUMBER]" />

          <DisplayInfo name="[MessageBox].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Hosts">

          <UName name="[Hosts].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Hosts].[MEMBER_CAPTION]" />

          <LName name="[Hosts].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Hosts].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Hosts].[DISPLAY_INFO]" />

        </HierarchyInfo>

        <HierarchyInfo name="Assembly">

          <UName name="[Assembly].[MEMBER_UNIQUE_NAME]" />

          <Caption name="[Assembly].[MEMBER_CAPTION]" />

          <LName name="[Assembly].[LEVEL_UNIQUE_NAME]" />

          <LNum name="[Assembly].[LEVEL_NUMBER]" />

          <DisplayInfo name="[Assembly].[DISPLAY_INFO]" />

        </HierarchyInfo>

      </AxisInfo>

    </AxesInfo>

    <CellInfo>

      <Value name="VALUE" />

      <FmtValue name="FORMATTED_VALUE" />

      <CellOrdinal name="CELL_ORDINAL" />

    </CellInfo>

  </OlapInfo>

  <Axes>

    <Axis name="Axis0">

      <Tuples>

        <Tuple>

          <Member Hierarchy="Measures">

            <UName>[Measures].[Count]</UName>

            <Caption>Count</Caption>

            <LName>[Measures].[MeasuresLevel]</LName>

            <LNum>0</LNum>

            <DisplayInfo>0</DisplayInfo>

          </Member>

        </Tuple>

      </Tuples>

    </Axis>

    <Axis name="Axis1">

      <Tuples>

        <Tuple>

          <Member Hierarchy="Adapter">

            <UName>[Adapter].[All].[MSMQ]</UName>

            <Caption>MSMQ</Caption>

            <LName>[Adapter].[Transport Type]</LName>

            <LNum>1</LNum>

            <DisplayInfo>131072</DisplayInfo>

          </Member>

        </Tuple>

        <Tuple>

          <Member Hierarchy="Adapter">

            <UName>[Adapter].[All].[ssb]</UName>

            <Caption>ssb</Caption>

            <LName>[Adapter].[Transport Type]</LName>

            <LNum>1</LNum>

            <DisplayInfo>131072</DisplayInfo>

          </Member>

        </Tuple>

        <Tuple>

          <Member Hierarchy="Adapter">

            <UName>[Adapter].[All].[Submit]</UName>

            <Caption>Submit</Caption>

            <LName>[Adapter].[Transport Type]</LName>

            <LNum>1</LNum>

            <DisplayInfo>131072</DisplayInfo>

          </Member>

        </Tuple>

      </Tuples>

    </Axis>

    <Axis name="SlicerAxis">

      <Tuples>

        <Tuple>

          <Member Hierarchy="Time">

            <UName>[Time].[AllTime]</UName>

            <Caption>AllTime</Caption>

            <LName>[Time].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>1</DisplayInfo>

          </Member>

          <Member Hierarchy="Direction">

            <UName>[Direction].[All]</UName>

            <Caption>All</Caption>

            <LName>[Direction].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>9</DisplayInfo>

          </Member>

          <Member Hierarchy="URL">

            <UName>[URL].[All]</UName>

            <Caption>All</Caption>

            <LName>[URL].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>93</DisplayInfo>

          </Member>

          <Member Hierarchy="Schema">

            <UName>[Schema].[All]</UName>

            <Caption>All</Caption>

            <LName>[Schema].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>27</DisplayInfo>

          </Member>

          <Member Hierarchy="Decryption Subject">

            <UName>[Decryption Subject].[All]</UName>

            <Caption>All</Caption>

            <LName>[Decryption Subject].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>1</DisplayInfo>

          </Member>

          <Member Hierarchy="Signing Subject">

            <UName>[Signing Subject].[All]</UName>

            <Caption>All</Caption>

            <LName>[Signing Subject].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>1</DisplayInfo>

          </Member>

          <Member Hierarchy="Services">

            <UName>[Services].[All]</UName>

            <Caption>All</Caption>

            <LName>[Services].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>4</DisplayInfo>

          </Member>

          <Member Hierarchy="MessageBox">

            <UName>[MessageBox].[All]</UName>

            <Caption>All</Caption>

            <LName>[MessageBox].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>1</DisplayInfo>

          </Member>

          <Member Hierarchy="Hosts">

            <UName>[Hosts].[All]</UName>

            <Caption>All</Caption>

            <LName>[Hosts].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>2</DisplayInfo>

          </Member>

          <Member Hierarchy="Assembly">

            <UName>[Assembly].[All]</UName>

            <Caption>All</Caption>

            <LName>[Assembly].[(All)]</LName>

            <LNum>0</LNum>

            <DisplayInfo>20</DisplayInfo>

          </Member>

        </Tuple>

      </Tuples>

    </Axis>

  </Axes>

  <CellData>

    <Cell CellOrdinal="0">

      <Value xsi:type="xsd:double">1897</Value>

      <FmtValue>1897</FmtValue>

    </Cell>

    <Cell CellOrdinal="1">

      <Value xsi:type="xsd:double">333</Value>

      <FmtValue>333</FmtValue>

    </Cell>

    <Cell CellOrdinal="2">

      <Value xsi:type="xsd:double">208</Value>

      <FmtValue>208</FmtValue>

    </Cell>

  </CellData>

</root>

 

The code fragments on this article are just a short example about how to use XMLA and BizTalk Server together. There are great scenarios for the use of XMLA. For BizTalk developers XMLA represents another interface to access the BizTalk OLAP databases. The recent Community Technical Preview of BizTalk Server 2006 comes with a set of great improvements in Business Activity Monitoring so that XMLA represents a nice alternative to consume the BizTalk multidimensional data.

No Comments