Developing OLAP cubes can be a fascinating task, but sometimes it can be challenging to provide customers with a seamless way to access and analyze the data. In this article, we will discuss how to expose SQL Server Analysis Services (SSAS) OLAP cubes over the internet using HTTP.

Problem

Imagine a scenario where a client has their production data center setup and their front-end SAAS application directly connected to the databases hosted on a private data center. Due to security restrictions, they do not allow any inbound traffic to be connected to the databases directly. The existing SSAS database embedded in their application only provides static reports, and any additional reports require intervention from the IT team.

Solution

To address this problem, we can use a utility provided by Microsoft called MSMDPUMP, which is shipped with SQL Server Analysis Services. This utility allows us to expose SSAS OLAP cubes over the internet using HTTP.

Prerequisites

Before enabling HTTP access to the OLAP server, we need to ensure that Internet Information Services (IIS) is properly configured. This can be done by following these steps:

1. Click Start and select “Turn Windows Features on or off”.
2. Navigate to Internet Information Services, expand it, and then expand World Wide Web Services.
3. Check the boxes against CGI and ISAPI Extensions under Application Development Features.
4. Expand Security and check the boxes against Basic Authentication and Windows Authentication.

Preparing the IIS Server

Once IIS is up and running, we need to configure it to support the transfer of data between the end-user and the OLAP database. Follow these steps:

1. Navigate to the IIS root folder (e.g., “C:\inetpub\wwwroot\”).
2. Create a new folder named “olap” under “wwwroot”.
3. Copy the necessary libraries provided by Microsoft from the default SQL Server Analysis Services installation directory (e.g., “C:\Program Files\Microsoft SQL Server\MSAS14.MSSQLSERVER\OLAP\bin\isapi”) into the “olap” directory.

Creating the Application Pool and Virtual Directory in IIS

Next, we need to create an Application Pool for the web application that will expose the SSAS OLAP Server over the internet. Follow these steps:

1. Open “inetmgr” by clicking Start and running it.
2. Expand Sites and then Default Web Site to locate the “olap” directory.
3. Right-click on Application Pools and select Add Application Pool.
4. In the “Add Application Pool” dialog box, enter “olap” as the name and select Classic as the Managed pipeline mode.
5. Click OK to create the Application Pool.
6. Right-click on the “olap” directory, select “Convert to Application”, and follow the prompts to run the application under the newly created Application Pool.

Configure Authentication in IIS

Once the application is created, we need to configure authentication for it. Follow these steps:

1. Select the application “olap” on the left-hand panel and double-click Authentication.
2. Enable Windows Authentication and Basic Authentication, and disable Anonymous Authentication.

Adding a Script Map for the Pump DLL

Now that the application is authenticated, we need to map it to use the MSMDPUMP.dll file. Follow these steps:

1. Select the application “olap” on the left-hand panel and double-click Handler Mappings.
2. Right-click inside the panel and select “Add Script Map”.
3. In the “Add Script Map” dialog box, enter “*.dll” as the Request Path and browse for the MSMDPUMP.dll executable.
4. Provide the name of the map as “olap” and click “Yes” to allow the ISAPI extension.

Connect to the SSAS Instance using HTTP (using SSMS)

Once all the above configurations are completed, we can now connect to the instance of Analysis Services using HTTP. Follow these steps:

1. Start SQL Server Management Studio and enter the Server Name as “http://<>/olap/msmdpump.dll”.
2. You should now be able to connect to the instance and browse all the databases and cubes.

Takeaway

This article has described how to configure IIS to allow HTTP connections to a local instance of SSAS Multidimensional. The steps are similar for a Tabular instance as well. By following these steps, you can expose your SSAS OLAP cubes over the internet, allowing users to perform analysis without any intervention from the IT team.

For more information, please refer to the official documentation from Microsoft.