Configuring Connections: Microsoft SQL Server Analysis Services (SSAS) using Linked Servers

Versions: 8.0+

When connecting to SQL Server Analysis Services, the standard approach is to connect via XML/A web services using the olap4j driver bundled with QuerySurge. In some environments, however, XML/A web services are not enabled, and an alternate method is required to query SSAS. This article describes the steps for creating a Linked Server in a SQL Server database engine instance, and the steps to forward an MDX query to Analysis Services through the linked server.

To create an SSAS Connection using Linked Servers, you will need the following information (check with a DBA or other knowledgeable resource in your organization):

• Database engine login credentials (ID and Password)
• SSAS server name
• SSAS catalog name
• Linked Server name (to be provisioned below)

Creating a Linked Server

As an alternative to connecting via XML/A web services directly to SSAS, a Linked Server can be created in a Database Engine instance. For authentication, this linked server can use credentials either of the Database Engine's service account, those of a user connected to the instance, or explicitly specified credentials.

Note: These steps required sysadmin-level access to SQL Server, and should be performed by or in consultation with a DBA. For more information, see Microsoft's documentation on Linked Servers.

To create a Linked Server using, follow these steps:

1. Open SQL Server Management Studio.
2. Connect to the appropriate Database Engine instance, with a user that either has the CONTROL SERVER permission or is a member of the sysadmin group.
3. In the Object Explorer, expand the tree: Database instance -> Server Objects. Right click Linked Servers, and click New Linked Server.
4. Enter the linked server properties:
   • Linked server: enter an identifier that will be referenced when writing queries against the linked server
   • Server type: Other data source
   • Provider: select the latest version of Microsoft OLE DB Provider for Analysis Services
   • Product name: MSOLAP.4
   • Data source: name of the SSAS instance
   • Catalog: SSAS catalog name
     
5. Click the Security page, and select appropriate authentication options (consult your SQL Server DBA for details).
             
6. Click the Server Options page, and make any required changes changes.
7. Click OK. The new Linked Server will appear in the folder.

Querying the Linked Server

With a Linked Server configured, the Database Engine can now act as a proxy to SSAS by using the T-SQL OPENQUERY function in your SQL. The OPENQUERY function takes as arguments the Linked Server name and an MDX query (as a string), and returns a table object that can be manipulated in a SQL SELECT statement like a traditional table. Note that the query string is limited to 8000 characters in length.

Following are examples of an MDX query: a) run in Management Studio directly on SSAS, and b) executed through the Linked Server.

Direct MDX query

MDX query executed with OPENQUERY

Connecting QuerySurge to the Database Engine

The Database Engine connection should be configured in QuerySurge like a normal MS SQL Server connection - see the appropriate guide for Windows Authentication or Database Authentication. Once a connection is created, a regular QueryPair can be used to test SSAS data against any other datastore. Note that the Source query is a regular SQL query directly against a SQL Server instance, while the Target query is an MDX query, issued through the Linked Server via the OPENQUERY function.

Note: The OPENQUERY function documentation is available here.

Important Usage Note: While MDX queries can be elaborate and may return elaborately structured resultsets, QuerySurge is designed to work in a "tabular" world. So, QuerySurge (and the Linked Server!) expects all queries including MDX queries to return 2D resultsets that conceptually look like regular "tables". If you are dealing with data that is more complex, then the solution is usually to write multiple QueryPairs to cover your test requirements.