Hyperion XML for Analysis API Reference

XML For Analysis API Reference

This document covers the following topics:


Top

XML for Analysis General Information

XML for Analysis is a Simple Object Access Protocol (SOAP)-based XML API, designed specifically for standardizing the data access interaction between a client application and a data provider working over the Web.

This specification defines two methods, Discover and Execute, which consume and send XML for stateless data discovery and manipulation.

Discover is used to obtain information and meta data from a Web Service. This information can include a list available data sources and data about the provider for a particular data source. Properties are used to define and shape what data is obtained. The client application may need many types of information; Discover allows you to specify this in a common way. This generic interface and use of properties allows extensibility without rewriting existing functions.

Execute is used to execute Multidimensional Expressions (MDX) or other provider-specific commands against a particular XML for Analysis data source. The following diagram illustrates one possible implementation of an n-tiered application.

Provided with the URL for a server hosting a Web service, the client sends Discover and Execute calls using the SOAP and HTTP protocols to the server. The server instantiates the XML for Analysis provider, which handles the Discover and Execute calls. The XML for Analysis provider fetches the data, packages it into XML, and then sends the requested data as XML to the client. The Discover and Execute methods enable users to determine what can be queried on a particular server and, based on this, submit commands to be executed. The following scenario illustrates how an Internet application or a Web Service could use these methods.


Top

Installation

The steps to set up Essbase and Deployment Services for use with XML for Analysis are comparable to the steps described in the Deployment Services installation documentation. Refer to those documents for more comprehensive descriptions of the various components.

Setting up the Essbase XML for Analysis Testing Environment

The steps to install the XMLA Provider on Windows systems are as follows:

  1. Install Essbase Analytic Server Release 7.1.
  2. Install Essbase Administration Services to manage the Analytic Server.
  3. Install Essbase Deployment Services 7.1. This installs the XML for Analysis file by default.
  4. Create an Essbase user "system" identified by "password," with supervisor permission. Also create an Essbase user "guest" identified by "password."

Configuring the XML for Analysis Provider

Successful installation of XML for Analysis can be tested by entering the following URL in a browser.

  http://<machineName>:<port>/xmla/EssbaseXmlForAnalysis

For Example,

  http://localhost:8080/xmla/EssbaseXmlForAnalysis

This should bring the default configuration of the XML for Analysis provider as follows:

Essbase(r) XML For Analysis - Release 7.1 Copyright (c) 2004 Hyperion Solutions Corporation. All rights reserved. 
Properties file used: essbase.properties
webservice.request.redirect.enterpriseServer.host=localhost
webservice.request.redirect.enterpriseServer.orbType=TCPIP
webservice.request.redirect.enterpriseServer.port=5001
webservice.encoding=UTF-8
webservice.guest.name=system
webservice.guest.password=password
webservice.session.timeout=50
japi.netRetryCount=20
japi.netRetryInterval=1000

Before connecting to the XML for Analysis provider, make sure that the User set up is done, and the Analytic Servers are added to the Essbase Deployment Servers.

User Setup

The XML for Analysis configuration requires that the signed on user for XML for Analysis be available in both the EDS server and the Essbase server. The essbase.properties file located under HYPERION_HOME/eds/bin points to the default user. In essbase.properties, that entry appears as follows:

webservice.guest.name=system
webservice.guest.password=password

The "system" user is a default user in EDS but may have to be added to the Essbase Analytic server user list.

Alternately, if the user wishes to use an existing Essbase user account, make sure that the essbase.properties entry is updated and also add the user through the Deployment Services Console.

Adding Analytic servers to Essbase Deployment Servers

For the XML for Analysis provider to locate the data source specified in an XML request, the Essbase Analytic Server has to be added to the Essbase Deployment Services through the Deployment Services Console. For more information, refer to the Essbase Deployment Services documentation.


Top

Configuration Instructions using BEA Weblogic Server

  1. Copy essbase.properties from %ARBORPATH%\eds\bin to the WebLogic Server directory.

    For example, copy essbase.properties to D:\bea\wlserver6.1.

  2. Add XML for Analysis configurations to essbase.properties that is in the Weblogic Server directory.

    Caution: do NOT modify the original properties file in %ARBORPATH%\eds\bin.

    Assuming Essbase Deployment Services is already configured for HTTP rather than TCP/IP, add the following:

        # XML for Analysis
        webservice.request.redirect.enterpriseServer.host=localhost
        webservice.request.redirect.enterpriseServer.orbType=http
        webservice.request.redirect.enterpriseServer.port=7001
        webservice.encoding=UTF-8
        webservice.guest.name=system
        webservice.guest.password=password
        
  3. Add both the Essbase Deployment Services 7.1 servlet and the EssbaseXMLforAnalysis servlet to the Weblogic application server.

    1. In a text editor, open web.xml in d:\bea\wlserver6.1\config\essbase\applications\DefaultWebApp\WEB-INF .
    2. Add the following servlet names and mappings within the <web-app> </web-app> tags.
             <servlet>
               <servlet-name>
                 EssbaseEnterprise
               </servlet-name>
               <servlet-class>
                com.essbase.server.plugins.http.EssbaseEnterprise
               </servlet-class>
             </servlet>
      
             <servlet>
               <servlet-name>
                 EssbaseXmlForAnalysis
               </servlet-name>
               <servlet-class>
                 com.essbase.webservices.EssbaseXmlForAnalysis
               </servlet-class>
             </servlet>
      
             <servlet-mapping>
              <servlet-name>
                EssbaseEnterprise
              </servlet-name>
             <url-pattern>
               /EssbaseEnterprise
             </url-pattern>
            </servlet-mapping>
      
             <servlet-mapping>
              <servlet-name>
                EssbaseXmlForAnalysis
              </servlet-name>
             <url-pattern>
               /EssbaseXmlForAnalysis
             </url-pattern>
            </servlet-mapping>
      
    3. Save and close web.xml.
  4. Configuration is complete.


Top

XML for Analysis - Methods

The following methods provide a standard way for XML applications to access basic information from the server. Because these methods are invoked using the SOAP protocol, they accept input and deliver output in XML. By default, these methods are stateless, so the server context ends at the completion of any command. For information about how to make stateful calls, see "Support for Statefulness in XML for Analysis."

The simplified interface model has two methods.

The XML namespace for these methods is "urn:schemas-microsoft-com:xml-analysis".

Connection information is supplied in each method call with the connection properties.


Top

Discover

The Discover method can be used to retrieve information, such as the list of available data sources on a server or details about a specific data source. The data retrieved with the Discover method depends on the values of the parameters passed to it.

Namespace

urn:schemas-microsoft-com:xml-analysis

SOAP Action

"urn:schemas-microsoft-com:xml-analysis:Discover"

Syntax

Discover (
   [in] RequestType As EnumString,
   [in] Restrictions As Restrictions,
   [in] Properties As Properties,
   [out] Result As Rowset)

Parameters

RequestType [in]

This required parameter consists of a RequestTypes enumeration value, which determines the type of information to be returned. The RequestTypes enumeration is used by the Discover method to determine the structure and content of the rowset returned in the Result parameter. The format of the Restrictions parameter and the resulting XML result set is also dependent on the value specified in this parameter. This enumeration can be extended to support provider-specific enumeration strings.

Each RequestTypes enumeration value corresponds to a return rowset. For rowset definitions, see "XML for Analysis Rowsets." Support is required for the following explicitly named RequestTypes enumeration values.

Enumeration value Description
DISCOVER_DATASOURCES Returns a list of XML for Analysis data sources available on the server or Web Service. (For an example of how these may be published, see "XML for Analysis Implementation Walkthrough.")
DISCOVER_PROPERTIES Returns a list of information and values about the requested properties that are supported by the specified data source (provider).
DISCOVER_SCHEMA_ROWSETS Returns the names, values, and other information of all supported RequestTypes enumeration values (including those listed here), and any additional provider-specific enumeration values.
DISCOVER_ENUMERATORS Returns a list of names, data types, and enumeration values of enumerators supported by a specific data source's provider.
DISCOVER_KEYWORDS Returns a rowset containing a list of keywords reserved by the provider.
DISCOVER_LITERALS Returns information about literals supported by the data source provider. Schema Rowset Constant Given a constant that corresponds to one of the schema rowset names defined by OLE DB, such as MDSCHEMA_CUBES, returns the OLE DB schema rowset in XML format. Note that providers may also extend OLEDB by providing additional provider-specific schema rowsets. The schema rowsets that tabular data providers (TDP) and multidimensional data providers (MDP) are required to support are listed in the section "DISCOVER_SCHEMA_ROWSETS Rowset."

Restrictions [in]

This parameter, of the Restrictions data type, enables the user to restrict the data returned in Result. The Result columns are defined by the rowset specified in the RequestType parameter. Some columns of Result can be used to filter the rows returned. For these columns and those that can be restricted, see the rowset tables in "XML for Analysis Rowsets." To obtain the restriction information for provider-specific schema rowsets, use the DISCOVER_SCHEMA_ROWSETS request type. This parameter must be included, but it can be empty.

Properties [in]

This parameter, of the Properties data type, consists of a collection of XML for Analysis properties. Each property enables the user to control some aspect of the Discover method, such as specifying the return format of the result set, the timeout, and specifying the locale in which the data should be formatted.

The available properties and their values can be obtained by using the DISCOVER_PROPERTIES request type with the Discover method. Standard XML for Analysis properties are detailed in "XML for Analysis Properties."

There is no required order for the properties listed in the Properties parameter. This parameter must be included, but it can be empty.

Result [out]

This required parameter contains the result set returned by the provider as a Rowset object. The columns and content of the result set are specified by the values specified in the RequestType and Restrictions parameters. The column layout of the returned result set is also determined by the value specified in RequestType. For more information about the rowset layouts that correspond to for each RequestType value, see "XML for Analysis Rowsets."

For more information about the Rowset data type, see "Data Types Used in XML for Analysis."

Example

