Configure web server to access SSAS cube using Excel

This post contains step-by-step instructions for configuring web server in order to access cube using excel. After successful configuration users can access the cube using excel and can create reports using cube data.

1. Connect to the server on which you want to configure web server for accessing cube using excel.
2. Create a folder named CUBE under C:\Inetpub\wwwroot.
3. Copy all the contents from the folder C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\isapi into the C:\Inetpub\wwwroot\CUBE directory.
4. Connect to “Internet Information Services” console. Follow following steps for the same. Click on “Start” -> click on option “Run” -> type “inetmgr” and press OK button. Refer following screen shot for the same.

     5. When you click on OK button, it will open an “Internet Information Services (IIS) Manager” console.   IIS Manager Console looks like following one.          

     6. Create “Application Pool”: 

• Right click on “Application Pools” node and select option “Add          Application Pools”. Refer following screen shot for the same.

You will get following screen when you click on “Add Application Pool”. Mention following details       under “Add Application Pool” window.

• Name: CUBE

• .Net Framework version: .Net Framework v2.0.50727

• Managed pipeline mode: Classic

• Start application pool immediately should be in a checked state.

         

      7. Convert to Application:

• Expand “Sites” folder and then expand “Default website” node. Right click on “CUBE” folder and select option “Convert to Application”. Refer following screen shot for the same.

• When you click on “Convert to Application”, it opens “Add Application” form.

• Click on Select button of “Add Application” window and that will open “Select Application Pool” window. Select “CUBE” under “Application Pool” drop down and click OK button.

• When you click on OK button, you will find that CUBE folder now appears as an application. Refer following screen for the same.

       8. Directory Property settings:

• Click on CUBE node and double click on “Handler Mappings” option. Refer following screen shot for the same.

• When you double click on “Handler Mappings” option, you will find following screen.

• Click on “Add Script Map” option which will open “Add Script Map” window. Insert the details as per following screen shot.

• When you click on OK button, you will get following message box. Click Yes.

       9.  Setting Authentication:

• Select CUBE node under IIS manager console. Double click on “Authentication” option.

• When you double click on “Authentication” option that will open Authentication details screen which looks like following one.

• Right click on “Anonymous Authentication” and select “Edit” option. When you click on “Edit” option, it opens “Edit Anonymous Authentication Credentials” window which looks like following one.

• Click on “Set...” button and insert credentials of user.

       10. Change Binding settings:

• Click on “Default Web Site” under IIS Manager Console and click on “Bindings” option. Refer following screen shot for the same.

• When you click on “Bindings” option, it will open “Site Bindings” window. Select a row which contains Port 80 and click on “Edit” button. Refer following screen for the same.

• When you click on “Edit” button, it will open “Edit Site Binding” window. Change Port to 8081 and click on OK button.

        

         11. Start “Actions”:

• Select “Default Web Site” node under IIS manager console. On the right side there is “Actions” pane, click on Start option.       

Conclusion: You have successfully established the configuration settings of web server and now user can access cube using excel.