Publishing OLAP based Pivotables from Excel

Hi

I want to publish an OLAP based pivottables from Excel to a HTML file using Office web componets.

I have a server with SQL Analysis Services 2005 cubes and I can browse the cube from my desktop in Excel 2003 Pivottables.

When I publish the Pivottable with Pivottable interactivity I get an error in the browser:

The query could not be processed

- An error was encountered in the transport layer

- The peer prematurely closed the connection

In the Pivottable I have set 'Save password' in properties.

Any suggestions ?

Thomas Black

I'm having the same error msg and problem to access SSAS 2005 cube over http at client machine.

This doesn't happen if you work in server (with Sql Server 2005 and web server installed).

|||Hello

did you manage to get around this error? I get this when I try to display a pivottable for data on a remote (but in LAN) server, connect goes through ...

Thanks for any help

Regards
Klaus Wiesel
|||

I used this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=38be67a5-2056-46a1-84b1-337ffb549c5c&DisplayLang=en

That enables Pivottables and charts in Sharepoint. Very easy to use. Useful for designing dashboards.

Regard

Thomas

|||

I also encountered this problem ,need any solutions!

thanks a lot!

|||

Hello

I solved this for me.

The following is done by my app:

1) Connect to the server to access the existing AS databases and their cubes

2) make a connectionstring that is later on passed to the pivottable control (named opData here):

.opData.ConnectionString = sConnString
.opData.DataMember = .coCUBE_NAME
When building the sConnString you have to use a named user (in the format domain\username) with his password to be able to display the data of the remote machine

Regards

Klaus

Publishing OLAP based Pivotables from Excel

Hi

I want to publish an OLAP based pivottables from Excel to a HTML file using Office web componets.

I have a server with SQL Analysis Services 2005 cubes and I can browse the cube from my desktop in Excel 2003 Pivottables.

When I publish the Pivottable with Pivottable interactivity I get an error in the browser:

The query could not be processed

- An error was encountered in the transport layer

- The peer prematurely closed the connection

In the Pivottable I have set 'Save password' in properties.

Any suggestions ?

Thomas Black

I'm having the same error msg and problem to access SSAS 2005 cube over http at client machine.

This doesn't happen if you work in server (with Sql Server 2005 and web server installed).

|||Hello

did you manage to get around this error? I get this when I try to display a pivottable for data on a remote (but in LAN) server, connect goes through ...

Thanks for any help

Regards
Klaus Wiesel
|||

I used this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=38be67a5-2056-46a1-84b1-337ffb549c5c&DisplayLang=en

That enables Pivottables and charts in Sharepoint. Very easy to use. Useful for designing dashboards.

Regard

Thomas

|||

I also encountered this problem ,need any solutions!

thanks a lot!

|||

Hello

I solved this for me.

The following is done by my app:

1) Connect to the server to access the existing AS databases and their cubes

2) make a connectionstring that is later on passed to the pivottable control (named opData here):

.opData.ConnectionString = sConnString
.opData.DataMember = .coCUBE_NAME
When building the sConnString you have to use a named user (in the format domain\username) with his password to be able to display the data of the remote machine

Regards

Klaus

Publish the cubes on the web portal

Hi all,
Is there the ability of publishing the cubes (which is built from an Analysis Services Project) on the web portal?
The purpose of this action is to let the BI users to do the analysis directly on the web portal.
The BI users can change the measure, slide and dice data, change the dimension (just like what you've done with the Analysis Services).

One of the options:

You can enable HTTP access to Analysis Services 2005 and let you users to access Analysis Services directly.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

PTS 9 redistributable location in RTM release?

Hi,

For some reason, I am unable to find the PTS installer for Analysis Services 9.0 OLE DB Provider in the RTM release.

Where can I find this? I need to provide this driver to my Excel users.

When I try to use the pts9 driver I have got from Sept CTP, I get this error when trying to refresh the Excel cube:

Errors n Network layers. Error Code 0xc10D00008 External Code 0x0000000

Thanks,

Philippe

PTS is no longer included with the release - you need to get it here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

|||

Look here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

PTS9 is one of the new "feature" packs posted a few weeks ago.

_-_-_ Dave

|||Is there any limitations with PTS with regards to displaying members of a parent while in a page field?|||

I know there are a couple of limitations in SQL2000.

I did not try with SQL2005 however, watch for things like no more than something like 14000 members or so will be displayed in a page field.

This is annoying because if your list is let say 15000, you will just not see the last 1000 members.

I am sure there are also others limitations like the number of members that can be displayed in the Row area and the numbers of dimensions you can show in the row area.

It would be nice to have someone from Microsoft letting us know what are the exact limitatons when using PTS9 and Excel 2003. It would also be nice to have a list of features available in a local pivot table and not available in a PTS version of a pivot.

It would also be nice to know what are the limitations of the "Offline cube" wizard. I was never able to get anything useful out of this wizard nor able to script it. It is not letting you define complex offline cubes.

Philippe

|||

Let's not confuse PTS with Excel Pivot Tables - they're not the same thing! To quote from the link given earlier in this thread:

PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.

But to get to the point, I'm not sure if the size limitations of Excel pivot tables are documented anywhere but the limitations of local cubes are (up to a point). In SQL2000 Books Online the topic "local cubes, compatibility issues" gives a good overview; I can't find an equivalent in SQL2005 BOL but although AS2005 local cubes have more functionality, there are still restrictions such as no support for partitioning, pro-active caching, stored procedures.

HTH,

Chris

PTS 9 redistributable location in RTM release?

Hi,

For some reason, I am unable to find the PTS installer for Analysis Services 9.0 OLE DB Provider in the RTM release.

Where can I find this? I need to provide this driver to my Excel users.

When I try to use the pts9 driver I have got from Sept CTP, I get this error when trying to refresh the Excel cube:

Errors n Network layers. Error Code 0xc10D00008 External Code 0x0000000

Thanks,

Philippe

PTS is no longer included with the release - you need to get it here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

|||

Look here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

PTS9 is one of the new "feature" packs posted a few weeks ago.

_-_-_ Dave

|||Is there any limitations with PTS with regards to displaying members of a parent while in a page field?|||

I know there are a couple of limitations in SQL2000.

I did not try with SQL2005 however, watch for things like no more than something like 14000 members or so will be displayed in a page field.

This is annoying because if your list is let say 15000, you will just not see the last 1000 members.

I am sure there are also others limitations like the number of members that can be displayed in the Row area and the numbers of dimensions you can show in the row area.

It would be nice to have someone from Microsoft letting us know what are the exact limitatons when using PTS9 and Excel 2003. It would also be nice to have a list of features available in a local pivot table and not available in a PTS version of a pivot.

It would also be nice to know what are the limitations of the "Offline cube" wizard. I was never able to get anything useful out of this wizard nor able to script it. It is not letting you define complex offline cubes.

Philippe

|||

Let's not confuse PTS with Excel Pivot Tables - they're not the same thing! To quote from the link given earlier in this thread:

PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.

But to get to the point, I'm not sure if the size limitations of Excel pivot tables are documented anywhere but the limitations of local cubes are (up to a point). In SQL2000 Books Online the topic "local cubes, compatibility issues" gives a good overview; I can't find an equivalent in SQL2005 BOL but although AS2005 local cubes have more functionality, there are still restrictions such as no support for partitioning, pro-active caching, stored procedures.

HTH,

Chris