In the following sample, the client sends the XML Discover call to request a list of cubes from the FoodMart 2000 catalog:

 <SOAP-ENV:Envelope
 xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
 <SOAP-ENV:Body>
 <Discover xmlns="urn:schemas-microsoft-com:xml-analysis" 
  SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <RequestType>MDSCHEMA_CUBES</RequestType>
   <Restrictions>
    <RestrictionList>
     <CATALOG_NAME>
      FoodMart 2000
     </CATALOG_NAME>
    </RestrictionList>
   </Restrictions>
 
   <Properties>
    <PropertyList>
     <DataSourceInfo>
      Provider=MSOLAP;Data Source=local;
     </DataSourceInfo>
     <Catalog>
      Foodmart 2000
     </Catalog>
     <Format>
      Tabular
     </Format>
    </PropertyList>
   </Properties>
  </Discover>
The provider returns the following result to the client:
<?xml version="1.0"?>
<SOAP-ENV:Envelope
 xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
 <SOAP-ENV:Body>
  <DiscoverResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
   <return>
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <!-- The XML schema definition of the result comes here -->
      ...
     </xsd:schema>
     <row>
      <CATALOG_NAME>FoodMart 2000</CATALOG_NAME>
      <CUBE_NAME>Sales</CUBE_NAME>
      ...
     </row>
 
     <row>
      <CATALOG_NAME>FoodMart 2000</CATALOG_NAME>
      <CUBE_NAME>Warehouse</CUBE_NAME>
      ...
     </row>
     ...
    </root>
   </return>
  </DiscoverResponse>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>


Top

Execute

The Execute method is used for sending action requests to the server. This includes requests involving data transfer, such as retrieving or updating data on the server.

Namespace

urn:schemas-microsoft-com:xml-analysis

SOAP Action

"urn:schemas-microsoft-com:xml-analysis:Execute"

Syntax

Execute (
   [in] Command As Command,
   [in] Properties As Properties,
   [out] Result As Resultset)

Parameters

Command [in]

This required parameter is of Command data type and consists of a provider-specific statement to be executed. XML for Analysis multidimensional providers must support the mdXML language, but they can also support other commands as needed.

Properties [in]

This parameter is of the Properties data type and consists of a collection of XML for Analysis properties. Each property allows the user to control some aspect of the Execute method, such as defining the information required for the connection, specifying the return format of the result set, or specifying the locale in which the data should be formatted.

The available properties and their values can be obtained by using the DISCOVER_PROPERTIES request type with the Discover method. Standard XML for Analysis properties are detailed in "XML for Analysis Properties."

There is no required order for the properties listed in the Properties parameter. This parameter must be included, but it can be empty.

Result [out]

This parameter contains the Resultset result returned by the provider. The Command parameter and values in the Properties parameter define the shape of the result set. If no shape-defining properties are passed, the XML for Analysis provider may use a default shape. The two result set formats defined by this specification are Tabular and Multidimensional, as specified by the client through the Format property. OLAP data lends itself to the Multidimensional format (although the Tabular format can also be used). A provider may support additional rowset types, and clients aware of the specialized types can request them.

Example

The following is an example of an Execute method call with <Statement> set to an OLAP MDX SELECT statement:

 <SOAP-ENV:Envelope
 xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
 <SOAP-ENV:Body>
 <Execute xmlns="urn:schemas-microsoft-com:xml-analysis" 
  SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <Command>
    <Statement>
     select [Measures].members on Columns from Sales
    </Statement>
   <Command>
   <Properties>
    <PropertyList>
     <DataSourceInfo>
 
Provider=Essbase;Data Source=local;
     </DataSourceInfo>
     <Catalog>Foodmart 2000</Catalog>
     <Format>Multidimensional</Format>
     <AxisFormat>ClusterFormat</AxisFormat>
    </PropertyList>
   </Properties>
  </Execute>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

This is the abbreviated response for the preceding method call:

<?xml version="1.0"?>
<SOAP-ENV:Envelope
 xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
 <SOAP-ENV:Body>
  <m:ExecuteResponse 
  xmlns:m="urn:schemas-microsoft-com:xml-analysis">
  <m:return
  SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <root xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset">
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xars="urn:schemas-microsoft-com:xars">
     ...<!-The schema for the data goes here. -- >
    </xsd:schema>
    ... <!-The data in MDDataSet format goes here. -- >
   </root>
  </m:return>
  </m:ExecuteResponse>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>


Top

Data Types Used in XML for Analysis

The following alphabetical list describes XML for Analysis data types and notes those data types that use standard XML data types. For more information about the XML Schema types, see http://www.w3.org/TR/xmlschema-2/. To view the schema structure, see http://www.w3.org/2001/XMLSchema-datatypes.xsd.

Boolean

The Boolean type uses the standard XML boolean data type.

Decimal

The Decimal type noted uses the standard XML decimal data type.

Integer

The Integer type noted in this document refers to the standard XML int data type.

EnumString

The EnumString data type defines a set of named constants for a given enumerator (enum). EnumString uses the standard XML string data type. The specific values for each of the named constants are specified with the enumerator definition.

MDDataSet

The MDDataSet format is one of the formats that can be returned in the Result parameter of the Execute method. This one is used for multidimensional data. Representing OLAP data in XML requires an OLAP-oriented rowset (or dataset), which is noted here. The XML namespace for the MDDataSet data type is "urn:schemas-microsoft-com:xml-analysis:mddataset".

For basic information about the OLE DB for OLAP dataset structures, see "MDDataset Data Type Mapping to OLE DB." For a full XML Schema Definition (XSD) sample of the MDDataSet, see Appendix D.

This specification defines the following XML structure for OLAP results. An MDDataSet consists of these main sections:

Providers can add additional annotations to the structure as long as they do not change the behavior and meaning of the schema defined here. This open content schema model allows new elements and attributes to be added from other namespaces but does not allow the semantics of defined elements and attributes to be changed.

OLAPInfo

The OLAPInfo section contains three elements:

The CubeInfo section contains a collection of cube elements. To define the structure, <OlapInfo> defines axes using the <AxesInfo> element (note the plural, Axes). Axes consists of a set of <AxisInfo> elements (note the singular, Axis) that alias to an ordinal, such as name="Axis0". The dimension hierarchies are then listed with their property definitions. In the example that follows, the standard member properties are represented in <HierarchyInfo> element by UName, Caption, LName, and LNum, as well as the nonstandard DisplayInfo element. For the Store hierarchy, the additional (nonstandard) member property, with the space character, [Store].[Store SQFT] is illustrated below.

<OlapInfo>
   <CubeInfo>
        <Cube>
             <CubeName>cubename</CubeName>
        </Cube>
    </CubeInfo>
   <AxesInfo>
      <AxisInfo name="Axis0">
         <HierarchyInfo name="Measures">
            <UName name="[Measures].[MEMBER_UNIQUE_NAME]"></UName>
               <Caption name="[Measures].[MEMBER_CAPTION]"></Caption>
                  <LName name="[Measures].[LEVEL_UNIQUE_NAME]"></LName>
                     <LNum name="[Measures].[LEVEL_NUMBER]"></LNum>
                        <DisplayInfo name="[Measures].[DISPLAY_INFO]"></DisplayInfo>
                  </HierarchyInfo>
               </AxisInfo> 
                  <AxisInfo name="Axis1">
                     <HierarchyInfo name="Store">
                            <UName name="[Store].[MEMBER_UNIQUE_NAME]"></UName>
                           <Caption name="[Store].[MEMBER_CAPTION]"></Caption>
                           <LName name="[Store].[LEVEL_UNIQUE_NAME]"></LName>
                           <LNum name="[Store].[LEVEL_NUMBER]"></LNum>
                           <DisplayInfo name="[Store].[DISPLAY_INFO]"></DisplayInfo>
                           <Store_x0020_SQFT name="[Store].[Store Name].[Store SQFT]"></Store_x0020_SQFT>
                     </HierarchyInfo>
                     <HierarchyInfo name="Time">
                           <UName name="[Time].[MEMBER_UNIQUE_NAME]"></UName>
                           <Caption name="[Time].[MEMBER_CAPTION]"></Caption>
                           <LName name="[Time].[LEVEL_UNIQUE_NAME]"></LName>
                           <LNum name="[Time].[LEVEL_NUMBER]"></LNum>
                           <DisplayInfo name="[Time].[DISPLAY_INFO]"></DisplayInfo>
                     </HierarchyInfo>
                   </AxisInfo>

The last thing defined by the OLAPInfo structure is the properties (column definitions) for the cells. This allows cells to contain additional properties. The properties in this example are Value, FmtValue, and the custom property FormatString.

<OlapInfo>
 ...
            <CellInfo>
                  <Value name="VALUE"></Value>
                  <FmtValue name="FORMATTED_VALUE"></FmtValue>
                  <FormatString name="FORMAT_STRING"></FormatString>
            </CellInfo>

HierarchyInfo Standard Elements

The following standard elements are required for the <HierarchyInfo> element. MDSCHEMA references refer to the OLE DB for OLAP schema definition.

The name attribute of the HierarchyInfo element should contain the HIERARCHY_UNIQUE_NAME, as defined in OLEDB.

ElementDescription
UNameMEMBER_UNIQUE_NAME property from OLE DB axis rowset
CaptionMEMBER_CAPTION property from OLE DB axis rowset
LNameLEVEL_UNIQUE_NAME property from OLE DB axis rowset
LNumLEVEL_NUMBER property from OLE DB axis rowset

CellInfo Standard Elements

The following are the standard elements for the element. Whether or not they are returned for any particular query depends on the query itself.

ElementDescription
ValueVALUE property from OLE DB cell properties
FmtValueFORMATTED_VALUE property from OLE DB cell properties
ForeColorFORE_COLOR property from OLE DB cell properties
BackColorBACK_COLOR property from OLE DB cell properties

Using Defaults in CellInfo and AxisInfo

A provider can optionally specify default values for individual member or cell properties in the AxisInfo or CellInfo section. This can provide a smaller result if the property always or almost always has the same value.

To indicate a default value for a property, the <Default> element can optionally be specified as a subelement of one of the member or cell property elements. For instance, to specify a default value for Store SQFT, the provider would be specified as follows:

<Store_x0020_SQFT name="Store SQFT">
 <Default>5000</Default>
</Store_x0020_SQFT>

Therefore, the absence of a member or cell property in the result indicates that the stated default is the value for the member property or the cell property. In the following result, in which the output for the <Store_x0020_SQFT> element is absent, the value for <Store_x0020_SQFT> is 5000 (the default value that was defined earlier):

