XML for Analysis (XMLA) is an open industry-standard Web service interface designed for online analytical processing. XMLA is a set of XML Message Interfaces built on the open standards of HTTP, XML, and Simple Object Access Protocol (SOAP). XMLA, which is not bound to any language, platform, or operating system, provides standardized data access between client applications and any multidimensional data source on the Web.
For more information on XMLA, visit www.xmla.org.
Key XMLA features:
Support for flattened rowsets
Support for stateful sessions
Backward XMLA level representation (level 1 is the top level)
User authentication through basic HTTP authentication
XMLA High-Availability functionality through Provider Services
XMLA administration and monitoring through Administration Services
Note: | XMLA is available for use with Essbase only. |
The following methods provide a standard way for XML applications to access basic information from the server. Because these methods are invoked using SOAP, 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.
The simplified interface model has two methods.
Discover
Execute
Discover obtains information and metadata from a Web Service. This information can include a list of available data sources and data about a data source provider. Properties define and shape the data obtained. Discover allows you to specify the types of information that the client application needs. The use of generic interface and properties enables extensibility without necessitating rewriting existing functions.
Execute executes Multidimensional Expressions (MDX) or other provider-specific commands against an XMLA data source. The following diagram illustrates a possible implementation of an n-tiered application.
Provided with the URL for a server hosting a Web Service, the client uses SOAP and HTTP protocols to send Discover and Execute calls to the server. The server instantiates the XMLA provider, which handles the calls. The XMLA provider fetches the data, packages it into XML, and sends the data to the client.
The Discover and Execute methods enable users to determine what can be queried on a server and, based on this, submit commands to be executed.
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 retrieves information, such as the list of data sources on a server or details about a 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 comprises a RequestType enumeration value, which determines the type of information to be returned. The RequestType enumeration is used by the Discover method to determine the structure and content of the rowset returned in the Result parameter. The Restrictions parameter format and XML result set are also dependent on the value specified in this parameter. This enumeration can be extended to support provider-specific enumeration strings.
Each RequestType enumeration value corresponds to a return rowset. For rowset definitions, see XMLA Rowsets. Support is required for the following explicitly named RequestType enumeration values.
Enumeration value | Description |
---|---|
DISCOVER_DATASOURCES | Returns a list of XMLA data sources available on the server or Web Service. (For an example of how these may be published, see "XMLA Implementation Walkthrough" in the XML for Analysis Specification, available on the Hyperion Developer Network.) |
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 RequestType 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 the provider of a specific data source. |
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 also may 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. Result columns are defined by the rowset specified in the RequestType parameter. Some columns of Result can filter the rows returned. For these columns and those that can be restricted, see the rowset tables in XMLA Rowsets. To obtain the restriction information for provider-specific schema rowsets, use the DISCOVER_SCHEMA_ROWSETS request type. This parameter can be empty, but it must be included.
Properties [in]
This parameter, of the Properties data type, comprises a collection of XMLA properties. Each property enables users to control some aspect of the Discover method, such as specifying the return format of the result set, the timeout, or the locale in which the data should be formatted.
You can obtain the available properties by using the DISCOVER_PROPERTIES request type with the Discover method.
The properties in the Properties parameter have no required order. This parameter can be empty, but it must be included.
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 in the RequestType and Restrictions parameters. The column layout of the returned result set also is determined by the value specified in RequestType. For information about the rowset layouts that correspond to for each RequestType value, see XMLA Rowsets.
Example
In the following sample, the client sends the XML Discover call to request a list of cubes from the Demo catalog:
<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>Demo</CATALOG_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
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> <m:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="CUBE_TYPE" type="xsd:string" sql:field="CUBE_TYPE"/> <xsd:element name="LAST_SCHEMA_UPDATE" type="xsd:dateTime" sql:field="LAST_SCHEMA_UPDATE" minOccurs="0"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Demo</CATALOG_NAME> <CUBE_NAME>Demo.Basic</CUBE_NAME> <CUBE_TYPE>CUBE</CUBE_TYPE> </row> </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The Execute method sends action requests, including those involving data transfer, such as retrieving or updating data on the server, to 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 an MDX statement to be executed.
Properties [in]
This parameter is of the Properties data type and consists of a collection of XMLA 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.
The properties in the Properties parameter have no required order. This parameter can be empty, but it must be included.
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 XMLA 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 also can 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 MDX SELECT statement:
<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> <Execute xmlns="urn:schemas-microsoft-com:xml-analysis" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <Command> <Statement> SELECT CrossJoin([Measures].CHILDREN , [Market].CHILDREN) on columns, [Product].Members on rows from Sample.Basic </Statement> </Command> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Catalog>Sample</Catalog> <Format>Multidimensional</Format> <AxisFormat>TupleFormat</AxisFormat> <Content>SchemaData</Content> </PropertyList></Properties> </Execute> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
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>
Information returned in the Result parameter of the Discover method is structured according to the rowset column layouts detailed in this section.
The CATALOGS rowset identifies the physical attributes associated with catalogs accessible from Analytic Services.
GUID: DBSCHEMA_CATALOGS
the section called “Flattened Rowset Examples” describes the rowset structure.
Table 2. CATALOGS Rowset Structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
DESCRIPTION | Always null |
Request Example
<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=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example (truncated)
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Demo</CATALOG_NAME> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The CUBES rowset contains information about the available cubes in a schema (or the catalog, if the provider does not support schemas).
GUID: MDSCHEMA_CUBES
Table 3 describes the rowset structure.
Table 3. MDSCHEMA_CUBES Rowset structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
CUBE_TYPE | “CUBE” |
LAST_SCHEMA_UPDATE | Time stamp of last outline update |
DESCRIPTION | Database description |
Request Example
<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>Demo</CATALOG_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="CUBE_TYPE" type="xsd:string" sql:field="CUBE_TYPE"/> <xsd:element name="LAST_SCHEMA_UPDATE" type="xsd:dateTime" sql:field="LAST_SCHEMA_UPDATE" minOccurs="0"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Demo</CATALOG_NAME> <CUBE_NAME>Demo.Basic</CUBE_NAME> <CUBE_TYPE>CUBE</CUBE_TYPE> </row> </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The DIMENSIONS rowset contains information about the dimensions in a given cube. Each dimension has one row.
GUID: MDSCHEMA_DIMENSIONS
Table 4 describes the rowset structure.
Table 4. MDSCHEMA_DIMENSIONS Rowset structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
DIMENSION_NAME | Dimension name |
DIMENSION_UNIQUE_NAME | Dimension name |
DIMENSION_CAPTION | Dimension name |
DIMENSION_ORDINAL | Dimension number. First dimension is 1, second is 2, and so on |
DIMENSION_TYPE | If Essbase dimension type is:
|
DIMENSION_CARDINALITY | Number of members in the dimension |
DEFAULT_HIERARCHY | Dimension name |
DESCRIPTION | Comment added for the dimension |
DIMENSION_UNIQUE_SETTINGS | 2 |
DIMENSION_IS_VISIBLE | True always |
Request Example
<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=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example(truncated)
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="DIMENSION_NAME" type="xsd:string" sql:field="DIMENSION_NAME"/> <xsd:element name="DIMENSION_UNIQUE_NAME" type="xsd:string" sql:field="DIMENSION_UNIQUE_NAME"/> <xsd:element name="DIMENSION_CAPTION" type="xsd:string" sql:field="DIMENSION_CAPTION"/> <xsd:element name="DIMENSION_ORDINAL" type="xsd:unsignedInt" sql:field="DIMENSION_ORDINAL"/> <xsd:element name="DIMENSION_TYPE" type="xsd:short" sql:field="DIMENSION_TYPE"/> <xsd:element name="DIMENSION_CARDINALITY" type="xsd:unsignedInt" sql:field="DIMENSION_CARDINALITY"/> <xsd:element name="DEFAULT_HIERARCHY" type="xsd:string" sql:field="DEFAULT_HIERARCHY"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> <xsd:element name="DIMENSION_UNIQUE_SETTINGS" type="xsd:int" sql:field="DIMENSION_UNIQUE_SETTINGS"/> <xsd:element name="DIMENSION_IS_VISIBLE" type="xsd:boolean" sql:field="DIMENSION_IS_VISIBLE"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Sample.Basic</CUBE_NAME> <DIMENSION_NAME>Year</DIMENSION_NAME> <DIMENSION_UNIQUE_NAME>[Year]</DIMENSION_UNIQUE_NAME> <DIMENSION_CAPTION>Year</DIMENSION_CAPTION> <DIMENSION_ORDINAL>1</DIMENSION_ORDINAL> <DIMENSION_TYPE>1</DIMENSION_TYPE> <DIMENSION_CARDINALITY>19</DIMENSION_CARDINALITY> <DEFAULT_HIERARCHY>[Year]</DEFAULT_HIERARCHY> <DIMENSION_UNIQUE_SETTINGS>2</DIMENSION_UNIQUE_SETTINGS> <DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The FUNCTIONS rowset exposes all functions supported by the MDP. Default sort order: ORIGIN, INTERFACE_NAME, and FUNCTION_NAME.
GUID: MDSCHEMA_FUNCTIONS
Table 5describes the rowset structure.
Table 5. MDSCHEMA_FUNCTIONS Rowset structure
Column Name | Essbase Mapping |
---|---|
FUNCTION_NAME | Name of the function |
DESCRIPTION | Description of the function |
PARAM_LIST | A comma delimited list of parameters |
RETURN_TYPE | Always 12 |
ORIGIN | 1 (always:MDX functions) |
INTERFACE_NAME | One of the following: Member, Set, Tuple, Numeric, Dimension, Level, Boolean |
OBJECT | One of the following values: Set, Member, Tuple, Level, Hierarchy, Dimension |
HELP_CONTEXT | Help context ID for the function |
CAPTION | Display caption of the function |
Request Example
<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_FUNCTIONS</RequestType> <Restrictions><RestrictionList></RestrictionList></Restrictions> <Properties> <PropertyList> <DataSourceInfo>Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example (truncated)
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="FUNCTION_NAME" type="xsd:string" sql:field="FUNCTION_NAME"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION"/> <xsd:element name="PARAMETER_LIST" type="xsd:string" sql:field="PARAMETER_LIST"/> <xsd:element name="RETURN_TYPE" type="xsd:int" sql:field="RETURN_TYPE"/> <xsd:element name="ORIGIN" type="xsd:int" sql:field="ORIGIN"/> <xsd:element name="INTERFACE_NAME" type="xsd:string" sql:field="INTERFACE_NAME"/> <xsd:element name="OBJECT" type="xsd:string" sql:field="OBJECT" minOccurs="0"/> <xsd:element name="HELP_CONTEXT" type="xsd:int" sql:field="HELP_CONTEXT" minOccurs="0"/> <xsd:element name="CAPTION" type="xsd:string" sql:field="CAPTION"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <!-- Begin: All MDX functions that return a Member (INTERFACE_NAME=Member) --> <row> <FUNCTION_NAME>Ancestor</FUNCTION_NAME> <DESCRIPTION>Given the input member, returns the ancestor at the specified level.</DESCRIPTION> <PARAMETER_LIST>Member, Level | Numeric Expression</PARAMETER_LIST> <RETURN_TYPE>12</RETURN_TYPE> <ORIGIN>1</ORIGIN> <INTERFACE_NAME>Member</INTERFACE_NAME> <HELP_CONTEXT>9142</HELP_CONTEXT> <CAPTION>Ancestor</CAPTION> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The HIERARCHIES rowset contains information about the hierarchies available in a dimension.
GUID: MDSCHEMA_HIERARCHIES
Table 6describes the rowset structure.
Table 6. MDSCHEMA_HIERARCHIES Rowset Structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
DIMENSION_UNIQUE_NAME | Dimension name |
HIERARCHY_NAME | Dimension name |
HIERARCHY_UNIQUE_NAME | Dimension name |
HIERARCHY_CAPTION | Dimension name |
DIMENSION_TYPE | If Essbase dimension type is:
|
HIERARCHY_CARDINALITY | Number of members in the dimension |
DEFAULT_MEMBER | Dimension name |
ALL_MEMBER | Dimension name |
DESCRIPTION | Dimension comment |
STRUCTURE | MD_STRUCTURE_UNBALANCED(2) |
HIERARCHY_UNIQUE_SETTINGS | 2 |
HIERARCHY_IS_VISIBLE | True |
Request Example
<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_HIERARCHIES</RequestType> <Restrictions> <RestrictionList> <CUBE_NAME>Sample.Basic</CUBE_NAME> <DIMENSION_UNIQUE_NAME>Year</DIMENSION_UNIQUE_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo>Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="DIMENSION_UNIQUE_NAME" type="xsd:string" sql:field="DIMENSION_UNIQUE_NAME"/> <xsd:element name="HIERARCHY_NAME" type="xsd:string" sql:field="HIERARCHY_NAME"/> <xsd:element name="HIERARCHY_UNIQUE_NAME" type="xsd:string" sql:field="HIERARCHY_UNIQUE_NAME"/> <xsd:element name="HIERARCHY_CAPTION" type="xsd:string" sql:field="HIERARCHY_CAPTION"/> <xsd:element name="DIMENSION_TYPE" type="xsd:short" sql:field="DIMENSION_TYPE"/> <xsd:element name="HIERARCHY_CARDINALITY" type="xsd:unsignedInt" sql:field="HIERARCHY_CARDINALITY"/> <xsd:element name="DEFAULT_MEMBER" type="xsd:string" sql:field="DEFAULT_MEMBER"/> <xsd:element name="ALL_MEMBER" type="xsd:string" sql:field="ALL_MEMBER"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> <xsd:element name="STRUCTURE" type="xsd:int" sql:field="STRUCTURE"/> <xsd:element name="HIERARCHY_UNIQUE_SETTINGS" type="xsd:int" sql:field="HIERARCHY_UNIQUE_SETTINGS"/> <xsd:element name="HIERARCHY_IS_VISIBLE" type="xsd:boolean" sql:field="HIERARCHY_IS_VISIBLE"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Sample.Basic</CUBE_NAME> <DIMENSION_UNIQUE_NAME>[Year]</DIMENSION_UNIQUE_NAME> <HIERARCHY_NAME>Year</HIERARCHY_NAME> <HIERARCHY_UNIQUE_NAME>[Year]</HIERARCHY_UNIQUE_NAME> <HIERARCHY_CAPTION>Year</HIERARCHY_CAPTION> <DIMENSION_TYPE>1</DIMENSION_TYPE> <HIERARCHY_CARDINALITY>19</HIERARCHY_CARDINALITY> <DEFAULT_MEMBER>[Year]</DEFAULT_MEMBER> <ALL_MEMBER>[Year]</ALL_MEMBER> <STRUCTURE>2</STRUCTURE> <HIERARCHY_UNIQUE_SETTINGS>2</HIERARCHY_UNIQUE_SETTINGS> <HIERARCHY_IS_VISIBLE>true</HIERARCHY_IS_VISIBLE> </row> </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The MEASURES rowset contains information about the available measures.
GUID: MDSCHEMA_MEASURES
Table 7describes the rowset structure.
Table 7. MDSCHEMA_MEASURES Rowset Structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
MEASURE_NAME | Member names in the Accounts dimension |
MEASURE_UNIQUE_NAME | Above member name |
MEASURE_CAPTION | Above member name |
MEASURE_AGGREGATOR | Essbase ADDITION: 1 Essbase SUBSTRACTION: 17 Essbase MULTIPLICATION:18 Essbase DIVISION:19 Essbase PERCENT:20 Essbase NOOP: 21 |
DESCRIPTION | Member comment |
DATA_TYPE | 5 |
EXPRESSION | Member formula |
MEASURE_IS_VISIBLE | True |
Request Example
<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_MEASURES</RequestType> <Restrictions> <RestrictionList> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Basic</CUBE_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo>Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example(truncated)
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="MEASURE_NAME" type="xsd:string" sql:field="MEASURE_NAME"/> <xsd:element name="MEASURE_UNIQUE_NAME" type="xsd:string" sql:field="MEASURE_UNIQUE_NAME"/> <xsd:element name="MEASURE_CAPTION" type="xsd:string" sql:field="MEASURE_CAPTION"/> <xsd:element name="MEASURE_AGGREGATOR" type="xsd:int" sql:field="MEASURE_AGGREGATOR"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> <xsd:element name="DATA_TYPE" type="xsd:unsignedShort" sql:field="DATA_TYPE"/> <xsd:element name="NUMERIC_PRECISION" type="xsd:unsignedShort" sql:field="NUMERIC_PRECISION"/> <xsd:element name="NUMERIC_SCALE" type="xsd:short" sql:field="NUMERIC_SCALE"/> <xsd:element name="EXPRESSION" type="xsd:string" sql:field="EXPRESSION" minOccurs="0"/> <xsd:element name="MEASURE_IS_VISIBLE" type="xsd:boolean" sql:field="MEASURE_IS_VISIBLE"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Sample.Basic</CUBE_NAME> <MEASURE_NAME>Measures</MEASURE_NAME> <MEASURE_UNIQUE_NAME>[Measures]</MEASURE_UNIQUE_NAME> <MEASURE_CAPTION>Measures</MEASURE_CAPTION> <MEASURE_AGGREGATOR>0</MEASURE_AGGREGATOR> <DATA_TYPE>5</DATA_TYPE> <NUMERIC_PRECISION>0</NUMERIC_PRECISION> <NUMERIC_SCALE>0</NUMERIC_SCALE> <MEASURE_IS_VISIBLE>true</MEASURE_IS_VISIBLE> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The MEMBERS rowset contains information about the available members.
GUID: MDSCHEMA_MEMBERS
Table 8 describes the rowset structure.
Table 8. MDSCHEMA_MEMBERS Rowset Structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
DIMENSION_UNIQUE_NAME | Dimension name |
HIERARCHY_UNIQUE_NAME | Dimension name |
LEVEL_UNIQUE_NAME | Level name |
LEVEL_NUMBER | Level number |
GENERATION_NUMBER | Generation number |
MEMBER_ORDINAL | Member number |
MEMBER_NAME | Member name |
MEMBER_UNIQUE_NAME | Unique member name |
MEMBER_TYPE | 1 (REGULAR) |
MEMBER_CAPTION | Member name |
MEMBER_ALIAS | Default alias |
CHILDREN_CARDINALITY | Child count |
PARENT_LEVEL | Level number of the parent. For dimension, same level number as the dimension level number |
PARENT_UNIQUE_NAME | Name of the parent. For dimension, same name as the dimension name |
PARENT_COUNT | Always 1 |
DESCRIPTION | Member comment |
Request Example
<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" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"> <SOAP-ENV:Header> <wsse:Security> <wsse:UsernameToken> <wsse:Username>system</wsse:Username> <wsse:Password>password</wsse:Password> </wsse:UsernameToken> </wsse:Security> </SOAP-ENV:Header> <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> <DIMENSION_UNIQUE_NAME>Year</DIMENSION_UNIQUE_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example(truncated)
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="DIMENSION_UNIQUE_NAME" type="xsd:string" sql:field="DIMENSION_UNIQUE_NAME"/> <xsd:element name="HIERARCHY_UNIQUE_NAME" type="xsd:string" sql:field="HIERARCHY_UNIQUE_NAME"/> <xsd:element name="LEVEL_UNIQUE_NAME" type="xsd:string" sql:field="LEVEL_UNIQUE_NAME"/> <xsd:element name="LEVEL_NUMBER" type="xsd:unsignedInt" sql:field="LEVEL_NUMBER"/> <xsd:element name="GENERATION_NUMBER" type="xsd:unsignedInt" sql:field="GENERATION_NUMBER"/> <xsd:element name="MEMBER_ORDINAL" type="xsd:unsignedInt" sql:field="MEMBER_ORDINAL"/> <xsd:element name="MEMBER_NAME" type="xsd:string" sql:field="MEMBER_NAME"/> <xsd:element name="MEMBER_UNIQUE_NAME" type="xsd:string" sql:field="MEMBER_UNIQUE_NAME"/> <xsd:element name="MEMBER_TYPE" type="xsd:int" sql:field="MEMBER_TYPE"/> <xsd:element name="MEMBER_CAPTION" type="xsd:string" sql:field="MEMBER_CAPTION"/> <xsd:element name="MEMBER_ALIAS" type="xsd:string" sql:field="MEMBER_ALIAS" minOccurs="0"/> <xsd:element name="CHILDREN_CARDINALITY" type="xsd:unsignedInt" sql:field="CHILDREN_CARDINALITY"/> <xsd:element name="PARENT_LEVEL" type="xsd:unsignedInt" sql:field="PARENT_LEVEL"/> <xsd:element name="PARENT_UNIQUE_NAME" type="xsd:string" sql:field="PARENT_UNIQUE_NAME"/> <xsd:element name="PARENT_COUNT" type="xsd:unsignedInt" sql:field="PARENT_COUNT"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Sample.Basic</CUBE_NAME> <DIMENSION_UNIQUE_NAME>[Year]</DIMENSION_UNIQUE_NAME> <HIERARCHY_UNIQUE_NAME>[Year]</HIERARCHY_UNIQUE_NAME> <LEVEL_UNIQUE_NAME>[Year].Levels(2)</LEVEL_UNIQUE_NAME> <LEVEL_NUMBER>2</LEVEL_NUMBER> <GENERATION_NUMBER>1</GENERATION_NUMBER> <MEMBER_ORDINAL>1</MEMBER_ORDINAL> <MEMBER_NAME>Jan</MEMBER_NAME> <MEMBER_UNIQUE_NAME>[Jan]</MEMBER_UNIQUE_NAME> <MEMBER_TYPE>1</MEMBER_TYPE> <MEMBER_CAPTION>Jan</MEMBER_CAPTION> <CHILDREN_CARDINALITY>0</CHILDREN_CARDINALITY> <PARENT_LEVEL>1</PARENT_LEVEL> <PARENT_UNIQUE_NAME>[Qtr1]</PARENT_UNIQUE_NAME> <PARENT_COUNT>1</PARENT_COUNT> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
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 default sort order: PROPERTY_TYPE, CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, and LEVEL_UNIQUE_NAME.
GUID: MDSCHEMA_PROPERTIES
Table 9 describes the rowset structure.
Table 9. MDSCHEMA_PROPERTIES Rowset Structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
HIERARCHY_UNIQUE_NAME | Dimension name |
LEVEL_UNIQUE_NAME | Dimension name |
PROPERTY_TYPE | 1 (MDPROP_MEMBER) |
PROPERTY_NAME | One of the following:
|
PROPERTY_CAPTION | One of the following:
|
DATA_TYPE | 1 (double) – attribute dimension 2 (boolean) – attribute dimension 3 (string) – attribute dimension, UDA or alias 4 (integer) – attribute dimension |
CHARACTER_MAXIMUM_LENGTH | 80 (for UDA or an attribute dimension) 30 (for alias) |
CHARACTER_OCTET_LENGTH | 320 (for UDA or an attribute dimension) 120 (for alias) |
PROPERTY_CONTENT_TYPE | 0 (MD_PROPTYPE_REGULAR) |
SQL_COLUMN_NAME | One of the following:
|
PROPERTY_ORIGIN | 1 (MD_USER_DEFINED) |
PROPERTY_ATTRIBUTE_HIERARCHY_NAME | For attribute dimensions, the attribute dimension name |
PROPERTY_CARDINALITY | ONE (for UDA and aliases) MANY (for attribute dimension) |
PROPERTY_IS_VISIBLE | True |
Request Example
<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_PROPERTIES</RequestType> <Restrictions> <RestrictionList> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Basic</CUBE_NAME> <DIMENSION_UNIQUE_NAME>Product</DIMENSION_UNIQUE_NAME> <LEVEL_UNIQUE_NAME>SKU</LEVEL_UNIQUE_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example(truncated)
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="DIMENSION_UNIQUE_NAME" type="xsd:string" sql:field="DIMENSION_UNIQUE_NAME"/> <xsd:element name="HIERARCHY_UNIQUE_NAME" type="xsd:string" sql:field="HIERARCHY_UNIQUE_NAME"/> <xsd:element name="LEVEL_UNIQUE_NAME" type="xsd:string" sql:field="LEVEL_UNIQUE_NAME" minOccurs ="0"/> <xsd:element name="MEMBER_UNIQUE_NAME" type="xsd:string" sql:field="MEMBER_UNIQUE_NAME" minOccurs ="0"/> <xsd:element name="PROPERTY_TYPE" type="xsd:short" sql:field="PROPERTY_TYPE" minOccurs ="0"/> <xsd:element name="PROPERTY_NAME" type="xsd:string" sql:field="PROPERTY_NAME" minOccurs ="0"/> <xsd:element name="PROPERTY_CAPTION" type="xsd:string" sql:field="PROPERTY_CAPTION" minOccurs ="0"/> <xsd:element name="DATA_TYPE" type="xsd:unsignedShort" sql:field="DATA_TYPE" minOccurs ="0"/> <xsd:element name="CHARACTER_MAXIMUM_LENGTH" type="xsd:unsignedInt" sql:field="CHARACTER_MAXIMUM_LENGTH" minOccurs ="0"/> <xsd:element name="CHARACTER_OCTET_LENGTH" type="xsd:unsignedInt" sql:field="CHARACTER_OCTET_LENGTH" minOccurs ="0"/> <xsd:element name="NUMERIC_PRECISION" type="xsd:unsignedShort" sql:field="NUMERIC_PRECISION" minOccurs ="0"/> <xsd:element name="NUMERIC_SCALE" type="xsd:short" sql:field="NUMERIC_SCALE" minOccurs ="0"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs ="0"/> <xsd:element name="PROPERTY_CONTENT_TYPE" type="xsd:short" sql:field="PROPERTY_CONTENT_TYPE" minOccurs ="0"/> <xsd:element name="SQL_COLUMN_NAME" type="xsd:string" sql:field="SQL_COLUMN_NAME" minOccurs ="0"/> <xsd:element name="LANGUAGE" type="xsd:unsignedShort" sql:field="LANGUAGE" minOccurs ="0"/> <xsd:element name="PROPERTY_ORIGIN" type="xsd:unsignedShort" sql:field="PROPERTY_ORIGIN" minOccurs ="0"/> <xsd:element name="PROPERTY_ATTRIBUTE_HIERARCHY_NAME" type="xsd:string" sql:field="PROPERTY_ATTRIBUTE_HIERARCHY_NAME" minOccurs ="0"/> <xsd:element name="PROPERTY_CARDINALITY" type="xsd:string" sql:field="PROPERTY_CARDINALITY" minOccurs ="0"/> <xsd:element name="MIME_TYPE" type="xsd:string" sql:field="MIME_TYPE" minOccurs ="0"/> <xsd:element name="PROPERTY_IS_VISIBLE" type="xsd:boolean" sql:field="PROPERTY_IS_VISIBLE" minOccurs ="0"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Sample.Basic</CUBE_NAME> <DIMENSION_UNIQUE_NAME>[Product]</DIMENSION_UNIQUE_NAME> <HIERARCHY_UNIQUE_NAME>[Product]</HIERARCHY_UNIQUE_NAME> <LEVEL_UNIQUE_NAME>[Product]</LEVEL_UNIQUE_NAME> <PROPERTY_TYPE>1</PROPERTY_TYPE> <PROPERTY_NAME>Caffeinated</PROPERTY_NAME> <PROPERTY_CAPTION>Caffeinated</PROPERTY_CAPTION> <DATA_TYPE>2</DATA_TYPE> <PROPERTY_CONTENT_TYPE>0</PROPERTY_CONTENT_TYPE> <SQL_COLUMN_NAME>Caffeinated</SQL_COLUMN_NAME> <PROPERTY_ORIGIN>1</PROPERTY_ORIGIN> <PROPERTY_ATTRIBUTE_HIERARCHY_NAME>Caffeinated </PROPERTY_ATTRIBUTE_HIERARCHY_NAME> <PROPERTY_CARDINALITY>MANY</PROPERTY_CARDINALITY> <PROPERTY_IS_VISIBLE>true</PROPERTY_IS_VISIBLE> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The SETS rowset contains information about the sets in a schema (or the catalog, if the provider does not support schemas).
GUID: MDSCHEMA_SETS
Table 10describes the rowset structure.
The LEVELS rowset contains information about the levels available in a dimension.
GUID: MDSCHEMA_LEVELS
Table 11 describes the rowset structure.
Table 11. MDSCHEMA_LEVELS Rowset Structure
Column Name | Essbase Mapping |
---|---|
CATALOG_NAME | Application name |
CUBE_NAME | Database name |
DIMENSION_UNIQUE_NAME | Name of the dimension to which the level belongs |
HIERARCHY_UNIQUE_NAME | Name of the dimension to which the level belongs |
LEVEL_NAME | Unique level name |
LEVEL_UNIQUE_NAME | Unique level name |
LEVEL_CAPTION | Level name |
LEVEL_NUMBER | Level number |
LEVEL_CARDINALITY | Number of members in the level |
LEVEL_TYPE | MDLEVEL_TYPE_ALL (for dimension level) MDLEVEL_TYPE_TIME (for dimension type TIME) MDLEVEL_TYPE_REGULAR (for all others) |
LEVEL_UNIQUE_SETTINGS | 2 (MDDIMENSIONS_MEMBER_NAME_UNIQUE) |
LEVEL_IS_VISIBLE | True |
ESSBASE_GEN_UNIQUE_NAME | Generation unique name |
ESSBASE_GEN_CAPTION | Generation caption |
Request Example
<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> <DIMENSION_UNIQUE_NAME>Year</DIMENSION_UNIQUE_NAME> </RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo>Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="CATALOG_NAME" type="xsd:string" sql:field="CATALOG_NAME"/> <xsd:element name="CUBE_NAME" type="xsd:string" sql:field="CUBE_NAME"/> <xsd:element name="DIMENSION_UNIQUE_NAME" type="xsd:string" sql:field="DIMENSION_UNIQUE_NAME"/> <xsd:element name="HIERARCHY_UNIQUE_NAME" type="xsd:string" sql:field="HIERARCHY_UNIQUE_NAME"/> <xsd:element name="LEVEL_NAME" type="xsd:string" sql:field="LEVEL_NAME"/> <xsd:element name="LEVEL_UNIQUE_NAME" type="xsd:string" sql:field="LEVEL_UNIQUE_NAME"/> <xsd:element name="LEVEL_CAPTION" type="xsd:string" sql:field="LEVEL_CAPTION"/> <xsd:element name="LEVEL_NUMBER" type="xsd:unsignedInt" sql:field="LEVEL_NUMBER"/> <xsd:element name="LEVEL_CARDINALITY" type="xsd:unsignedInt" sql:field="LEVEL_CARDINALITY"/> <xsd:element name="LEVEL_TYPE" type="xsd:int" sql:field="LEVEL_TYPE"/> <xsd:element name="LEVEL_UNIQUE_SETTINGS" type="xsd:int" sql:field="LEVEL_UNIQUE_SETTINGS"/> <xsd:element name="LEVEL_IS_VISIBLE" type="xsd:boolean" sql:field="LEVEL_IS_VISIBLE"/> <xsd:element name="DESCRIPTION" type="xsd:string" sql:field="DESCRIPTION" minOccurs="0"/> <xsd:element name="ESSBASE_GEN_UNIQUE_NAME" type="xsd:string" sql:field="ESSBASE_GEN_UNIQUE_NAME"/> <xsd:element name="ESSBASE_GEN_CAPTION" type="xsd:string" sql:field="ESSBASE_GEN_CAPTION"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <CATALOG_NAME>Sample</CATALOG_NAME> <CUBE_NAME>Sample.Basic</CUBE_NAME> <DIMENSION_UNIQUE_NAME>[Year]</DIMENSION_UNIQUE_NAME> <HIERARCHY_UNIQUE_NAME>[Year]</HIERARCHY_UNIQUE_NAME> <LEVEL_NAME>[Year].Levels(2)</LEVEL_NAME> <LEVEL_UNIQUE_NAME>[Year].Levels(2)</LEVEL_UNIQUE_NAME> <LEVEL_CAPTION>[Year].Level 2</LEVEL_CAPTION> <LEVEL_NUMBER>2</LEVEL_NUMBER> <LEVEL_CARDINALITY>12</LEVEL_CARDINALITY> <LEVEL_TYPE>4</LEVEL_TYPE> <LEVEL_UNIQUE_SETTINGS>2</LEVEL_UNIQUE_SETTINGS> <LEVEL_IS_VISIBLE>true</LEVEL_IS_VISIBLE> <ESSBASE_GEN_UNIQUE_NAME>[Year].[Months]</ESSBASE_GEN_UNIQUE_NAME> <ESSBASE_GEN_CAPTION>[Year].Months</ESSBASE_GEN_CAPTION> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
GUID: DISCOVER_SCHEMA_ROWSETS
Table 12describes the rowset structure.
Table 12. DISCOVER_SCHEMA Rowset Structure
Column Name | Essbase Mapping |
---|---|
SchemaName | The name of the schema/request. This returns the values in the RequestTypes enumeration, plus any additional types supported by the provider. The provider defines rowset structures for the additional types. |
Restrictions | List of restrictions allowed |
Description | Description of the schema |
GUID: DISCOVER_DATASOURCES
Table 13 describes the rowset structure.
Table 13. DISCOVER_DATASOURCES Rowset Structure
Column Name | Essbase Mapping |
---|---|
DataSourceName | Name of the data source |
DataSourceDescription | Description of the data source |
DataSourceInfo | Provider=Essbase Data Source= name of the Analytic Server |
ProviderName | XMLA for Essbase |
ProviderType | MDP |
AuthenticationMode | Authenticated |
GUID: DISCOVER_PROPERTIES
Table 14 describes the rowset structure.
Table 14. DISCOVER_PROPERTIES Rowset Structure
Column Name | Essbase Mapping |
---|---|
PropertyName | Name of the property |
PropertyDescription | Description of the property |
PropertyType | XML data type of the property. |
PropertyAccessType | Access for the property. The value can be Read, Write, or ReadWrite |
IsRequired | True if a property is required, false if it is not required |
Value | Current value of the property |
Request Example
<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=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="PropertyName" type="xsd:string" sql:field="PropertyName"/> <xsd:element name="PropertyDescription" type="xsd:string" sql:field="PropertyDescription"/> <xsd:element name="PropertyType" type="xsd:string" sql:field="PropertyType"/> <xsd:element name="PropertyAccessType" type="xsd:string" sql:field="PropertyAccessType"/> <xsd:element name="IsRequired" type="xsd:boolean" sql:field="IsRequired"/> <xsd:element name="Value" type="xsd:string" sql:field="Value"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <PropertyName>ProviderName</PropertyName> <PropertyDescription>The name of the Analytic Services Provider </PropertyDescription> <PropertyType>string</PropertyType> <PropertyAccessType>Read</PropertyAccessType> <IsRequired>false</IsRequired> <Value>Analytic Services XML for Analysis Provider</Value> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
GUID: DISCOVER_ENUMERATORS
Table 15 describes the rowset structure.
Table 15. DISCOVER_ENUMERATORS Rowset Structure
Column Name | Essbase Mapping |
---|---|
EnumName | Name of the enumerator that contains a set of values |
EnumDescription | Description of the enumerator |
ElementName | Name of one of the value elements in the enumerator set Example: TDP |
ElementDescription | Description of the element |
EnumType | Data type of the Enum values |
ElementValue | Value of the element Example: 01 |
Request Example
<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_ENUMERATORS</RequestType> <Restrictions> <RestrictionList></RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="EnumName" type="xsd:string" sql:field="EnumName"/> <xsd:element name="EnumDescription" type="xsd:string" sql:field="EnumDescription" minOccurs="0"/> <xsd:element name="ElementName" type="xsd:string" sql:field="ElementName"/> <xsd:element name="ElementDescription" type="xsd:string" sql:field="ElementDescription" minOccurs="0"/> <xsd:element name="ElementValue" type="xsd:string" sql:field="ElementValue" minOccurs="0"/> <xsd:element name="EnumType" type="xsd:string" sql:field="EnumType"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <EnumName>ProviderType</EnumName> <ElementName>TDP</ElementName> <EnumType>string</EnumType> </row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
GUID: DISCOVER_KEYWORDS
Table 16 describes the rowset structure.
Table 16. DISCOVER_KEYWORDS Rowset Structure
Column Name | Essbase Mapping |
---|---|
Keyword | A list of keywords reserved by a provider Example: AND |
Request Example
<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_KEYWORDS</RequestType> <Restrictions> <RestrictionList></RestrictionList> </Restrictions> <Properties> <PropertyList> <DataSourceInfo> Provider=Essbase;Data Source=localhost </DataSourceInfo> <Format>Tabular</Format> </PropertyList> </Properties> </Discover> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Response Example
<?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:DiscoverResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis"> <m:return xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence maxOccurs="unbounded" minOccurs="0"> <xsd:element name="Keyword" type="xsd:string" sql:field="Keyword"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row><Keyword>aggregate</Keyword></row> <row><Keyword>ancestors</Keyword></row> < ................More Rows............. > </root> </m:return> </m:DiscoverResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
GUID: DISCOVER_LITERALS
the section called “Example 1” describes the rowset structure.
Table 17. DISCOVER_LITERALS Rowset Structure
Column Name | Essbase Mapping |
---|---|
LiteralName | Name of the literal described in the row Example: DBLITERAL_LIKE_PERCENT |
LiteralValue | Contains the literal value Example, if LiteralName is DBLITERAL_LIKE_PERCENT and the percent character (%) is used to match zero or more characters in a LIKE clause, this column’s value would be “%.” |
LiteralInvalidChars | Characters, in the literal, that are not valid Example: If table names can contain anything other than a numeric character, this string would be “0123456789” |
LiteralInvalidStartingChars | Characters that are not valid as the first character of the literal. If the literal can start with any valid character, this is null. |
LiteralMaxLength | Maximum number of characters in the literal. If there is no maximum or the maximum is unknown, the value is -1. |
Flattening a rowset is a way to present multidimensional data in a grid. This two-dimensional, tabular presentation of data can facilitate understanding of the output of a multidimensional XMLA request.
The following examples illustrate flattened rowsets as MDX queries and results. MDX is used for ease of presentation; however, the example queries are intended to be considered in terms of XMLA SOAP requests. Remember that in XMLA, level 0 represents a dimension, rather than a leaf member, as in MDX. Therefore, although these examples are in MDX, the levels are reversed as if they were in XMLA.
The following query requests all members of level 1.
SELECT NON EMPTY {[Profit]} ON COLUMNS, NON EMPTY [Product].Levels(1).ALLMEMBERS ON ROWS FROM Sample.Basic
This query has the following result:
[Product].[Family].[MEMBER_CAPTION] | [Profit] |
---|---|
100 | 30468 |
200 | 27954 |
300 | 25799 |
400 | 21301 |
Diet | 28826 |
The following query requests a maximum of two levels. The flattening of rowsets includes level 1 in this request for levels(2). When using flattened rowsets, if you query for level N, levels 1 through N are returned.
SELECT NON EMPTY {[Profit] } ON COLUMNS, NON EMPTY [Product].Levels(2).ALLMEMBERS ON ROWS FROM Sample.Basic
This query has the following result (truncated):
[Product].[Family].[MEMBER_CAPTION] | [Product].[SKU].[MEMBER_CAPTION] | [Profit] |
---|---|---|
100 | 100–10 | 22777 |
100 | 100–20 | 5708 |
100 | 100–30 | 1983 |
200 | 200–10 | 7201 |
200 | 200–20 | 12025 |
200 | 200–30 | 4636 |
200 | 200–40 | 4092 |
... | ... | ... |
The following query builds on the previous, and also asks for the result set to include the member unique name and level number properties for the set of levels 1 through N, where N=2. Each member and each property is allotted a row.
SELECT NON EMPTY {[Profit]} ON COLUMNS, NON EMPTY [Product].Levels(2).ALLMEMBERS DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM Sample.Basic
This query has the following result (truncated):
[Product]. [Family]. [MEMBER_ UNIQUE_ NAME] | [Product]. [Family]. LEVEL_ NUMBER | [Product]. [SKU]. [MEMBER_ UNIQUE_ NAME] | [Product]. [SKU]. LEVEL_ NUMBER | [Profit] |
---|---|---|---|---|
[100] | 1 | [100–10] | 2 | 22777 |
[100] | 1 | [100–20] | 2 | 5708 |
[100] | 1 | [100–30] | 2 | 1983 |
[200] | 1 | [200–10] | 2 | 7201 |
[200] | 1 | [200–20] | 2 | 12025 |
[200] | 1 | [200–30] | 2 | 4636 |
[200] | 1 | [200–40] | 2 | 4092 |
[300] | 1 | [300–10] | 2 | 12195 |
[300] | 1 | [300–20] | 2 | 2511 |
[300] | 1 | [300–30] | 2 | 2511 |
... | ... | ... | ... | ... |
By implementing CrossJoin in a flattened rowsets query, you can use multiple dimensions (at least two). In this example, Market and Product dimensions are requested. For each dimension, the same logic as in previous examples applies: Each dimension, level, and property is allotted one column (in this case, one level and one property are requested).
SELECT NON EMPTY {[Profit] } ON COLUMNS, NON EMPTY Crossjoin ([Market].Levels(1).AllMembers,[Product].Levels(1).ALLMEMBERS) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM Sample.Basic
This query has the following result (truncated):
[Market]. Levels(1). [MEMBER_CAPTION] | [Product]. [Family]. [MEMBER_CAPTION] | [Profit] |
---|---|---|
East | Colas | 12656 |
East | Root Beer | 2534 |
East | Cream Soda | 2627 |
East | Fruit Soda | 6344 |
East | Diet Drinks | 2408 |
West | Colas | 3549 |
West | Root Beer | 9727 |
West | Cream Soda | 10731 |
West | Fruit Soda | 5854 |
West | Diet Drinks | 8087 |
... | ... | ... |
In this example, CrossJoin is used to request levels 1–2 for Market and Product.
SELECT NON EMPTY { [Profit] } ON COLUMNS, NON EMPTY Crossjoin ([Market].Levels(2).AllMembers,[Product].Levels(2).ALLMEMBERS) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM Sample.Basic
This query has the following result (truncated):
[Market]. Levels(1). [MEMBER_ CAPTION] | [Market]. Levels(2). [MEMBER_ CAPTION] | [Product]. [Family]. [MEMBER_ CAPTION] | [Product]. [SKU]. [MEMBER_ CAPTION] | [Profit] |
---|---|---|---|---|
East | New York | Colas | Cola | 3498 |
East | New York | Root Beer | Old Fashioned | -2594 |
East | New York | Root Beer | Birch Beer | 3086 |
East | New York | Cream Soda | Dark Cream | 2496 |
East | New York | Cream Drinks | Vanilla Cream | -1952 |
East | New York | Fruit Soda | Grape | 1329 |
East | New York | Fruit Soda | Orange | 1388 |
East | New York | Fruit Soda | Strawberry | 951 |
... | ... | ... | ... | ... |
The following example uses CrossJoin to represent multiple dimensions, requests a different number of levels for each dimension, and requests multiple properties.
SELECT NON EMPTY { [Profit] } ON COLUMNS, NON EMPTY Crossjoin ([Market].Levels(1).AllMembers,[Product].Levels(2).ALLMEMBERS) DIMENSION PROPERTIES MEMBER_CAPTION, LEVEL_NUMBER ON ROWS FROM Sample.Basic
This query has the following result (truncated):
[Market]. Levels(1). [MEMBER_ CAPTION] | [Market]. Levels(1). [LEVEL_ NUMBER] | [Product]. [Family]. [MEMBER_ CAPTION] | [Market]. Levels(1). [LEVEL_ NUMBER] | [Product]. [SKU]. [MEMBER_ CAPTION] | [Market]. Levels(1). [LEVEL_ NUMBER] | [Profit] |
---|---|---|---|---|---|---|
East | 1 | Colas | 1 | Cola | 2 | 11129 |
East | 1 | Colas | 1 | Diet Cola | 2 | 1114 |
East | 1 | Colas | 1 | Caffeine Free Cola | 2 | 413 |
East | 1 | Root Beer | 1 | Old Fashioned | 2 | -2540 |
East | 1 | Root Beer | 1 | Diet Root Beer | 2 | 982 |
East | 1 | Root Beer | 1 | Birch Beer | 2 | 4092 |
East | 1 | Cream Soda | 1 | Dark Cream | 2 | 3233 |
East | 1 | Cream Soda | 1 | Vanilla Cream | 2 | -918 |
... | ... | ... | ... | ... | ... | ... |
The following example uses multiple, nested CrossJoins.
SELECT NON EMPTY { [Profit] } ON COLUMNS, NON EMPTY {CROSSJOIN ( CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Product].[Family].ALLMEMBERS ), [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM Sample.Basic
This query has the following result (truncated):
[Market]. Levels(1). [MEMBER_ CAPTION] | [Product]. [Family]. [MEMBER_ CAPTION] | [Year]. Levels(1). [MEMBER_ CAPTION] | [Profit] |
---|---|---|---|
East | Colas | Qtr1 | 2747 |
East | Colas | Qtr2 | 3352 |
East | Colas | Qtr3 | 3740 |
East | Colas | Qtr4 | 2817 |
East | Root Beer | Qtr1 | 562 |
East | Root Beer | Qtr2 | 610 |
East | Root Beer | Qtr3 | 372 |
East | Root Beer | Qtr4 | 990 |
... | ... | ... | ... |
The following examples illustrate an XMLA response and request.
This is an example of a flattened rowset request. To flatten the result, you must use Tabular format in the PropertyList element, as shown in the example.
<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> <Execute xmlns="urn:schemas-microsoft-com:xml-analysis" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <Command> <Statement> WITH MEMBER [Year].[calctest] AS '4' SELECT NON EMPTY { [Profit] } ON COLUMNS, NON EMPTY {[Year].ALLMEMBERS } ON ROWS FROM Sample.Basic </Statement> </Command> <Properties> <PropertyList> <DataSourceInfo>Provider=Essbase;Data Source=localhost </DataSourceInfo> <Catalog>Sample</Catalog> <Format>Tabular</Format> <AxisFormat>TupleFormat</AxisFormat> </PropertyList> </Properties> </Execute> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
An example of a flattened rowset response:
<?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:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="row" type="row" /> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:complexType name="row"> <xsd:sequence minOccurs="0" maxOccurs="unbounded"> <xsd:element name="column1" type="xsd:string" sql:field="[Year].Levels(1).[MEMBER_CAPTION]" minOccurs="0"/> <xsd:element name="column2" type="xsd:string" sql:field="[Year].Levels(2).[MEMBER_CAPTION]" minOccurs="0"/> <xsd:element name= "column3" type="xsd:double" sql:field= "[Profit]" minOccurs="0"/> </xsd:sequence> </xsd:complexType> </xsd:schema> <row> <column3>105522.000000</column3> </row> <row> <column1>Qtr1</column1> <column3>24703.000000</column3> </row> <row> <column1>Qtr1</column1> <column2>Jan</column2> <column3>8024.000000</column3> </row> <row> <column1>Qtr1</column1> <column2>Feb</column2> <column3>8346.000000</column3> </row> <row> <column1>Qtr1</column1> <column2>Mar</column2> <column3>8333.000000</column3> </row> <row> <column1>Qtr2</column1> <column3>27107.000000</column3> </row> <row> <column1>Qtr2</column1> <column2>Apr</column2> <column3>8644.000000</column3> </row> <row> <column1>Qtr2</column1> <column2>May</column2> <column3>8929.000000</column3> </row> <row> <column1>Qtr2</column1> <column2>Jun</column2> <column3>9534.000000</column3> </row> <row> <column1>Qtr3</column1> <column3>27912.000000</column3> </row> <row> <column1>Qtr3</column1> <column2>Jul</column2> <column3>9878.000000</column3> </row> <row> <column1>Qtr3</column1> <column2>Aug</column2> <column3>9545.000000</column3> </row> <row> <column1>Qtr3</column1> <column2>Sep</column2> <column3>8489.000000</column3> </row> <row> <column1>Qtr4</column1> <column3>25800.000000</column3> </row> <row> <column1>Qtr4</column1> <column2>Oct</column2> <column3>8653.000000</column3> </row> <row> <column1>Qtr4</column1> <column2>Nov</column2> <column3>8367.000000</column3> </row> <row> <column1>Qtr4</column1> <column2>Dec</column2> <column3>8780.000000</column3> </row> <row> <column1>calctest</column1> <column3>4.000000</column3> </row> </root> </m:return> </m:ExecuteResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>