XMLA at Your (Analysis) Service
Together, ADO MD.NET and
AMO provide an almost overwhelmingly rich array of programming
interfaces. Nothing is “hidden”: Anything that Management Studio and
Visual Studio can do against Analysis Services databases and cubes, you
can do, too, by using these two libraries. But it’s important to
understand that underlying each of these libraries is nothing but XMLA.
You saw how the Analysis Services designers were, effectively,
sophisticated front ends for generating XMLA scripts. You also saw how
SQL Server Management Studio
innately supports the generation of XMLA scripts for virtually any
Analysis Services entity and sports a dedicated XMLA query window for
executing those scripts.
We’d like to take a
moment now to explore XMLA a bit further, by showing you how to use it
from Management Studio for more than DDL scripts, how to use XMLA
programmatically, and how you can expose “raw” XMLA functionality from
ADO MD.NET. This will provide you with only a high-level understanding
of how XMLA works, but we think it’s still extremely important. If you
understand how to work with XMLA, you can potentially control Analysis
Services from virtually any environment or platform that supports XML
and Web services. Even if you do not plan to leave the .NET environment,
understanding XMLA means understanding the native protocol of Analysis
Services, and that has plenty of value in itself.
An API of Many Faces
You can understand XMLA
from at least three different vantage points. First, you can see XMLA
as a simple, programmable API. The core XMLA Web service provides just
two methods: Discover (used to fetch metadata information from the server) and Execute
(typically used to execute MDX queries and fetch the results). These
methods accept and return information in the form of XML documents,
which can be parsed or transformed to process or display the results.
Although XMLA can be
used as a true Web service (and before the SQL Server 2005 release of
Analysis Services, it had to be), its elevation to native API status
changes things a bit. On the .NET platform, XMLA works over TCP/IP
rather than HTTP, and a thin, managed assembly provides an API with
certain enhancements over that provided by a Web reference proxy class.
Specifically, if you want to use XMLA, you should set a reference to the
Microsoft.AnalysisServices.Xmla library (listed on the .NET tab of the Add Reference dialog box in Visual Studio). Then you simply instantiate a Microsoft.AnalysisServices.Xmla.XmlaClient object to start writing XMLA code.
Before we look at an XMLA
coding example, we will explore the second way to use XMLA: through the
XMLA Query window in SQL Server Management Studio. As you will see, it is
possible to create scripts that query your OLAP cubes as well as scripts
that can create cubes, modify their structure, or perform an array of
management tasks on them.
Finally, you can use ADO
MD.NET to access the underlying XML payload returned by XMLA when MDX
queries are executed. You get XML-formatted results from your MDX
queries without having to submit your MDX queries in XML format or deal
directly with XMLA as an API.
Using XMLA from SQL Server Management Studio
We’ll start our tour of
XMLA in the XMLA Query window in Management Studio. The best way to
understand XMLA is to use it in conjunction with Template Explorer
because the builtin XMLA templates give you an excellent jump start on
understanding the syntax and variety of possible XMLA queries.
Show the Template Explorer window if
it is not visible, click the Analysis Server button (second from the
left) on the window’s toolbar, and drill down on the XMLA node in the
tree view. Drill down further on each of the three child nodes
(Management, Schema Rowsets, and Server Status) to reveal the various
XMLA templates available.
Using <Discover> for Metadata and Status Information
To see how easy it is to
perform XMLA queries, drag the Sessions node (the third-to-last node
under Server Status) into the XMLA Query window. Move the cursor left to
deselect the text. You should see the following text in the window:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_SESSIONS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties><PropertyList></PropertyList></Properties>
</Discover>
The parent <Discover> element indicates that we will be calling the XMLA Web service’s Discover method (rather than its Execute method). You can think of the <RequestType> and <Restrictions> elements in much the same way that you think of the GetSchemaDataSet’s enumerated constant and restrictions array. The former is set to DISCOVER_SESSIONS, and the latter is empty; this will result in a complete list of all active sessions for the server.
The <Properties>
child element is required for all XMLA commands. Usually this element/
parameter will contain connection and formatting information. But
because we already supplied server-level connection information when the
XMLA window was opened, all default settings are acceptable to us and
we will leave this element empty.
To see the XML-formatted
results of this command, press F5, Ctrl+E (or use the various Execute
toolbar or menu commands). The results might look bewildering at first,
but the XML results are easier to interpret if you scroll past the <xsd:schema> element to the first <row> element. That element’s content format should look similar to the following:
<row>
<SESSION_ID>12394A18-5CB8-4AD1-AEB1-88E151C0030B</SESSION_ID>
<SESSION_SPID>2356</SESSION_SPID>
<SESSION_CONNECTION_ID>27</SESSION_CONNECTION_ID>
<SESSION_USER_NAME>MYMACHINE\UserName</SESSION_USER_NAME>
<SESSION_CURRENT_DATABASE>Chapter18</SESSION_CURRENT_DATABASE>
<SESSION_START_TIME>2005-12-25T20:52:56</SESSION_START_TIME>
<SESSION_ELAPSED_TIME_MS>841</SESSION_ELAPSED_TIME_MS>
<SESSION_LAST_COMMAND_START_TIME>2005-12-25T20:52:57</SESSION_LAST_COMMAND_START_TIME>
<SESSION_LAST_COMMAND_END_TIME>2005-12-25T20:52:56</SESSION_LAST_COMMAND_END_TIME>
<SESSION_LAST_COMMAND_ELAPSED_TIME_MS>0</SESSION_LAST_COMMAND_ELAPSED_TIME_MS>
<SESSION_IDLE_TIME_MS>0</SESSION_IDLE_TIME_MS>
<SESSION_CPU_TIME_MS>80</SESSION_CPU_TIME_MS>
<SESSION_LAST_COMMAND>DISCOVER_SESSIONS</SESSION_LAST_COMMAND>
<SESSION_LAST_COMMAND_CPU_TIME_MS>0</SESSION_LAST_COMMAND_CPU_TIME_MS>
</row>
XMLA supports two response formats, tabular and multidimensional. Typically, as in this case, the Discover method returns tabular-formatted results, with each row contained in a separate <row> element, and each column contained in its own named child element. The Execute method, which we will examine shortly, typically uses the multidimensional format for its response content.
More Info
The tabular XMLA response format is nearly identical to the XML format used by the ADO.NET DataTable object, which is used, in turn, by the DataSet object’s ReadXml and WriteXml
methods. Therefore, when you use XMLA programmatically, you can tweak
tabular response content slightly and convert it to an ADO.NET DataTable, which is suitable for binding to Windows Forms or ASP.NET controls. |
Note
The content of the <SESSION_USER_NAME>
element in the XML snippet just shown has been obfuscated to
MYMACHINE\UserName; actual results would present the real domain name
and user name of the user logged into the session. |
Using <Execute> for DDL Tasks
Let’s try another
query. This time, we’ll use XMLA to perform an administrative
task—processing the Adventure Works cube in our Adventure Works DW
database:
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Type>ProcessFull</Type>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
</Object>
</Process>
</Command>
<Properties />
</Execute>
Notice that this command is contained in an <Execute> element instead of the <Discover> element used in our last query. That’s because this query performs an action. The <Execute> element contains a child <Command> element and an empty <Properties> child element, as with the last query. Within the <Command> element, the <Process> child element (and its child <Type> and <Object> elements) tells Analysis Services to run a full process against the Adventure Works cube in the Adventure Works DW database.
Important
The
cube ID supplied is “Adventure Works DW,” even though the cube itself
is named “Adventure Works.” XMLA, as it turns out, works with IDs rather
than names. In the case of the Adventure Works cube, the ID and name do
not match. To determine the ID of a cube, right-click on it in the
Object Explorer window in Management Studio and choose Properties from
the shortcut menu. The cube’s ID will appear in the second row of the
General page in the Cube Properties dialog box, as shown in Figure 9. |
Press F5 to run the
process task. During processing, you will see the “Executing query...”
message and animated icon appear in the XMLA Query window’s status bar
and various status information on the Messages tab of the Results pane.
When the processing is done, the Results tab will appear, displaying a
standard “empty” XMLA message indicating that the process terminated
successfully:
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</return>
It
might seem counterintuitive for an empty response to indicate success,
but consider that a failed process will always return an error message
on the Messages tab. For example, if you were to intentionally change
the <CubeID> element’s contents from Adventure Works DW to Adventure Works (which is a valid cube name but an invalid cube ID) and re-execute the query, you would see the following text on the Messages tab:
Executing the query ...
Errors in the metadata manager. Either the cube with the ID of 'Adventure Works' does
not exist in the database with the ID of 'Adventure Works DW', or the user does not
have permissions to access the object.
Execution complete
Using <Execute> to Run MDX Queries
To round out
your understanding of XMLA you must understand that XMLA can also be
used to execute MDX queries. It is a bit ironic that we are pointing
this out after
discussing XMLA’s DDL scripting capabilities because XMLA was
originally designed primarily for MDX query execution (and metadata
discovery). As such, XMLA easily accommodates the notion of cell sets
containing axes, positions, and a collection of cells. Each of these
entities is neatly packaged in the multidimensional-formatted XML
response payload.
Under most circumstances, you would let ADO MD.NET process this XML document and populate a CellSet (or DataSet, DataTable, or DataReader)
object with its contents, but you can use the Management Studio XMLA
window to examine the XML content directly. This is relatively easy to
do because the XML content, though somewhat complex, is quite readable
if you have an understanding of OLAP, MDX, and cell sets.
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>
SELECT Shippers.[Shipper Name].MEMBERS ON COLUMNS,
Time.Year.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Total Sales]
</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>Chapter18</Catalog>
</PropertyList>
</Properties>
</Execute>
Note that we once again use <Execute> as our parent tag, indicating that we are calling XMLA’s Execute method. The <Execute> element, as before, contains <Command> and <Properties> subelements. This time the <Command> element consists merely of a <Statement> element whose value is the MDX command we want to execute.
The <Properties> element, rather than being left empty as in our previous examples, consists of a <PropertyList>
child element, which can in turn contain several child elements
containing simple values. In the case of our query, we are only
specifying the database name (in the <Catalog> element). For all other properties, we are using default values. Without a <Catalog> value, the query would run against the default database.
Note
It
might seem odd that we must specify a database name, since the
connection information we supplied to the XMLA window already indicates
the database we want to use, but XMLA connections effectively
accommodate only server information. The fact that the XMLA Query window
uses the standard Connection To Analysis Services dialog box (on whose
Connection Properties tab you can specify a database after clicking the
Options button) is an anomaly. The database selection made in the
Connection To Analysis Services dialog box is ignored; therefore, when
you create an XMLA query, you must explicitly specify your database
within the XMLA query itself. |
Execute the query now
to fetch the multidimensional response. You will see that a large XML
response is returned. To make this XML easier to read, copy and paste it
into a new XMLA Query window. This will allow you to expand and
contract elements within the XML using the outline nodes in the left
margin of the XMLA editor.
Now contract the xs:schema node in the return/root element (it begins on the third line of the document), and then contract the OlapInfo/CubeInfo element two lines below it. Go down one line more and examine the AxesInfo element. Notice that each axis, as well as the “Slicer” axis (which represents the MDX command’s WHERE clause), is described. Now contract the OlapInfo node (on line 4 of the document) and examine the Axes node immediately below it, looking at each <Axis> node within it. Notice that each tuple and member is enumerated, complete with Caption, UniqueName (<UName> element), and other property values.
For example, the entire COLUMNS axis (axis 0) is described in the XML response payload as follows, with one tuple per shipper (and one each for the All and Unknown members):
<Axis name="Axis0">
<Tuples>
<Tuple>
<Member Hierarchy="[Shippers].[Shipper Name]">
<UName>[Shippers].[Shipper Name].[All Shippers]</UName>
<Caption>All Shippers</Caption>
<LName>[Shippers].[Shipper Name].[(All)]</LName>
<LNum>0</LNum>
<DisplayInfo>66536</DisplayInfo>
</Member>
</Tuple>
<Tuple>
<Member Hierarchy="[Shippers].[Shipper Name]">
<UName>[Shippers].[Shipper Name].&[1]</UName>
<Caption>Speedy Express</Caption>
<LName>[Shippers].[Shipper Name].[Shipper Name]</LName>
<LNum>1</LNum>
<DisplayInfo>0</DisplayInfo>
</Member>
</Tuple>
<Tuple>
<Member Hierarchy="[Shippers].[Shipper Name]">
<UName>[Shippers].[Shipper Name].&[2]</UName>
<Caption>United Package</Caption>
<LName>[Shippers].[Shipper Name].[Shipper Name]</LName>
<LNum>1</LNum>
<DisplayInfo>131072</DisplayInfo>
</Member>
</Tuple>
<Tuple>
<Member Hierarchy="[Shippers].[Shipper Name]">
<UName>[Shippers].[Shipper Name].&[3]</UName>
<Caption>Federal Shipping</Caption>
<LName>[Shippers].[Shipper Name].[Shipper Name]</LName>
<LNum>1</LNum>
<DisplayInfo>131072</DisplayInfo>
</Member>
</Tuple>
<Tuple>
<Member Hierarchy="[Shippers].[Shipper Name]">
<UName>[Shippers].[Shipper Name].[All Shippers].UNKNOWNMEMBER</UName>
<Caption>Unknown</Caption>
<LName>[Shippers].[Shipper Name].[Shipper Name]</LName>
<LNum>1</LNum>
<DisplayInfo>131072</DisplayInfo>
</Member>
</Tuple>
</Tuples>
</Axis>
For the Slicer axis, notice that the Measures tuple/member has a UniqueName of [Measures].[Total Sales], which squares with the WHERE clause in our MDX query:
<Axis name="SlicerAxis">
<Tuples>
<Tuple>
<Member Hierarchy="[Measures]">
<UName>[Measures].[Total Sales]</UName>
<Caption>Total Sales</Caption>
<LName>[Measures].[MeasuresLevel]</LName>
<LNum>0</LNum>
<DisplayInfo>0</DisplayInfo>
</Member>
...
Now contract the Axes node and examine the CellData node immediately following it. Notice that each cell’s Value and FormattedValue (<FmtValue> element) is provided, and that the cells are identified by the ordinal address we discussed earlier:
<CellData>
<Cell CellOrdinal="0">
<Value xsi:type="xsd:double">1.35445859E6</Value>
<FmtValue>$1,354,458.59</FmtValue>
</Cell>
<Cell CellOrdinal="1">
<Value xsi:type="xsd:double">3.7398319E5</Value>
<FmtValue>$373,983.19</FmtValue>
</Cell>
...
Calling Execute Programmatically from .NET
With this understanding of XMLA’s encoding of CellSet
objects, you can imagine how it would be possible to create an XSL
stylesheet that transforms the data into a human-readable HTML page. In
fact, Microsoft has created just such a stylesheet. It is called
Xamd.xsl, and it is distributed as part of a sample application in the
XMLA SDK 1.1 (which you can download for free from http://msdn.microsoft.com,
and which is actually designed for SQL Server 2000). You can use the
stylesheet to transform any two-axis XML cell set into well-formed HTML,
with the results displayed in a table.
You can thus execute MDX queries via XMLA directly from .NET (using Microsoft.AnalysisServices.Xmla, as discussed earlier). You can then use the stylesheet, some System.Xml code, and a WebBrowser control to display the results. To do so, you’ll first need to make sure you include the following namespaces in your code:
using System.Xml;
using System.Xml.Xsl;
using System.IO;
Once the namespaces are properly imported, you can use the code in Listing 2, which
performs the XSL transformation for the MDX query we just discussed.
Listing 2. Executing MDX Queries with XMLA
// XMLA:
Microsoft.AnalysisServices.Xmla.XmlaClient xmlac = new
Microsoft.AnalysisServices.Xmla.XmlaClient();
// System.Xml:
XslCompiledTransform xslCellSetTable = new XslCompiledTransform();
XmlTextWriter xtwCellSetTable = new XmlTextWriter(Path.GetFullPath(".") +
"\\Execute.htm", null);
XmlDocument xdcResults = new XmlDocument();
string command = "<Statement>" + txtMDX.Text + "</Statement>";
string properties = "<PropertyList>" +
" <Catalog>Chapter18</Catalog>" +
"</PropertyList>";
string results;
try
{
// Load the stylesheet:
xslCellSetTable.Load(Path.GetFullPath(".") + "\\xamd.xsl");
// Fetch the XML into a string:
xmlac.Connect(ConnectionString);
xmlac.Execute(command, properties, out results, false, true);
// Load the XML text into an XmlDocument
xdcResults.LoadXml(results);
// Strip off the outer <return> tag (which the stylesheet is not expecting):
xdcResults.LoadXml(xdcResults.FirstChild.InnerXml);
// Transform the XML to an HTML file:
xslCellSetTable.Transform(xdcResults, xtwCellSetTable);
// Close XmlTextWriter:
xtwCellSetTable.Close();
// Display generated HTML file in the WebBrowser control:
wbrMain.Navigate(Path.GetFullPath(".") + "\\Execute.htm");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection:
xmlac.Disconnect();
}
|
Notice that the XmlaClient object has an Execute method that accepts XML strings identical to the contents of the <Command> and <Properties> child elements of the <Execute>
element supplied in Management Studio’s XMLA Query window. The results
are assigned to a third string argument. The last two arguments are
Booleans of relatively little importance in this case.
Once the Execute method call is complete and the XML result string has been assigned to the string variable results, the code simply loads it into an XmlDocument
object, applies the stylesheet transformation, and dumps the resultant
HTML to a file called Execute.htm. The code then displays the file’s
contents in a WebBrowser control. Your results should look similar to those depicted in Figure 10.
Manipulating XMLA Response Content from ADO MD.NET
In addition to using
the stylesheet in conjunction with low-level XMLA coding, you can also
use it with ADO MD.NET. That is because the AdomdCommand object has an ExecuteXmlReader method that directly returns the XMLA response payload generated when an AdomdCommand objects MDX query is executed. So by combining the ExecuteXmlReader method and code similar to that in Listing 2, the results can once again be displayed as HTML in a WebBrowser control.
Sample code for this technique is provided in Listing 3 (taken from the AdomdCommand .ExecuteXmlReader source code region in the form frmAPIs in the sample code’s Chapter19 Windows Forms application). This code makes the same assumptions as Listing 2.
Listing 3. Using ExecuteXmlReader
// ADO MD.NET:
AdomdConnection conXML = new AdomdConnection(ConnectionString);
AdomdCommand comXML = new AdomdCommand(txtMDX.Text, conXML);
// System.Xml:
XslCompiledTransform xslCellSetTable = new XslCompiledTransform();
XmlTextWriter xtwCellSetTable = new XmlTextWriter(Path.GetFullPath(".") +
"\\Execute.htm", null);
try
{
// Open the connection:
conXML.Open();
// Load the stylesheet:
xslCellSetTable.Load(Path.GetFullPath(".") + "\\xamd.xsl");
// Fetch and transform the XML to an HTML file:
xslCellSetTable.Transform(comXML.ExecuteXmlReader(), xtwCellSetTable);
// Close the XmlTextWriter:
xtwCellSetTable.Close();
// Display generated HTML file in the WebBrowser control:
wbrMain.Navigate(Path.GetFullPath(".") + "\\Execute.htm");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection:
conXML.Close();
}
|
For the code in Listing 3 to compile properly, the following namespace inclusions must be properly inserted at the top of your source code file:
using Microsoft.AnalysisServices.AdomdClient;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
