Client Architecture Requirements for Analysis Services Development

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Microsoft SQL Server SQL Server Analysis Services supports a thin-client architecture. The SQL Server Analysis Services calculation engine is entirely server-based, so all queries are resolved on the server. As a result, only a single round trip between the client and the server is required for each query, resulting in scalable performance as queries increase in complexity.

The native protocol for SQL Server Analysis Services is XML for Analysis (XML/A). SQL Server Analysis Services provides several data access interfaces for client applications, but all of these components communicate with an instance of SQL Server Analysis Services using XML for Analysis.

Several different providers are provided with SQL Server Analysis Services to support different programming languages. A provider communicates with an SQL Server Analysis Services server by sending and receiving XML for Analysis in SOAP packets over TCP/IP or over HTTP through Internet Information Services (IIS). An HTTP connection uses a COM object instantiated by IIS, called a data pump, which acts as a conduit for SQL Server Analysis Services data. The data pump does not examine the underlying data contained in the HTTP stream in any way, nor are any of the underlying data structures available to any of the code in the data library itself.

Logical client architecture for Analysis Services

Win32 client applications can connect to an SQL Server Analysis Services server using OLE DB for OLAP interfaces or the Microsoft® ActiveX® Data Objects (ADO) object model for Component Object Model (COM) automation languages, such as Microsoft Visual Basic®. Applications coded with .NET languages can connect to an SQL Server Analysis Services server using ADOMD.NET.

Existing applications can communicate with SQL Server Analysis Services without modification simply by using one of the SQL Server Analysis Services providers.

Programming Language Data Access Interface
Visual Basic 6 ADO MD
.NET languages ADO MD.NET
Any language that supports SOAP XML for Analysis

SQL Server Analysis Services has a Web architecture with a fully scalable middle tier for deployment by both small and large organizations. SQL Server Analysis Services provides broad middle tier support for Web services. ASP applications are supported by OLE DB for OLAP and ADO MD, ASP.NET applications are supported by ADOMD.NET. The middle tier, illustrated in the following figure, is scalable to many concurrent users.

Logical diagram for middle-tier architecture

Both client and middle tier applications can communicate directly with SQL Server Analysis Services without using a provider. Client and middle tier applications may send XML for Analysis in SOAP packets over TCP/IP, HTTP, or HTTPS. The client may be coded using any language that supports SOAP. Communication in this case is most easily managed by Internet Information Services (IIS) using HTTP, although a direct connection to the server using TCP/IP may also be coded. This is the thinnest possible client solution for SQL Server Analysis Services.

Analysis Services in Tabular or SharePoint Mode

In SQL Server 2017, the server can be started in VertiPaq in-memory analytics engine (VertiPaq) mode for tabular databases and for Power Pivot workbooks that have been published to a SharePoint site.

Power Pivot for Excel and SQL Server Data Tools are the only client environments that are supported for creating and querying in-memory databases that use SharePoint or Tabular mode, respectively. The embedded Power Pivot database that you create by using the Excel and Power Pivot tools is contained within the Excel workbook, and is saved as part of the Excel .xlsx file.

However, a Power Pivot workbook can use data that is stored in a traditional cube if you import the cube data into the workbook. You can also import data from another Power Pivot workbook if it has been published to a SharePoint site.


When you use a cube as a data source for a Power Pivot workbook, the data that you get from the cube is defined as an MDX query; however, the data is imported as a flattened snapshot. You cannot interactively work with the data or refresh the data from the cube.

Interfaces for Power Pivot Client

Power Pivot interacts with the VertiPaq in-memory analytics engine storage engine within the workbook by using the established interfaces and languages for Analysis Services: AMO and ADOMD.NET, and MDX and XMLA. Within the add-in, measures are defined by using a formula language similar to Excel, Data Analysis Expressions (DAX). DAX expressions are embedded within the XMLA messages that are sent to the in-process server.


Communications between Power Pivot and Excel use the MSOLAP OLEDB provider (version 11.0). Within the MSOLAP provider, there are four different modules, or transports, that can be used for sending messages between the client and server.

TCP/IP Used for normal client-server connections.

HTTP Used for HTTP connections via the SSAS data pump service, or by a call to the SharePoint Power Pivot Web Service (WS) component.

INPROC Used for connections to the in-process engine.

CHANNEL Reserved for communications with the Power Pivot System Service in the SharePoint farm.

See Also

OLAP Engine Server Components