<Member Hierarchy="Store">
      <UName>[Store].[CA]</UName>
      <Caption>CA</Caption>
      <LName>[Store].[State]</LName>
      <LNum>2</LNum>
</Member>

If the element is present but without a value, this implies an empty string result (""), as shown in the following example:

<Store_x0020_SQFT />

Typically, if a property is null, it is simply omitted. However, if a default value has been defined for a property, then to indicate a null value for a property, use the nil attribute from the XML Schema specification, as follows:

<Store_x0020_SQFT xsi:nil='true' />

Axes

Under Axes, the Axis items are listed in the order that they occur in the dataset, starting at zero. The AxisFormat property setting determines how Axis elements are formatted. All XML for Analysis providers must support the following values for the property AxisFormat:

Support of the CustomFormat value as a distinct format is optional for a provider. If a client requests CustomFormat, the provider may choose, at its discretion, to return one of the TupleFormat and ClusterFormat formats. While providers must support all three of the above values, clients can request the format they want; therefore clients may choose not to make use of all three available formats.

Axis Formats

The TupleFormat and ClusterFormat settings for the AxisFormat property provide two different ways of representing tuples. The MDDataset definition gives the provider two ways to specify tuples as multidimensional tuples or as a Cartesian product. This provides a client application a choice between simplicity and minimizing space requirements.

An axis represents a set of tuples, where all tuples in the set have the same dimensionality. A set can be represented in different ways with different advantages. For example, the following set of four tuples can be represented as a collection of two-dimensional tuples or a Cartesian product of two one-dimensional sets.
1999199920002000
ActualBudgetActualBudget

The following line represents the set of four tuples as collection of two-dimensional tuples:

{ ( 1999, Actual ), ( 1999, Budget ), ( 2000, Actual ), ( 2000, Budget ) }

The following line represents the set of four tuples as a Cartesian product of two one-dimensional sets:

{ 1999, 2000 } x { Actual, Budget }

Both representations have advantages and disadvantages. Two-dimensional tuples are simpler for client tools to use. A Cartesian product of one-dimensional sets uses less space and preserves the multidimensional nature of the set.

The following table lists operations that can be used to define and characterize the structure and members of an axis.

OperationDescription
MemberThe smallest unit of an axis representing the member of a dimension hierarchy
TupleA vector of members from different dimension hierarchies
MembersA set of Member objects from the same dimension hierarchy
TuplesA collection of Tuple objects with the same dimensionality
UnionA union of sets
CrossProductA Cartesian product of sets

Based on the previous example, these operations translate the two-dimensional tuples and Cartesian product of one-dimensional sets as follows:

Two-dimensional tuples

Tuples ( 
Tuple( Member(1999), Member(Actual) ), 
Tuple( Member(1999), Member(Budget) ), 
Tuple( Member(2000), Member(Actual) ), 
Tuple( Member(2000), Member(Budget) ) 
)

Cartesian product of one-dimensional sets

CrossProduct ( 
Members (Member(1999), Member(2000) ), 
Members (Member(Actual), Member(Budget) ) 
)

The XML representation of these operations follows these rules (where member_properties value refers to the list of member properties defined in the corresponding AxisInfo section):

member      : <Member> member_properties </Member>
tuple       : <Tuple> member_list </Tuple>
set         : <Members> member_list </Members>
set         : <Tuples> tuple_list </Tuples>
set         : <CrossProduct> set_list </CrossProduct>
set         : <Union> set_list </Union>
member_list : member [ member ... ]
tuple_list  : tuple [ tuple ... ]
set_list    : set [ set ... ]

As shown above, the same set can have different representations using different operations. The client can request a specific representation using the AxisFormat property.

TupleFormat

In TupleFormat, an axis is represented as a set of tuples. The following operations must be used in the specified order:

<Axis>
      <Tuples>
            <Tuple>
                  <Member Hierarchy="name">

In addition, <Member> elements must have the Hierarchy attribute that specifies the hierarchy name of the member.

The following example illustrates the TupleFormat.
199919992000
ActualBudgetBudget

<Axes>
 <Axis name="Axis0">	
  <Tuples>
   <Tuple>
    <Member Hierarchy="Time">
     <UName>[Time].[1999]</UName>
     …
    </Member>
    <Member Hierarchy="Category">
     <UName>[Scenario].[Actual]</UName>
     …
    </Member>
   </Tuple>
   <Tuple>
    <Member Hierarchy="Time">
     <UName>[Time].[1999]</UName>
     …
    </Member>
    <Member Hierarchy="Category">
     <UName>[Scenario].[Budget]</UName>
     …
    </Member>
   </Tuple>
   <Tuple>
    <Member Hierarchy="Time">
     <UName>[Time].[2000]</UName>
     …
    </Member>
 
    <Member Hierarchy="Category">
     <UName>[Scenario].[Budget]</UName>
     …
    </Member>
   </Tuple>
  </Tuples>
 </Axis>
 ...
</Axes>

ClusterFormat

In ClusterFormat, an axis is represented as a set of clusters. Each cluster represents a crossproduct of members from different dimension hierarchies. Providers will define their own provider-specific clustering algorithms. The following operations must be used in the specified order:

<Axis>
 <CrossProduct Size="size">
  <Members Hierarchy="name">
   <Member>

For representing objects as clusters, the <CrossProduct> element must have a Size attribute indicating the number of tuples that results from the product of individual Member sets within the CrossProduct. The <Members> element must have a Hierarchy attribute that specifies the dimension hierarchy name of all members in the set.

A crossproduct may contain members from a single dimension hierarchy.

The following example illustrates two clusters:

19991999200020002001
ActualBudgetActualBudgetBudget
cluster 1cluster 2

<Axes>
 <Axis name="Axis0">
  <CrossProduct Size = "4">
   <Members Hierarchy="Time">
    <Member>
     <UName>[Time].[1999]</UName>
     …
    </Member>
    <Member>
     <UName>[Time].[2000]</UName>
     …
    </Member>
   </Members>
   <Members Hierarchy="Category">
    <Member>
     <UName>[Scenario].[Actual]</UName>
     …
    </Member>
    <Member>
     <UName>[Scenario].[Budget]</UName>
     …
    </Member>
   </Members>
  </CrossProduct>
  <CrossProduct Size = "1">
   <Members Hierarchy="Time">
 
    <Member>
     <UName>[Time].[2001]</UName>
     …
    </Member>
   </Members>
   <Members Hierarchy="Category">
    <Member>
     <UName>[Scenario].[Budget]</UName>
     …
    </Member>
   </Members>
  </CrossProduct>
 </Axis>
 …
</Axes>

CustomFormat

CustomFormat allows the provider to generate the axes in any valid combination of the operations defined in the sections above, with following restrictions:

The CustomFormat gives the most flexibility and power to a provider to optimize the axis representation.

This section is an example of what a provider may choose to return for CustomFormat.

WAWACACA
UmbrellaUmbrellaSunglassesSunglasses
ActualBudgetActualBudget

A provider can choose to generate the representation below for the CustomFormat result. In this example, a set of tuples is crossjoined with a set of members.

CrossProduct( 
Tuples ( 
Tuple ( Member(WA), Member(Umbrella) ), 
Tuple ( Member(CA), Member(Sunglasses) ) ), 
  	Members ( Member(Actual), Member(Budget) ) 
)

The above theoretical formulation can be represented in XML as follows:

<Axis name="Axis0">
 <CrossProduct>
  <Tuples>
   <Tuple>
    <Member Hierarchy="Store">
<UName>[Store].[WA]</UName>
...
</Member>
    <Member Hierarchy="Product">
<UName>[Product].[Umbrella]</UName>
...
</Member>
   </Tuple>
   <Tuple>
    <Member Hierarchy="Store">
<UName>[Store].[CA]</UName>
...
</Member>
 
    <Member Hierarchy="Product">
<UName>[Product].[Sunglasses]</UName>
...
</Member>
   </Tuple>
  </Tuples>
  <Members Hierarchy="Category">
  <Member>
  <UName>[Category].[Actual]</UName>
  ...
  </Member>
  <Member>
  <UName>[Category].[Budget]</UName>
  ...
  </Member>
  </Members>
 </CrossProduct>
</Axis>

CellData

The Axes section is followed by the CellData section, which contains the property values for each cell. A mandatory CellOrdinal attribute indicates the ordinal of the cell. CellOrdinal is numbered 0 to n-1, for n cells. Cell elements can be missing if all cell properties are the default (NULL is the default if no default has been specified). Note that the type of the <Value> element must be specified in the CellData section, while other standard properties, whose type is defined in the schema need not have a type specified.

   <CellData>
      <Cell CellOrdinal="0">
         <Value xsi:type="xsd:double">16890</Value>
         <FmtValue>16,890.00</FmtValue>
         <FormatString>Standard</FormatString>
      </Cell>
      <Cell CellOrdinal="1">
         <Value xsi:type="xsd:int">50</Value>
         <FmtValue>50</FmtValue>
         <FormatString>Standard</FormatString>
      </Cell>
      <Cell CellOrdinal="2">
         <Value xsi:type="xsd:double">36175.2</Value>
         <FmtValue>$36,175.20</FmtValue>
         <FormatString>Currency</FormatString>
      </Cell>
   </CellData>

No property is returned more than once in the CellData section, even if an MDX command has multiple references to a cell property. For example, provided the following MDX query :

Select from sales cell properties Value, FormattedValue, Value

The CellInfo section of OlapInfo contains the same sequence of cell properties:

   <CellInfo>
      <CellOrdinal/>
      <Value/>
      <FmtValue/>
      <Value/>
   </CellInfo>

However, the CellData section eliminates the duplication,returning the data only once:

   <Cell CellOrdinal="0">
      <Value xsi:type="xsd:int">10</Value>
      <FmtValue>$10.00</FmtValue>
   </Cell>

The axis reference for a cell can be calculated based on CellOrdinal. Conceptually, cells are numbered in a dataset as if the dataset were a p-dimensional array, where p is the number of axes. Cells are addressed in row-major order. The following illustration shows the formula for calculating the ordinal number of a cell.

We will apply the above formula to the result set shown in the following table. The query asked for four measures on columns and a crossjoin of two states with four quarters on rows. In following the dataset result, the CellOrdinal property for the part of the dataset result shown in the box is the set {9, 10, 11, 13, 14, 15, 17, 18, 19}. This is because the cells are numbered in row major order, starting with a CellOrdinal of zero for the upper left cell.

