I’ve been diving deep into how long Analysis Services can take to open a connection for a few months now and thanks to some tips from many people (Alberto Ferrari, Greg Galloway, Darren Gospel to name a few) here are some ideas to improve performance.
1. Make sure your network is not slow
OK, sounds kinda obvious, but this was killing me for months.
Most of me problems ended up being driver related with various incompatibilities between our NICs, Hyper-V and some of the new features.
A simple internet speed test or benchmarking tool would have shown that the VMs running SSAS were running very slow with packet loss. I blogged more details here
http://blogs.prodata.ie/post/Solved-slow-SSAS-connections-on-Hyper-V.aspx
2. Avoid Named Instances
It can take up to double the time to open a connection to a named instance. As a test I opened 1,000 connections to a remote server with five scenarios:
- By Name (Default Instance)
- By IP (Default Instance)
- By Server Name and Instance Name (Named Instance)
- By Name and Port number (Named Instance)
- By IP and Port number (Named Instance)
The results show that if we are using a named instance (e.g. bob-pc\tabular) then connections can be almost double the time to make. Once we manually specify the port number and bypass the “browser” service, connection time is about the same for the other test cases.
There was a very small improvement for using the IP instead of the name, but this was so small I’m not sure it would be worth the effort (2.5%)
lesson learned: Use fixed port numbers when using SSAS instances. If you do need named instances and want to simplify connections it should be possible to give each instance the same default port number (2383) and bind a different IP address to each instance. We’ve been using this trick on SQL DBEngine clusters for years to simplify connections and manageability.
2. Optimising Local Connections
If the client and SSAS are running on the same server (e.g. development laptop). You can optimise the connection even further by using the IP address and/or avoiding using the alias “localhost”
Ok these are small numbers, but its a 45% gain to be made on connection time 
one eye opener or me is there was a measurable difference to show that using “localhost” was not the best option for me.
3. IP4 or IP6 ?
I ran a quick test comparing use of IP4 to IP6 for connecting to an SSAS Server. This did show a 30% improvement with the older IP4 (I have no idea why this would be so!)
4. Do I care about connection time ?
Many tools like Excel either keep a connection open to SSAS or re-use a single connection, so connection time might not be that important.
Other tools may open a connection per request or in some cases (reporting services) many many connections per user – In this case the impact of faster connections may be more noticeable.
