XML for Analysis (XMLA) development

XML for Analysis (XMLA) is an XML protocol based on Simple Object Access Protocol (SOAP), which is designed for universal data access to any standard multidimensional data source residing on the Web. The benefit of XMLA is that its application-level transport protocol is SOAP, which enables XMLA service providers to have the role of the WebService provider, so that clients written in various languages can easily access it without additional hassle.

There has been a lot of documentation on XMLA, especially Microsoft, which provides a large number of documents at multiple angles, but the organization of these documents is relatively loosely organized, some of which are prone to problems that are scattered all over the network, and the purpose of this document is to organize and simply explain these scattered content. For ease of use, this article is constantly updated as appropriate (recent updates are available through http//www.bi-professional.com). 

Remember that many years ago when we provided business intelligence solutions based on the Java EE system, all OLAP server interfaces were traditional development interfaces, although a unified meta-data structure was defined for query and analysis in our Java EE service, but still for each OLAP The server develops JNI code to accomplish the transformation of the traditional language interface to the Java system. Also, each vendor's query language is different, and the Microsoft Analysis service is an MDX statement on OLEDB/ADOMD, and Essbase and Oracle Express are their own DML languages on C-implemented DLLs. Mondrian and so on did not appear at that time.

Today, XMLA is increasingly supported by vendors, with the exception of Microsoft Ssas,essbase (7.0 +), SAS, Mondrian, etc., which provide "xmla+mdx" interfaces, which reduces learning costs for solution development that includes OLAP client logic , which increases the portability of the platform.

This article will take SSAS as an example to explain the XMLA development process. Although they are mentioned in the manual, the content is fragmented and the format of the manual is not easy to get started quickly. So it may be helpful to provide a simple guideline.

Another reason to write this article is not want to let some fragmentary experience in their own brain, let some scattered information frozen in their hard disk or even CD-ROM (such as the netizen old Bamboo said, http://blog.csdn.net/java2000_net/archive/2008/ 10/31/3192658.aspx). Therefore, hope to see friends, if you have no value, can only say sorry, so blog is only from their own interests, and their work level and the article is very different. First, the concept of XMLA

The XML for Analysis (XMLA) open standard supports data access to data sources residing on the World Wide Web. Microsoft SQL Server Analysis Services implements XMLA by the XMLA 1.1 specification.

XML for Analysis (XMLA) is an XML protocol based on Simple Object Access Protocol (SOAP), which is designed for universal data access to any standard multidimensional data source residing on the Web. XMLA also eliminates the need to deploy client components that expose Component Object Model (COM) or Microsoft. NET Framework interfaces. XMLA is optimized for the Internet if the round-trip communication with the server takes a significant amount of time and resources, and stateful connections to the data source limit the number of user connections on the server.

XMLA is a native protocol to Microsoft SQL Server Analysis Services for all interactions between a client application and an Analysis Services instance. Analysis Services fully supports XML for analysis 1.1, and also provides extensions that support metadata management, session management, and locking capabilities. When communicating with an analysis Services instance, both analytical Management Objects (AMO) and adomd.net use the XMLA protocol.

Attention:

1) is provided by Microsoft, only to indicate that Microsoft's own client development component of the use of XMLA. The use of XMLA by other clients (Java, etc.) is similar.

2) We can only use XMLA over HTTP, and the more efficient XML over TCP/IP is Microsoft's patented protection technology for its own SQL Server Manage studio/business Intelligence Development Studio. Processing XMLA Traffic

The XMLA open standard describes the following two general access methods: Discover and Execute. These methods use the loosely coupled client and server architectures supported by XML to process incoming and outgoing information about an analysis Services instance.

• The Discover method obtains information and metadata from the Web service. This information can contain a list of available data sources and information about any data source provider. property to define and shape the data obtained from the data source. The Discover method is a common method of defining multiple types of information that a client application may need to obtain from the data source of an analysis Services instance. Properties and generic interfaces provide extensibility without overriding existing functions in the client application.
• The Execute method enables an application to run a provider-specific command on an XMLA data source.

Although the XMLA protocol is optimized for WEB applications, it can also be used for LAN-oriented applications. The following applications can benefit from this XML-based API:

• Client/server applications that need to use flexible technology between the client and the server
• Client/server applications for multiple operating systems
• Clients that do not need a noticeable state to increase server capacity

1.2. XMLA and Unified Dimension model

XMLA is the protocol used by business intelligence applications that adopt a unified Dimensional model (UDM) approach.

1.3, Summary This section is primarily from Microsoft's online documentation, which is intended to give a general understanding of unfamiliar concepts before continuing. This article will be updated on an ongoing basis (recent updates are available through http//www.bi-professional.com). ii. Configuring XMLA over HTTP access sass (OLAP) document CollectionsWith respect to the configuration of XMLA, there is already a large amount of data on the network. This section classifies the guidelines that can be referenced in the configuration process under different circumstances, some of which need to be concerned when problems are encountered.

1. SQL Server Analysis Services 2000

XML for Analysis SDK

How to build XMLA using SQL Server 2000

JPivot-MS XMLA

