Using Excel to connect to Analysis Services
A customer mailed me with a problem last week – they had some Excel workbooks set up ( normal Excel not Power Pivot ), which sourced data from AS cubes, and some users could not get the workbook to refresh the data.
In this situation many people quickly point the finger to the poor Analysis Services guys , however there are some things that you can check on the Excel side. Remember that when you create the connection in the Excel wizard, it will generate a connection string similar to what you would create in Visual Studio ( For example , when you are connecting from Reporting Services to a Cube ).
So my first port of call would be to check the connection string being generated in a situation like this. To do this , go to >
1) Data ( on the ribbon )
2) Then Connections
3) Select the connection that you are using and click on Properties
4) Click on the definition tab
In the “connection string” box you will see the connection string. It is here that you can play with various options. In the case of my customer , I had suspected that it was a security issue preventing the data refresh , so I added the line “Persist Security Info=True;” to the connection string and that did the trick. It should have been there by default though.
I’ve had some customers remove the “MDX Compatibility=1” and “MDX Missing Mode=Error” and it helped fix problems , but remember it is there for a reason. You can also try other tricks like increasing the time out and packet size here ,”Command Timeout=180″ or however many seconds you wish to specify, although once again do so with caution.