Recently I had to setup an Analysis Services cube and expose it to external users. This is usually done by using Internet Information Server (IIS) and creating a new WebSite which hosts msmdpump.dll. This DLL more or less wraps XMLA commands inside HTTP thus allowing external users to access the cube via HTTP. Besides Windows Authentication this setup also allows Basic Authentication and so external users can simply connect by specifying Username and Password in e.g. Excel when connecting to the cube:
There are already a lot of whitepapers out there which describe how to set things up correctly. Here are just some examples:
– MSDN: http://msdn.microsoft.com/en-us/library/gg492140.aspx
– MSBI Academy (great video!): http://msbiacademy.com/?p=5711 by Rob Kerr
They provide very useful information and you should be familiar with the general setup before proceeding here or using the final PowerShell script.
The PowerShell script basically performs the following steps:
- Create a local folder as base for your WebSite in IIS
- Copy SSAS ISAPI files (incl. msmdpump.dll) to the folder
- Create and Configure an IIS AppPool
- Create and Configure a IIS WebSite
- Add and enable an ISAPI entry for msmdpump.dll
- Configure Authentication
- Configure Default Document
- Update connection information to SSAS server
I tested it successfully with a clean installation of IIS 8.0 (using applicationhost.config.clean.install). In case you already have other WebSites running you may still consider doing the steps manually or adopting the script if necessary. The script is written not to overwrite any existing Folders, WebSites, etc. but you never know.
So here is my final script:
The script can also be downloaded here.
The process of setting up HTTP connectivity is the same for Analysis Services Multidimensional and Tabular so the script works for both scenarios, just change the server name accordingly.