Next, we apply the above formula to the cell that is {CA, Q3, Store Cost}. Axis k=0 has Uk=4 members and axis k=1 has Uk=8 tuples. P is the total number of axes in the query, here equal to 2. S0, the initial summation is i=0 to 1. For i=0, the tuple ordinal on axis 0 of {Store Cost} is 1. For i = 1, the tuple ordinal of {CA, Q3} is 2.

For i=0, Ei = 1, so for i = 0 the sum is 1 * 1 = 1 and for i=1, the sum is 2 (tuple ordinal) * 4 (the value of Ei, computed as 1 * 4), or 8, and so the sum is equal to 1 + 8 = 9, the cell ordinal for that cell.

Unit Sales Store Cost Store Sales Sales Count
CA Q1 16,890.00 14,431.09 $36,175.20 5498
Q2 18,052.00 15,332.02 $38,396.75 5915
Q3 18,370.00 15,672.83 $39,394.05 6014
Q4 21,436.00 18,094.50 $45,201.84 7015
OR Q1 19,287.00 16,081.07 $40,170.29 6184
Q2 15,079.00 12,678.96 $31,772.88 4799
Q3 16,940.00 14,273.78 $35,880.46 5432
Q4 16,353.00 13,738.68 $34,453.44 5196

The complete XML output for the above dataset is shown in Appendix D.

Command Data Type

The Command data type is an XML document type. In this version of the XML for Analysis specification the Command data type consists solely of the <Statement> tag, of type string, which contains the text for the command statement. For example, the <Statement> element with an MDX statement may look like this:

<Statement>
  SELECT Measures.MEMBERS on columns from Sales
</Statement>

In a future version of this specification, the XML document for the Command data type will be expanded beyond the single <Statement> element defined in this specification.

The XML for Analysis specification requires that multidimensional providers support the mdXML language. The mdXML language will be based on MDX; currently mdXML consists solely of the <Statement> element. For multidimensional providers, the <Statement> element must contain an MDX language statement. Future enhancements to mdXML will make additional elements beyond the <Statement> element available. The <Statement> element will continue to support a complete MDX statement as type string, even if it is expanded to also allow for other XML elements.

The <Statement> element may be empty, as in <Statement/>. This can be used for the Command in which BeginSession or EndSession is sent in the SOAP header.

In addition to future enhancements of mdXML, the MDX language itself is extensible. Providers can add extensions to the language to support additional features that are not provided in the base language set. For more information about mdXML, please see section "Relationship between MDX and mdXML.

Properties

The Properties data type represents a collection of XML for Analysis properties. Each property is defined by an XML element, and the value of the property is the data contained by the XML element. The name of the XML element corresponds to the name of the property.

Each provider can extend the set of properties, but provider-specific property names must be well-formed XML tags.

An example follows:

<PropertyList>
 <DataSourceInfo>
  Provider=MSOLAP;Data Source=local;
 </DataSourceInfo>
 <Catalog>
  Foodmart 2000
 </Catalog>
 <Format>
  Multidimensional
 </Format>
</PropertyList>

Restrictions

The Restrictions data type represents a collection of restrictions to be applied during the execution of a Discover method. The Restriction name specifies the rowset column name that is being restricted. The Restriction value defines the data for the column.

Each provider can add new schema rowsets, but columns that can be restricted should have names that meet the well-formedness constraints of XML.

The following example sends a restriction for a column name in the MDSCHEMA_CUBES schema rowset:

<RestrictionList>
 <CATALOG_NAME>
  FoodMart 2000
 </CATALOG_NAME>
 ...
</RestrictionList>

When needed, a column can be restricted with multiple values. Each value is represented in a <Value> element. An example follows:

<RestrictionList>
 <LiteralName>
  <Value>DBLITERAL_QUOTE_PREFIX</Value>
  <Value>DBLITERAL_QUOTE_SUFFIX</Value>
  <Value>DBLITERAL_ESCAPE_UNDERSCORE_PREFIX</Value>
  <Value>DBLITERAL_ESCAPE_UNDERSCORE_SUFFIX</Value>
 </LiteralName>
 ...
</RestrictionList>

The Members schema rowset has a special restriction that does not correspond to a column in the rowset result. This special restriction operator is called the TREE_OP restriction. The type of the TREE_OP restriction is integer. The TREE_OP restriction is a bitmask, so bitwise combinations of its values are valid. The following table contains the possible values for the TREE_OP restriction.

Symbolic constant (OLEDB)Integer valueDescription
MDTREEOP_CHILDREN1Returns only the immediate children.
MDTREEOP_SIBLINGS2Returns members on the same level.
MDTREEOP_PARENT4Returns only the immediate parent.
MDTREEOP_SELF8Returns the immediate member in the list of returned rows.
MDTREEOP_DESCENDANTS16Returns all descendants.
MDTREEOP_ANCESTORS32Returns all ancestors.

Resultset

The Resultset data type is a self-describing XML result set. The type of data it will contain is indicated by the XML for Analysis Format property.

By default, the XML schema is returned with the result set. This can be changed using the Content property, described in "XML for Analysis Properties."

Rowset

The XML schema embedded within the rowset defines the specific structure of the Rowset return data type. The general structure of the XML for Analysis rowset is similar to the Microsoft® SQL Server™ 2000 rowset format obtained with the FOR XML RAW clause, but it is element-centric rather than attribute-centric, and it allows hierarchical data.

XML does not allow certain characters as element and attribute names. XML for Analysis supports encoding as defined by SQL Server 2000 to address this XML constraint. For column names that contain invalid XML name characters (according to the XML 1.0 specification), the nonvalid Unicode characters are encoded using the corresponding hexadecimal values. These are escaped as _xHHHH_ where HHHH stands for the four-digit hexadecimal UCS-2 code for the character in most-significant bit first order. For example, the name "Order Details" is encoded as Order_x0020_Details, where the space character is replaced by the corresponding hexadecimal code.

Encoding can make Extensible Style Language (XSL) transformations difficult. To support a quick lookup of the actual unencoded column names, add the sql:field attribute into the XML rowset schema with each column. This attribute resides in the "urn:schemas-microsoft-com:xml-sql" namespace.

An example follows:

<xsd:element name="Order_x0020_Details" type="string" sql:field="Order Details" />

A Null value for a column within a row can be expressed in the following ways:

For example, if a row has a single column called Store_Name and its value is null, it can be represented as either:

	<row>
	</row>

-Or-

	<row>
	    <Store_name xsi:nil='true'/>
	</row>

For flat data, the XML for Analysis rowset format appears as in the following example. The column names, which are specific to the query, are defined in the schema as the element names.

A pair of <row> tags encapsulates each row:

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"
 <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   </xsd:element>
      <xsd:complexType name="row">
         <xsd:choice maxOccurs="unbounded" minOccurs="0">
            <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"></xsd:element>
            <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION"></xsd:element>
            <xsd:element name="ROLES" type="xsd:string" sql:field="ROLES"></xsd:element>
            <xsd:element name="DATE_MODIFIED" type="xsd:time" sql:field="DATE_MODIFIED"></xsd:element>
         </xsd:choice>
      </xsd:complexType>
   </xsd:schema>
   <row>
      <CATALOG_NAME>FoodMart 2000</CATALOG_NAME>
      <DESCRIPTION></DESCRIPTION>
      <ROLES>All Users</ROLES>
      <DATE_MODIFIED>3/11/2001 6:49:36 PM</DATE_MODIFIED>
   </row>
   …
</Root>

