Access Analysis Services over Internet

Vikas GargData Architect
I am a Data Architect having 14+ Years of experience with the BI technologies like Azure, SSIS, SSAS, Power BI ,SQL Server and SSRS and DW.
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.
Vikas GargData Architect
I am a Data Architect having 14+ Years of experience with the BI technologies like Azure, SSIS, SSAS, Power BI ,SQL Server and SSRS and DW.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.