December 10, 2009

Querying SSAS with SQL

I spent the better part of two days attempting to set up a SQL Server Analysis Services (SSAS) server as a linked server to a master database, with the chief goal of querying financial data for display through a ColdFusion web interface. My version of ColdFusion does not easily support an MSOLAP connection, but a little research turned up an ostensibly easy method for querying SSAS by way of normal SQL Server data source. Simply adding the data repository as a linked server failed spectacularly and produced a number of infuriating and worthless error messages, not limited to:

OLE DB provider "MSOLAP" for linked server "<servername>" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP" for linked server "<servername>" returned message "The peer prematurely closed the connection.".

Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "<servername>".

After combing through a plethora of confused Google results, giving up in frustration and starting again fresh the next day, I managed to get it working; I figured I'd catalog my trials for posterity. I'm working on Microsoft SQL Server 2005, 64-bit Enterprise edition. Any text contained in < >s is a placeholder for plaintext.

sp_addlinkedserver
@server='<SSAS server name or handle>',
@provider='MSOLAP',
@srvproduct = '',
@datasrc='<actual server name>',
@catalog='<database with the cubes you want to query>'

Innocuous enough, but I was stuck here for several reasons. I am an SSAS/MDX newbie and many suggestions insisted that the SSAS database must be wrapped in [ ]s — wrong. My connection defaulted to a different database when I didn't specify an initial catalog and it outright failed when I included the brackets. Is there an MDX command to switch databases? Apparently not.

Additionally, the linked server defaults to the current security context to make the connection, which is a problem when you're logged in as sa (best practices fail, I know). For whatever reason, you can only use an Active Directory (AD) account to log into SSAS. You can check this setting in the Properties menu (Security tab) for your freshly created linked server. You can probably do this with the provider string parameter to sp_addlinkedserver, too, but I wasn't sure if I'd need a complete provider string or if I could just use

User Id=<domain\username>&Password=<password>
and I was in no mood to experiment. At first I received a delegation error, which led me on a goose chase for a solution in AD, but the AD account just needed the proper permissions on the SSAS server (I set mine up in the serveradmin role, but that is probably overkill).

Finally, in order for

select *
from openquery(<SSAS server handle>, '<MDX query>')
to work, I needed to go into Server Objects > Linked Servers > Providers > MSOLAP > Properties and Enable "Allow Inprocess".

And now I can query an SSAS server through an already existed ColdFusion DSN for the master database.

6 comments:

James Zimmerman said...

This helped me. Thanks!
I had previously scripted the creation of the server from my Development server and it wouldn't work, but following your script worked for me.

Christopher John said...

I'm glad it helped!

Dave Phillips said...

FYI for anyone else who came across this because they have the same issue, but doing it the above way doesn't help. I continued to get the 'peer prematurely closed the connection' issue. What I finally determined (almost by chance) is that it was because I was setting the linked server up from a client workstation utilizing SQL Server Management studio to connect to the SQL Server remotely. Once I remote desktopped into the server (the server on which I was setting up the 'linked server' connection) and launched SQL Server Management studio there and then tested the connection, it was successful. So, if you're trying to do this remotely, stop and remote desktop in and your headache will go away. :)

Naresh Ravula said...

Hello CHRISTOPHER JOHN,
I tried what ever u said,but after did these changes i get an error i.e,
Microsoft SQL Server: Error 7373.
canu please suggest me to fix this issue.

Thanks,
Naresh Ravula

Rick Davis said...

This is the solution all over the net for CF to connect to MSOLAP, but does anyone know if there is a way to cut out the middleman and go straight from CF to MSOLAP?

I am not a CF developer, I am a SQL Development DBA trying to get a load of CF developers to start working with cubes. Its no fun I tell you!

Christopher John said...

Hi Rick,

I'm no longer worker with CF or MSOLAP, so I'm afraid I won't be much help. If I recall correctly, there was a way to hook CF directly to an OLAP data source, but I never had the time to fiddle with it (I was in the same position you are, DBA turned rogue MSOLAP dev).

Good luck!
Chris