Using MSFT Query and Excel 2003, I can connect to my OLAP cube in An. Serv. 2005. However, when I save the worksheet and then reopen it and try to refresh the data (right click the worksheet and select, Refresh Data), I get the first error message: "An error was encountered in the transport layer". And the second error message: "Errors in the OLE DB provider. An error occured while loading the connection dialog box component for prompting".
I can't find any related articles to fix this problem and get this running.
Thanks for any help.
-lance
Hi Lance. First thing I think you should determine is where the problem lies. Is is a problem with MSFT Query, security access, or with the OLAP Cube (or database) in AS 2005. A quick way to do this is download and install the Cube Analysis add in for Excel as a quick test. See if you can access the database/cube with the Cube Analysis add-in. The results will probably point you where to focus your attention. You can download the Cube Analysis Add-in from here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en
PGoldy
|||Thanks for your help Paul.
Clearly, I'm no expert with connecting Excel to Analysis Services.
I downloaded the add-in you suggested. Now when I try to create a new connection using this add in (Cube Analysis > Manage Connection > New Connection), and add the same Analysis Server info I added when connecting via the method (Data > Pivot Table > External Data Source > Get Data > OLAP Cube > New Data Source), I get the error message:
"OLAP Server error on conecction 216.128.xxxxx"
"OLAP Server error: The peer prematurely closed the connection. An error was encountered in the transport layer."
Still not sure at what level this error is occuring, and how to get it resolved.
Thanks,
-lance
|||
Hi Lance:
Unfortunately this got uglier instead of better. Sorry about that. First - I've not seen the errors you're seeing so this is a bit of a fishing expedition. It seems like you have client side problems - what I'm not sure. Last easy thing to see if it's client or server side problem is using SQL Manager. Try SQL Manager on a couple of different machines and issue the simplest of MDX queries against the cube. Results should tell us if it's a cube problem, or client-side problem.
Good luck.
PGoldy
|||Hi Paul,
I think I've narrowed down the possibilites and believe it's a client-side config problem - I can connect and refresh repeatedly to both cubes with no problems (I just verified this a minute ago). My client machine is relatively clean (it's only a month or two old) so I haven't had much time to clutter it up with other software that may cause hard-to-diagnose conflicts.
Thanks for any further insights.
-lance
|||Hi Lance:
Here's a post which reported the same error message, and provides a solution.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236201&SiteID=1
However, it's a programmatic solution which controls the connection string to AS which directly may not be much help, but could point you in the right direction. You can control the connection string in Excel with the Cube Analysis add-in (I don't know about MSFT query). Perhaps you could look at your connection string and compare it with the details in the above link and determine what's different/not working for you.
You access the connection string used by the Cube Analysis add-in when you manage the connection. In Excel select Cube Analysis | Manage Connection. Highlight your existing connection and select Edit. Click Next to get past the server specification and you'll see the list of cubes exposed in your AS database. The cube you originally designated should be highlighted. In the lower left portion of the dialog is the "Advanced Provider String..." button. Click the "Advanced Provider String..." button and it exposes the connection string as used to access AS. You can hand edit the string and see if your changes make a difference in your connection.
Good luck - PGoldy
|||Hi,
I get exact the same error. The excel pivot table has worked for weeks, and over night without change of anything, I got the same error. I tried the report some times an had always this error. At midday, we have a lower network load because of lunch, all works fine with the excel pivot table. So, in my opinion its a problem of a timeout. Does anyone know, where and what I could change to correct this problem?
Thanks
Hans
Hi Hans:
You can manage the timeout with the connection string in the Excel add-in for Cube Analysis. In Excel select Cube Analysis | Manage Connection and you see the "Manage Connection" dialog. Highlight your connection and click Edit on the bottom of the dialog. Click Next to get by the "Define Connection - Choose Server " dialog and you see the "Define Connection - Choose Data" dialog. Click the Advanced Provider String... button on the bottom of the dialog and you can see the connection string. The default timeout value for the connection string is 30s. (Connect Timeout=30) You can adjust the timeout value to fit your environment.
Good luck.
PGoldy
|||Hi Paul,
Is the connection string of CubeAnalysis and of the "normal" Pivotable of Excel the same connection string? Is this connection string saved in the Excel Sheet for one time? I changed the connection string in Cube Analysis from Database to HTTP (http://myOlapServer/olap/msmdpump.dll:80) and all worked fine in Cube Analysis, but in the Pivot Table all errors are still the same. So it seems to me, that this are 2 independent connection strings. Am I wrong?
Thanks
Hans
Hi Hans:
I'm not 100% positive, but somewhat certain that the pivot table connection string and the cube analysis connection strings are stored separately and not shared. The cube analysis connection information is stored in an XML file on your local machine. It is not stored as part of the workbook. The XML file has extension .ODC.
Good luck.
PaulG
|||Thanks for all your help. I fixed the problem, by going through every option in the Add Cube process. What finally solved it was, clicking the Options tab in step 3 and checking the box for Refresh on Connection and Save username and password.
thanks again
No comments:
Post a Comment