Problems connecting to SSAS 2005 from OWC Pivot table on a Client

[Amadeus Consulting]

We are currently trying to connect to a cube on our SSAS 2005 server and we
are running into problem getting the connection to work properly on the
client. We have gone through the steps of setting up a virtual directory
that contains the msmdpump.dll and .ini file and pointing it to the correct
instance of SSAS, then setting the datasource to
http://<servername>/<virtualdir>/msmdpump.dll as described in the Microsoft
article found here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx.

When I test the OWC on the server that hosts SSAS everything works fine and
behaves as expected. When I test on the client I can’t seem to even connect
to the server at all. I’m trying to determine what might be causing this
problem. I have tried using Integrated Security on the virtual dir that
contains the msmdpump.dll and logging in as the same user that I’m logged in
under on the server, but still experience the same problem. I’ve been
perusing the OLAP groups on google and noticed a lot of people are talking
about have OLEDB 9 installed on the client, so I installed the SQL 2005
client tools on the machine, but still no luck. A few people have been
talking about WinInet versus WinHttp as a potential problem, but I'm not
really sure if this is related.

Is there something that I’m missing in trying to get this to work? Is there
a workaround available if this is a bug?

 

[Wei-Dong XU [MS]]

Hi Steve,

Thanks for posting at the newsgroup!

From my understanding, you are facing the pivottable connection error to
SSAS 2005.

Currently as I know, the pivottable component cannot work very well with
SSAS2005. And so far without inside debugging or troubleshooting, it is
very hard for us to provide some assistance for you. So I have two
suggestions for you:
1. please use other method to retrieve the SSAS 2005 data, in ADOMD.net or
using reporting service; then we replace the pivottable with other methods.

2. I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Please feel free to let me know if you have any further question on this
issue.

Best Regards,
Wei-Dong XU
Microsoft Support

 

[Antoine Prevot]

Hi Steve,

Are you just trying to connect a PivotTable to your SSAS2005 cube ? Just
some steps :

1) on the client, use the SQL2005 setup to install connectivity components &
SQLXML client features (client tools section), it should be enough. Verify
that you are using OWC11, 'cause OWC10 (XP) won't work.

2) SQL Server configuration manager :

be sure that this different protocols are enabled

* SQL Server 2005 Network Configuration
Shared Memory
TCP/IP
Named Pipes (if you are using it)
* SQL Native Client Configuration
Shared Memory (order 1)
TCP/IP (order 2)
Named Pipes (order 3)

3) SQL Server 2005 Surface Area Configuration - Surface Area Configuration
for Services and Connections :

be sure that remote connections are allowed :

MSSQLSERVER -> Database Engine -> Remote Connections
select "Local and remote connections" (choose TCP/IP, named pipes or
both of em)

MSSQLSERVER -> Analysis Services -> Remote Connections
select "Local and remote connections"

4) check the PivotTable connection string
it should be something like :
Provider=MSOLAP.3;Data Source=YOUR_SERVER_NAME;Initial Catalog=[YOUR CUBE
PROJECT];

be sure that the provider used is MSOLAP.3 instead of MSOLAP.2 (old version).

5) test your connectivity with a "simple" application :

ie use Excel (2003) to connect a pivot table, there's a wizzard integrated,
it'll probably throw more explicit messages on the reasons why you can't
access your server.

Data -> PivotTable and PivotChart report ...

6) post more details if you still can't connect to SSAS.

There's a bunch of reasons to fail ... if your server is a domain
controller, if your client is on another domain / AD branch, if the sever is
behind a firewall, if god is against you ... don't loose hope

____________
hope this helps, and please, excuse my poor english !

 

[bren]

I'm dont' know why Wei-Dong XU of Microsoft is saying the the
PivotTable component won't work well with SSAS 2005. I'm currently
developing a WinForm app that has the component on a form and is
displaying data from an SSAS 2005 cube.

I have it running on a client, accessing data from a server over the
internet using a VPN connection, all in the same Windows Domain.

I hope that Microsoft is not saying that I should stop developing with
this configuration. It seems to be working.

I'm just having trouble learning how to code against the object model.
For me, it's complex, and seems to fold into itself.

But getting the connection to work was not too difficult. The steps
that Tonio gave you should work.

Bren