Connecting to Analysis Services with HTTP or HTTPS

Analysis Services allows you to connect via a web service using SOAP, but there's not alot of documentation on this. Improved Web Connectivity in Microsoft SQL Server 2000 Analysis Services is an MSDN article that explains some of the technical requirements.

Not having this set up is the cause of alot of connectivity problems. For example, pivot tables from Office web components, which get embedded in a web page, will fail to connect from a remote machine unless this is working correctly. Other Excel based components that try to access an Analysis Services server over a network (usually by being embedded in a web page) will also not work without it. For example, in MS Project Server, if you can only view the Portfolio Analyzer views when logged in to the actual analysis services server, this is the cause.

Fixing it is easy if you know what you are doing. There is a file called msolap.asp included as part of the Analysis Services installation, usually under \Program Files\Microsoft Analysis Services\ . This file needs to be placed in the web root of the same machine (yes, IIS needs to be running). Then voila, it magically works.

There is one major exception - if Sharepoint is installed on the same server (as it sometimes is, and Project Server requires Sharepoint). In this case, msolap.asp must be excluded from the managed paths for the Sharepoint (this is done under the Sharepoint administration for the virtual server ("Define Managed Paths"). Exclude msolap.asp, and it will be clear to get out and do it's job.