For hierarchical data (or nested rowsets), such as that returned by OLE DB for data mining queries, the XML for Analysis rowset format appears as in the following example. The structure of the rows is not changed, but the data-specific schema defines an element subtype that contains the nested data. In this case, the nested element is <NODE_DISTRIBUTION>.

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
 <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <xsd:complexType name="row">
         <xsd:choice maxOccurs="unbounded" minOccurs="0">
            <xsd:sequence maxOccurs="unbounded" minOccurs="0">
               <xsd:element name="NODE_DISTRIBUTION" sql:field="NODE_DISTRIBUTION">
                  <xsd:complexType>
                     <xsd:choice maxOccurs="unbounded" minOccurs="0">
                        <xsd:element name="ATTRIBUTE_NAME" type="xsd:string" sql:field="ATTRIBUTE_NAME"></xsd:element>
                           <xsd:element name="ATTRIBUTE_VALUE" type="xsd:string" sql:field="ATTRIBUTE_VALUE"></xsd:element>
                           <xsd:element name="SUPPORT" type="xsd:double" sql:field="SUPPORT"></xsd:element>
                        <xsd:element name="PROBABILITY" type="xsd:double" sql:field="PROBABILITY"></xsd:element>
                        <xsd:element name="VARIANCE" type="xsd:double" sql:field="VARIANCE"></xsd:element>
                        <xsd:element name="VALUETYPE" type="xsd:int" sql:field="VALUETYPE"></xsd:element>
                     </xsd:choice>
                  </xsd:complexType>
               </xsd:element>
            </xsd:sequence>
            <xsd:element name="MODEL_CATALOG" type="xsd:string" sql:field="MODEL_CATALOG"></xsd:element>
            <xsd:element name="MODEL_SCHEMA" type="xsd:string" sql:field="MODEL_SCHEMA"></xsd:element>
            <xsd:element name="MODEL_NAME" type="xsd:string" sql:field="MODEL_NAME"></xsd:element>
            <xsd:element name="ATTRIBUTE_NAME" type="xsd:string" sql:field="ATTRIBUTE_NAME"></xsd:element>
            <xsd:element name="NODE_NAME" type="xsd:string" sql:field="NODE_NAME"></xsd:element>
            <xsd:element name="NODE_UNIQUE_NAME" type="xsd:string" sql:field="NODE_UNIQUE_NAME"></xsd:element>
            <xsd:element name="NODE_TYPE" type="xsd:unsignedInt" sql:field="NODE_TYPE"></xsd:element>
            <xsd:element name="NODE_GUID" type="xsd:string" sql:field="NODE_GUID"></xsd:element>
            <xsd:element name="NODE_CAPTION" type="xsd:string" sql:field="NODE_CAPTION"></xsd:element>
            <xsd:element name="CHILDREN_CARDINALITY" type="xsd:unsignedInt" sql:field="CHILDREN_CARDINALITY"></xsd:element>
            <xsd:element name="PARENT_UNIQUE_NAME" type="xsd:string" sql:field="PARENT_UNIQUE_NAME"></xsd:element>
            <xsd:element name="NODE_DESCRIPTION" type="xsd:string" sql:field="NODE_DESCRIPTION"></xsd:element>
            <xsd:element name="NODE_RULE" type="xsd:string" sql:field="NODE_RULE"></xsd:element>
            <xsd:element name="MARGINAL_RULE" type="xsd:string" sql:field="MARGINAL_RULE"></xsd:element>
            <xsd:element name="NODE_PROBABILITY" type="xsd:double" sql:field="NODE_PROBABILITY"></xsd:element>
            <xsd:element name="MARGINAL_PROBABILITY" type="xsd:double" sql:field="MARGINAL_PROBABILITY"></xsd:element>
            <xsd:element name="NODE_SUPPORT" sql:type="xsd:double" sql:field="NODE_SUPPORT"></xsd:element>
            <xsd:element name="MSOLAP_MODEL_COLUMN" sql:type="xsd:string" sql:field="MSOLAP_MODEL_COLUMN"></xsd:element>
            <xsd:element name="MSOLAP_NODE_SCORE" sql:type="xsd:double" sql:field="MSOLAP_NODE_SCORE"></xsd:element>
            <xsd:element name="MSOLAP_NODE_SHORT_CAPTION" sql:type="xsd:string" sql:field="MSOLAP_NODE_SHORT_CAPTION"></xsd:element>
         </xsd:choice>
      </xsd:complexType>
   </xsd:schema>
   <row>
      <MODEL_CATALOG>FoodMart 2000</MODEL_CATALOG>
      <MODEL_NAME>customer pattern discovery</MODEL_NAME>
      <ATTRIBUTE_NAME>Customers.Name.Member Card</ATTRIBUTE_NAME>
      <NODE_NAME>2147483652</NODE_NAME>
      <NODE_UNIQUE_NAME>2147483652</NODE_UNIQUE_NAME>
      <NODE_TYPE>2</NODE_TYPE>
      <NODE_CAPTION>All</NODE_CAPTION>
      <CHILDREN_CARDINALITY>8</CHILDREN_CARDINALITY>
      <PARENT_UNIQUE_NAME>0</PARENT_UNIQUE_NAME>
      <NODE_DESCRIPTION>All</NODE_DESCRIPTION>
      <NODE_RULE></NODE_RULE>
      <MARGINAL_RULE></MARGINAL_RULE>
      <NODE_PROBABILITY>1</NODE_PROBABILITY>
      <MARGINAL_PROBABILITY>1</MARGINAL_PROBABILITY>
      <NODE_DISTRIBUTION>
         <ATTRIBUTE_NAME>Customers.Name.Member Card</ATTRIBUTE_NAME>
         <ATTRIBUTE_VALUE>missing</ATTRIBUTE_VALUE>
         <SUPPORT>0</SUPPORT>
         <PROBABILITY>0</PROBABILITY>
         <VARIANCE>0</VARIANCE>
         <VALUETYPE>1</VALUETYPE></NODE_DISTRIBUTION>
      <NODE_DISTRIBUTION>
         <ATTRIBUTE_NAME>Customers.Name.Member 
 
Card</ATTRIBUTE_NAME>
         <ATTRIBUTE_VALUE>Bronze</ATTRIBUTE_VALUE>
         <SUPPORT>3077</SUPPORT>
         <PROBABILITY>0.551334886221107</PROBABILITY>
         <VARIANCE>0</VARIANCE>
         <VALUETYPE>4</VALUETYPE></NODE_DISTRIBUTION>
      <NODE_DISTRIBUTION>
         <ATTRIBUTE_NAME>Customers.Name.Member Card</ATTRIBUTE_NAME>
         <ATTRIBUTE_VALUE>Golden</ATTRIBUTE_VALUE>
         <SUPPORT>659</SUPPORT>
         <PROBABILITY>0.118079197276474</PROBABILITY>
         <VARIANCE>0</VARIANCE>
         <VALUETYPE>4</VALUETYPE></NODE_DISTRIBUTION>
      <NODE_DISTRIBUTION>
         <ATTRIBUTE_NAME>Customers.Name.Member Card</ATTRIBUTE_NAME>
         <ATTRIBUTE_VALUE>Normal</ATTRIBUTE_VALUE>
         <SUPPORT>1332</SUPPORT>
         <PROBABILITY>0.238666905572478</PROBABILITY>
         <VARIANCE>0</VARIANCE>
         <VALUETYPE>4</VALUETYPE></NODE_DISTRIBUTION>
      <NODE_DISTRIBUTION>
         <ATTRIBUTE_NAME>Customers.Name.Member Card</ATTRIBUTE_NAME>
         <ATTRIBUTE_VALUE>Silver</ATTRIBUTE_VALUE>
         <SUPPORT>513</SUPPORT>
         <PROBABILITY>9.19190109299409E-02</PROBABILITY>
         <VARIANCE>0</VARIANCE>
         <VALUETYPE>4</VALUETYPE></NODE_DISTRIBUTION>
      <NODE_SUPPORT>5581</NODE_SUPPORT>
      <MSOLAP_MODEL_COLUMN>Customers.Name.Member 
 
Card</MSOLAP_MODEL_COLUMN>
      <MSOLAP_NODE_SCORE>1948.401692055</MSOLAP_NODE_SCORE>
      <MSOLAP_NODE_SHORT_CAPTION>All</MSOLAP_NODE_SHORT_CAPTION>
</row>
</root>

String

The String type corresponds to the standard XML string data type.

UnsignedInt

The UnsignedInt data type corresponds to the XML unsignedInt schema type.

EmptyResult

Some XML for Analysis commands are not expected to return a result. For commands that do not return a result, the following namespace on the <root> return element is used:

urn:schemas-microsoft-com:xml-analysis:empty

The root element of an empty result looks like the following:

<root xmlns="urn:scshemas-microsoft-com:xml-analysis:empty"/>

XML for Analysis Rowsets

Information returned in the Result parameter of the Discover method is structured according to the rowset column layouts detailed in this section.

The following sections describe the columns in each rowset. Each section includes a table that provides the following information for each column.

Column heading Contents
Column name The name of the column in the output rowset.
Type A description of the data type for the column. For more information on data types supported by XML for Analysis, see "Data Types Used in XML for Analysis."
Description A brief description of the purpose of the column.
Restriction Indicates whether the column can be used to restrict the returned rowset by inclusion in the Restrictions parameter of the Discover method. Yes means that the column is available to use as a Restrictions item to filter results by this field.
Essbase Mapping Indicates the corresponding Essbase Analytic Server mapping.
Nullable Indicates whether the data must be returned or if a null string is allowed if the column does not apply. Yes means nulls are allowed, and the data is optional. No means that the data is required.
Required Indicates whether the column is required or optional

XML for Analysis Schema Rowsets


Top

CATALOGS Rowset

The CATALOGS rowset identifies the physical attributes associated with catalogs accessible from the DBMS. For some systems, such as Microsoft Access, there may be only one catalog. For Microsoft SQL Server, this rowset would enumerate all catalogs (databases) defined in the system database.

GUID: DBSCHEMA_CATALOGS

Restriction columns: CATALOG_NAME

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Catalog name. Cannot be NULL. Essbase Application name.
DESCRIPTION DBTYPE_WSTR Human-readable description. Essbase Application description.


Top

ACTIONS Rowset

The ACTIONS schema rowset allows OLAP tools to be easily integrated with other applications by providing descriptive and content information to perform the action.

GUID: MDSCHEMA_ACTIONS

Restriction columns: CATALOG_NAME, CUBE_NAME, COORDINATE, COORDINATE_TYPE

Implementation status: We currently return an empty rowset.

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The catalog name. Application name.
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this cube belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube. Database name.
ACTION_NAME DBTYPE_WSTR Optional. The action name. This column value should not be changed and usually is not presented to users. None
ACTION_TYPE DBTYPE_WSTR Optional. One of the enumerated action types. None
COORDINATE DBTYPE_WSTR Required. A string indicating the "coordinate" of the action. Its format is dependent on COORDINATE_TYPE. Generally used as a restriction when querying for the actions available on a particular object. As per specs
COORDINATE_TYPE DBTYPE_UI4 Required. One of the following:
MDACTION_COORDINATE_CUBE
MDACTION_COORDINATE_DIMENSION
MDACTION_COORDINATE_LEVEL
MDACTION_COORDINATE_MEMBER
MDACTION_COORDINATE_SET
MDACTION_COORDINATE_CELL
The format of COORDINATE is determined by this value.
As per specs
CAPTION DBTYPE_WSTR Optional. The friendly name for the action. Will be presented to users and can be localized. None
DESCRIPTION DBTYPE_WSTR Optional. A description of the action. Will be presented to users and can be localized. None
CONTENT DBTYPE_WSTR Optional. The string containing the action. Its meaning is dependent on the action type. None
APPLICATION DBTYPE_WSTR Optional. The application "name." This is a loosely defined field that might be useful in the following circumstances: · Distinguishing proprietary actions · Logically grouping actions for presentation to the user The APPLICATION column should not be interpreted to contain a path name None


Top

CUBES Rowset

The CUBES rowset contains information about the available cubes in a schema (or the catalog, if the provider does not support schemas). It has the structure described in the following table.

GUID: MDSCHEMA_CUBES

Restriction columns: CATALOG_NAME, CUBE_NAME
Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this cube belongs. NULL if the provider does not support catalogs. Application name.
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this cube belongs. NULL if the provider does not support schemas. None.
CUBE_NAME DBTYPE_WSTR Required. Name of the cube. Database name.
CUBE_TYPE DBTYPE_WSTR Required. Cube type. A provider-specific value or one of the following:
"CUBE"
"VIRTUAL CUBE"
"CUBE"
CUBE_GUID DBTYPE_GUID Optional. Cube GUID. NULL if no GUID exists. None
CREATED_ON DBTYPE_DBTIMESTAMP Optional. Date and time of cube creation. NULL if unknown. None
LAST_SCHEMA_UPDATE DBTYPE_DBTIMESTAMP Optional. Date and time of last schema update. NULL if unknown. None
SCHEMA_UPDATED_BY DBTYPE_WSTR Optional. User ID of the person who last updated the schema. NULL if unknown. None
LAST_DATA_UPDATE DBTYPE_DBTIMESTAMP Optional. Date and time of last data update. NULL if unknown. None
DATA_UPDATED_BY DBTYPE_WSTR Optional. User ID of the person who last updated the data. NULL if unknown. None
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the cube. If no description exists, this column is NULL. None
ANNOTATIONS DBTYPE_WSTR Optional. A string containing annotations added by analysts through their client applications. For more information about this feature, see "The ANNOTATIONS Column," later in this chapter. None


