I recently faced this error while connecting to Microsoft SQL Server 2005 Analysis services from Microsoft
Excel 2007. Inspite of the several solutions given on various sites none of them seemed to work.
 
The solution was simple, you need to install Microsoft OLE DB Driver 9.0 for SQL 2005, old 8.0 drivers
doesnt work.
 
To install the driver go to this page :
 
and choose the appropriate download. Note that the download needs to be installed for every client access machine.