Hyperion XML for Analysis API Reference |
This document covers the following topics:
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.
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:
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.
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.
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.
For example, copy essbase.properties to D:\bea\wlserver6.1.
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
<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>
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.
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)
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>
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>
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.
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>
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.
Element | Description |
UName | MEMBER_UNIQUE_NAME property from OLE DB axis rowset |
Caption | MEMBER_CAPTION property from OLE DB axis rowset |
LName | LEVEL_UNIQUE_NAME property from OLE DB axis rowset |
LNum | LEVEL_NUMBER property from OLE DB axis rowset |
The following are the standard elements for the
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:
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):
If the element is present but without a value, this implies an empty string result (""), as shown in the following example:
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:
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.
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.
The following line represents the set of four tuples
as collection of two-dimensional tuples:
The following line represents the set of four tuples
as a Cartesian product of two one-dimensional sets:
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.
Based on the previous example, these operations translate the two-dimensional tuples and Cartesian product of one-dimensional sets as follows:
Two-dimensional tuples
Cartesian product of one-dimensional sets
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):
As shown above, the same set can have different representations using different operations. The client can request a specific representation using the AxisFormat property.
In TupleFormat, an axis is represented as a set of tuples. The following operations must be used in the specified order:
In addition, <Member> elements must have the Hierarchy attribute
that specifies the hierarchy name of the member.
The following example illustrates the TupleFormat.
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:
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:
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.
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.
The above theoretical formulation can be represented in XML as follows:
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.
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 :
The CellInfo section of OlapInfo contains the same sequence of cell properties:
However, the CellData section eliminates the duplication,returning the data only once:
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.
The complete XML output for the above dataset is shown in Appendix D.
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:
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.
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:
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:
When needed, a column can be restricted with multiple values.
Each value is represented in a <Value> element. An example follows:
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.
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."
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:
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:
-Or-
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:
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>.
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:
The root element of an empty result looks like the following:
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.
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
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.
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
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.
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.
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.
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.
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.
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.
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.
The LEVELS rowset contains information about the levels available in a dimension.
GUID: MDSCHEMA_LEVELS
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.
Element Description Value VALUE property from OLE DB cell properties FmtValue FORMATTED_VALUE property from OLE DB cell properties ForeColor FORE_COLOR property from OLE DB cell properties BackColor BACK_COLOR property from OLE DB cell properties
Using Defaults in CellInfo and AxisInfo
<Store_x0020_SQFT name="Store SQFT">
<Default>5000</Default>
</Store_x0020_SQFT>
<Member Hierarchy="Store">
<UName>[Store].[CA]</UName>
<Caption>CA</Caption>
<LName>[Store].[State]</LName>
<LNum>2</LNum>
</Member>
<Store_x0020_SQFT />
<Store_x0020_SQFT xsi:nil='true' />
Axes
Axis Formats
1999 1999 2000 2000 Actual Budget Actual Budget
{ ( 1999, Actual ), ( 1999, Budget ), ( 2000, Actual ), ( 2000, Budget ) }
{ 1999, 2000 } x { Actual, Budget }
Operation Description Member The smallest unit of an axis representing the member of a dimension hierarchy Tuple A vector of members from different dimension hierarchies Members A set of Member objects from the same dimension hierarchy Tuples A collection of Tuple objects with the same dimensionality Union A union of sets CrossProduct A Cartesian product of sets
Tuples (
Tuple( Member(1999), Member(Actual) ),
Tuple( Member(1999), Member(Budget) ),
Tuple( Member(2000), Member(Actual) ),
Tuple( Member(2000), Member(Budget) )
)
CrossProduct (
Members (Member(1999), Member(2000) ),
Members (Member(Actual), Member(Budget) )
)
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 ... ]
TupleFormat
<Axis>
<Tuples>
<Tuple>
<Member Hierarchy="name">
1999 1999 2000 Actual Budget Budget
<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
<Axis>
<CrossProduct Size="size">
<Members Hierarchy="name">
<Member>
1999 1999 2000 2000 2001 Actual Budget Actual Budget Budget cluster 1 cluster 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
WA WA CA CA Umbrella Umbrella Sunglasses Sunglasses Actual Budget Actual Budget
CrossProduct(
Tuples (
Tuple ( Member(WA), Member(Umbrella) ),
Tuple ( Member(CA), Member(Sunglasses) ) ),
Members ( Member(Actual), Member(Budget) )
)
<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
<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>
Select from sales cell properties Value, FormattedValue, Value
<CellInfo>
<CellOrdinal/>
<Value/>
<FmtValue/>
<Value/>
</CellInfo>
<Cell CellOrdinal="0">
<Value xsi:type="xsd:int">10</Value>
<FmtValue>$10.00</FmtValue>
</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
Command Data Type
<Statement>
SELECT Measures.MEMBERS on columns from Sales
</Statement>
Properties
<PropertyList>
<DataSourceInfo>
Provider=MSOLAP;Data Source=local;
</DataSourceInfo>
<Catalog>
Foodmart 2000
</Catalog>
<Format>
Multidimensional
</Format>
</PropertyList>
Restrictions
<RestrictionList>
<CATALOG_NAME>
FoodMart 2000
</CATALOG_NAME>
...
</RestrictionList>
<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>
Symbolic constant (OLEDB) Integer value Description MDTREEOP_CHILDREN 1 Returns only the immediate children. MDTREEOP_SIBLINGS 2 Returns members on the same level. MDTREEOP_PARENT 4 Returns only the immediate parent. MDTREEOP_SELF 8 Returns the immediate member in the list of returned rows. MDTREEOP_DESCENDANTS 16 Returns all descendants. MDTREEOP_ANCESTORS 32 Returns all ancestors.
Resultset
Rowset
<xsd:element name="Order_x0020_Details" type="string" sql:field="Order Details" />
<row>
</row>
<row>
<Store_name xsi:nil='true'/>
</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>
<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>
urn:schemas-microsoft-com:xml-analysis:empty
<root xmlns="urn:scshemas-microsoft-com:xml-analysis:empty"/>
XML for Analysis Rowsets
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
CATALOGS Rowset
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.
ACTIONS 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
CUBES Rowset
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
DIMENSIONS Rowset
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:
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:
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
FUNCTIONS 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
HIERARCHIES Rowset
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:
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:
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:
None.
(Optional for XMLA provider)
MEASURES Rowset
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.
MEMBERS Rowset
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_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.
PROPERTIES Rowset
Column name
Type indicator
Description
Essbase Mapping
PROPERTY_TYPE
DBTYPE_I2
Required. The following bitmasks are used to describe the property type:
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:
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
SETS 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:
None (SETS Rowset should be optional)
DESCRIPTION
DBTYPE_WSTR
Optional. A human-readable description of the set.
None (SETS Rowset should be optional)
LEVELS Rowset
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
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.
SCHEMA Rowset
Column name
Type indicator
Description
Essbase Mapping
SchemaName
DBTYPE_WSTR
-
As per xmla spec.
SchemaGuid
DBTYPE_WSTR
Restriction
DBTYPE_WSTR
Description
DBTYPE_WSTR
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