Top

DIMENSIONS Rowset

The DIMENSIONS rowset contains information about the dimensions in a given cube. It has one row for each dimension. Because OLE DB for OLAP treats measures as just another dimension, a row is returned in the DIMENSIONS rowset for the Measures dimension.

GUID: MDSCHEMA_DIMENSIONS

Restriction columns: CATALOG_NAME, CUBE_NAME, DIMENSION_NAME, DIMENSION_UNIQUE_NAME

Implementation Status: Implemented but for the following: 1. DIMENSION_CARDINALITY - we always return 1 now. Need support in Essbase C-API, instead of mid tier counting, for good performance. Also since this field is approximation anyway, we want to keep this to 1 till C-API returns approx value.

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this dimension belongs. NULL if the provider does not support catalogs. Application name.
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this dimension belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube to which this dimension belongs. NULL if this dimension does not belong to any cube (which is possible in multicube architectures). For more information, see "Hypercubes and Multicubes" in Chapter 22: OLE DB for OLAP Concepts. Database Name
DIMENSION_NAME DBTYPE_WSTR Required. Name of the dimension. If a dimension is part of more than one cube, there is one row for each cube/dimension combination. The field rgpwszDimensionNames, in the MDAXISINFO structure, corresponds to this element. For more information, see "Hypercubes and Multicubes" in Chapter 22: OLE DB for OLAP Concepts. Dimension name
DIMENSION_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the dimension. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
DIMENSION_GUID DBTYPE_GUID Optional. The GUID of the dimension. NULL if no GUID exists. None
DIMENSION_CAPTION DBTYPE_WSTR Required. A label or a caption associated with the dimension. Used primarily for display purposes. If a caption does not exist, DIMENSION_NAME is returned. Dimension name.
DIMENSION_ORDINAL DBTYPE_UI4 Required. Ordinal number of the dimension, among the group of dimensions that form the cube. Dimension number. First dimension is 1, second is 2 and so on.
DIMENSION_TYPE DBTYPE_I2 Required. The dimension type. Can be one of the following values:
  • MD_DIMTYPE_TIME (1), which indicates a Time dimension.
  • MD_DIMTYPE_MEASURE (2), which indicates a Measures dimension.
  • MD_DIMTYPE_OTHER (3), which indicates the dimension is neither a Time dimension nor a Measures dimension.
  • MD_DIMTYPE_UNKNOWN (4), which indicates that the provider is unable to classify the dimension.
  • TIME maps to Essbase TIME
  • MEASURE maps to Essbase ACCOUNTS
  • OTHER maps to rest of the Essbase types
DIMENSION_CARDINALITY DBTYPE_UI4 Required. The number of members in the dimension. This value can be an approximation of the real cardinality. Consumers should not rely on this to be accurate. TBD: Do we have to count or Is the count already available in the metadata result from Essbase OLAP server.
DEFAULT_HIERARCHY DBTYPE_WSTR Required. Indicates the default hierarchy for this dimension. This is a unique name. Each dimension must have a default hierarchy. Dimension name
DIMENSION_UNIQUE_SETTINGS DBTYPE_I4 If the dimension contains only members with unique names or keys, this column contains a bitmap that specifies which columns contain unique values.
The following bit value constants are defined in Msmd.h for this bitmap:
  1. MDDIMENSIONS_MEMBER_KEY_UNIQUE
  2. MDDIMENSIONS_MEMBER_NAME_UNIQUE
Always MDDIMENSIONS_MEMBER_NAME_UNIQUE (= = 2)
DIMENSION_IS_VISIBLE DBTYPE_BOOL Contains TRUE if the dimension is visible. Always true
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the dimension. NULL if no description exists. None
ANNOTATIONS DBTYPE_WSTR Optional. A string containing annotations added by analysts through their client applications. For more information, see the ANNOTATIONS Column. None


Top

FUNCTIONS Rowset

The FUNCTIONS rowset exposes all functions supported by the MDP, including user-defined functions (UDF) registered on the client.

GUID: MDSCHEMA_FUNCTIONS

Restriction columns: LIBRARY_NAME, INTERFACE_NAME, FUNCTION_NAME, ORIGIN,

Implementation Status: We currently return an empty rowset.

Column name Type indicator Description Essbase Mapping
FUNCTION_NAME DBTYPE_WSTR Required. The name of the function. MDX functions name
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the function. None
PARAM_LIST DBTYPE_WSTR Optional. A comma delimited list of parameters in Visual Basic style, for example: Name as String. None
RETURN_TYPE DBTYPE_I4 Required. VARTYPE of the return data type of the function. TBD - Not defined anywhere in the spec
ORIGIN DBTYPE_I4 Required. Indigenous or user-defined function, such as MSMD_SCHEMA_FUNCTIONS_ORIGIN_MSOLAP, or a UDF such as MSMD_FUNCTIONS_ORIGIN_UDF, which are both supported by MSOLAP. TBD - Spec need to change names to NATIVE and UDF
INTERFACE_NAME DBTYPE_WSTR Required. Name of the interface for UDF and Group name for the MDX functions. TBD
LIBRARY_NAME DBTYPE_WSTR Optional. Name of the type library for UDFs. NULL for MDX functions. None
DLL_NAME DBTYPE_WSTR Optional. Name of the .dll or .exe in which this function is implemented. NULL for MDX functions None
HELP_FILE DBTYPE_WSTR Optional. Name of the help file in which this function is documented. NULL for MDX functions. None
HELP_CONTEXT DBTYPE_I4 Optional. Help context ID for this function in the help file. None
OBJECT DBTYPE_ WSTR Optional. Object, such as dimension or level, to which function applies. For example, Products.count uses the dimension Products, with "count" as the function. None


Top

HIERARCHIES Rowset

The HIERARCHIES rowset contains information about the hierarchies available in a dimension. If the dimension does not contain a hierarchy or has just one hierarchy, the HIERARCHY_NAME column will contain a NULL and both the HIERARCHY_UNIQUE_NAME and HIERARCHY_CAPTION columns will contain the name of the dimension.

GUID: MDSCHEMA_HIERARCHIES

Restriction columns: CATALOG_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_NAME, HIERARCHY_UNIQUE_NAME

Implementation Status: Implemented but for the following: 1. HIERARCHY_CARDINALITY - we always return 1 now. Need support in Essbase C-API, instead of mid tier counting, for good performance. Also since this field is approximation anyway, we want to keep this to 1 till C-API returns approx value.

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this hierarchy belongs. NULL if the provider does not support catalogs. Application name.
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this hierarchy belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube to which this hierarchy belongs. Database name
DIMENSION_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the dimension to which this hierarchy belongs. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
HIERARCHY_NAME DBTYE_WSTR Required. Name of the hierarchy. If the dimension does not contain a hierarchy or has just one hierarchy, this column contains a NULL. Dimension name
HIERARCHY_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the hierarchy. This must be an unambiguous name. If the dimension does not contain a hierarchy or has just one hierarchy, this column will contain the name of the dimension. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
HIERARCHY_GUID DBTYPE_GUID Optional. Hierarchy GUID. NULL if there is no GUID. None
HIERARCHY_CAPTION DBTYPE_WSTR Required. A label or a caption associated with the hierarchy. Used primarily for display purposes. If a caption does not exist, HIERARCHY_NAME is returned. If the dimension does not contain a hierarchy or has just one hierarchy, this column will contain the name of the dimension. Dimension name
DIMENSION_TYPE DBTYPE_I2 Required. The type of the dimension to which this hierarchy belongs.
Can be one of the following values:
  • MD_DIMTYPE_TIME, which indicates a Time dimension.
  • MD_DIMTYPE_MEASURE, which indicates a Measures dimension.
  • MD_DIMTYPE_OTHER, which indicates that the dimension is neither a Time dimension nor a Measures dimension.
  • MD_DIMTYPE_UNKNOWN, which indicates that the provider is unable to classify the dimension.
TIME maps to Essbase TIME
MEASURE maps to Essbase ACCOUNTS
OTHER maps to rest of the Essbase types
HIERARCHY_CARDINALITY DBTYPE_UI4 Required. The number of members in the hierarchy. Because of the presence of multiple hierarchies, this number might not be the same as DIMENSION_CARDINALITY. This value can be an approximation of the real cardinality. Consumers should not assume that this value is accurate. TBD: Do we have to count number of members in a Dimension or Is the count already available in the metadata result from Essbase OLAP server.
DEFAULT_MEMBER DBTYPE_WSTR Optional. The default member for this hierarchy. This is a unique name. Every hierarchy must have a default member. Dimension name.
ALL_MEMBER DBTYPE_WSTR Optional. The member at the highest level of rollup in the hierarchy. This can be different from DEFAULT_MEMBER. Dimension name.
HIERARCHY_UNIQUE_SETTINGS DBTYPE_I4 A bitmap that specifies which columns contain unique values, if the hierarchy only has members with unique names or keys.
The following bit value constants are defined in Msmd.h for this bitmap:
  • MDDIMENSIONS_MEMBER_KEY_UNIQUE
  • MDDIMENSIONS_MEMBER_NAME_UNIQUE
Always MDDIMENSIONS_MEMBER_NAME_UNIQUE (= = 2)
HIERARCHY_IS_VISIBLE DBTYPE_BOOL Returns TRUE if dimension is visible. Always true.
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the hierarchy. NULL if no description exists. None
ANNOTATIONS DBTYPE_WSTR Optional. A string containing annotations added by analysts through their client applications. For more information about this feature, see "The ANNOTATIONS Column," later in this chapter. None
STRUCTURE DBTYPE_I2 The type of hierarchy. Can be one of the following values:
  • MD_STRUCTURE_FULLYBALANCED
  • MD_STRUCTURE_RAGGEDBALANCED
  • MD_STRUCTURE_UNBALANCED
  • MD_STRUCTURE_NETWORK
