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:
- Connect to the Database Engine in Management Studio.
- Expand Server Objects, right-click Linked Servers, and click New Linked Server.
- 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.