Access Analysis Services over Internet

Published on
4,462 Points
Last Modified:
Vikas Garg
I am a BI developer having 10 Years of experience with the BI technologies like SSIS, SSAS, Power BI ,SQL Server and SSRS since 2008.
The SQL Server Analysis Service is a popular tool in the field of Business Intelligence used for Analysis of the Data Warehouse by making a cube out of it.

One of its features that can be utilized for a brorad range of functions is Accessing Analysis Service over the Internet.

One can configure the Analysis Service using Microsoft's Internet Information Service (IIS) so the service will be accessible over HTTP (Internet), not over TCP/IP.

It's important to configure Analysis Service over HTTP when
  1. Client can not access Analysis Service over TCP/IP.
  2. Client connection are from non-trusted domains.
  3. Your Java application running on a non-Windows platform like Unix.
When we configure SSAS over IIS, we create an application/service for IIS that requires a destination folder containing MSMDPUMP.dll, which is the heart of the configuration. So we will create a folder on the IIS machine and then copy some files to this folder.
  • I have created OLAP folder to the machine as shown below.

  • Now we need to copy MSMDPUMP.dll and related files from Analysis Service folder C:\Program Files\Microsoft SQL Server\MSAS11.SQL2012\OLAP\bin\isapi to the Destination folder OLAP we just created.

  • From the above files we just need to modify configuration in the msmdpump.ini file. We will be required to change the ServerName tag from Localhost to Out Analysis Service Machine and and Instance name. Here the machine name is DSK-822 and the Instance is SQL2012 for DSK-822\SQL2012.
  • To configure the same in IIS we need to create an application pool that will be used in this application. I have created the Application pool named OLAP with the pipeline mode to classic. Click OK and your AppPool is created.

  • Now we need to add the application to the default website. Right click on Default Website, select Add Application. The Add Application Dialog Box will open; give the Application a suitable Alias Name and select the Application pool OLAP we just created for the application. Also give the physical path of our destination folder OLAP in which we copied MSMDPUMP.dll files.
  • This will create OLAP application. Select this OLAP application and open Authentication tab.
  • In the authentication tab Enable Windows authentication and disable Anonymous Authentication.
  • Now we need to add a script map so the MSMDPUMP.dll can be accessed through IIS for it. Double Click on Handler mapping.
  • Click on Add Script Map and in the Request path add *.dll, in the Executable give the path of MSMDPUMP.dll of OLAP folder. and give the map a suitable name and click OK.
  • Now that the configuration part is over let's test it. The URL for the application will be http://localhost/OLAP/MSMDPUMP.dll
  • To test it open SSMS and in the Server Type select Analysis Services and in the Server name enter this URL and connect. If you are able to connect than that's it.
  • You can also use IP address of your system in place of localhost too.
Author:Vikas Garg

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month