2. SQL Server Analysis Services 2005,2008

2.1 Windows 5/iis6:windows xp/windows Server 2003

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows XP

Troubleshooting Analysis Services 2005 Connectivity problems

Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios

About identity authentication (also available for SASS and other versions of Windows):

How to troubleshoot Windows Integrated validation issues between SSAS + SSRS + WSS3.0

Specifying credential and connection information for a report data source

How to configure SQL Server 2005 Analysis Services to use Kerberos authentication

Configuring IIS to support Kerberos and NTLM authentication

How to ensure that you use Kerberos authentication when you create a remote connection to a SQL Server 2005 instance

How to use Kerberos Authentication in SQL Server

2.2 Windows 6/iis7:windows Server 2008/vista

Configuring HTTP Access to SQL Server for Analysis Services on Microsoft Windows Server 2008

Ssas:connecting via HTTP on Windows Vista

3. XMLA over TCP/IP

Analysis

Services 2005 PROTOCOL–XMLA over TCP/IP

Microsoft Patent Literature: Systems and methods of utilizing and expanding

standard

iii. XMLA over TCP/IP access to analysis Services 2005/2008 OLAP
xml/a over TCP/IP as SQL Server Analysis Service 2005/2008 self client (SQL Server Management studio/business Intelligence developm    ENT Studio) and the basic protocol for server-side communication, but cannot be used directly in our third-party development. Because xml/a over TCP/IP is a reserved track for Microsoft, it is a patented technology that is currently available only for its own use. The author of this paper detailed analysis of the agreement, interested can see.   But the actual development, for a long time we want to adopt xml/a must pass xml/a over HTTP, with the IIS extension. Interested in technical details of colleagues, not through the Google Patent Search tool (http://www.google.com/patents) to obtain information about XML/A over TCP/IP patent full-text learning. iv. format and examples of XMLA requestsThe XMLA request consists of a multilayer packet, as shown in the overall structure, and can be found against MSDN for more information (http://technet.microsoft.com/zh-cn/library/ms186656.aspx):

Where the HTML packet is the transport protocol on which soap is dependent, and the layer of soap is separated from XMLA, there is no logical relationship between the HTML packet and XMLA (in fact, as described in the fourth article in this series, Microsoft's own transport protocol is TCP/IP, Further, the SOAP packets are compressed and encrypted.

The SOAP packet is adjacent to XMLA and is used by XMLA to register the session header command (four commands) for the maintenance sessions. Each command that does not specify a session header is a separate, ephemeral session that maintains an atomized transactional attribute for the client.

The composition and structure of the XMLA method

In the XMLA Introduction section we know that XMLA has two methods, namely Discover, Execute.

The Discover method is used to retrieve structural information from the SSAS server and the specified multidimensional database instance, including the cube list, Catalog, DataSource, Dimensions, levels, and so on, which gets the server. The structure of the Discover method is as follows:

<Discover>

<RequestType>...</RequestType>

<Restrictions>...</Restrictions>

<Properties>...</Properties>

</Discover>

The Execute method carries the command parameter and obtains the query data from the specified cube. The most common command is the Statement commands that perform the MDX statement query (<statement>mdx query clause</statement>):

<Execute>

<Command>...</Command>

<Properties>...</Properties>

<Parameters>...</Parameters>
</Execute>

XMLA SOAP Request instance (HTTP packets removed)

The following table is a complete XMLA discover request to get a list of data sources, the first column of the table splits the parts of the XMLA packet, and the second column corresponds to the meaning of each part.

<?xml version= "1.0″encoding=" utf-8″?>
<soap-env:envelope
xmlns:soap-env= "http://schemas.xmlsoap.org/soap/envelope/"
soap-env:encodingstyle= "http://schemas.xmlsoap.org/soap/encoding/" >
<SOAP-ENV:Body>

Soap Table Header

<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>
<Content>Data</Content>
</PropertyList>
</Properties>
</Discover>

Xmla method

</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Soap

It is important to note that there are subtle differences in XMLA access to SSAS 2000/2005/2008, and that the higher-version SSAS have more stringent properties for SOAP packets, while in the lower versions they can be ignored appropriately. Therefore, if the XMLA program that can run under the lower version of SSAS is unable to obtain the results at a later date, you should review the documentation carefully and track the comparisons to see if this is the cause.

About returning results

If you know the sequence of requests to complete a particular task, the descriptive information about the results can be easily obtained by looking up MSDN or XMLA spec. Therefore, this series does not intend to describe the results of the XMLA return.

example of a step: Step1 :Gets a list of the XML for Analysis (XMLA) provider data sources available on the server or WEB service;

Discover_datasources

return results

<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>

<Content>Data</Content>

</PropertyList>

</Properties>

</Discover>

Rowset: Contains the names, attributes, support features, and so on, of the available data source datasource.

STEP2 :Returns a list of directories (catalog) in the database for the specified data source;

Dbschema_catalogs

return results

<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>http://www. jbean.cn</DataSourceInfo>

<Content>Data</Content>

</PropertyList>

</Properties>

</Discover>

Rowset: Gets the name, description, access control list ACL, and so on of the catalog in the data source Jbean returned in the previous step.

STEP3 :Returns the specified data source, catalog under the cube list;

Mdschema_cubes

return results

<discover xmlns= "Urn:schemas-microsoft-com:xml-analysis"

soap-env:encodingstyle= "http://schemas.xmlsoap.org/soap/encoding/" >

<RequestType>mdschema_cubes</RequestType>

<Restrictions>

<RestrictionList>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<datasourceinfo>http://www. Jbean.cn</datasourceinfo>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

<Content>SchemaData</Content>

</PropertyList>

</Properties>

</Discover>

Rowset: Returns the list of cubes under the specified data source (Jbean), Catalog (Adventure Works DW 2008);

Task two: Building a multidimensional database structure

The next step is to get a cube ( here is the final step of the task to get the Adventure Works cube ) The basic steps required for the dimension, hierarchy, and member structure.

As shown in 2, we will expand the ' Adventure Works 'Cube's dimensions and expands its sale channel dimension to the member level of the dimension. example of a step: Step1 :Gets a list of dimensions (Dimension) for the specified cube;

Mdschema_dimensions

return results

<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>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<datasourceinfo>http://www. Jbean.cn</datasourceinfo>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

<Content>SchemaData</Content>

</PropertyList>

</Properties>

</Discover>

Rowset: Returns a list of all the dimensions of cube (Adventure works) under the specified data source (Jbean) and catalog (Adventure works DW 2008);

STEP2 :Gets a list of default levels (level) for the specified dimension (Dimension);

Mdschema_dimensions

return results

<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>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<DIMENSION_UNIQUE_NAME>[Sales Channel]</DIMENSION_UNIQUE_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<datasourceinfo>http://www. Jbean.cn</datasourceinfo>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

<Content>SchemaData</Content>

</PropertyList>

</Properties>

</Discover>

Rowset: Returns a list of all levels of the dimension (Sales Channel) in the specified data source (Jbean), Catalog (Adventure Works DW 2008), and CUBE (Adventure works);

STEP3 :Gets a list of members (Member) under the specified hierarchy (level);

Mdschema_members

return results

<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>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<LEVEL_UNIQUE_NAME>[Sales channel].[ Sales Channel]. [(All)] </LEVEL_UNIQUE_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<datasourceinfo>http://www. Jbean.cn</datasourceinfo>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

<Content>SchemaData</Content>

</PropertyList>

</Properties>

</Discover>

Rowset: Returns the list of members under the specified level (all) of the dimension sale Channel; Analysis and example of XMLA Execute method

The Execute method of XMLA is simpler that is to execute the XMLA command (MDX Statement) provided in the command element and use the XMLA Rowset data type (rowset: expression for tabular results) or the XMLA Mddataset data type ( Cube: The expression used for multidimensional results returns any result data.

Example of the Execute method:

Statement of EXECUTE Command

return results

<Execute xmlns= "Urn:schemas-microsoft-com:xml-analysis"

soap-env:encodingstyle= "http://schemas.xmlsoap.org/soap/encoding/" >

<Command>

<Statement>

SELECT

NON EMPTY {{{[measures].[ Internet Sales Amount]}}} on COLUMNS,

NON Empty{{[delivery date].[ Calendar]. [Month]. Members} ' on ROWS

From [Adventure Works]

</</statement>

</</command>

<Properties>

<PropertyList>

<DataSourceInfo>http://www.JBean.cn</DataSourceInfo>

<Catalog>Adventure Works DW</Catalog>

<Format>Multidimensional</Format>

<Content>Data</Content>

<AxisFormat>TupleFormat</AxisFormat>

</PropertyList>

</Properties>

</</execute>

Executes the MDX statement under the specified data source (Jbean) and catalog (Adventure Works DW 2008) and returns:

Rowset or cube, MDX query results;

Summarize

At this point, the introduction of XMLA in this series is all over. This article will be updated on an ongoing basis (recent updates are available through http//www.bi-professional.com). As mentioned earlier, the purpose of this series is primarily to help unfamiliar colleagues to gradually apply XMLA along various chapters, and until the end of this series, it is really the starting point for actual development based on your business needs.

If you need further information during the development process, you can access the following resources:

1) XMLA1.1 specification: http://www.xmlforanalysis.com/

2) Microsoft XMLA Reference: http://technet.microsoft.com/zh-cn/library/ms186604.aspx

3) Analysis Services data access interface: http://technet.microsoft.com/zh-cn/library/ms126037.aspx

4) Multidimensional Expressions (MDX) Reference: http://technet.microsoft.com/zh-cn/library/ms145506.aspx

5) Rex (waRehouse EXplorer)-an opensource java Client for XMLA

6) SAP BI XMLA and XMLA connector:http://help.sap.com/saphelp_nw04/helpdata/en/ba/e380e03c3a4dbf8cf082f0c910f9cf/ Frameset.htm

7) Oracle Essbase-xmla Provider for Hyperion essbase:http://www.oracle.com/technology/products/bi/essbase/ Provider-services.html

XML for Analysis (XMLA) development