Getting Results
When the XMLA Provider receives a request, it passes the request to the MDX query engine, which parses and executes it. After obtaining the MDX results, the XMLA Provider packages them into a SOAP reply and sends them back to the requesting client. An Execute response can be quite long depending on the amount of data returned and the format used. To see the results of an Execute query, load the sample application and run an MDX query. To load the sample application, simply open it in Internet Explorer (IE). You can either copy the file to a virtual directory and open it in HTTP or double-click the file to open it in the browser. You'll see all the XML that the query returned in the sample Web application; Figure 2 shows part of the results.
The SOAP response from a call to an Execute method looks similar to the results from a call to Discover. As Listing 2 shows, the calling code includes the usual SOAP Envelope and Body tags as the top-level wrappers, then shows the MDX query packaged for transmission in XML. You have two options for the format of an Execute request's results: Rowset and MDDataSet (which appears as Multidimensional in the listing). The Rowset format is a flattened tabular structure that contains rows and columns along with the data elements. MDDataSet is a multidimensional format that contains three sections: OLAPInfo, Axes, and CellData. You'll see these three sections if you scroll through the results of the sample application. The multidimensional format represents the multidimensional data in a hierarchical format that's more representative of the structure of the data than the flattened tabular format. OLAPInfo defines the structure of the results. The first section of OLAPInfo, CubeInfo, lists the cubes where the data originated. Next, AxesInfo has an AxisInfo element for each axis in the data. Every AxisInfo element contains the hierarchies, members, and properties for that axis. AxisInfo always contains the standard properties Uname (Unique Name), Caption, Lname (Level Name), and Lnum (Level Number). In addition, AxisInfo might contain a default value specified for cell properties. If the query results include many repeating values, these default values can dramatically reduce the size of the returned data by returning only the data elements that are different from the default. Last, the CellData section of a multidimensional format contains CellInfo standard and custom properties for each cell the MDX query returns. The standard properties are Value, FmtValue (Format Value), ForeColor, and BackColor. Optional properties depend on the MDX query you use to retrieve the results.
Describing XMLA results in abstract terms is difficult because the exact data returned varies depending on the query you use. The easiest way to understand OLAPInfo is to walk through an example of the results from a specific query. Consider the following MDX query:
select
{[Product].children} on rows,
{[Store].children} on columns
from Sales
Running this query through the XMLA Provider by using the Execute method results in the AxesInfo section that Figure 3 shows. The query returns columns (Axis0) and rows (Axis1). Each axis contains only one hierarchy: The columns axis contains the Store hierarchy, and the rows axis contains the Product hierarchy. After defining the dimensional axes, Figure 3 shows the slicer dimension, which is an MDX dimension for filtering multidimensional data. Slicer dimensions appear in the WHERE clause of an MDX query and display every hierarchy in the cube that doesn't appear in the dimensional axes. The repetition of this information is useful in XMLA because you can use the information to show which other hierarchies are available in a given cube and write further queries against those hierarchies.
As I noted earlier, the last part of the OLAPInfo section of a multidimensional format, CellInfo, describes the properties the query will return for each cell in the result set. Because the query I use in this example doesn't specify any additional properties, the CellInfo section displays only the basic Value and FmtValue information:
<OlapInfo>
<!— the AxesInfo goes here — >
<CellInfo>
<Value name="VALUE" />
<FmtValue name="FORMATTED_VALUE" />
</CellInfo>
</OlapInfo>
The next section of the results in MDDataSet format is Axes, which contains the data the query returns organized in either TupleFormat, as Figure 4 shows, or Cluster-Format. Let's look at an example to see the differences between these two formats. Say you have three country categories (Canada, Mexico, and USA) and three product categories (Drink, Food, and Non-Consumable), which produce nine combinations of countries and products. Logically, you have several options for representing this set in a written notation. First, you can simply list the combinations:
{(Canada, Drink), (Canada, Food), (Canada, Non-Consumable),
(Mexico, Drink), (Mexico, Food), (Mexico, Non-Consumable),
(USA, Drink), (USA, Food), (USA, Non-Consumable)}
This is the kind of set representation that the TupleFormat uses. Each pair is a tuple, and each tuple contains a member from each dimension you included in the results. So if you had three dimensions in the query, the resulting tuple would have three members.
Alternatively, you can use a mathematical representation of the combinations of the two sets. Using the concept of a Cartesian product, you can represent the set of data as:
{Canada, Mexico, USA} x {Drink, Food, Non-Consumable}
The Cartesian product operator (x) between the two sets represents the set of all possible combinations of the two sets. The ClusterFormat uses this representation. And although this is a much more compact representation, it requires more interpretation to understand and navigate.
The last section in MDDataSet is CellData, which contains values for each cell the MDX query returns. An ordinal number in a zero-based array refers to the cells. (To learn how to calculate ordinal numbers, see the Web sidebar "Mapping the Tuple Ordinals" at InstantDoc ID 44007.) If a cell isn't present in the array, the default value from AxisInfo serves as the value for the cell. If no default value is specified, the value is null.
A Convenient Marriage
This article has introduced XMLA as a Web services layer that uses SOAP to tap into OLAP data. XMLA provides the basis for standards-based, Internet-ready analytic applications, which can be easily deployed and shared across and among enterprises. By using the XML for Analysis SDK, you can use XMLA today in SQL Server 2000 Analysis Services (or in other vendors' platforms), and XMLA will be a core part of the SQL Server 2005 Analysis Services platform. With its flexibility and broad support, XMLA is an excellent tool for current or future analytic application projects.
End of Article
Prev. page
1
2
[3]
next page -->
