Setting up a Linked Server

Open SQL Server Management Studio

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.

In the Object Explorer, expand the tree: Database instance -> Server Objects. Right click Linked Servers, and click New Linked Server.

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

Click the Security page, and select appropriate authentication options (consult your SQL Server DBA for details).

Click the Server Options page, and make any required changes changes.

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.

For an example on an MDX query that includes filtering, please refer to the SSAS Example Query (MDX) page.

MDX query executed with OPENQUERY

Rename Columns

To rename columns from the OpenQuery, use double quotes over the column names and then state your as column name. Eg:

SELECT
"[Customer].[Education].[Education].[MEMBER_CAPTION]" as [Education],
"[Measures].[Internet Order Quantity]" as [Order Quantity],
"[Measures].[Internet Sales Amount]" as [Sales Amount]
FROM OPENQUERY(ADVENTUREWORKSOLAP,
'SELECT
NON EMPTY
{
	[Measures].[Internet Order Quantity],
	[Measures].[Internet Sales Amount]
} ON COLUMNS,
NON EMPTY
{ (
	[Customer].[Education].[Education].ALLMEMBERS
) } ON ROWS
FROM [Adventure Works]');
');