How to configure HTTP access to the TFS Analysis Services Cube

This is a fairly obscure Team Foundation Server (TFS) and SQL Server Analysis Services (SSAS) configuration, but if you find yourself in this situation, it is another option for making the TFS Analysis Services cube available to your users. I would be surprised if it was ever tested by the TFS product team, but I can confirm that It Works On My Machine and doesn't require any dubious registry changes.

The main configuration comes from this MSDN article: Configure HTTP Access to SQL Server Analysis Services on IIS 7.0, but this blog posts goes in to the TFS specific steps.

This approach provides an alternative means for connecting to Analysis Services when your OLAP solution calls for the following capabilities:

• Client access is over Internet or extranet connections, with restrictions on which ports can be enabled. Or, client connections are from non-trusted domains in the same network.
• Client application runs in a network environment that allows HTTP but not TCP/IP connections.
• Authentication methods other than Windows integrated security are required. IIS supports Anonymous connections and Basic authentication. Configuring Analysis Services for HTTP access lets you use these alternative authentication methods with Analysis Services.

Note: The MSDN article and this blog post don't talk about how to configure using a HTTPS (SSL) connection. However, I don't see any reason why it wouldn't work - I just haven't tried it.

Configuring the SSAS ISAPI Plugin

• Copy the contents of C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi

Note: The directory may be different, based upon the version, name and configuration of your SSAS instance.

• To: C:\Inetpub\OLAP

• Open Internet Information Services (IIS) Manager
• Navigate to \Sites\Team Foundation Server
• Right-Click 'Team Foundation Server' and choose 'Add Application…'

• On the 'Add Application' dialog, enter the following information:
• Alias: olap
• Physical path: c:\inetpub\olap
• Application Pool: Team Foundation Server Application Pool (default)

Now we need to configure the Handler Mappings and Authentication.

• First, open 'Handler Mappings'

• From the Actions pane, select 'Add Script Map…'

• On the 'Add Script Map' dialog, enter the following information:

• Request path: msmdpump.dll
• Executable: c:\inetpub\olap\msmdpump.dll
• Name: olap

• On the dialog that appears, select 'Yes'. This will allow this particular extension to execute.

• Back on the /olap Feature configuration screen, select 'Authentication'

We want to make the following changes to allow remote clients to authenticate as themselves to SSAS (Windows Authentication), rather than using the identity of the TFS application pool (Anonymous Authentication).

• Anonymous Authentication: Disabled

• Windows Authentication: Enabled

• Basic Authentication: Disabled (see note below)

Connecting to a remote SSAS server

At this point, you have added an additional way to access a SQL Server Analysis Services server that is hosted on the same server as your TFS application tier (i.e. a single-server installation).

If you SSAS server is on another machine (i.e. a dual-server or complex environment), then you can also configure the ISAPI extension to connect to that remote server.

Note: This topology adds a double-hop authentication step, where credentials must flow from the client to the web server, and on to the backend Analysis Services server. If you are using Windows credentials and NTLM, you will get an error because NTLM does not allow delegation of client credentials to a second server. The most common solution is to use Basic authentication with Secure Sockets Layer (SSL), but this will require users to provide a user name and password when accessing the MSMDPUMP virtual directory. A more straightforward approach might be to enable Kerberos and configure Analysis Services constrained delegation so that users can access Analysis Services in a transparent manner.

The ISAPI extension uses the Analysis Services OLE DB provider to connect to the SSAS server. You will need to install this provider if it is not installed already. For a TFS Application Tier, it will be already installed as part of the SQL Client Connectivity components that the TFS installation wizard checks for.

Microsoft SQL Server 2008 R2 SP1 Feature Pack download page

• SQLSERVER2008_ASOLEDB10.msi (x64) (54.3 MB)
• SQLSERVER2008_ASOLEDB10.msi (x86) (23.9 MB)

Open C:\Inetpub\olap\msmdpump.ini and modify the ServerName setting.

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>100</ConnectionPoolSize>

</ConfigurationSettings>

Verifying the HTTP endpoint from Excel

With this additional endpoint configured we can now try and connect manually from Excel.

• Open Excel

• Select Data > From Other Sources > From Analysis Services (ALT-A-FO-Down-Enter)

On the 'Data Connection Wizard' dialog, specify the path to the ISAPI plugin as the server name. It's not intuitive to put a URL in a place where it's expecting a server name, but this is a supported value for the connection string.

Server name: http://yourtfsserver:8080/olap/msmdpump.dll

If everything is working correctly, you should see the following dialog:

Configure TFS to use the ISAPI extension

On a Team Foundation Server Application Tier, open the Team Foundation Server Administration tool and navigate to the Reporting configuration screen. It should look something like this:

• Select the 'Edit' action link, then select 'OK' on the dialog that is advising you that warehouse/cube processing will be stopped.

• Select the 'Analysis Services' tab and update the server settings to the path for the ISAPI extension. You will also need to re-enter the password for the data source.

Note: If your TFS server has been configured to allow access over the Internet, it's important to configure this server name to an address that users can reach. This server name is what Excel will use when you choose 'Create Report' in either Excel or Team Explorer. It is also used for the TFS data sources in Reporting Services. For example, use http://tfs.yourcompany.com:8080/olap/msmdpump.dll instead of http://tfs01:8080/olap/msmdpump.dll

• Once you select OK, the settings will be saved. You will then need to select the 'Start Jobs' link to start the warehouse/cube processing jobs again. You configuration page will look something like this:

Verifying the TFS Cube Configuration in Excel

Now that we have configured TFS to use the ISAPI extension endpoint, we can use the TFS plugin for Excel to create a connection to the SSAS server, without having to tell users how to manually connect.

• Open a blank workbook in Excel

• Select Team > New Report from the menu.

• On the 'Connect to Team Project' dialog, select your server, collection and project. Select 'Connect'
• On the 'New Work Item Report' dialog, select a work item query that you want to create a report from.

• Once the report is generated, you will now have a workbook that is configured with a data source connection to the TFS SSAS database.
• In the PivotTable Field List, you will see all the available fields for reporting on.

• If you want to view the data source that the TFS Excel add-in generated, you can go to Data > Properties (ALT-A-P) on the menu, then select the 'Definition' tab. If you look closely, you will find the path to the ISAPI extension is listed in the connection string.

If you have an environment that could use this configuration, and you want to see it supported by the product team, you can vote for it and add comments on the TFS UserVoice site.