THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Creating a Linked Server for Analysis Services

I think I’ve finally recovered (in more ways than one!) from PASS Summit 2010 in Seattle a mere few weeks ago. I seem to be eternally plagued with computer problems at every conference I attend, but always in different ways. Accordingly, I have devised different strategies for anticipating and coping with problems. I foiled this last disaster by having my slides and demos duplicated on Erika’s laptop which I had to call into action literally seconds before the session began due to a hard drive failure, but missed one little detail which was actually a last minute bonus addition to my session on Analysis Services Stored Procedures (ASSP). So it wasn’t the end of the world, but it was a forehead slapping moment!

As part of that session, I told a story of how I came to appreciate the use of ASSP for a client project in which I had to dynamically construct MDX queries on the fly using T-SQL stored procedures and pass the resulting query into an OPENQUERY function. The problem that I had in that situation was the limitation imposed by the OPENQUERY function – I  had to write my MDX query in 8000 characters or less! Erika and I worked out all kinds of ways to live within that constraint before we tried ASSPs, but ultimately the complexity of these queries kept pushing over the query string over the limit and thus began our adventures with ASSP. I’ll add a post on this technique to my series on Using Dynamic MDX in Reporting Services soon. (The first post describes the use of StrToMember() and StrToSet() and the second post shows how to use MDX in a dynamic OLE DB query.

I had a few extra minutes of time in my session, so I thought I’d show the audience “the rest of the story” by demonstrating how I could use the OPENQUERY function with the ASSP that I created earlier in the session. I  had the OPENQUERY working on my laptop just a few minutes before the session started, but in preparing Erika’s laptop, I didn’t think about setting up a linked server to Analysis Services in advance. No worries – I’ll just do it live! Well, my memory failed me on how to fill out the New Linked Server dialog box in Management Studio, so I asked the audience if anyone knew what to do and the reply I got was, “It’s a mystery!” (It took me a few times to realize that’s what they said – I was suffering from the “SQL plague” and my ears were really plugged up!)

Nothing a little search engine won’t resolve, right? Well, I turned up a few hits (like the Books Online entry Adding a Linked Server), but nothing was particularly helpful for interpreting which parameters are really needed and which are optional – especially when I was on the spot! So my post today is my contribution to resolving the mystery. Of course, now that I’m offstage and working on my replaced hard drive and with a much less congested head, I was able to set up a linked server quite quickly.

To set up a linked server for Analysis Services, follow these steps:

  1. Connect to the Database Engine in Management Studio.
  2. Expand Server Objects, right-click Linked Servers, and click New Linked Server.
  3. Define a name for the linked server, and set the following options as shown in the screenshot below:
  • Provider: Microsoft OLE DB Provider for Analysis Services 10.0
  • Product name: MSOLAP.4
  • Data source: (the name of your server)
  • Catalog: (the name of the Analysis Services database)


To test the linked server, you can write a query like this:

select * from openquery(AdventureWorksOLAP, 'select [Measures].[Sales Amount] on columns from [Adventure Works]')

A couple of things to note - This query is executed as a database engine query, not as an MDX query. The first argument of the OPENQUERY is the name of your linked server.

Published Tuesday, November 30, 2010 4:30 PM by smisner
Filed under: , ,



Lupe said:

Great article exactly what i was looking for

April 15, 2013 11:59 AM

Dimitri said:

Is that a way to create linked server to tabular model supporting Dax queries?

June 24, 2013 11:10 PM

Brian said:

I've posted an alternative solution to this on codeplex that allows sending nvarchar(max) queries to any OLAP server on your network.

No more setting up linked servers to every OLAP database. No 8000 character query limit. No single column set returned when the result is empty. Only a strongly typed result.

August 15, 2013 11:48 PM

ms said:

great info, now we can export data to a csv file via a specific customer mdx query relative to their needs. Wasted so much time with dodgy ssis packages, this is much simpler.

September 2, 2013 9:20 PM

ms said:

also created an easier way to get data out:

alter procedure dbo.GetOLAPdata_byMDX (

@OLAPdb nvarchar(max),

@OLAPmdx nvarchar(max)

) as

-- extracts data from olap cube and returns in resultset format via sql server


DECLARE @SQL nvarchar(max) =''

if not exists(select * from sys.servers where name = @OLAPdb)


EXEC master.dbo.sp_addlinkedserver @server = @OLAPdb, @srvproduct=N'MSOLAP.4', @provider=N'MSOLAP', @datasrc=N'localhost', @catalog=@OLAPdb


set @SQL = 'select * from openquery(['+@OLAPdb+'],'''+@OLAPmdx+''')'

exec sp_executesql @SQL

--print @SQL

September 2, 2013 10:50 PM

rohit said:

How can i run the XMLA request through linked server.

March 18, 2014 7:00 AM

ob said:

I am sorry, I am getting an error

"Server 'XXXX' is not configured for DATA ACCESS."

Thank you

August 4, 2014 4:07 PM

Abhishek said:

Thanks! Really great inforamtion

November 27, 2014 1:32 AM

Abhishek said:

Thanks! Really great information

November 27, 2014 1:32 AM

Jimuta Bahan Sahu said:

It is working if i am in the same network. Now I am using a static ip but it is not working showing some error.

"OLE DB PROVIDER SQLNCLI11 for linked server "my server" return message  Login Time Out Expired"

January 21, 2015 7:48 AM

Jigyashu Keshri said:

Very Direct. Liked the solution. Thank you

February 19, 2015 7:45 AM

Richo said:

Thanks Stacia - fantastic - got me over the line after tearing my hair out trying to get this to work.

Also really enjoying your Advanced Integration Services videos on Pluralsight - great stuff.

September 16, 2015 12:04 AM


Hi Stacia,

I have written a tsql query which uses mdx query using OpenRowSet. SQL Server database and Analysis server database are on the same server.I want to know, what is the security threat in creating a linked server (which is required for communication between sql server db and analysis server db).



October 22, 2015 1:10 PM

Hari said:

Please help me to create linked server to Tabular Cubes.

November 5, 2015 3:00 AM

Simon N said:

Extracting data from a cube using this method is very slow - 30 minutes on 2 million rows (~20 fields).  Does anyone know of a better alternative?

June 15, 2016 3:20 PM
New Comments to this post are disabled
Privacy Statement