June 14, 2009 at 8:57 am
Hi All
I am trying to use Excel 2007 to connect to my 2008 Analysis Server (64 bit SQL 2008 on Small Business Server 2008).
From Excel, I go:
Data --> From Other Sources --> From Analysis Services
Then I get an error message: Unable to connect to data source. Reason: Unable to locate database server.
Should this functionality work out of the box? Or do I need to install an add-in or something?
Any help would be most appreciated.
thanks
Kevin
June 14, 2009 at 9:14 am
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
You need to go the above directory in your box to find the x86 ODBC driver to configure your connection to Excel because there is no x64 driver for Office so you must use x86 driver to configure your connection. In the ODBC look for the ACE driver and in there you should be able to configure connection to Excel 2007. If you don't have Office 2007 running then you may need to download the ACE driver.
Kind regards,
Gift Peddie
June 14, 2009 at 6:27 pm
Hi Gift
Thanks for your prompt reply.
I have been able to use the ACE driver to allow me to use Excel as a datasource to populate my table.
However, what I am trying to do is populate an Excel spreadsheet by connecting to an Analysis Services cube. I am trying to prepare a report for a bunch of users who know how to use Excel, and they will want to query the cube for their answers.
(Or am I missing a point in how to use your suggestion?)
regards
Kevin
June 14, 2009 at 8:32 pm
I now understand what you are doing so in the same window go to the create a data connection wizard and click on other/advanced it will take you to the data link property in there look for the OLEDB provider for Analysis Services 10 and configure your connection with it.
Kind regards,
Gift Peddie
June 15, 2009 at 2:16 am
Hi Gift
Thanks for your help. I had been doing what you were suggesting and it just wasn't working for me. Have had to do a fresh install, and now it all works 🙂
A few hours of my life I will never recover 🙁
Appreciate your assistance.
regards
Kevin
January 25, 2010 at 11:49 am
Hi, Can anyone help me here? I made a connection using the report builder 2.0 for my analysis services in sql server 2008 enterprise edition and it works just fine! but when I try to do the same on Excel 2007, I have an error : "Unable to connect to data source. Reason: Unable to locate database server."
Thanks in advance
Cafc
January 25, 2010 at 12:01 pm
Are you doing just a vanilla report or a SharePoint Dashboard?
Generally, MSFT recommends that you use Excel Services on SP. (That is THE reason for MOSS 2007...)
January 25, 2010 at 12:12 pm
cedric.capela (1/25/2010)
Hi, Can anyone help me here? I made a connection using the report builder 2.0 for my analysis services in sql server 2008 enterprise edition and it works just fine! but when I try to do the same on Excel 2007, I have an error : "Unable to connect to data source. Reason: Unable to locate database server."Thanks in advance
Cafc
When last I checked Excel 2007 as analysis service 2008 datasource works but I think there are known issues I need to look for thread and post again.
Kind regards,
Gift Peddie
January 25, 2010 at 12:15 pm
Revenant (1/25/2010)
Are you doing just a vanilla report or a SharePoint Dashboard?Generally, MSFT recommends that you use Excel Services on SP. (That is THE reason for MOSS 2007...)
The question is actually not related to MOSS 2007 Report Builder 2.0 is SQL Server 2008 end user reporting tool.
Kind regards,
Gift Peddie
January 25, 2010 at 12:17 pm
thank you for your quick answer! But i am not using de MOSS 2007!!
I am just trying to use Excel 2007 to connect to my 2008 Analysis Server (Servre 2008).
From Excel, I go:
Data --> From Other Sources --> From Analysis Services
Then I get an error message: Unable to connect to data source. Reason: Unable to locate database server.
Its just that!!
I really don't understand at all!!
ps: the user account has access to the Analysis Services cube. and as said i made a connection with report builder 2.0 and just it works fine!!
thanks again
January 25, 2010 at 12:50 pm
cedric.capela (1/25/2010)
thank you for your quick answer! But i am not using de MOSS 2007!!I am just trying to use Excel 2007 to connect to my 2008 Analysis Server (Servre 2008).
From Excel, I go:
Data --> From Other Sources --> From Analysis Services
Then I get an error message: Unable to connect to data source. Reason: Unable to locate database server.
Its just that!!
I really don't understand at all!!
ps: the user account has access to the Analysis Services cube. and as said i made a connection with report builder 2.0 and just it works fine!!
thanks again
Here are two articles from Microsoft the first is about connection which is your issue. The second is development related so I think you should try these and post back.
http://support.microsoft.com/kb/940167
Kind regards,
Gift Peddie
January 26, 2010 at 4:43 am
hi, again!
the solution for the problem is here:
necessary packages:
- SQLSERVER2008_ASOLEDB10.msi
- msxml6_x86.msi
Thank you very much for help.
September 16, 2013 at 6:35 pm
I found this problem on a client machine that had a mixed history of components being installed and removed, where they had previously been able to connect Excel to a SQL Server 2012 SP1 SSAS instance, but couldn't create a new connection to the cube. Strangely, the user was able to re-use an "existing connection" but couldn't create a new one.
The fix:
(ps Thanks to previous posters for the info.)
This is an update to this for 2012 SP1:
(
Install MSXML 6.0:
http://www.microsoft.com/en-us/download/details.aspx?id=3988
)
AND
(
Install the SQL Server 2012 SP1 feature pack:
http://www.microsoft.com/en-us/download/details.aspx?id=35580
Or
Just install the MSOLAP.5 provider
)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply