THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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)

image

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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.

https://olapextensions.codeplex.com/

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

SET NOCOUNT ON;

DECLARE @SQL nvarchar(max) =''

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

begin

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

end

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

exec sp_executesql @SQL

--print @SQL

September 2, 2013 10:50 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement