Introduction
You could easily connect to a remote SQL Server Database from SQL Server Management Studio. However, connecting to an Analysis Service Database needs a little more effort. The rest of this article explains how to configure an Analysis Server for HTTP access and how to connect to it using SQL Server Management Studio.
Configuring SSAS 2005
The first step is to configure an Analysis Service instance for HTTP access. The configuration process is different for Windows Server 2003 and Windows XP. There are two great articles at Technet which explains the configuration process in detail.
Based upon the type of machine you have, you could follow one of the steps above to configure SSAS for HTTP access. After you
complete the process, make sure that everything is configured correctly by opening your browser and navigating to
http://localhost/olap/msmdpump.dll. If everything is configured correctly, your
browser should display a message similar to the following:
There are chances that some of you may see a different screen when you try to open the above URL on your browser. You could probably get
an HTTP 500 error and your screen may look like the following:
It happens if you have turned on the IE settings to show "friendly error messages". You could turn this off by going to "Tools => Options => Advanced"
and uncheck the check box.
After you have turned off the "friendly error" display settings, you would see the SOAP/XML response on your browser when you navigate to
http://localhost/olap/msmdpump.dll if SSAS is correctly configured on IIS.
Connecting to SSAS database
The next step is to connect to the SSAS database from SQL Server Management Studio. Open Management Studio and click on the "connect" button on top
object explorer.
This will open the connection dialog. Enter the URL at which you have configured SSAS for HTTP access. I have SSAS configured on a machine named
TOSHIBA-USER and the name of my virtual directory is olap and hence I would enter the following URL:
http://TOSHIBA-USER/olap/msmdpump.dll
Click "connect" button and Management Studio will connect to the Analysis Server and will display the objects in the serve explorer window.
Conclusions
I had come across a lot of issues while trying to configure SSAS on my machine. I hope the details given above will help some of you
and save a few hours.
