We have produced some Excel files using Xmla Pivot Tables against Iccube schemas.
Everything is working well except the fact that we noticed :
Each Pivot Table on a single file (even single worksheet tab) triggers a connection to the server. Thus, when opening (and refreshing) a file with 5 Pivot tables, we are asked 5 times to enter credentials for the same connection.
Is there a way to avoid that ? (Of course, for security reasons, we cannot store the password in the connection string, just would like to register one time per connection needed)
I have to produce an excel file with more than 100 Pivots and cannot imagine entering 100 times a password when opening it.
As you can see on following picture, I have 3 opened sessions : 1 (green) for html session and 2 (red) because I opened an excel file with 2 Pivot Tables.
Even with a single connection shared by all the pivot table this seems to be the default behavior of Excel. One possible solution would be to write a macro to request the user credentials and somehow edit the connection on the fly with this information.
Hope that helps.