None.
(Optional for XMLA provider)


Top

MEASURES Rowset

The MEASURES rowset contains information about the available measures.

GUID: MDSCHEMA_MEASURES

Restriction columns: CATALOG_NAME, CUBE_NAME, MEASURE_NAME, MEASURE_UNIQUE_NAME

Implementation Status: Implemented but for the following: 1. Following fields needs clarification and currently we return null value: NUMERIC_PRECISION, NUMERIC_SCALE, MEASURE_UNITS.

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this measure belongs. NULL if the provider does not support catalogs. Application name.
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this measure belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube to which this measure belongs. Database name
MEASURE_NAME DBTYPE_WSTR Required. Name of the measure. Member names in the Accounts dimension
MEASURE_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the measure. For providers that generate unique names by qualification, each component of this name is delimited. Above member name
MEASURE_CAPTION DBTYPE_WSTR Required. A label or caption associated with the measure. Used primarily for display purposes. If a caption does not exist, MEASURE_NAME is returned. Above member name (Shouldn't it be Optional?)
MEASURE_GUID DBTYPE_GUID Optional. Measure GUID. NULL if the measure does not have a GUID. None
MEASURE_AGGREGATOR DBTYPE_I4 Required. How a measure was derived. Can be one of the following values:
MDMEASURE_AGGR_SUM
MDMEASURE_AGGR_COUNT
MDMEASURE_AGGR_MIN
MDMEASURE_AGGR_MAX
MDMEASURE_AGGR_AVG
MDMEASURE_AGGR_VAR
MDMEASURE_AGGR_STD: The aggregation function was SUM, COUNT, MIN, MAX, AVG, VAR, or STDEV, respectively.
MDMEASURE_AGGR_CALCULATED: The measure was derived from a formula that was not any single function above.
MDMEASURE_AGGR_UNKNOWN: The aggregation function or formula used to derive this measure was unknown.
MDMEASURE_AGGR_STD - 7
DATA_TYPE DBTYPE_UI2 Required. Data type of the measure. Can be any of the types listed in Appendix A: Data Types. Always NUMERIC (DBTYPE_R8 - 5)
NUMERIC_PRECISION DBTYPE_UI2 Required. If the measure object's data type is exact numeric, this is the maximum precision of the property. NULL for all other property types. Always NULL (SC to confirm)
NUMERIC_SCALE DBTYPE_I2 Required. If the measure object's type indicator is DBTYPE_NUMERIC or DBTYPE_DECIMAL, this is the number of digits to the right of the decimal point. Otherwise, this value is NULL. None.
MEASURE_UNITS DBTYPE_WSTR Required. The unit of measurement. Examples are "dollars", "boxes sold", "tons shipped", "pounds", and "millions". Typically, a consumer appends this to the display caption of the measure. If a unit is not available, the provider returns an empty string. None.
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the measure. NULL if no description exists. None
MEASURE_IS_VISIBLE DBTYPE_BOOL Returns TRUE if dimension is visible. Always true.


Top

MEMBERS Rowset

The MEMBERS rowset contains information about the available members.

GUID: MDSCHEMA_MEMBERS

Restriction columns: CATALOG_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_NAME, MEMBER_UNIQUE_NAME.

Implementation Status: Implemented but for the following: 1. PARENT_UNIQUE_NAME is returned as NULL, as query members by name does not return this information.

For the MEMBERS rowset columns, a column exists for each property of the member. The type indicator depends on the PROPERTIES rowset from which the structure of these columns can be evaluated. All other schema rowsets are usually orders of magnitude smaller than the MEMBERS rowset. The MEMBERS rowset has hundreds of thousands of rows, and there is seldom any need for all the members; therefore, applications should not attempt to cache the MEMBERS schema rowset. The common need is for a restricted subset of the members under certain conditions, specifically, when rendering an axis of a dataset.

The axis rowsets are designed to contain most of the information that an application might need. That is, most of the columns in an axis rowset are the same as the columns in the MEMBERS rowset. If the application needs additional information the application can use the DIMENSION PROPERTIES clause in the MDX statement that created the dataset. This is the case when additional columns from the MEMBERS rowset are required for those members on the axis. This puts these additional columns on the axis rowset. For more information about axis rowsets, refer to Axis Rowsets.

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this member belongs. NULL if the provider does not support catalogs. Application name
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this member belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube to which this member belongs. Database name
DIMENSION_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the dimension to which this member belongs. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
HIERARCHY_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the hierarchy. If the member belongs to more than one hierarchy, there is one row for each hierarchy to which it belongs. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
LEVEL_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the level to which the member belongs. For providers that generate unique names by qualification, each component of this name is delimited. (Optional for XMLA provider) String value of Essbase Level number (the distance from the bottom of the hierarchy).
LEVEL_NUMBER DBTYPE_UI4 Required. The distance of the member from the root of the hierarchy. The root level is zero. (Optional for XMLA provider) Essbase level number (the distance from the bottom of the hierarchy and not from the root as defined in XMLA specs)
MEMBER_ORDINAL DBTYPE_UI4 Required. Ordinal number of the member. Sort rank of the member when members of this dimension are sorted in their natural sort order. If providers do not have the concept of natural ordering, this should be the rank when sorted by MEMBER_NAME. Member number. Check all MDX functions that depend on MEMBER_ORDINAL and member ordering.
MEMBER_NAME DBTYPE_WSTR Required. Name of the member. Member name
MEMBER_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the member. For providers that generate unique names by qualification, each component of this name is delimited. For regular members we use the member name itself.
For shared member we prefix member name with parent name (for example, Diet.100-20)
For attribute dimension, we use the long name.
MEMBER_TYPE DBTYPE_I4 Required. Type of the member. Can be one of the following values:
  • MDMEMBER_TYPE_REGULAR
  • MDMEMBER_TYPE_ALL
  • MDMEMBER_TYPE_FORMULA
  • MDMEMBER_TYPE_MEASURE
  • MDMEMBER_TYPE_UNKNOWN
MDMEMBER_TYPE_FORMULA takes precedence over MDMEMBER_TYPE_MEASURE. Therefore, if there is a formula (calculated) member on the Measures dimension, it is listed as MDMEMBER_TYPE_FORMULA.
We return MDMEMBER_TYPE_REGULAR which is 1.
MEMBER_GUID DBTYPE_GUID Optional. Member GUID. NULL if no GUID exists. None
MEMBER_CAPTION DBTYPE_WSTR Required. A label or caption associated with the member. Used primarily for display purposes. If a caption does not exist, MEMBER_NAME is returned. Member name
CHILDREN_CARDINALITY DBTYPE_UI4 Required. Number of children that the member has. This can be an estimate, so consumers should not rely on this to be the exact count. Providers should return the best estimate possible. Child count of a member.
PARENT_LEVEL DBTYPE_UI4 Required. The distance of the member's parent from the root level of the hierarchy. The root level is zero. Essbase Level number of current member + 1
PARENT_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the member's parent. NULL is returned for any members at the root level. For providers that generate unique names by qualification, each component of this name is delimited. Parent member name
PARENT_COUNT DBTYPE_UI4 Required. Number of parents that this member has. For more information, refer to Complex Member Relationships. Always 1
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the member. None
TREE_OP DBTYPE_UI4 MDTREEOP_CHILDREN (1),
MDTREEOP_SIBLINGS (2),
MDTREEOP_PARENT (4),
MDTREEOP_SELF (8),
MDTREEOP_DESCENDANTS (16),
MDTREEOP_ANCESTORS (32)
TREE_OP options are supported in combination with member name.


Top

PROPERTIES Rowset

The PROPERTIES rowset contains information about the available properties for each level of the dimension, assuming that each level defines a class of members. The properties of all members in this class are the same.

For a data store that does not support named levels, a dummy level includes all members in the dimension. The name of this level is the same as the name of the dimension.

The PROPERTIES rowset also contains all available properties for cells in a dataset. Providers are required to have at least three properties (the VALUE, FORMATTED_VALUE, and CELL_ORDINAL properties of a cell); therefore, the PROPERTIES rowset always has at least three rows. Consumers can distinguish between member properties and cell properties by looking at the PROPERTY_TYPE column.

A property can be both a member property and a cell property. By restricting the PROPERTY_TYPE field of this rowset, consumers can retrieve only the member property, only the cell property, or both. The default sort order is PROPERTY_TYPE, CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, and LEVEL_UNIQUE_NAME.

GUID: MDSCHEMA_PROPERTIES

Implementation Status: Currently we return no rows for this command. Required properties do not have to be listed here, and when we add optional properties in the future this command will return appropriate rows.

Column name Type indicator Description Essbase Mapping
PROPERTY_TYPE DBTYPE_I2 Required. The following bitmasks are used to describe the property type:
  • MDPROP_MEMBER. Property of a member. Can be used in the DIMENSION PROPERTIES clause of the SELECT statement.
  • MDPROP_CELL. Property of a cell. Can be used in the CELL PROPERTIES clause that occurs at the end of the SELECT statement.
Always MDPROP_MEMBER
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this property belongs. NULL if the provider does not support catalogs. Application name.
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this property belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube to which this property belongs. Database name
DIMENSION_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the dimension. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
HIERARCHY_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the hierarchy. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
LEVEL_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the level to which this property belongs. If the provider does not support named levels, it should return the DIMENSION_UNIQUE_NAME value for this field. For providers that generate unique names by qualification, each component of this name is delimited. None. Optional for XMLA Providers
MEMBER_UNIQUE_NAME DBTYPE_WSTR Required. Used for data stores that do not support named levels or have properties on a member-by-member basis. Contains the unique name of the member to which the property belongs. If the property applies to all members in a level, this column is NULL. For providers that generate unique names by qualification, each component of this name is delimited. TBD
PROPERTY_NAME DBTYPE_WSTR Required. Name of the property. For attribute dimension, the name of the dimension is the name of the property For normal dimension the properties include generation number, member number etc
PROPERTY_CAPTION DBTYPE_WSTR Required. A label or caption associated with the property. Used primarily for display purposes. If a caption does not exist, PROPERTY_NAME is returned. None (Should be optional)
DATA_TYPE DBTYPE_UI2 Required. Data type of the property. Can be any of the types listed in Appendix A: Data Types. TBD
CHARACTER_MAXIMUM_LENGTH DBTYPE_UI4 Required. The maximum possible length of a value in this property. For character, binary, and bit properties, it is one of the following:
  • The maximum length of the property in characters, bytes, or bits, respectively, if one is defined.
  • Zero (0) if there is no defined maximum length.
  • NULL for all other properties.
TBD
CHARACTER_OCTET_LENGTH DBTYPE_UI4 Required. Maximum length in octets (bytes) of the property, if the type of the property is either character or binary. A value of 0 means that the property has no defined maximum length. NULL for all other property types. TBD
NUMERIC_PRECISION DBTYPE_UI2 Required. If the property object's data is numeric, this is the maximum precision of the property. NULL for all other property types. TBD
NUMERIC_SCALE DBTYPE_I2 Required. If the property object's type indicator is DBTYPE_NUMERIC or DBTYPE_DECIMAL, this is the number of digits to the right of the decimal point. Otherwise, this value is NULL. SC to get back
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the property. NULL if no description exists. None


Top

SETS Rowset

The SETS rowset contains information about the sets in a schema (or the catalog, if the provider does not support schemas).

GUID: MDSCHEMA_SETS

Implementation status: We currently return an empty rowset.

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Name of the catalog to which the set belongs. NULL if the provider does not support catalogs. None (SETS Rowset should be optional)
SCHEMA_NAME DBTYPE_WSTR The name of the schema to which the dimension belongs. NULL if the provider does not support schemas. None (SETS Rowset should be optional)
CUBE_NAME DBTYPE_WSTR Name of the cube to which the set belongs. Cannot be NULL. None (SETS Rowset should be optional)
SET_NAME DBTYPE_WSTR The name of the set, as specified in the CREATE SET statement. None (SETS Rowset should be optional)
SCOPE DBTYPE_I4 The scope of the set. One of the following:
  • MDSET_SCOPE_GLOBAL
  • MDSET_SCOPE_SESSION
None (SETS Rowset should be optional)
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the set. None (SETS Rowset should be optional)


Top

LEVELS Rowset

The LEVELS rowset contains information about the levels available in a dimension.

GUID: MDSCHEMA_LEVELS

Column name Type indicator Description Essbase Mapping
CATALOG_NAME DBTYPE_WSTR Optional. The name of the catalog to which this level belongs. NULL if the provider does not support catalogs. Application name
SCHEMA_NAME DBTYPE_WSTR Optional. The name of the schema to which this level belongs. NULL if the provider does not support schemas. None
CUBE_NAME DBTYPE_WSTR Required. Name of the cube to which this level belongs. Database name
DIMENSION_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the dimension to which this level belongs. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
HIERARCHY_UNIQUE_NAME DBTYE_WSTR Required. Unique name of the hierarchy. If the level belongs to more than one hierarchy, there is one row for each hierarchy to which it belongs. For providers that generate unique names by qualification, each component of this name is delimited. Dimension name
LEVEL_NAME DBTYPE_WSTR Required. Name of the level. Level name
LEVEL_UNIQUE_NAME DBTYPE_WSTR Required. Unique name of the level. For providers that generate unique names by qualification, each component of this name is delimited. Level name
LEVEL_GUID DBTYPE_GUID Optional. Level GUID. NULL if the level does not have a GUID. None.
LEVEL_CAPTION DBTYPE_WSTR Required. A label or caption associated with the hierarchy. Used primarily for display purposes. If a caption does not exist, LEVEL_NAME is returned. Level name.
LEVEL_NUMBER DBTYPE_UI4 Required. The distance of the level from the root of the hierarchy. The root level is zero. Level number. Counted from leaf.
LEVEL_CARDINALITY DBTYPE_UI4 Required. The number of members in a level. This value can be an approximation of the real cardinality. Consumers should not assume that this value is accurate. 1 for now.
LEVEL_TYPE DBTYPE_I4 Required. The following bitmasks are used to specify the level characteristics:
  • MDLEVEL_TYPE_REGULAR
  • MDLEVEL_TYPE_ALL
  • MDLEVEL_TYPE_CALCULATED
  • MDLEVEL_TYPE_TIME
  • MDLEVEL_TYPE_TIME_YEARS
  • MDLEVEL_TYPE_TIME_HALF_YEAR
  • MDLEVEL_TYPE_TIME_QUARTERS
  • MDLEVEL_TYPE_TIME_MONTHS
  • MDLEVEL_TYPE_TIME_WEEKS
  • MDLEVEL_TYPE_TIME_DAYS
  • MDLEVEL_TYPE_TIME_HOURS
  • MDLEVEL_TYPE_TIME_MINUTES
  • MDLEVEL_TYPE_TIME_SECONDS
  • MDLEVEL_TYPE_TIME_UNDEFINED
  • MDLEVEL_TYPE_UNKNOWN
MDLEVEL_TYPE_REGULAR

The LEVEL_TYPE column exists mainly as a means to transmit formatting instructions to an application. For example, a level with a type of LEVEL_TYPE_TIME_DAYS might indicate to an application that the members of the level should be expressed in text format (that is, "Sunday", "Monday", "Tuesday", and so on). LEVEL_TYPE_REGULAR can be used to signify a level that does not require any special formatting.
DESCRIPTION DBTYPE_WSTR Optional. A human-readable description of the level. NULL if no description exists. None.


Top

SCHEMA Rowset

GUID: DISCOVER_SCHEMA_ROWSETS

Restriction columns: SchemaName

Implementation status: Fully implemented.

Information about following are returned: MDSCHEMA_CUBES, MDSCHEMA_DIMENSIONS, MDSCHEMA_HIERARCHIES, MDSCHEMA_LEVELS, MDSCHEMA_MEASURES, MDSCHEMA_PROPERTIES, MDSCHEMA_MEMBERS, MDSCHEMA_FUNCTIONS, MDSCHEMA_ACTIONS, MDSCHEMA_SETS, DISCOVER_DATASOURCES, DISCOVER_SCHEMA_ROWSETS, DISCOVER_ENUMERATORS, DISCOVER_KEYWORDS, and DISCOVER_LITERALS.

SchemaName can be specified as a restriction and if there is no match no rows will be returned.

Column name Type indicator Description Essbase Mapping
SchemaName DBTYPE_WSTR - As per xmla spec.
SchemaGuid DBTYPE_WSTR
Restriction DBTYPE_WSTR
Description DBTYPE_WSTR


Top

Essbase XMLA Sample Queries

DISCOVER_DATASOURCES

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>DISCOVER_DATASOURCES
   </RequestType> 
   <Restrictions>
      <RestrictionList>
      </RestrictionList>
   </Restrictions> 
   <Properties>
      <PropertyList>
         <Format>Tabular
         </Format>
      </PropertyList>
   </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

DISCOVER_PROPERTIES

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>DISCOVER_PROPERTIES
   </RequestType> 
   <Restrictions>
      <RestrictionList>
      </RestrictionList>
   </Restrictions> 
   <Properties>
      <PropertyList>
         <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
         </DataSourceInfo>
         <Format>Tabular
         </Format>
      </PropertyList>
   </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

DBSCHEMA_CATALOGS

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>DBSCHEMA_CATALOGS
   </RequestType> 
   <Restrictions>
      <RestrictionList>
      </RestrictionList>
   </Restrictions> 
   <Properties>
      <PropertyList>
         <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
         </DataSourceInfo>
         <Format>Tabular
         </Format>
      </PropertyList>
   </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

MDSCHEMA_CUBES

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>MDSCHEMA_CUBES
   </RequestType> 
   <Restrictions>
      <RestrictionList>
         <CATALOG_NAME>Sample
         </CATALOG_NAME>
      </RestrictionList>
   </Restrictions> 
   <Properties>
      <PropertyList>
         <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
         </DataSourceInfo>
         <Format>Tabular
         </Format>
         <Catalog>Sample
         </Catalog>
      </PropertyList>
   </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope> 

MDSCHEMA_DIMENSIONS

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>MDSCHEMA_DIMENSIONS
   </RequestType> 
   <Restrictions>
      <RestrictionList>
         <CATALOG_NAME>Sample
         </CATALOG_NAME>
         <CUBE_NAME>Basic
         </CUBE_NAME>
      </RestrictionList>
   </Restrictions> 
   <Properties>
      <PropertyList>
         <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
         </DataSourceInfo>
         <Format>Tabular
         </Format>
      </PropertyList>
   </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

MDSCHEMA_HIERARCHIES

<SOAP-ENV:Envelope
   xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema"
   xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/"
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <RequestType>MDSCHEMA_HIERARCHIES
   </RequestType>
   <Restrictions>
      <RestrictionList>
         <CATALOG_NAME>Sample
         </CATALOG_NAME>
         <CUBE_NAME>Basic
         </CUBE_NAME>
      </RestrictionList>
   </Restrictions>
   <Properties>
      <PropertyList>
         <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
         </DataSourceInfo>
         <Catalog>Sample
         </Catalog>
         <Format>Tabular
         </Format>
         <Content>SchemaData
         </Content>
      </PropertyList>
   </Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

MDSCHEMA_MEMBERS

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>MDSCHEMA_MEMBERS
   </RequestType> 
   <Restrictions>
      <RestrictionList>
         <CATALOG_NAME>Sample
         </CATALOG_NAME>
         <CUBE_NAME>Basic
         </CUBE_NAME>
      </RestrictionList>
   </Restrictions> 
      <Properties>
         <PropertyList>
            <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
            </DataSourceInfo>
            <Format>Tabular
            </Format>
         </PropertyList>
      </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

MDSCHEMA_LEVELS

<SOAP-ENV:Envelope
   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
<SOAP-ENV:Body> 
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
   <RequestType>MDSCHEMA_LEVELS
   </RequestType> 
   <Restrictions>
      <RestrictionList>
         <CATALOG_NAME>Sample
         </CATALOG_NAME>
         <CUBE_NAME>Basic
         </CUBE_NAME>
         </RestrictionList>
      </Restrictions> 
   <Properties>
      <PropertyList>
         <DataSourceInfo>Provider=Essbase;Data Source=btmachine2
         </DataSourceInfo>
         <Format>Tabular
         </Format>
      </PropertyList>
   </Properties>
</Discover> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

©2001-2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com